How to access a MySQL database from PHP using PDO_MYSQL

PDO_MYSQL is a driver that allows us to establish a connection from PHP to a MySQL database. 

In this article, we will build an example step by step that will show how to connect a MySQL database from PHP using PDO_MYSQL.

In the example, we will cover the following points:

• Establish a connection to a MySQL database
• Run a query to retrieve the rows of a table
• Show the query results

First, let’s create a database in MySQL with an Orders table that has the following structure and data:

CREATE DATABASE IF NOT EXISTS `mydatabase` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; USE `mydatabase`; CREATE TABLE `orders` (   `id` int(11) NOT NULL,   `client_id` int(11) NOT NULL,   `received` datetime NOT NULL,   `processed` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,   `total` float NOT NULL,   `paid` float DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `orders` (`id`, `client_id`, `received`, `processed`, `total`, `paid`) VALUES (1, 1, '2019-05-19 13:18:13', '2019-05-20 09:11:57', 100, 75), (2, 1, '2019-05-19 13:21:18', '2019-05-20 15:32:14', 50.2, 20.5), (3, 2, '2019-05-20 14:19:18', '2019-05-20 21:27:30', 75, 60), (4, 2, '2019-05-20 15:47:15', '2019-05-21 08:23:28', 90.7, 20), (5, 3, '2019-05-21 08:23:15', '2019-05-21 10:33:24', 120, 100), (6, 1, '2019-01-22 05:20:08', '2019-02-25 12:10:19', 500, NULL), (7, 1, '2019-05-23 13:18:13', '2019-05-24 09:11:57', 150, 150), (8, 3, '2019-05-24 11:33:28', '2019-05-25 12:16:12', 200, 50), (9, 2, '2019-05-25 14:14:21', '2019-05-26 06:33:19', 250, 250), (10, 3, '2019-05-26 11:12:12', '2019-05-26 17:13:13', 300, 0); ALTER TABLE `orders` ADD PRIMARY KEY (`id`);

Script 1. Creation of database to use in the example

Establish a connection to a MySQL database with PDO_MYSQL

Opening a connection with PDO_MYSQL is really simple. We only have to use the connection parameters that we normally use in the following structure:

<?php /*host and database name*/ $pdo_parameters = 'mysql:host=localhost;dbname=mydatabase'; /*name of database user*/ $database_user = 'root'; /*user database password*/ $user_password = 'password'; /*connection command*/ $pdo_connection = new PDO($pdo_parameters, $database_user, $user_password); ?>

Script 2. Opening connection to the database with PDO_MYSQL

Run a query to retrieve the rows of a table

To execute a query, we first build a text string that contains the command, then we can execute it with the “query” function.

$sql = 'SELECT id, processed, total, paid FROM orders ORDER BY id';  $pdo_connection->query($sql);

Script 3. Executing a query

Show the query results 

To show the results, we are going to use a table. We will fill the rows of the table through a loop.

<table> <tr> <th><strong>id</strong></th> <th><strong>total</strong></th> <th><strong>paid</strong></th> </tr> <?php foreach ($pdo_connection->query($sql) as $row) { ?> <tr> <td><?php print $row['id'] ?></td> <td><?php print $row['total'] ?></td> <td><?php print $row['paid'] ?></td> </tr> <?php } ?> </table>

Script 4. Showing query results in a table

Finally, the complete code of our example should look like this:

<?php $pdo_parameters = 'mysql:host=localhost;dbname=mydatabase'; $database_user = 'root'; $user_password = '311377'; $pdo_connection = new PDO($pdo_parameters, $database_user, $user_password); $sql = 'SELECT id, processed, total, paid FROM orders ORDER BY id'; ?> <table>     <tr>     <th><strong>id</strong></th>     <th><strong>total</strong></th>     <th><strong>paid</strong></th> </tr> <?php     foreach ($pdo_connection->query($sql) as $row) { ?>     <tr>     <td><?php print $row['id'] ?></td>     <td><?php print $row['total'] ?></td>     <td><?php print $row['paid'] ?></td>     </tr> <?php } ?> </table>

Script 5. Complete example code

The prepared PDO state does not give the same result as the manual query

I'm using PHP and PDO prepared statements to access a database. I have this prepared query which I want to execute (multiple times through a foreach-loop, but I don't see that affecting this really):

insert into forum_access (forum_id, user_id) select * from (select ?, ?)
 as tmp where not exists (select * from forum_access
 where forum_id = ? and user_id = ?) limit 1

Then I'm using PDO execute with variables in an array to execute this statement, like this:

$values = Array(2, 1, 2, 1); // Normally it's variables here
$stmt->execute($values);

This executes, but the strange thing is that it inserts a row with values (2, 2) into forum_access. The really strange thing is that when I run the SQL query with variables manually inserted, like this:

insert into forum_access (forum_id, user_id) select * from (select 2, 1)
 as tmp where not exists (select * from forum_access
 where forum_id = 2 and user_id = 1) limit 1

it correctly inserts a row with values (2, 1).

I expect this to have something to do with the way that PDO/MySQL treats prepared statements. I'm more or less a novice at prepared statements and have no idea what is going wrong here. Hopefully someone else can shed some light on this.

Notes: I have reasons to use the quite complicated insert... select query instead of insert... on duplicate key. Maybe not perfect reasons, but good enough to not be interested in suggestions to fundamentally change the query.

Using PHP 5.3 and MySQL 5.0 on WAMP server.

---

I sort of solved the problem myself. It seems MySQL gets confused by the insert values from the select statement and mixes these up. I tried to name these values, like this:

insert into forum_access (forum_id, user_id) select * from
 (select ? as forum_id, ? as user_id) as tmp
 where not exists (select * from forum_access
 where forum_id = ? and user_id = ?) limit 1

