[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SELECT prepared statement - php MYSQL

Posted on 2016-08-29
10
Medium Priority
?
82 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Technology Partners: 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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

656 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