Solved

SELECT prepared statement - php MYSQL

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

Author Comment

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

Expert Comment

by:zephyr_hex
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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 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 108

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
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…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

743 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now