while loop not working correctly

[jakebur01]

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 />";


  }

[craygo]

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

[jakebur01]

what would i put after that statement to display the # of rows?

 

[craygo]

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

[jakebur01]

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

[craygo]

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

[jakebur01]

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

[craygo]

try changing mysqli_error() to mysqli_error($db)  i don't use mysqli much so bare with me

 

Ray

[jakebur01]

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

[craygo]

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

[jakebur01]

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

[craygo]

GROUP BY dog_listing.Breed_id

change to

 

GROUP BY dog_breed.Breed_id

[jakebur01]

WOW!!!

 

THank you so much.

 

I really appreciate you helping me.

 

It works great now!