Solved

SELECT prepared statement - php MYSQL

Posted on 2016-08-29
10
58 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 42

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
 

Author Comment

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

Undefined variable: row
0
 
LVL 42

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

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

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
 

Author Comment

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

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

Accepted Solution

by:
zephyr_hex (Megan) earned 500 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
 

Author Comment

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

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 109

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article discusses four methods for overlaying images in a container on a web page
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

773 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