Using php and access database

Access MySQL from PHP

In this tutorial, we are going to access the MySQL database from PHP. Accessing MySQL from PHP is very simple and easy to implement. PHP contains MySQLi class functions to connect and access the database. Some of those are used in this article.

• prepare() – To prepare query statement object
• fetch_row() – To read row data as an associative array. There are many MySQL fetch functions in PHP to read table rows.
• execute() – executes queries with the reference of the statement object created.

This article contains a short example of accessing databases using PHP and MySQL. The upcoming sections explain the example code to guide beginners.

Example files and database script

This example uses the database table named animals. It performs the create, read, update and delete (CRUD) functionalities with the database.

File Structure

This is the example code file structure. It contains the files in the root to perform read-write operations. The landing page contains a navigation link to go to the list page.

Database script

Import this database script into the development environment. It contains the CREATE statement of the target table on which the CRUD is performed.

Then configure the database details at the top of each file in the example code.

CREATE TABLE `animals` ( `id` int(11) NOT NULL, `animal_name` varchar(255) NOT NULL, `animal_color` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- Indexes for dumped tables -- -- -- Indexes for table `animals` -- ALTER TABLE `animals` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `animals` -- ALTER TABLE `animals` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; 

Create and Configure Database

Open PHPMyAdmin or any other database client to create the database. Select the newly created database and create the table on which the CRUD is going to be performed.

After creating the database and tables connect this from PHP by setting the configuration. The following code is used to connect MySQL from PHP. It requires hostname, database name, database username, database password.

This example code uses the procedural way of connecting the MySQL database.

Create New Record

The code shows the INSERT query to add a new row to the database table animals by mapping values to its columns animal_name and animal_color.

Since the column values are string data, those are enclosed with single quotes. The prepare() function creates the SQL statement object to bind the params. Then, the code executes the query with the reference of this object.

prepare("INSERT INTO animals (animal_name, animal_color) VALUES (?, ?)"); $sqlStatement->bind_param("ss", $animalName, $animalColor); if ($sqlStatement->execute()) < $message = "Added Successfully."; >else < $message = "Problem in Adding New Record."; >$sqlStatement->close(); $conn->close(); print $message; ?> 

Read Data from Table

The SELECT query is used to read database table rows. The following code shows how to read all rows from the “animals” table. I used the fetch_assoc() function to get the associative array column fields and values for each row.

The code will return a list of animal names and it’s color in an associative array.

After MySQL fetch, it iterates the result using PHP foreach() and prints the data into the browser.

query($sqlStatement); $conn->close(); if ($result->num_rows > 0) < while ($row = $result->fetch_assoc()) < $tblEntries[] = $row; >> foreach ($tblEntries as $record) < ?>Animal Name:  
Animal Color:  
 
 ?> 

This code will display the result in the browser as like as below.

We can read filtered data by using the WHERE clause. The following code executes the SELECT query with a prepared statement. It reads the animals in ‘grey’ color from the database.

bind_param("s", 'Grey'); $result = $conn->query($sqlStatement); $conn->close(); ?> 

Update Table Row

The UPDATE query is used to change the value of the column by setting a new value. It uses the WHERE clause to apply the condition for updating the row data.

The following code is used to change the animal_name as ‘Rat‘ for the rows which has the value Grey in the animal_color.

prepare("UPDATE animals SET animal_color=? WHERE animal_name=?"); $sqlStatement->bind_param("ss", $newColor, $animalName); if ($sqlStatement->execute()) < $message = "Edited Successfully."; >else < $message = "Problem in Edit."; >$sqlStatement->close(); $conn->close(); print $message; ?> 

Deleting the selected row

The DELETE query shown below is to delete all the rows containing animal_color as ‘grey’

prepare("DELETE FROM animals WHERE animal_color = ?"); $sqlStatement->bind_param("s", $animalColor); if ($sqlStatement->execute()) < $message = "Delete Successfully."; >else < $message = "Problem in Delete."; >$sqlStatement->close(); $conn->close(); print $message; ?> 

Источник

PHP Connect to MySQL

PHP 5 and later can work with a MySQL database using:

• MySQLi extension (the «i» stands for improved)
• PDO (PHP Data Objects)

Earlier versions of PHP used the MySQL extension. However, this extension was deprecated in 2012.

Should I Use MySQLi or PDO?

If you need a short answer, it would be «Whatever you like».

Both MySQLi and PDO have their advantages:

PDO will work on 12 different database systems, whereas MySQLi will only work with MySQL databases.

So, if you have to switch your project to use another database, PDO makes the process easy. You only have to change the connection string and a few queries. With MySQLi, you will need to rewrite the entire code — queries included.

Both are object-oriented, but MySQLi also offers a procedural API.

Both support Prepared Statements. Prepared Statements protect from SQL injection, and are very important for web application security.

MySQL Examples in Both MySQLi and PDO Syntax

In this, and in the following chapters we demonstrate three ways of working with PHP and MySQL:

MySQLi Installation

For Linux and Windows: The MySQLi extension is automatically installed in most cases, when php5 mysql package is installed.

PDO Installation

Open a Connection to MySQL

Before we can access data in the MySQL database, we need to be able to connect to the server:

Example (MySQLi Object-Oriented)

$servername = «localhost»;
$username = «username»;
$password = «password»;

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) die(«Connection failed: » . $conn->connect_error);
>
echo «Connected successfully»;
?>

