SELECT prepared statement - php MYSQL

I have managed to get inserting records using a prepared statement to work but I am struggling to use the SELECT if I am not looking for specific records i.e. I just want to show a list of all the names and email addresses from the database.

$stmt = $link->prepare("SELECT email, firstName FROM `users`");
	$stmt->bind_param('ss', $email, $firstName);
	$stmt->execute();
	$stmt->bind_result($email, $firstName);
	while($stmt->fetch()) {
		
		echo $row["firstName"];
	}

Open in new window

LVL 1
Black SulfurAsked:
Who is Participating?
 
zephyr_hex (Megan)Connect With a Mentor DeveloperCommented:
Ah, ok.  Not sure what version of PHP you're on, but perhaps it's before fetchAll().

Try this:

$stmt = $link->prepare("SELECT email, firstName FROM `users`");
$stmt->execute();
$result = $stmt->get_result();
while ($data = $result->fetch_assoc())
{
    var_dump($data);
}

Open in new window

0
 
zephyr_hex (Megan)DeveloperCommented:
I wouldn't expect parameter binding with the list.  Have you tried removing lines 2 and 4?
0
 
Black SulfurAuthor Commented:
I tried it now and I get an error:

Undefined variable: row
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
zephyr_hex (Megan)DeveloperCommented:
Try the following

$stmt = $link->prepare("SELECT email, firstName FROM `users`");
$stmt->execute();
$result = $stmt->fetchAll();

//for testing
var_dump($result);

foreach($result as $row) {
     echo $row["firstName"];
}

Open in new window

0
 
Black SulfurAuthor Commented:
I just get a blank screen.
0
 
zephyr_hex (Megan)DeveloperCommented:
Then enable error reporting and see what the error is.  At a minimum, var_dump should return something.  Since it's not, it tells me you have an error in your code.
0
 
Black SulfurAuthor Commented:
Sorry, here it is:

Fatal error: Uncaught Error: Call to undefined method mysqli_stmt::fetchAll()
0
 
Black SulfurAuthor Commented:
I am using PHP 7 as far as I know. Will try that now.
0
 
Black SulfurAuthor Commented:
Cool, that works, thanks! I think I asked this here before but my head is spinning at the moment! Since I am using a prepared statement there is no need for me to use real_escape_string in the query at all?
0
 
Ray PaseurCommented:
The basics of several PHP / MySQL processes are shown in this article.
https://www.experts-exchange.com/articles/11177/PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

There is no need to escape anything that is not injected as a variable  into a query string.  A query with no injected variables will not have SQL injection or uncaught syntax errors.

You may find that the object-oriented version of MySQLi is easier to work with.  Maybe try a test script each way and see which version seems more fluent as you read the code.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.