Help for writing code without mysql_result

[yathrakaaran]

Hi Everyone, Sorry I am a complete newbie and am trying to secure my code using MySQLi but I am stuck at mysql_result since there is no equivalent in mysqli. Can someone help me with my code please ?

$total_results = mysqli_query($mysqli,"SELECT COUNT(*) FROM gallery_photos WHERE category_name='" . addslashes($category_name) . "'");
if (!$total_results) {
die('Could not query:' . mysqli_error());
}
$total_results = mysql_result($total_results, 0);

When I use this I am getting an error

 

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

 

As a solution  I was told to use

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

But I do not know how to add this to my code to make it work. I am sorry if this is too elementary ...

[requinix]

But I do not know how to add this to my code to make it work.

Copy and paste? Make sure the variables are using the right names.

[fastsol]

I find it easier to add a AS to the COUNT and return it as a normal table column name.  Then you just use normal fetching on the query.

$total_results = mysqli_query($mysqli,"SELECT COUNT(*) AS `count` FROM gallery_photos WHERE category_name='" . addslashes($category_name) . "'");
if (!$total_results) {
die('Could not query:' . mysqli_error());
}
$row = mysqli_fetch_assoc($total_results);
$total_results = $row['count'];

[KevinM1]

You should use prepared statements, and fetch the results from that. Look at the procedural example in the docs: http://php.net/manual/en/mysqli.prepare.php

[Jacques1]

Actually, I wonder where you've dug out this addslashes() and mysql_result() stuff. We abandoned that somewhere in the early 2000s together with Netscape Navigator and Java applets.

 

So whatever book or online tutorial or whatever you're currently using: You'll need a new one which reflects today's technology, not the web as it was back in 2000. Quite a lot has changed since.

 

Switching to MySQLi is a good start. However, it's not enough to merely append an “i” to the function names and fix some mysql_result() calls. You'll need to unlearn a lot of old nonsense and learn more modern techniques.

• Printing MySQL error messages directly on the screen may have been acceptable back in the days of personal home pages, but nowadays, people don't want to be greeted with cryptic warnings about your database issues. What are they supposed to do with this information? Debugging is your job, not theirs. In fact, it might not be the best idea to inform the whole world about your server setup and your database structure.
• addslashes() was never an acceptable way of escaping values. What this incredibly stupid function does is look for some ASCII characters and blindly prepend an ASCII backslash. What if the input is not ASCII? Then you have a problem. Escaping always depends on the character encoding of the input, which is why PHP introduced the mysql_real_escape_string() function back in 2002. The MySQLi equivalent would be mysqli_real_escape_string(). But like KevinM1 already said, a much more secure alternative is to use prepared statements.

 

A sanitized version of the above code would looks something like this:

<?php

/*
 * Make MySQLi throw an exception in case on an error. This way we don't have
 * to manually check every single action.
 */
$mysqli_driver = new mysqli_driver();
$mysqli_driver->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT;

$database = new mysqli('YOUR_HOST', 'YOUR_USER', 'YOUR_PASSWORD', 'YOUR_DATABASE');

$photos_count_stmt = $database->prepare('
	SELECT
		COUNT(*)
	FROM
		gallery_photos
	WHERE
		category_name = ?
');
$photos_count_stmt->bind_param('s', $category_name);
$photos_count_stmt->execute();

$photos_count_stmt->bind_result($photos_count);
$photos_count_stmt->fetch();

// the count is now stored in $photos_acount

[yathrakaaran]

Copy and paste? Make sure the variables are using the right names.

requinix, unfortunately copy and pasting did not work for me. Inee to try again. I am a total newbie.

 

 

Actually, I wonder where you've dug out this addslashes() and mysql_result() stuff. We abandoned that somewhere in the early 2000s together with Netscape Navigator and Java applets.

 

So whatever book or online tutorial or whatever you're currently using: You'll need a new one which reflects today's technology, not the web as it was back in 2000. Quite a lot has changed since.

 

Switching to MySQLi is a good start. However, it's not enough to merely append an “i” to the function names and fix some mysql_result() calls. You'll need to unlearn a lot of old nonsense and learn more modern techniques.

• Printing MySQL error messages directly on the screen may have been acceptable back in the days of personal home pages, but nowadays, people don't want to be greeted with cryptic warnings about your database issues. What are they supposed to do with this information? Debugging is your job, not theirs. In fact, it might not be the best idea to inform the whole world about your server setup and your database structure.
• addslashes() was never an acceptable way of escaping values. What this incredibly stupid function does is look for some ASCII characters and blindly prepend an ASCII backslash. What if the input is not ASCII? Then you have a problem. Escaping always depends on the character encoding of the input, which is why PHP introduced the mysql_real_escape_string() function back in 2002. The MySQLi equivalent would be mysqli_real_escape_string(). But like KevinM1 already said, a much more secure alternative is to use prepared statements.

 

A sanitized version of the above code would looks something like this:

<?php

/*
 * Make MySQLi throw an exception in case on an error. This way we don't have
 * to manually check every single action.
 */
$mysqli_driver = new mysqli_driver();
$mysqli_driver->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT;

$database = new mysqli('YOUR_HOST', 'YOUR_USER', 'YOUR_PASSWORD', 'YOUR_DATABASE');

$photos_count_stmt = $database->prepare('
	SELECT
		COUNT(*)
	FROM
		gallery_photos
	WHERE
		category_name = ?
');
$photos_count_stmt->bind_param('s', $category_name);
$photos_count_stmt->execute();

$photos_count_stmt->bind_result($photos_count);
$photos_count_stmt->fetch();

// the count is now stored in $photos_acount

Jaques..you are so right about the code being from early 2000 It is.. it was from an old tutorial. I now know that I have to learn prepared statements. Thank you for your detailed explanations. Thank you for writing the code.Learning a lot from this single post.

 

You should use prepared statements, and fetch the results from that. Look at the procedural example in the docs: http://php.net/manual/en/mysqli.prepare.php

Thank you KevinM1..I will , thank you for the link.

 

 

I find it easier to add a AS to the COUNT and return it as a normal table column name.  Then you just use normal fetching on the query.

$total_results = mysqli_query($mysqli,"SELECT COUNT(*) AS `count` FROM gallery_photos WHERE category_name='" . addslashes($category_name) . "'");
if (!$total_results) {
die('Could not query:' . mysqli_error());
}
$row = mysqli_fetch_assoc($total_results);
$total_results = $row['count'];

Fastsol...thank you. Your code worked. I need to to see a working example to learn from it. I am not just copying an pasting I promise. Thank you all for answering, you guys know so much. Glad I posted the question here.

[maxxd]

To tell you the truth, if you're making the switch, I'd recommend PDO() over mysqli(). Despite the similarities in name, I've found it much easier to refactor old-school mysql_* code to PDO than mysqli(). And PDO is a bit more forward-looking as it's not specifically tied to one database type.

Edited August 23, 2014 by maxxd

[yathrakaaran]

Thank you everyone for helping me with this issue. All of you taught me about something. After my basics of PHP it sounds like I need to learn prepared statements...