[SOLVED] Weird problem with mysql prepared statements

[rkarolis]

I have this piece of code:

 

public function getEntry($uniqId,$pageNo){

  $mysqli=kjrDB::dbStart();
   
  $qry="SELECT entries.title,entries.body FROM entries JOIN details ON details.id=entries.id WHERE details.uniqID=? AND entries.page=?";

  if($stmt=$mysqli->prepare($qry)){
    $stmt->bind_param('si',$uniqId,$pageNo);
    $stmt->execute();
    $stmt->bind_result($title,$body);
    $stmt->fetch();
    $stmt->close();
    return array('title'=>$title,'body'=>$body);
  }else{
    trigger_error('Could Not Prepare Query',E_USER_WARNING);
    return false;
  } 

}

 

So here's the problem, query itself is correct as far as i know, i ran it in both phpmyadmin and cmd (running windows) and it returns what it's supposed to return. But here $body is always empty. It's as if bind_result() isn't passing retrieved data to the variable. No errors/warnings are generated.  :banghead: Spent all night going through the variables, looking for typo's, var_dump'ing everything, even rewrote the whole thing from scratch.

 

Desperately in need for help :/

[PFMaBiSmAd]

trigger_error() is affected by the error_reporting and display_errors settings. So are php detected errors. Are you developing and debugging your code on a system with error_reporting set to E_ALL and display_errors set to ON to get php to actually show php errors and the output from trigger_error()?

[rkarolis]

Yep, E_ALL, display_errors ON. Both $mysqli->error and $stmt->error come up clean too

[PFMaBiSmAd]

$title contains what you expect, but $body does not? How do you know this, what is your code that uses the results of that function and produces the symptom?

 

Do you have more than one row that matches that query, perhaps one without a body? The posted code only retrieves one row from the result of the query.

[rkarolis]

Yeah, it's only supposed to retrieve one row. All i do with the output at this point is just echo it onto the screen.

 

I noticed the problem when only the title got printed. At that point i var_dump'ed $title and $body and all i get for $body is string '' (length=0), when the title was matched to a single row, that does, in fact, contain a body.

 

I'm really confused here. I've been doing this for a few years now, but it's the first time that i'm in such a hole

[PFMaBiSmAd]

what is your code that uses the results of that function and produces the symptom?

 

We only see the information you provide in your posts. In the thousands of posts made on a forum like this, only a very small number of times code does not procude the expected results are due to bugs in php/mysql. The remainder (over 99.5%) of the unexpected results are due to coding errors and data that is not what you think it is. For all we know you have a conditional test that is setting body to an empty string - if($body = "") instead of testing if it is empty - if($body == "")

[rkarolis]

I see. Sorry about that. Well it hardly gets any simpler than that. the function i posted belongs to a class called kjrBlog, it's used like this:

 

$blog=new kjrBlog();

$uniqId='04082009-1';
$pno=1;

$blogEntry=$blog->getEntry($uniqId,$pno);

if($blogEntry===false){
  echo 'Blog Entry Not Found';
}else{
  echo $blogEntry['title'].'---'.$blogEntry['body'];
}

[PFMaBiSmAd]

Everything is pointing to what your query returns. Add the following after the $stmt->fetch() statement -

 

    $stmt->store_result();
    printf("Number of rows: %d.\n", $stmt->num_rows);

 

Posting your table definitions and the rows that should match the WHERE condition would help.

[PFMaBiSmAd]

I just tried your code in a basic class and it produced the expected results using test data that matches what you show -

 

Table: entries

id title      body  page

1  title 1  body 1  1

 

Table: details

id uniqID

1  04082009-1

 

Output -

 

title 1---body 1

[rkarolis]

Thank you for all your efforts.

 

I back tracked the problem. The `body` field in the entries was created as LONGTEXT, changing the column type resolved the problem (though somewhat reduces the functionality). A quick search on google revealed it's a known (and quite old) bug in php with mysqli crashing on longtext columns.