display how many records per category

[jesushax]

hi below is my code

 

what im trying to do is count how many records there are per VacCat and display them next to the category name

 

unfortunatley my effory doesnt work

 

any help?

 

<?php
session_start();
include($_SERVER['DOCUMENT_ROOT'] . '/new/includes/header.php');
include($_SERVER['DOCUMENT_ROOT'] . '/new/includes/access.inc');
echo '<h2 class="titles">Edit Vacancies</h2>'."\n";
echo '<p>Click a vacancy category below to edit archive and delete vacancies.</p>'."\n";
$SQL = mysql_query("SELECT VacCat FROM tblVac WHERE VacArchive='0' GROUP BY VacCat") or die (mysql_error());
$COUNTSQL = mysql_query("SELECT VacCat, COUNT(VacTitle) FROM tblVac GROUP BY VacCat") or die (mysql_error()); 


if (mysql_num_rows($SQL) <0) {
echo "<p>Sorry there are no current vacancies.</p>";
} else {
while ($count = mysql_num_rows($COUNTSQL)) {
while ($row = mysql_fetch_array($SQL)) {
echo '<div style="padding:5px 0; clear:both;">'."\n";
echo '<img src="/new/images/layout/close.gif" alt="arrow" /> '."\n";
echo '<a href="/new/admin/vacancies/edit_vac_list.php?CAT='.$row["VacCat"].' ">'.$row["VacCat"].' Vacancies</a> '.$count["VacTitle"]."\n";
echo "</div>"."\n";
}
}
}
include($_SERVER['DOCUMENT_ROOT'] . '/new/includes/footer.php');
?>

[piyushsharmajec]

i dont sure but change condition mysql_num_rows($sql<0)  to  mysql_num_rows($sql<1)

and check table VacCat in database

[jesushax]

no thats not the problem

 

i already have a loop working displaying the records the bit ive been tinkering with to try make work is

 

$COUNTSQL = mysql_query("SELECT VacCat, COUNT(VacTitle) FROM tblVac GROUP BY VacCat") or die (mysql_error());

 

and

 

while ($count = mysql_num_rows($COUNTSQL)) {

.$count["VacTitle"]

}

 

thanks for your input though

[jesushax]

anyone gimmie a hand here, is the sql even right?

 

Cheers

[craygo]

$COUNTSQL = mysql_query("SELECT VacCat, COUNT(VacTitle) as VacCount FROM tblVac GROUP BY VacCat") or die (mysql_error()); 
while ($count = mysql_num_rows($COUNTSQL)) {
echo "Total ".$count['VacCat']." = ". $count["VacCount"];
}

 

You may have to store the results of the count in an array first then while looping through the rows output the count in the array.

 

You could also use one query to list everything and use php to total up each catagory.

 

Ray

 

[craygo]

After actually looking at your code, why are you using 2 queries??

 

Should only need one query to do this

<?php
session_start();
include($_SERVER['DOCUMENT_ROOT'] . '/new/includes/header.php');
include($_SERVER['DOCUMENT_ROOT'] . '/new/includes/access.inc');
echo '<h2 class="titles">Edit Vacancies</h2>'."\n";
echo '<p>Click a vacancy category below to edit archive and delete vacancies.</p>'."\n";
$SQL = mysql_query("SELECT VacCat, COUNT(VacTitle) AS VacCount FROM tblVac WHERE VacArchive='0' GROUP BY VacCat") or die (mysql_error());

if (mysql_num_rows($SQL) < 1) {
echo "<p>Sorry there are no current vacancies.</p>";
} else {
  while ($row = mysql_fetch_array($SQL)) {
  echo '<div style="padding:5px 0; clear:both;">'."\n";
  echo '<img src="/new/images/layout/close.gif" alt="arrow" /> '."\n";
  echo '<a href="/new/admin/vacancies/edit_vac_list.php?CAT='.$row["VacCat"].' ">'.$row["VacCat"].' Vacancies</a> '.$row["VacCount"]."\n";
  echo "</div>"."\n";
  }
}
include($_SERVER['DOCUMENT_ROOT'] . '/new/includes/footer.php');
?>

 

Ray

[jesushax]

thanks alot

 

i was presuming id have to do two seperate queires for it for some reason

 

THanks for you help