MySQL to MySQLi

PHP
1

Hi everyone… I am a PHP newbie. Sorry if this is such an elementary question. I am trying to rewrite/update a gallery code using mysqli instead of mysql. I tried to follow an online tutorial. I am getting several errors so how do I rewrite the following bit of code using mysqli? Can someone help me please?


$result =
mysql_query
("SELECT
c.category_name
,c.category_image
,COUNT(p.photo_caption)
FROM
gallery_category as c
LEFT JOIN
gallery_photos as p
ON
p.category_name = c.category_name
GROUP BY
c.category_name");

while ($row = mysql_fetch_array($result)) {
$row[0] = trim($row[0]);
$row[0] = str_replace(" ", "-", $row[0]);
$row[0] = str_replace("'", "%27", $row[0]);

Thanks for any help…

2

For the most part MySQLi is pretty much the same with the exception of some functions which may have changed names completely or were removed, for what you have above simply changing mysql to mysqli should suffice.

3

If you still get errors, come back with the code and the error messages.

4

This is what I thought at first too then I got all the error :slight_smile: Thank you for helping me Chris.

5

For the most part MySQLi is pretty much the same with the exception of some functions which may have changed names completely or were removed, for what you have above simply changing mysql to mysqli should suffice.

Thank you very much guys… When I change MySQL to MySQLi I get ,

Warning: mysqli_query() expects at least 2 parameters, 1 given in C:\\wamp\\www\\viewgallery.php on line 52

and

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, null given in C:\\wamp\\www\\viewgallery.php on line 54

. Line 52 to 54 from the above code is

GROUP BY
c.category_name");
while ($row = mysqli_fetch_array($result)) {
6

it may be also noted that many mysqli functions require the connection object as first parameter.

7

By simply appending the i after mysql DOES NOT work. mysql_result has been removed completely and will not work on the new MySQLi library.

For OOP

<?php
$mysqli = new mysqli('localhost', 'username', 'password', 'database');
if($mysqli->connect_errno) {
	echo "Please fix database connection";
	exit;
}

$result = $mysqli->query("SELECT c.category_name,c.category_image,COUNT(p.photo_caption) FROM gallery_category as c LEFT JOIN gallery_photos as p ON p.category_name = c.category_name GROUP BY c.category_name");

while ($row = $result->fetch_array(MYSQLI_BOTH)) {
	$row[0] = trim($row[0]);
	$row[0] = str_replace(" ", "-", $row[0]);
	$row[0] = str_replace("'", "%27", $row[0]);
	echo $row[0 . "<br />"];
}
?>

For procedural

<?php
$mysqli = new mysqli('localhost', 'username', 'password', 'database');
if(mysqli_connect_errno()) {
	echo "Please fix database connection";
	exit;
}

$result = mysqli_query($mysqli, "SELECT c.category_name,c.category_image,COUNT(p.photo_caption) FROM gallery_category as c LEFT JOIN gallery_photos as p ON p.category_name = c.category_name GROUP BY c.category_name");

while ($row = mysqli_fetch_array($result)) {
	$row[0] = trim($row[0]);
	$row[0] = str_replace(" ", "-", $row[0]);
	$row[0] = str_replace("'", "%27", $row[0]);
	echo $row[0] . "<br />";
}
?>
8

Dormilich, your answer lead me to the answer. Thank you so much. I was not adding the database connection that was my mistake.

9

therockers…wow, thank you for taking the time to write all the code up. Thank you, you guys are awesome.

10

I find it easier to use the object-oriented interface than using the wrapper functions. I find it much easier to follow.

also note that you can finally make MySQLi automatically throw exceptions when it encounters an error.

11

I need to really focus on learn further Dormilich. I am a child in the world of code. The rockers showed that

 <?php
$mysqli = new mysqli('localhost', 'username', 'password', 'database');
if($mysqli->connect_errno) {
    echo "Please fix database connection";
    exit;
}

$result = $mysqli->query("SELECT c.category_name,c.category_image,COUNT(p.photo_caption) FROM gallery_category as c LEFT JOIN gallery_photos as p ON p.category_name = c.category_name GROUP BY c.category_name");

while ($row = $result->fetch_array(MYSQLI_BOTH)) {
    $row[0] = trim($row[0]);
    $row[0] = str_replace(" ", "-", $row[0]);
    $row[0] = str_replace("'", "%27", $row[0]);
    echo $row[0 . "<br />"];
}
?>

It all made sense till

MYSQLI_BOTH

then it lost me…
You said

What does this mean? Can you give me an example?
Thanks for being patient with me…

12

see http://php.net/manual/en/mysqli-result.fetch-array.php

I’ll give you an example in PDO, since I hardly use MySQLi.


try {
    // create connection ($dsn holds the connection details)
    $pdo = new PDO($dsn, $login, $pass);
    // set error handling
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // query the db
    $stmt = $pdo->query($sql_without_user_data);
    // tell PDO to only fetch the first item of each row
    $stmt->setFetchMode(PDO::FETCH_COLUMN, 0);
    // fetch data
    foreach ($stmt as $item) {
        echo $item, '<br>';
    }
}
// if there is an error, notify the user
catch (PDOException $exc) {
    error_log($exc->getMessage());
    echo 'Oops, something went wrong!';
}
// properly close your HTML page
13

I will certainly try to understand this. Looks like a whole new language to me right now. I am sure I will understand it after I dissect each word of your answer :slight_smile: ( may be not) …thank you for taking your time for me.

14

Hi Everyone, I got past most of the errors. But I am stuck at mysql_result as there are no equivalent for it in mysqli. Can anyone help me? This is the code I am getting the errors at

// Figure out the total number of results in DB:  
$total_results = mysql_query("SELECT COUNT(*) FROM gallery_photos WHERE category_name='" . addslashes($cname) . "'");
if (!$total_results) {
die('Could not query:' . mysql_error());
}
$total_results = mysql_result($total_results, 0);

The error that I am getting is

Warning: mysql_result() expects parameter 1 to be resource, object given in C:\wamp\www\viewgallery.php on line 182

What do I change this to, to make it work?

15

it’s more simple than you might think.


$row = $mysqli_result->fetch_row();
$the_count = $row[0];

admittedly, it’s a bit easier in PDO (see above).

16

Dormilich, thanks you for helping me still…But I am ashamed to admit that I do not know how to use your code to make my code work. May be it is very simple but my knowledge is very basic. I tried but it didn’t work.

17

I second this too, OOP is professional coding standard and you want to stick to it for good programming practices too. No matter you like it or not, the cold hard truth is that if you are not comfortable with OOP, you wont be able to find a decent job in this industry.

18

You should also do prepares now instead of queries.

Something like this.

<?php
$mysqli = new mysqli('localhost', 'username', 'password', 'database');
if($mysqli->connect_errno) {
    echo "Please fix database connection";
    exit;
}

$result = $mysqli->prepare("SELECT c.category_name,c.category_image,COUNT(p.photo_caption) FROM gallery_category as c LEFT JOIN gallery_photos as p ON p.category_name = c.category_name GROUP BY c.category_name");
$result->execute();
$result->bind_result($c_category_name, $c_category_image, $p_photo_caption);

while($result->fetch()) {
	echo $c_category_name . "<br />";
	echo $c_category_image . "<br />";
	echo $p_photo_caption;
}
?>

You might want to reconfigure it to fit your codes.

19

You should read a bit on mysqli on the official manual and check the examples of mysqli_query: http://php.net/manual/en/mysqli.query.php
You could also check:
http://php.net/manual/en/mysqli-result.fetch-array.php
The first example in the comments is looping through the results.

Also, I wrote a tutorial about how to do a simple SELECT with mysqli, you might want to check it there:
http://www.mogosselin.com/mysqli-select-prepared-statements/

20

which doesn’t really help when there are no parameters to use …