Show Row

[ryan1234]

At the beginning of my code I select the row where column id="a certain value"

 

$id1 = $_GET['id'];
$sql = "SELECT * FROM person WHERE = $id1";
$query = $handle->prepare($sql);
$query->execute();
$results = $query -> fetchAll(PDO::FETCH_ASSOC);

 

I then try and display the results

 

<?php echo $results['name'];
?>

 

However I keep getting this error:

 

Notice:Undefined index: name ..............

 

Any ideas?

 

Thanks.

[Andy123]

I assume that you do have a "name" column in your table. Perhaps your query is not returning any results and you are trying to retrieve a value that does not exist. You should check if your query was executed successfully before trying to retrieve values. I was just about to say that your query is so simple so it is probably not a syntax error, but then I just looked at it again. You are missing a column name in your WHERE clause.

 

$sql = "SELECT * FROM person WHERE id = $id1";

 

If you use the errorInfo function, it should tell you this as well.

Edited December 9, 2012 by Andy123

[ryan1234]

I've added what you said and still getting the same error. Not too sure how to use the errorInfo function (bit of a novice sorry).

[Andy123]

I just assumed that you have an "id" column in your table. You can use the error function like this:

 

$query = $handle->prepare($sql);
$results = $query -> fetchAll(PDO::FETCH_ASSOC);
die('Error info: ' . print_r($handle->errorInfo()));

 

Please post what it says.

Edited December 9, 2012 by Andy123

[ryan1234]

Thanks.

 

Says this:

 

Array ( [0] => 00000 [1] => [2] => ) Error info: 1

Edited December 9, 2012 by PFMaBiSmAd
removed micro-font format

[PFMaBiSmAd]

The ->fetchall() method returns the rows in an array. $results[0] is the 1st row (if any.) $results[1] is the 2nd row (if any.) If you have a column `name` you would reference the first row's value using -$results[0]['name']

 

The ->fetchAall() method is intend to be iterated over using php array functions. If you expect at most one row, use the ->fetch() method.

[ryan1234]

Great that worked. Thanks.

[PFMaBiSmAd]

A word about using prepared query statements. You prepare the query without any external data in it, using place-holders where the data actually goes, Then you either bind a variable with the data, bind the literal data value, or supply the data when the ->execute() method is called.

 

By putting the variable holding the external data into the query statement in the ->prepare() method, that would prepare any injected sql as well.

[Andy123]

The ->fetchall() method returns the rows in an array. $results[0] is the 1st row (if any.) $results[1] is the 2nd row (if any.) If you have a column `name` you would reference the first row's value using -$results[0]['name']

 

Makes perfect sense. I never worked with these methods because I am using a framework, so my advice was a bit off.

 

A word about using prepared query statements. You prepare the query without any external data in it, using place-holders where the data actually goes, Then you either bind a variable with the data, bind the literal data value, or supply the data when the ->execute() method is called.

 

By putting the variable holding the external data into the query statement in the ->prepare() method, that would prepare any injected sql as well.

 

I was thinking about this as well. I just want to add another point about prepared statements. Another benefit is a performance gain that is particularly noticeable for queries that are executed frequently. By using prepared statements, the database management system can reuse the execution plan for subsequent calls that have different parameter values. This is demonstrated in the figure below.

 

Source: MSDN

 

In Microsoft SQL Server (I haven't looked at it for MySQL), the execution plan is cached, and execution plans from prepared statements and stored procedures have a higher priority in the cache than those of "ad hoc" queries and are therefore less likely to be flushed - or it will happen less frequently. Perhaps it works slightly differently in MySQL, but I can't imagine that it is much different.

 

Just in case anyone is interested.