jakebur01 Posted March 12, 2008 Share Posted March 12, 2008 i have two tables, one for categories and one for actual listings. I am trying to list the number of listings avail able in the category by counting the number of listings in the dog_listing table and displaying it next to each category. My problem is that I am getting the first category being repeated all the way down the page until it times out. It is not looping through each category in the dog_breed table for some reason. if (mysqli_connect_errno()) { echo 'Error: Could not connect to database. Please try again later.'; exit; } $limit=30; // rows to return $numresults=mysqli_query($db, "SELECT * FROM dog_breed"); $numrows=@mysqli_num_rows($numresults); // next determine if offset has been passed to script, if not use 0 if (empty($offset)) { $offset=1; } $result=mysqli_query($db, "SELECT Breed_id, Breed from dog_breed where `Breed_id` <= '93' order by Breed_id asc ");//. echo "<table><tr><td><div style=padding-left:25px; padding-top:5px>"; while ($myrow=mysqli_fetch_array($result)) { $id = stripslashes($myrow["Breed_id"]); $breed = stripslashes($myrow["Breed"]); if($result = mysqli_query($db,'select * from dog_listing ' ."where Breed_id='$id' " ." and Breed='$breed'")) { $row_cnt = mysqli_num_rows($result); } else { $row_cnt = 0; } echo "<strong><a href=\"#\">$breed ($row_cnt)</a><strong><br />"; } Quote Link to comment https://forums.phpfreaks.com/topic/95807-while-loop-not-working-correctly/ Share on other sites More sharing options...
craygo Posted March 12, 2008 Share Posted March 12, 2008 you can do it in the query. I am not sure what field links the 2 tables but I will assume it is breed_id. Also I am giving alias names to your tables l for dog_listing and b for dog_breed $sql = "SELECT count(l.breed_id) AS list_count, b.Breed_id AS breed_id, b.Breed AS breed FROM dog_breed AS b LEFT JOIN dog_breed AS b ON b.Breed_id = l.Breed_id WHERE b.Breed_id <= '93' GROUP BY l.Breed_id ORDER BY b.Breed_id ASC"; Ray Quote Link to comment https://forums.phpfreaks.com/topic/95807-while-loop-not-working-correctly/#findComment-490496 Share on other sites More sharing options...
jakebur01 Posted March 12, 2008 Author Share Posted March 12, 2008 what would i put after that statement to display the # of rows? Quote Link to comment https://forums.phpfreaks.com/topic/95807-while-loop-not-working-correctly/#findComment-490505 Share on other sites More sharing options...
craygo Posted March 12, 2008 Share Posted March 12, 2008 now just print out the fields <?php $sql = "SELECT count(l.breed_id) AS list_count, b.Breed_id AS breed_id, b.Breed AS breed FROM dog_breed AS b LEFT JOIN dog_breed AS b ON b.Breed_id = l.Breed_id WHERE b.Breed_id <= '93' GROUP BY l.Breed_id ORDER BY b.Breed_id ASC"; $result = mysql_query($sql) or die(mysql_error()); while($rows = mysql_fetch_assoc($result)){ echo $rows['breed']." has ".$rows['list_count']." Listings<br>\n"; } ?> Ray Quote Link to comment https://forums.phpfreaks.com/topic/95807-while-loop-not-working-correctly/#findComment-490508 Share on other sites More sharing options...
jakebur01 Posted March 12, 2008 Author Share Posted March 12, 2008 thanks Ray, but now i am getting this error: PHP Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result $result=mysqli_query($db, "SELECT count(dog_listing.Breed_id) AS list_count, dog_breed.Breed_id AS Breed_id, dog_breed.Breed AS Breed FROM dog_breed AS dog_breed LEFT JOIN dog_breed AS dog_breed ON dog_breed.Breed_id = dog_listing.Breed_id WHERE dog_breed.Breed_id <= '93' GROUP BY dog_listing.Breed_id ORDER BY dog_breed.Breed_id ASC"); while($rows = mysqli_fetch_assoc($result)){ echo $rows['Breed']." has ".$rows['list_count']." Listings<br>\n"; } `Jake Quote Link to comment https://forums.phpfreaks.com/topic/95807-while-loop-not-working-correctly/#findComment-490513 Share on other sites More sharing options...
craygo Posted March 12, 2008 Share Posted March 12, 2008 problem is your query is probably failing. You didn't do the query correctly. you are linking dog_breed to itself FROM dog_breed AS dog_breed LEFT JOIN dog_breed AS dog_breed you may want to add some error checking until your code is finished. Keep your query statements separate from the queries so you can echo things out to trouble shoot $sql = query statement $result = mysqli_query($db, $sql) or die("There is an error in your query ".mysqli_error()); Ray Quote Link to comment https://forums.phpfreaks.com/topic/95807-while-loop-not-working-correctly/#findComment-490523 Share on other sites More sharing options...
jakebur01 Posted March 12, 2008 Author Share Posted March 12, 2008 ok, I believe I corrected the linking. But, not all of the categories are in the dog_listing table...is this what this error is about. That it is not returning any rows? There is an error in your query PHP Warning: mysqli_query() expects parameter 2 to be string, object given in C:\Inetpub\Websites\index.php on line 236 PHP Warning: mysqli_error() expects exactly 1 parameter, 0 given in C:\Inetpub\Websites\index.php on line 236 $sql=mysqli_query($db, "SELECT count(dog_listing.Breed_id) AS list_count, dog_breed.Breed_id AS Breed_id, dog_breed.Breed AS Breed FROM dog_listing AS dog_listing LEFT JOIN dog_breed AS dog_breed ON dog_breed.Breed_id = dog_listing.Breed_id WHERE dog_breed.Breed_id <= '93' GROUP BY dog_listing.Breed_id ORDER BY dog_breed.Breed_id ASC"); $result = mysqli_query($db, $sql) or die("There is an error in your query ".mysqli_error()); while($rows = mysqli_fetch_assoc($result)){ echo $rows['Breed']." has ".$rows['list_count']." Listings<br>\n"; } `Jake Quote Link to comment https://forums.phpfreaks.com/topic/95807-while-loop-not-working-correctly/#findComment-490535 Share on other sites More sharing options...
craygo Posted March 12, 2008 Share Posted March 12, 2008 try changing mysqli_error() to mysqli_error($db) i don't use mysqli much so bare with me Ray Quote Link to comment https://forums.phpfreaks.com/topic/95807-while-loop-not-working-correctly/#findComment-490537 Share on other sites More sharing options...
jakebur01 Posted March 12, 2008 Author Share Posted March 12, 2008 There is an error in your query PHP Warning: mysqli_query() expects parameter 2 to be string, object given in C:\Inetpub\Websites\TWOPOINTENTERPR\JHearnsberger\index.php on line 236 Quote Link to comment https://forums.phpfreaks.com/topic/95807-while-loop-not-working-correctly/#findComment-490541 Share on other sites More sharing options...
craygo Posted March 12, 2008 Share Posted March 12, 2008 problem is you are running the query twice remove the mysqli_query from the $sql parameter $sql = "SELECT count(dog_listing.Breed_id) AS list_count, dog_breed.Breed_id AS Breed_id, dog_breed.Breed AS Breed FROM dog_listing AS dog_listing LEFT JOIN dog_breed AS dog_breed ON dog_breed.Breed_id = dog_listing.Breed_id WHERE dog_breed.Breed_id <= '93' GROUP BY dog_listing.Breed_id ORDER BY dog_breed.Breed_id ASC"; you are running the query in the next line Also you want to have the breeds first then the listings and no need to rename them if you are going to name them the same thing so it should be $sql = "SELECT count(dog_listing.Breed_id) AS list_count, dog_breed.Breed_id AS Breed_id, dog_breed.Breed AS Breed FROM dog_breed LEFT JOIN dog_listing ON dog_breed.Breed_id = dog_listing.Breed_id WHERE dog_breed.Breed_id <= '93' GROUP BY dog_listing.Breed_id ORDER BY dog_breed.Breed_id ASC"; Ray Quote Link to comment https://forums.phpfreaks.com/topic/95807-while-loop-not-working-correctly/#findComment-490547 Share on other sites More sharing options...
jakebur01 Posted March 12, 2008 Author Share Posted March 12, 2008 Thank you for taking the time to help me. It's working now, but it's only listing two rows. And their are a couple of hundred categories. <?php @ $db = mysqli_connect(''); $sql = "SELECT count(dog_listing.Breed_id) AS list_count, dog_breed.Breed_id AS Breed_id, dog_breed.Breed AS Breed FROM dog_breed LEFT JOIN dog_listing ON dog_breed.Breed_id = dog_listing.Breed_id WHERE dog_breed.Breed_id <= '93' GROUP BY dog_listing.Breed_id ORDER BY dog_breed.Breed_id ASC"; $result = mysqli_query($db, $sql) or die("There is an error in your query ".mysqli_error($db)); while($rows = mysqli_fetch_assoc($result)){ echo $rows['Breed']." has ".$rows['list_count']." Listings<br>\n"; } ?> //printed this on page //Please Select A Breed Below. //Affenpinscher has 1 Listings //Afghan Hound has 0 Listings Quote Link to comment https://forums.phpfreaks.com/topic/95807-while-loop-not-working-correctly/#findComment-490582 Share on other sites More sharing options...
craygo Posted March 12, 2008 Share Posted March 12, 2008 GROUP BY dog_listing.Breed_id change to GROUP BY dog_breed.Breed_id Quote Link to comment https://forums.phpfreaks.com/topic/95807-while-loop-not-working-correctly/#findComment-490612 Share on other sites More sharing options...
jakebur01 Posted March 12, 2008 Author Share Posted March 12, 2008 WOW!!! THank you so much. I really appreciate you helping me. It works great now! Quote Link to comment https://forums.phpfreaks.com/topic/95807-while-loop-not-working-correctly/#findComment-490617 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.