MoFish Posted November 23, 2013 Share Posted November 23, 2013 (edited) Hi, I have a foreach running on the results for SQL query, but only get two results appearing. I am expecting three to be displayed. Can anyone see any major problem with this? public function get_all_league() { $db = new sql(); $sql = "SELECT * FROM $this->tablename"; $db->query($sql); $data = $db->fetch_array(); $section_object = new section_object(); if(!empty($data)){ while($k = $db->fetch_array($db)) { $total[] = $k; } } return $total; } Thanks, MoFish Edited November 23, 2013 by MoFish Quote Link to comment https://forums.phpfreaks.com/topic/284200-for-each-unexpected-result/ Share on other sites More sharing options...
Ch0cu3r Posted November 23, 2013 Share Posted November 23, 2013 ut only get two results appearing. What are you expecting? I have a foreach running on the results for SQL query, What foreach? Quote Link to comment https://forums.phpfreaks.com/topic/284200-for-each-unexpected-result/#findComment-1459676 Share on other sites More sharing options...
MoFish Posted November 23, 2013 Author Share Posted November 23, 2013 (edited) What are you expecting? What foreach? Appologies, It is a while statement: while($k = $db->fetch_array($db)) There are three entries in the database and it is only returning two in the print_r statement. Do I need to maybe add the values of $data into the total[] array I am returning somehow? Thanks MoFish Edited November 23, 2013 by MoFish Quote Link to comment https://forums.phpfreaks.com/topic/284200-for-each-unexpected-result/#findComment-1459679 Share on other sites More sharing options...
Ch0cu3r Posted November 23, 2013 Share Posted November 23, 2013 (edited) This is because you call $db->fetch_array(); before the while loop. Each time you call this it'll return a row from the query. Basically yourare losing a result for checking if the query returned anything. What you should be doing is use mysqli_num_rows (<-- mysqli) or mysql_num_rows (mysql) to check for this. Edited November 23, 2013 by Ch0cu3r Quote Link to comment https://forums.phpfreaks.com/topic/284200-for-each-unexpected-result/#findComment-1459681 Share on other sites More sharing options...
MoFish Posted November 23, 2013 Author Share Posted November 23, 2013 (edited) Hi, I updated the code as suggested - but it now returns no values. Can you clarify if i got this correct? Should the fetch_array still be in the while loop as I had previously? public function get_all_league() { $db = new sql(); $sql = "SELECT * FROM $this->tablename"; $db->query($sql); $data = $db->mysql_num_rows(); $section_object = new section_object(); if(!empty($data)){ while($k = $db->fetch_array($db)) { $total[] = $k; } } return $total; } Edited November 23, 2013 by MoFish Quote Link to comment https://forums.phpfreaks.com/topic/284200-for-each-unexpected-result/#findComment-1459684 Share on other sites More sharing options...
objnoob Posted November 23, 2013 Share Posted November 23, 2013 You're looping through each item in the $data array. The number of items in the $data array depends on the fetch mode you're using. Since you're not explicitly specifying a fetch mode (fetch num, fetch both, fetch assoc, etc) you will have to assume the default fetch mode is being used. The default fetch mode, assuming you're using fetch_array() method of a mysqli_result object, is both. This will represent each column of a mysqli_result row twice; once by column index and once by column name. Since each column is represented twice, any and all row arrays (fetch_array) fetched will have an even number of items. x columns multiplied by 2 representations will always total an even number of representations (2, 4, 6, . So, expecting 3 is just not possible. Now that we've covered your fetch both mode paradox, there are other inhibitions that I spot in your code. Caliing $db->query($sql); should, if it doesn't, result with a return value that is not being stored. You should design the query() method of your sql class to return a value that represents the query's result! $the_returned_result = $db->query($sql); // query the database and set the variable $the_returned_result to the returned value of sql::query() $data = $the_returned_result->fetch_array(); // fetch from the returned value Quote Link to comment https://forums.phpfreaks.com/topic/284200-for-each-unexpected-result/#findComment-1459685 Share on other sites More sharing options...
objnoob Posted November 23, 2013 Share Posted November 23, 2013 This is because you call $db->fetch_array(); before the while loop. Each time you call this it'll return a row from the query. Basically yourare losing a result for checking if the query returned anything. What you should be doing is use mysqli_num_rows (<-- mysqli) or mysql_num_rows (mysql) to check for this. No need, since the query has a LIMIT clause. Quote Link to comment https://forums.phpfreaks.com/topic/284200-for-each-unexpected-result/#findComment-1459686 Share on other sites More sharing options...
MoFish Posted November 23, 2013 Author Share Posted November 23, 2013 (edited) function query($sql_query) { $this->sql_query=$sql_query; $this->sql_result=mysql_query($this->sql_query); $this->error=mysql_error(); if (!$this->sql_result) { echo "<b>" . $sql_query . "</b><br><br>"; $this->error=mysql_error(); echo $this->error; } echo mysql_error(); } is my query function objnoob Edited November 23, 2013 by MoFish Quote Link to comment https://forums.phpfreaks.com/topic/284200-for-each-unexpected-result/#findComment-1459687 Share on other sites More sharing options...
MoFish Posted November 23, 2013 Author Share Posted November 23, 2013 So let me get this right - I am OK to continue to use the fetch_array() approach I had previously? However I need to amend the query() function to return a value? Quote Link to comment https://forums.phpfreaks.com/topic/284200-for-each-unexpected-result/#findComment-1459688 Share on other sites More sharing options...
Ch0cu3r Posted November 23, 2013 Share Posted November 23, 2013 No need, since the query has a LIMIT clause. What? Where is the limit here $sql = "SELECT * FROM $this->tablename"; How else are you supposed to check if the query returned anything. Quote Link to comment https://forums.phpfreaks.com/topic/284200-for-each-unexpected-result/#findComment-1459689 Share on other sites More sharing options...
MoFish Posted November 23, 2013 Author Share Posted November 23, 2013 public function get_all_league() { $db = new sql(); $sql = "SELECT * FROM $this->tablename"; $returned = $db->query($sql); $data = $returned->$db->fetch_array(); $section_object = new section_object(); if(!empty($data)){ while($k = $db->fetch_array($db)) { $total[] = $k; } } return $total; } Updated code. No Joy Quote Link to comment https://forums.phpfreaks.com/topic/284200-for-each-unexpected-result/#findComment-1459691 Share on other sites More sharing options...
MoFish Posted November 23, 2013 Author Share Posted November 23, 2013 What? Where is the limit here $sql = "SELECT * FROM $this->tablename"; How else are you supposed to check if the query returned anything. There is no LIMIT you are correct with this... Quote Link to comment https://forums.phpfreaks.com/topic/284200-for-each-unexpected-result/#findComment-1459692 Share on other sites More sharing options...
objnoob Posted November 23, 2013 Share Posted November 23, 2013 Yes, it would be wise. However, you are storing the result in $this->sql_result. So, you COULD just $data = $db->sql_result->fetch_array(); I don't recommend this because it limits you to 1 sql_result, and you will not be able to use sql::query() again while relying on sql::sql_result. Quote Link to comment https://forums.phpfreaks.com/topic/284200-for-each-unexpected-result/#findComment-1459693 Share on other sites More sharing options...
MoFish Posted November 23, 2013 Author Share Posted November 23, 2013 (edited) My brain is melting I'm now getting no results at all. Edited November 23, 2013 by MoFish Quote Link to comment https://forums.phpfreaks.com/topic/284200-for-each-unexpected-result/#findComment-1459694 Share on other sites More sharing options...
Ch0cu3r Posted November 23, 2013 Share Posted November 23, 2013 Yes, it would be wise. However, you are storing the result in $this->sql_result. So, you COULD just $data = $db->sql_result->fetch_array(); I don't recommend this because it limits you to 1 sql_result, and you will not be able to use sql::query() again while relying on sql::sql_result. That would be the case if MoFish is using mysqli object. The mysql_*() is procedural code only Quote Link to comment https://forums.phpfreaks.com/topic/284200-for-each-unexpected-result/#findComment-1459695 Share on other sites More sharing options...
objnoob Posted November 23, 2013 Share Posted November 23, 2013 The limit is here: $sql = "SELECT * FROM $this->tablename WHERE `staff_id`='".clean_db($id)."' LIMIT 1"; This means sql::sql_result would be no more than 1 row ALWAYS! So, checking the number of rows is moot. Fetch once.... if the fetch is false on the first fetch, you've got 0 rows, otherwise 1. Second fetch is not necessary, but may require a free result or close cursor to free up the database's result buffer. Quote Link to comment https://forums.phpfreaks.com/topic/284200-for-each-unexpected-result/#findComment-1459696 Share on other sites More sharing options...
MoFish Posted November 23, 2013 Author Share Posted November 23, 2013 objnoob I've scrolled up my posts and do not see the same code posted as you have quoted in your sql above. Quote Link to comment https://forums.phpfreaks.com/topic/284200-for-each-unexpected-result/#findComment-1459697 Share on other sites More sharing options...
objnoob Posted November 23, 2013 Share Posted November 23, 2013 That would be the case if MoFish is using mysqli object. The mysql_*() is procedural code only Yeah, I see. He's knee deep without the basics. I'm sure it's a copy paste snippet here and snippet there collection of code statements in an attempt. Quote Link to comment https://forums.phpfreaks.com/topic/284200-for-each-unexpected-result/#findComment-1459698 Share on other sites More sharing options...
MoFish Posted November 23, 2013 Author Share Posted November 23, 2013 There should be no LIMIT in the SQL Quote Link to comment https://forums.phpfreaks.com/topic/284200-for-each-unexpected-result/#findComment-1459699 Share on other sites More sharing options...
Ch0cu3r Posted November 23, 2013 Share Posted November 23, 2013 @objnoob are you reading/replying to the right thread? Quote Link to comment https://forums.phpfreaks.com/topic/284200-for-each-unexpected-result/#findComment-1459700 Share on other sites More sharing options...
MoFish Posted November 23, 2013 Author Share Posted November 23, 2013 (edited) Yeah, I see. He's knee deep without the basics. I'm sure it's a copy paste snippet here and snippet there collection of code statements in an attempt. Aww, I thought I had done rather well to get the results coming out from the database - I just couldn't figure out why I was missing one. So what exactly am I missing? Have i been lead astray during the cross wires of posts somehow? lol Edited November 23, 2013 by MoFish Quote Link to comment https://forums.phpfreaks.com/topic/284200-for-each-unexpected-result/#findComment-1459701 Share on other sites More sharing options...
MoFish Posted November 23, 2013 Author Share Posted November 23, 2013 @objnoob are you reading/replying to the right thread? lol - I'm very confused also... Quote Link to comment https://forums.phpfreaks.com/topic/284200-for-each-unexpected-result/#findComment-1459702 Share on other sites More sharing options...
objnoob Posted November 23, 2013 Share Posted November 23, 2013 objnoob I've scrolled up my posts and do not see the same code posted as you have quoted in your sql above. Yep. There's a number of issues and misunderstandings you're experiencing, so i'll end with a single suggestion... KISS Quote Link to comment https://forums.phpfreaks.com/topic/284200-for-each-unexpected-result/#findComment-1459703 Share on other sites More sharing options...
objnoob Posted November 23, 2013 Share Posted November 23, 2013 Here's the original... Hi, I have a foreach running on the results for SQL query, but only get two results appearing. I am expecting three to be displayed. Can anyone see any major problem with this? public function get_all_league() { $db = new sql(); $sql = "SELECT * FROM $this->tablename"; $db->query($sql); $data = $db->fetch_array(); $section_object = new section_object(); if(!empty($data)){ while($k = $db->fetch_array($db)) { $total[] = $k; } } return $total; } Thanks, MoFish Quote Link to comment https://forums.phpfreaks.com/topic/284200-for-each-unexpected-result/#findComment-1459704 Share on other sites More sharing options...
objnoob Posted November 23, 2013 Share Posted November 23, 2013 what the heck, lol. i'm not crazy...... this is copy + paste: Hi, I have a foreach running on the results for SQL query, but only get two results appearing. I am expecting three to be displayed. Can anyone see any major problem with this? public function get_league($id){$db = new sql();$sql = "SELECT * FROM $this->tablename WHERE `staff_id`='".clean_db($id)."' LIMIT 1";$db->query($sql);$data = $db->fetch_array();$section_object = new section_object();foreach($data as $k => $v){if(!is_numeric($k))$section_object->$k = $v;}return $section_object;}Thanks, MoFish Like This Quote MultiQuote Quote Link to comment https://forums.phpfreaks.com/topic/284200-for-each-unexpected-result/#findComment-1459705 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.