Help converting mysqli to pdo

[peterb]

Hello, I must have over slept as I just noticed that switching to a new host with Php 8.1 started a nightmare.

From some of the reading that I have been doing, I created the following function to connect to my database. This part actually works.

	function db_connect() {
		// PDO is the acronym of PHP Data Objects
		global $link;
		
		//	Create a connection to the database using PDO MySQLi
			$db_server  = 'localhost';
			$db_user	= '';
			$db_pass	= '';
			$db_name 	= '';
			$charset	= 'utf8';
		
		$options = [
			PDO::ATTR_EMULATE_PREPARES => true,
			PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
			PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
			PDO::ATTR_CASE => PDO::CASE_NATURAL
		];
		
		try {
			$link = new PDO("mysql:host=$db_server;dbname=$db_name;charset=$charset", $db_user, $db_pass, $options);
			echo "Connected successfully"."<br>";
		} catch(PDOException $e) {
			echo "Connection failed: ".$e->getMessage()."<br>";
		}
	}

Although I tried to change the following code, I keep seeing errors and though it best to ask what is really needed to make this code PDO compatible:

		$result3 = mysqli_query($link,"select *, count($table_name_2.cid) as number_photos 
			from $table_name_2
			left join $table_name_1 on ($table_name_2.cid = $table_name_1.catid) 
			where $table_name_1.catid != 0 and $table_name_2.published != 0
			group by $table_name_2.cid
			order by $table_name_2.ordering ASC")
			or die('Query failed: ' . mysqli_error($link) );
		
		$id=0; $catid = 0;
		$CatalogPhotoCount = array();
		while($row3 = mysqli_fetch_array($result3)) {
			$id++;
			$catid = $row3['cid'];
			// create new array and populate
			$CatalogPhotoCount[] = array('category' => $catid, 'number_photos' => $row3['number_photos'] );
			//echo "# ".$id."- category ".$row3['cid']." has ". $row3['number_photos']." photos " ."<br>";
		}
		
		$numofqueries2 = mysqli_query($link,"select count(*) as count from $table_name_2");
		$rowcnt2 = mysqli_fetch_array($numofqueries2);	
		echo 'Total number of categories: '.$rowcnt2["count"]; echo "<br>";
		
		mysqli_free_result($result3);
		mysqli_close($link);

I look forward to your answers so that I can change the rest of my code.
Thanks in advance.

[droopsnoot]

What errors do you see? Of course you know that part of the issue here is that you’ve converted the connection to PDO, but continue to use all the mysqli_ functions against the connection object, which won’t work.

Can you update the second code block to show the code you tried after converting it all to use PDO?

Personally, I really dislike global variables, so I’d change your database connection function to return the connection object rather than relying on the calling code using the same variable name, but it’s only a minor thing compared to getting the rest working properly.

[peterb]

Hello droopsnoot,

I made the following changes to my code shown with commented mysqli lines commented:

		//$result3 = mysqli_query($link,"select *, count($table_name_2.cid) as number_photos 
		$result3 = $link->query("select *, count($table_name_2.cid) as number_photos 
			from $table_name_2
			left join $table_name_1 on ($table_name_2.cid = $table_name_1.catid) 
			where $table_name_1.catid != 0 and $table_name_2.published != 0
			group by $table_name_2.cid
			order by $table_name_2.ordering ASC")
			or die('Query failed: ' . mysqli_error($link) );
		
		$id=0; $catid = 0;
		$CatalogPhotoCount = array();
		
		//while($row3 = mysqli_fetch_array($result3)) 
		while($row3 = $result3->fetch())
		{
			$id++;
			$catid = $row3['cid'];
			// create new array and populate
			$CatalogPhotoCount[] = array('category' => $catid, 'number_photos' => $row3['number_photos'] );
			//echo "# ".$id."- category ".$row3['cid']." has ". $row3['number_photos']." photos " ."<br>";
		}
		
		$numofqueries2 = $link->query("select count(*) as count from $table_name_2");
		$numofqueries2->fetchAll(PDO::FETCH_ASSOC);
		//$rowcnt2 = mysqli_fetch_array($numofqueries2);
		$rowcnt2 = $numofqueries2->fetch($numofqueries2);	
		echo 'Total number of categories: '.$rowcnt2["count"]; echo "<br>";

and I am receiving the following errors, most are the result of prior errors:
Warning: PDOStatement::fetch() expects parameter 1 to be int, object given
Trying to access array offset on value of type bool
mysqli_get_server_info() expects parameter 1 to be mysqli,

[m_hutley]

@peterb

$rowcnt2 = $numofqueries2->fetch($numofqueries2);

You shouldnt have $numofqueries2 inside the fetch there.

Also, you’ve already fetchAll’d the query, so there would be nothing left for it to fetch…

[peterb]

Hello m_hutley,

I commented the line

//$numofqueries2->fetchAll(PDO::FETCH_ASSOC);
Based on examples I read, is fetchAll(PDO::FETCH_ASSOC) supposed to have PDO::FETCH_ASSOC in the brackets or not?

but I am still receiving:

PDOStatement::fetch() expects parameter 1 to be int, object given in C:\wamp\www\test\dbc.php on line 199
which is

$rowcnt2 = $numofqueries2->fetch($numofqueries2);

Okay, I removed $numofqueries2 from the above and the query works.
I followed a few examples on the net for the above.

[m_hutley]

@peterb

Based on examples I read, is fetchAll(PDO::FETCH_ASSOC) supposed to have PDO::FETCH_ASSOC in the brackets or not?

If you dont put PDO::FETCH_ASSOC (or another mode value), it will default to PDO::FETCH_BOTH, and return both numeric and associative indexes. So for a query like ‘SELECT name,date,city’ , FETCH_BOTH will return an array that has:

[0 => "John",
 "name" => "John",
 1 => "10/12/2024",
 "date" => "10/12/2024",
 2 => "Chicago",
 "city" => "Chicago"]

Which is just generally redundant if you’re only going to use one or the other to reference the array, and makes the fetch microscopically longer and bigger which may be an issue in performance based systems. (also, if you’re walking the array with a foreach, suddenly you get every value twice)

[mabismad]

For the connection code -

In the connection function, as already posted, return the connection from the function.

You should name the connection variable $pdo, so that you can see or search to find which code has and has not been converted.

Set emulated prepared queries to false. i.e. you want to use real prepared queries whenever possible.

Because you are setting the default fetch mode to assoc, you don’t need to specify it in each fetch statements, simplifying the code.

Only catch and handle database exceptions in your code for USER RECOVERABLE errors, such as when inserting/updating duplicate user submitted data. Simply let php catch and handle all other database errors, where php will use its error related settings to control what happens with the actual error information via an uncaught exception error (database statement errors will ‘automatically’ get displayed/logged the same as php errors.) Short-version, remove the exception try/catch logic for the connection code.

For the query code -

You need to list out the columns you are selecting and only select what you are using. Once you do this, you can use the ->fetchAll() method to fetch all the data at once, no loop needed.

If you alias the cid column as category in the query, the fetched data will already be in the correct format for storing into $CatalogPhotoCount.

Since you are now using exceptions for database statement errors, remove any or die() error handling, since it won’t ever get executed upon an error/exception.

For the select count(*) … query, it will produce a single row with a single value. Just use the ->fetchColumn() method to fetch it.

You should build any sql query statement in a php variable, such as $sql. This makes debugging easier, since you can echo the query, and it also separates the sql query syntax as much as possible from the php syntax, helping to eliminate typo mistakes.

The only variable you should uniquely name is the final variable holding the result from a query. Use and reuse simple, general variable names for things like the PDOStatement objects, e.g. $stmt

[m_hutley]

@mabismad

The only variable you should uniquely name is the final variable holding the result from a query. Use and reuse simple, general variable names for things like the PDOStatement objects, e.g. $stmt

I feel like this is a “please make yourself predictable and more hackable” statement. If I have to guess at variable names because i managed to upload a PHP file that includes your database script, $stmt, $pdo, $db are all gonna live at the top of the list…

[peterb]

Hello guys,
I appreciate the help and suggestions. Although using similar variables may be helpful for others to understand the code easier, I tend to change variables to make them less predictable.
As the code is working now, I will make changes to the rest of my code using the info and test results.
Many thanks from an old timer!

[SamA74]

Another thing to note:-

@peterb

or die('Query failed: ' . mysqli_error($link) );

You still have this mysqli funtion in there.
You probably don’t need that bit of error handling since you have set error mode to exceptions, which should be handled at some point.

[peterb]

Thanks.

[peterb]

Well it looks like I’m back. I received the following error after making changes to other code.

I received this error:

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ORDER BY id ASC’ at line 1 in C:\wamp\www\my-image-gallery-4\imagegallery.p.php on line 393

from the first line of this code:

		$numofqueries1 = $link->prepare("select count(*) as count from $table_name_1 WHERE catid=$cid ORDER BY id ASC");
		$numofqueries1->execute();
		//$rowcnt1 = mysqli_fetch_array($numofqueries1);
		$rowcnt1 = $numofqueries1->fetch(PDO::FETCH_ASSOC);

By the way, I changed the method of closing the db as follows:

		$result2->closeCursor();	// free_result
		$result3->closeCursor();	// free_result
		$link = null;				// close PDO connection

I haven't seen any other way of doing that.
Once again thanks for looking at this.

[mabismad]

That’s not how to perform a prepared query. The main point of using a prepared query is that you are separating the parsing (and planning of the query execution) from the evaluation of the data values, so that nothing about a value can break the sql query syntax, which is how sql injection is accomplished. You should be putting a prepared query place-holder ? in the sql query statement for each value, then supply the actual data value(s) as an array to the ->execute([…]) call.

If you had done the things already suggested, including this -

@mabismad

You should build any sql query statement in a php variable, such as $sql. This makes debugging easier, since you can echo the query, and it also separates the sql query syntax as much as possible from the php syntax, helping to eliminate typo mistakes.

You would be able to echo the sql query statement to see what it is, and probably determine what is wrong with it. Most likely, the $cid variable is either empty (in which case you have a problem leading up to this query since you should never get to the point of preparing and execution this query if a required input is not valid) or it’s a sting, in which case using a prepared query correctly will solve the problem.

You also missed the following points that simplify the code, making it easier to convert old code -

@mabismad

Because you are setting the default fetch mode to assoc, you don’t need to specify it in each fetch statement, simplifying the code.

@mabismad

For the select count(*) … query, it will produce a single row with a single value. Just use the ->fetchColumn() method to fetch it.

@peterb

I changed the method of closing the db as follows:

In most cases, you don’t need to close prepared query statements or database connections since php does this for you when your script ends, simplifying the code.

[peterb]

Okay, I will try to adapt the changes to a different style and thinking.
Thanks