and lo and behold, this actually works.

I'm still not certain why it should work. But my immediate concern is getting a working app, so for the moment I'm content.

Thanks for all your help and sorry to bother with something that I could and did solve myself.

Try inserting a php object into the DateTime mysql object & ldquo; ad_endDate & rdquo;

 So I looked up PDO Insert statements. Still kind of confusing. so now my code reads:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>test14.php</title>
</head>
<?php
$servername = "server";
$username = "username";
$password = "password";
$dbname   = "dbname";

 //Setup for endDate value
date_default_timezone_set("America/Denver");
$startDate = new DateTime('12:00:00');

  echo "<p>Today is: {$startDate->format( 'l Y-m-d g:i:s' )}</p>";

  // N gives a numeric day to the days of the week
  // 2 means Tuesday
  while ( $startDate->format( 'N' ) != 2 )

  $startDate->modify( '+1 days' );

  echo "<p>Start date is: {$startDate->format('l Y-m-d g:i:s')}</p>";

  //Set endDate at Tuesday
  $endDate = $startDate ->modify("+2 Weeks");
  echo "<p>End date is: {$endDate->format('l Y/m/d g:i:s')}</p>";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
     // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = $conn->prepare("INSERT INTO wp_awpcp_ads (ad_enddate)
VALUES (:end_date)
");
  $sql->bindParam(':end_date',strtotime($endDate), PDO::PARAM_STR);
        // use exec() because no results are returned
    $conn->exec($sql);
    echo "New record created successfully";

    }
catch(PDOException $e)
    {
    echo $sql . "<br>" . $e->getMessage();
    }

$conn = null;
?>
<body>
</body>
</html>

I'm getting 2 warnings:

Warning: strtotime() expects parameter 1 to be string, object given in [site_url]/test14.php on line 40

Warning: PDO::exec() expects parameter 1 to be string, object given in [site_url]/test14.php on line 42

New record created successfully

and I'm still getting all zeros in my table. Does it matter if it's a wordpress table? I'm not having any issues with the now() functions just DateTime()functions. Is there another way to setup a date like this?

---

I have been trying to find a way to add a custom end date value to a mysql database field with the type "DateTime"

Specifically, the customer wants all ads to be "ended" on a tuesday at noon following 2 weeks of activity. so far I've been helped at phphelp forums and have this snippet:

    <?php
    $servername = "server";
    $username = "username";
    $password = "password";
    $dbname = "dbname";

//Setup for endDate value
date_default_timezone_set("America/Denver");
$startDate = new DateTime('12:00:00');

  echo "<p>Today is: {$startDate->format( 'l Y-m-d H:i:s' )}</p>";

  // N gives a numeric day to the days of the week
  // 2 means Tuesday
  while ( $startDate->format( 'N' ) != 2 )

$startDate->modify( '+1 days' );

  echo "<p>Start date is: {$startDate->format('l Y-m-d H:i:s')}</p>";

  //Set endDate at Tuesday
  $endDate = $startDate ->modify("+2 Weeks");
  echo "<p>End date is: {$endDate->format('l Y/m/d H:i:s')}</p>";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
     // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "INSERT INTO wp_awpcp_ads
    (
    ad_contact_name,
    ad_contact_email,
    ad_contact_phone,
    ad_title,
    ad_category_id,
    ad_item_price,
    ad_details,
    disabled,
    disabled_date,
    ad_postdate,
    ad_startdate,
    ad_last_updated,
    ad_enddate
  )
VALUES
  (
  '$_POST[name]',
  '$_POST[email]',
  '$_POST[phone]',
  '$_POST[title]',
  '$_POST[category]',
  '$_POST[price]',
  '$_POST[details]',
  1,
  now(),
  now(),
  now(),
  now(),
  $endDate->format('Y-m-d H:i:s'))";
        // use exec() because no results are returned
    $conn->exec($sql);
    echo "New record created successfully";
    }
catch(PDOException $e)
    {
    echo $sql . "<br>" . $e->getMessage();
    }

$conn = null;

This works fine except my ad_enddate row shows 0000-00-00 00:00:00 and i'm at wit's end to solve this issue. I've changed my code to msqli to pdo, and nothing. I'm completely new to php and need this by last week.

---

this enter link description here can help You with date problem, I think it's due to $end_date format.

And this is great answer about SQL Injections in PHP :) enter link description here

PDO / MySQL rowCount does not return as expected

Post-answer edit: I think this was a bug in my own code -- I don't know what it was but I proceeded to fix it. See answer below.

I'm using MySQL/PHP to perform a series of INSERT ... ON DUPLICATE KEY UPDATE statements. The documentation I've read indicates that the row count for this will return:

-1 : an error
0 : update, no changes to row made (i.e. all values duplicated)
1 : row inserted
2 : update performed on row with duplicate key

However, I'm only seeing results of 0s where I should be seeing 2s (since I am watching the code update various database values.) Here is the code:

$stmt = $db->prepare('INSERT INTO sometable (`id`, `name`, `email`) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE `name` = ?, `email` = ? ;');

$stmt->execute( array ( $id, $name, $email, $name, $email ) );

$rc = $stmt->rowCount();
echo $rc;

$rc is always coming up 0 for updates (even when values were definitely changed) or 1 (for successful inserts, as expected.)

What am I missing? :)

---

Try using the MySQL function, if it returns the right result, the problem will be PDO:rowCount()

$stmt = $db->prepare('INSERT INTO table (`id`, `name`, `email`) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE `name` = ?, `email` = ? ;');

$stmt->execute( array ( $id, $name, $email, $name, $email ) );

$rc = $db->query("SELECT ROW_COUNT()")->fetchColumn();
echo $rc;