funbinod Posted June 5, 2016 Share Posted June 5, 2016 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. Link to comment Share on other sites More sharing options...
justsomeguy Posted June 6, 2016 Share Posted June 6, 2016 Check for error messages when you execute the query. Link to comment Share on other sites More sharing options...
funbinod Posted June 10, 2016 Author Share Posted June 10, 2016 there is no error msg. Link to comment Share on other sites More sharing options...
justsomeguy Posted June 10, 2016 Share Posted June 10, 2016 You changed that code to check for errors, and nothing is returning false? Link to comment Share on other sites More sharing options...
funbinod Posted June 10, 2016 Author Share Posted June 10, 2016 yes! nothing is returning false. Link to comment Share on other sites More sharing options...
justsomeguy Posted June 10, 2016 Share Posted June 10, 2016 I don't see a reason why that wouldn't work. Link to comment Share on other sites More sharing options...
Don E Posted June 10, 2016 Share Posted June 10, 2016 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'; } Link to comment Share on other sites More sharing options...
funbinod Posted June 11, 2016 Author Share Posted June 11, 2016 @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? Link to comment Share on other sites More sharing options...
Ingolme Posted June 11, 2016 Share Posted June 11, 2016 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. Link to comment Share on other sites More sharing options...
funbinod Posted June 11, 2016 Author Share Posted June 11, 2016 (edited) 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 Link to comment Share on other sites More sharing options...
Ingolme Posted June 11, 2016 Share Posted June 11, 2016 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; } 1 Link to comment Share on other sites More sharing options...
funbinod Posted June 11, 2016 Author Share Posted June 11, 2016 it echoed 1 and 2. this means it stopped while binding result?? Link to comment Share on other sites More sharing options...
Ingolme Posted June 11, 2016 Share Posted June 11, 2016 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); Link to comment Share on other sites More sharing options...
funbinod Posted June 12, 2016 Author Share Posted June 12, 2016 no improvement Link to comment Share on other sites More sharing options...
Don E Posted June 12, 2016 Share Posted June 12, 2016 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. Link to comment Share on other sites More sharing options...
funbinod Posted June 12, 2016 Author Share Posted June 12, 2016 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 Link to comment Share on other sites More sharing options...
funbinod Posted June 12, 2016 Author Share Posted June 12, 2016 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... Link to comment Share on other sites More sharing options...
Ingolme Posted June 12, 2016 Share Posted June 12, 2016 I prefer using PDO than MySQLi partly because there's no need to call a store_result() method. Link to comment Share on other sites More sharing options...
Don E Posted June 12, 2016 Share Posted June 12, 2016 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! Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now