help needed on a mysql query

[funbinod]

hello all!

i was writing a mysql prepared statement on php and i stuck at one point that made me feel something interesting.

 

the following query works fine only after i remove the column 'description'. but selecting the column doesn't work and shows no error.

	if($stmt = $mysqli->prepare("SELECT item,sid,catid,oqty,unitid,sprice,pprice,description FROM stock WHERE (icode=? or bcode=?) AND cid=?")) {
		$stmt->bind_param('ssi', $icode, $icode, $cid);
		$stmt->execute();
		$stmt->bind_result($item,$sid,$catid,$oqty,$unitid,$sprice,$pprice,$description);
		$stmt->fetch();
		$stmt->close();
		$stmt->free_result();
	} else {
		echo 'Error: '.$mysqli->error;
	}

and yes! these is existence of column named 'description'.

 

 

i'm stuck at this point. please guide me why is this happening.

 

 

[justsomeguy]

Check for error messages when you execute the query.

[funbinod]

there is no error msg.

[justsomeguy]

You changed that code to check for errors, and nothing is returning false?

[funbinod]

yes! nothing is returning false.

[justsomeguy]

I don't see a reason why that wouldn't work.

[Don E]

Try adding $stmt->store_result(); after $stmt->bind_result() and then add/check if $stmt->num_rows returns anything.

 

i.e.:

$stmt->bind_param('ssi', $icode, $icode, $cid);
$stmt->execute();
$stmt->bind_result($item,$sid,$catid,$oqty,$unitid,$sprice,$pprice,$description);
$stmt->store_result();

if($stmt->num_rows)
{
   echo $stmt->num_rows; //test to see if num_rows
   
  //if any num_rows returned, then fetch....
  // if you know a single row is returned: $stmt->fetch();
  //if multiple rows....: while($stmt->fetch()) {}
  
}
else
{
  echo 'no rows returned';

}

[funbinod]

@Don E

----------------

the problem is only that it doesn't work when i select 'description' column. otherwise it works fine. using the column even doesn't show any error. it just doesn't work without showing any error. i even tried getting error, if any, on execute (i.e., if(!$stmt->execute()) {echo $mysqli->error;} ) but it didn't show any error msg.

 

==========================

 

is 'description' a reserved word?

[Ingolme]

It should show an error if you used a reserved word. But just to be sure, you can surround it in backticks `description`. If it's still not working after that then a reserved word is not the problem.

[funbinod]

its still not working using backticks `description`.

 

what could be the possible reason for not working?? its worrying me...

 

 

edit:

the column's type is set to longtext()...

 

edit:

i even tried changing the column's name... still no improve...

Edited June 11, 2016 by funbinod

[Ingolme]

It's time to do some more serious debugging.

Check to see how many numbers it prints before it stops. I've also reordered some of your instructions so that they make more sense.

$stmt = $mysqli->prepare("SELECT item,sid,catid,oqty,unitid,sprice,pprice,description FROM stock WHERE (icode=? or bcode=?) AND cid=?");
if($stmt) {
  echo 1;
  $stmt->bind_param('ssi', $icode, $icode, $cid);
  echo 2;
  $stmt->bind_result($item,$sid,$catid,$oqty,$unitid,$sprice,$pprice,$description);
  echo 3;
  $success = $stmt->execute();
  echo 4;
  if(!$success) {
    echo $stmt->error;
  }
  echo 5;
  $stmt->fetch();
  echo 6;
  $stmt->free_result();
  echo 7;
  $stmt->close();
  echo 8;
} else {
  echo 'Error: '.$mysqli->error;
}

[funbinod]

it echoed 1 and 2. this means it stopped while binding result??

[Ingolme]

You probably need to have declared the variables beforehand.

$item = null;
$sid = null;
$catid = null;
$oqty = null;
$unitid = null;
$sprice = null
$pprice = null;
$description = null;
$stmt->bind_result($item,$sid,$catid,$oqty,$unitid,$sprice,$pprice,$description);

[funbinod]

no improvement

[Don E]

Have you tried doing a simple select to see if it returns anything like the following with description:

 

SELECT description FROM stock

 

or

 

SELECT description FROM stock WHERE icode = 1

 

or

 

SELECT item,sid,catid,oqty,unitid,sprice,pprice,description FROM stock WHERE icode = 1

 

Good luck.

[funbinod]

getting more interesting..

 

it works fine for all columns when i use simple "$mysqli->query".

 

but when i use prepared statement, it doesn't work for the column - description, even selecting only that coulmn

[funbinod]

 

Try adding $stmt->store_result(); after $stmt->bind_result()

 

i found out that its not by adding $stmt->store_result() AFTER bind_result(). but its solved by adding $stmt->store_result() BEFORE bind result.

 

somewhere on the web i read that when selecting a longtext() field, we have to use store_result before bind_result.

and now the problem is solved. thank u all for being with me to solve this problem...

 

 

[Ingolme]

I prefer using PDO than MySQLi partly because there's no need to call a store_result() method.

[Don E]

 

i found out that its not by adding $stmt->store_result() AFTER bind_result(). but its solved by adding $stmt->store_result() BEFORE bind result.

 

somewhere on the web i read that when selecting a longtext() field, we have to use store_result before bind_result.

 

 

Good to know! Glad you figured it out!