Note on the object-oriented example above:

$connect_error was broken until PHP 5.2.9 and 5.3.0. If you need to ensure compatibility with PHP versions prior to 5.2.9 and 5.3.0, use the following code instead:

// Check connection
if (mysqli_connect_error()) die(«Database connection failed: » . mysqli_connect_error());
>

Example (MySQLi Procedural)

$servername = «localhost»;
$username = «username»;
$password = «password»;

// Create connection
$conn = mysqli_connect($servername, $username, $password);

// Check connection
if (!$conn) die(«Connection failed: » . mysqli_connect_error());
>
echo «Connected successfully»;
?>

Example (PDO)

$servername = «localhost»;
$username = «username»;
$password = «password»;

try $conn = new PDO(«mysql:host=$servername;dbname=myDB», $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo «Connected successfully»;
> catch(PDOException $e) echo «Connection failed: » . $e->getMessage();
>
?>

Note: In the PDO example above we have also specified a database (myDB). PDO require a valid database to connect to. If no database is specified, an exception is thrown.

Tip: A great benefit of PDO is that it has an exception class to handle any problems that may occur in our database queries. If an exception is thrown within the try < >block, the script stops executing and flows directly to the first catch() < >block.

Close the Connection

The connection will be closed automatically when the script ends. To close the connection before, use the following:

Источник

Microsoft Access Database using PHP PDO ODBC Example

In this tutorial we will see an example of PHP code on how to connect to MS Access Database using PDO library.

'; print_r(PDO::getAvailableDrivers()); echo '

'; ?>

Before we proceed make sure the PDO library is enabled. Add the above code into php file and run it in the browser. It will tell you which libraries are currently active

If it is not active, edit the PHP.ini file and locate the PDO line for ODBC

Complete PHP PDO code to query adventure works MS access database

 echo "
Testing Access PDO"; $content=access_result_pdo_x($db_param,$sql); print_r($content); exit; // Connection to ms access $db = new PDO("odbc:Driver=;Dbq=".$db.";Uid=; Pwd=;"); $db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); if ($db) echo "
PDO connection success\n"; else echo "
pdo connection failed\n"; try< $result = $db->query($sql); $row = $result->fetchAll(PDO::FETCH_ASSOC); print_r($row); >catch(PDOExepction $e)< echo $e->getMessage(); > function access_result_pdo_x($db_param,$sql) < try < echo "Calling access_result_pdo_x
"; if(!file_exists($db_param["name"])) < throw new RuntimeException('Access Database file path is incorrect or file does not exist.'); >try < $cnnt_str="odbc:Driver=;Dbq=".$db_param["name"].";Uid=; Pwd=;"; $db = new PDO($cnnt_str); echo $cnnt_str; $db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); > catch (exception $e) < print_r($e,true); $content["error_flag"]="true"; $content["error_mesg"]="access connection error ". $e->getMessage() ; $content["legends"]=array("Error"); $content["data"]=array(0); return $content; > //run the query try < $stmt=$db->query($sql); $stmt->setFetchMode(PDO::FETCH_ASSOC); > catch (exception $e) < $content["error_flag"]="true"; $err=$db->errorInfo(); $content["error_mesg"]="access query error ".$e->getMessage(); $content["legends"]=array("Error"); $content["data"]=array(0); return $content; > // Loop to handle all results $row_id=0; $column_names=array(); $column_types=array(); $col_data=array(); $row_limit=$db_param['row_limit']; if (!isset($row_limit) ) $row_limit=10; while( ($row = $stmt->fetch()) && $row_id <$row_limit) < //$row_data[$row_id]=array(); // each key and value can be examined individually as well $col_id=0; //clog(2,print_r($row,true)); foreach($row as $key =>$value) < if (is_int($key)) continue; // clog(2,"key = val = "); $col_data[$col_id][$row_id]=utf8_encode($value); if ($row_id==0) < //$col_meta=$stmt->getColumnMeta($col_id); //xlog(0,"mysql column type =".$col_meta["native_type"]); array_push($column_names,utf8_encode($key)); //just capture column names only once array_push($column_types,"VARCHAR"); > $col_id++; //array_push($row_data[$row_id],$value); // print "
Key: " . $key . " Value: " . $value; > $row_id++; > if ($col_id==0) < $column_names[]="No Data"; $column_types[]="VARCHAR"; $col_data[0][0]="no data"; >$content["legends"]=$column_names; $content["columnTypes"]=$column_types; $content["data"]=$col_data; return $content; > catch (exception $e) < $content["legends"]=array("Error"); $content["data"]=array(0); $content["error_flag"]="true"; $content["error_mesg"]="fetch error ".print_r($e,true); return $content; >> ?>

Источник