Displaying results of a prepared statement

[Guest]

I am trying to use prepared statements using mysqli.

My connection and sql all work fine as if I bind the results to variables I can display the variables in the page.

However I am trying to understand and learn and I am not grasping how to display my results using echo $row['col1'];

echo $row['col2];

This is my query below, I use $row = $stmt->fetch(); after the query then in the display part of the page try to echo $row['col1']; but it does not display anything. I know there is a result, as I have displayed it by using $stmt->bind_result($col1, $col2, etc....);

Please could anyone enlighten me as to where I am going wrong / what I am not understanding about displaying the column without binding the result.

There is only ONE full result to display in this instance.

$sql = 'SELECT table1.col1, table1.col2, table1.col3, table1.col4, table2.column1, table2.column2, table2.column3, table3.c1, table3.c2, table3,c3 FROM table1, table2, table3 WHERE table1.col1 = ? AND table1.col2=table2.column1 AND table1.col3=table3.c1';

$stmt = $conn->stmt_init();

if ($stmt->prepare($sql)) {

$stmt->bind_param('i', $_GET['the IdNumber']);

$stmt->execute();

$stmt->store_result();

$numRows = $stmt->num_rows;

$row = $stmt->fetch();

} else {

echo $stmt->error;

}

Any help is much appreciated, thank you in advance.

[bregent]

Sorry, I'm not familiar with mysqli. I  know you said you are getting results from the query, but your querystring variable contains a space in the name:

$stmt->bind_param('i', $_GET['the IdNumber']);

[Guest]

Sorry to confuse this is not the issue as I just put in general terms for colums and ids to make it easier to understand and I have mistyped a space.

My issue I think is with

$row = $stmt->fetch();

and echo $row['col1']; etc...

if I bind the result they display if I use these last 2 lines I get nothing.

Thank you for you response though, this could easily have been the answer.

[osgood_]

tessimon wrote:

Sorry to confuse this is not the issue as I just put in general terms for colums and ids to make it easier to understand and I have mistyped a space.

My issue I think is with

$row = $stmt->fetch();

and echo $row['col1']; etc...

if I bind the result they display if I use these last 2 lines I get nothing.

Thank you for you response though, this could easily have been the answer.

Did you solve the issue as I'm having exactly the same problem as you. I cannot find a way to display the results by echoing them out to the page.........I'm begining to think can it even be done using prepared statements as I've seen no examples on the web which explain the procedure.

I can do it using real_escape_string but that is really messy if you have a lots of $_POSTS or $_GETS and I'm not even sure real_escape_string fully protects against injections.

[osgood_]

Aftert much searching and trawling through a load of trash I final found an answer which was plain to follow.

<?php

$id = $_GET['id'];

$stmt = $conn->prepare("SELECT productId, productName, productDate, productQuantity FROM products WHERE id=?");

$stmt->bind_param('i',$id);

$stmt->bind_result($productId, $productName, $productDate, $productQuantity);

$stmt->execute();

$stmt->fetch();

?>

Then you can echo out the database column anywhere on the page.

<h1><?php echo $productName;?></h1>

I dont know why productID becomes a variable - $productID and so on, which need to be in the bind_results function. These thing work in mysteroius ways.

Ok now this opens up another question after looking at your code how do you do it when selecting from more than one table???

Does it become:

$stmt->bind_result($table1.col1, table2.column1);

or what?

I can tell you one thing unless you get the bind_result in the correct order you'll be ****** over a barrel backwards because nothing will show on the page.

In a sql query it didnt matter what order they came in so I can see the prepared statement thing is going to be a bit of a nightmare for complex queries.

Good luck.