Jump to content
funbinod

help needed on a mysql query

Recommended Posts

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.

 

 

Share this post


Link to post
Share on other sites

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';

}

Share this post


Link to post
Share on other sites

@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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 by funbinod

Share this post


Link to post
Share on other sites

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;
}
  • Like 1

Share this post


Link to post
Share on other sites

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);

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

 

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...

 

 

:)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

 

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! :happy0046:

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...