Problem With Mysqli Prepare Statement

[cypher86]

hello,

it's been a while and yet here i am with my problems...

 

i decided to review some code and modify it using the prepare statement in order to offer a better security (at least on sql injection side).

 

here a code snippet:

$esecuzione=$db->prepare("............");
$esecuzione->bind_param('s',$id);
$esecuzione->execute();
$esecuzione->bind_result($creditore,$n_perizia,$data_incarico,$username,$indirizzo,$telefono,$giudice);
$esecuzione->fetch();
$esecuzione->close();

$asta=$db->prepare(".......");
$asta->bind_param('s',$n);
$asta->execute();
$asta->bind_result($data_si,$ora_si,$data_ci,$ora_ci);
$asta->fetch();
$asta->close();

$lotto=$db->prepare(".........");
$lotto->bind_param('s',$id]);
$lotto->execute();
$lotto->store_result();
$num_rows=$lotto->num_rows
$lotto->bind_result($pr_base1,$pr_rilancio1,$numero,$venduto);

//some code

while($lotto->fetch()){
//do stuff
}

 

i have a necessity either to know the number of rows return and to fetch them and i face this problem (the problem i have is on the $lotto variable):

when i use the store_result i get the num_rows but then i cannot fetch them while on the other end if i dont use store_result i cannot get the num_rows but i can fetch the rows. how is that possible?

 

i dont receive any error, it simply exit from the while cycle on the first case and get a null for the num_rows on the second case.

[Muddy_Funster]

tried this?

while($row = $lotto->fetch_array(MYSQLI_ASSOC)){

[PFMaBiSmAd]

^^^ That's not how you retrieve data from a prepared query.

 

 

It would take seeing your actual code that doesn't work to be able to help with what it is doing. I suspect your // some code or // do stuff is overwriting a variable.

[Muddy_Funster]

^^^ That's not how you retrieve data from a prepared query.

 

 

It would take seeing your actual code that doesn't work to be able to help with what it is doing. I suspect your // some code or // do stuff is overwriting a variable.

My bad, I just lifted the idea from the manual page

<?php
// Connect to server and database
$mysqli = new mysqli("$dbServer", "$dbUser", "$dbPass", "$dbName");

// Open First Stored Procedure using MYSQLI_STORE_RESULT to retain for looping
$resultPicks = $mysqli->query("CALL $proc ($searchDate, $maxRSI, $incRSI, $minMACD, $minVol, $minTrades, $minClose, $maxClose)", MYSQLI_STORE_RESULT);

// process one row at a time from first SP
while($picksRow = $resultPicks->fetch_array(MYSQLI_ASSOC)) {

[cypher86]

hi,

the way you proposed make useless the use of bind_result.

 

following the rest of the code:

while($lotto->fetch()){

$pr_base=str_replace(".","",$pr_base1);

$centesimi_base=explode(",",$pr_base);

$pr_rilancio=str_replace(".","",$pr_rilancio1);

$centesimi_rilancio=explode(",",$pr_rilancio);

$testo_prezzo=traslitterazione($centesimi_base[0]);

$testo_rilancio=traslitterazione($centesimi_rilancio[0]);

if($lotto->num_rows==1) $i="UNICO";

if($lotto->num_rows!=1){

$pdf->SetFont('Times','B',12);

$pdf->Cell(0,5," Lotto $numero",0,1,'L');

$pdf->Cell(0,5,"

[cypher86]

any clue?

[PFMaBiSmAd]

Any clue about what? Your first post in the is thread doesn't contain actual code. The last code you posted is incomplete because it doesn't show the code from the point where you are forming the query statement through to where the problem is occurring at.

 

Sorry for posting the Programming Riot Act, but programming is an exact science. Computers only do exactly what their code and data tells them to do. We only see the information you supply in your post. While it might be possible your symptom is due to a bug in php (~.2%), it's more likely (~99.8%) that it is due to something you are doing in your code. The only why anyone here can help determine if it is something in your code is if you post ALL the relevant code the reproduces the problem (so that they can reproduce the problem if need be.) All the relevant code in this case is the query statement through to the end of any loop that is accessing the data from that query statement.

 

Also, for the specific code you post, indicate what symptom or incorrect result you are getting and what the expected result should be.

Edited November 16, 2012 by PFMaBiSmAd

[cypher86]

here the full page code.

<?php

session_start();

require('../lib/pdf.class.php');

include("../lib/db_tool.inc.php");

include("../lib/traslitterazione.php");

 

$db=db_connect_param();

 

$esecuzione=$db->prepare("......");

$esecuzione->bind_param('s',$_GET['id']);

$esecuzione->execute();

$esecuzione->bind_result($creditore,$n_perizia,$data_incarico,$username,$indirizzo,$telefono,$giudice);

$esecuzione->fetch();

$esecuzione->close();

 

$esecutato=$db->prepare("...");

$esecutato->bind_param('s',$n_perizia);

$esecutato->execute();

$esecutato->bind_result($cognome,$nome);

while($esecutato->fetch()) {

$nominativo_esec=$nominativo_esec . "-$cognome $nome";

}

 

$asta=$db->prepare("...");

$asta->bind_param('s',$n_perizia);

$asta->execute();

$asta->bind_result($data_si,$ora_si,$data_ci,$ora_ci);

$asta->fetch();

$asta->close();

 

$lotto=$db->prepare("...");

$lotto->bind_param('s',$_GET['id']);

$lotto->execute();

$lotto->bind_result($pr_base1,$pr_rilancio1,$numero,$venduto);

 

if($lotto->num_rows==1)

$testo3=$testo3."UNICO";

else

$testo3=$testo3.$lotto->num_rows;

 

//Instanciation of inherited class

$pdf=new PDF();

$pdf->AliasNbPages();

$pdf->SetMargins(20,20,20);

$pdf->AddPage();

$pdf->Image('intestazione.gif');

$i=1;

while($lotto->fetch()){

$pr_base=str_replace(".","",$pr_base1);

$centesimi_base=explode(",",$pr_base);

$pr_rilancio=str_replace(".","",$pr_rilancio1);

$centesimi_rilancio=explode(",",$pr_rilancio);

$testo_prezzo=traslitterazione($centesimi_base[0]);

$testo_rilancio=traslitterazione($centesimi_rilancio[0]);

if($lotto->num_rows==1) $i="UNICO";

if($lotto->num_rows!=1){

$pdf->SetFont('Times','B',12);

$pdf->Cell(0,5," Lotto $numero",0,1,'L');

$pdf->Cell(0,5,"