?
Solved

SELECT prepared statement - php MYSQL

Posted on 2016-08-29
10
Medium Priority
?
88 Views
Last Modified: 2016-08-29
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

0
Comment
Question by:Black Sulfur
  • 5
  • 4
10 Comments
 
LVL 44

Expert Comment

by:zephyr_hex (Megan)
ID: 41775196
I wouldn't expect parameter binding with the list.  Have you tried removing lines 2 and 4?
0
 
LVL 1

Author Comment

by:Black Sulfur
ID: 41775207
I tried it now and I get an error:

Undefined variable: row
0
 
LVL 44

Expert Comment

by:zephyr_hex (Megan)
ID: 41775222
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 1

Author Comment

by:Black Sulfur
ID: 41775236
I just get a blank screen.
0
 
LVL 44

Expert Comment

by:zephyr_hex (Megan)
ID: 41775267
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
 
LVL 1

Author Comment

by:Black Sulfur
ID: 41775279
Sorry, here it is:

Fatal error: Uncaught Error: Call to undefined method mysqli_stmt::fetchAll()
0
 
LVL 44

Accepted Solution

by:
zephyr_hex (Megan) earned 2000 total points
ID: 41775341
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
 
LVL 1

Author Comment

by:Black Sulfur
ID: 41775348
I am using PHP 7 as far as I know. Will try that now.
0
 
LVL 1

Author Comment

by:Black Sulfur
ID: 41775362
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 41775381
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month15 days, 23 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question