[RESOLVED] mysqli prepared statement help

[adrive]

is there any way to retrieve our records' individual field without using bind_result?? I miss the way mysqli->query used to do..we had the choice to use fetch_row, fetch_array etc..

i've searched high and low for any example..but i can't seem to find any.

usually for prepared statements you'll do something like this :

$stmt = $mysqli->prepare($sql);
$stmt->execute();
$stmt->bind_result($field1, $field2);

while($stmt->fetch()){
  echo $field1, $field2;
}

$stmt->close();

but is there any way to do it like this ?

$stmt = $mysqli->prepare($sql);

$result = $stmt->execute(); //maybe somehow, theres a function to return a set of records

while($row = $result->fetch_row()){
  echo $row[0], $row[1];
}

$stmt->close();

[laserlight]

Apparently not, though the PDO extension supports such a feature.

[NogDog]

Note, however, that you can use [man]mysqli_query/man and the resulting mysqli_result object to do the same thing, but you do lose the ability to bind input parameters, thus you would need to use [man]mysqli_real_escape_string/man for non-numeric input parameters.

[adrive]

thanks guys, i'll still need to use sql statements in a prepared manner though.

by the way, is there any way to change the way to bind parameters by index then?

etc :
$stmt->bind_param("s", $param1);

maybe like..

$stmt->bind_param(0, $param1, "s");
$stmt->bind_param(1, $param2, "s");
$stmt->bind_param(2, $param3, "i");

reason being is that if we had too much parameters at one go..wouldn't it look something like this ?

$stmt->bind_param("ssssssssssssdissssssssssssidddddd", $param1, $param2, ..........................);

[NogDog]

Actually, by using some newlines, you can use the one command, yet make it easy to read and still a lot less typing than a separate function call for each parameter. You can even add in some one-line comments to remind yourself what each is for:

$stmt->bind_param(
   "ssdss",
   $param1,  // a comment
   $param2,  // another comment
   $param3,  // etc.
   $param4,  // etc.
   $param5   // and so forth
);

[adrive]

well it looks better that way, but the parameter to declare field types can't though. I was just wondering if there're any alternatives?

lastly, do i need to call mysqli->stmt->free_result on delete/update/insert ?? or is it only applicable to select statements?

or maybe mysqli->stmt->close will do it automatically?

[NogDog]

adrive wrote:

well it looks better that way, but the parameter to declare field types can't though. I was just wondering if there're any alternatives?

If you really need to select that many fields at once, that is probably a rare occurrence. I won't say it will never happen, but how often do you really need more than a half dozen or so fields from a query? It is another reason to not just do a "SELECT " instead of providing an explicit field list: only retrieve the fields you will actually use. (It also avoids issues with the field order being changed in your table definition and then breaking your script if it does a "SELECT ".

lastly, do i need to call mysqli->stmt->free_result on delete/update/insert ?? or is it only applicable to select statements?

or maybe mysqli->stmt->close will do it automatically?

You only need a free_result if you did a store_result, which is really only applicable with select queries. I belive a close() would release it, too, but it's not explicitly stated as such in the manual.

[adrive]

thanks nogdog, you helped me 🙂

[NogDog]

You're quite welcome. It works both ways, too: the research I do to try to help others helps me learn more about PHP. 🙂

Don't forget to mark this thread resolved, if it is.