Page 1 of 1

Problem with counting rows

Posted: Tue Feb 02, 2010 11:57 am
by SWorld
Hello,

I am very new to php, and need your help

I have 2 tables, one for main categories, and the other for sub categories

I am listing the main categories within 3 columns per each row like this:

Main Category 1 Main Category 2 Main Category 3
Main Category 4 Main Category 5 Main Category 6

Table Name: maincategories
Fields: id, main



And at the same time counting sub categories that belong to each main category

Table Name: subcategories
Fields: id, main_id, sub

I keep seeing this error:
Warning: mysql_result() [function.mysql-result]: Unable to jump to row 27 on MySQL result index 10

I can see the main category name, but can never see the sub category counts


this is the code:

Code: Select all

<?php
                
include "config.php";
 
                
 
$result = mysql_query("SELECT * FROM maincategories ORDER BY main") or die(mysql_error());
 
while ($row = mysql_fetch_array($result)) {
$myvar = $row[id];
$query = "SELECT main_id, COUNT(sub) FROM subcategories WHERE main_id = '$myvar' GROUP BY main_id"; 
 
 
 
 
 
$display_columns = 3;
$count = mysql_numrows($result);
$padding = ($display_columns-1)-(($count-1)%$display_columns);
 
 
print "<table border='0' width='100%' cellspacing='0' cellpadding='0'>\n";
for($i=0; $i<$count; $i++){
if($i%$display_columns == 0)
 
print "<tr>\n";
print "<td width=\"33%\">\n";
$result2 = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_array($result2)){
echo '<IMG SRC="images/icon.jpg" WIDTH="44" HEIGHT="44" border="0">
<a href="category.php?category=' . mysql_result($result,$i,$main) . '"><strong>
' . mysql_result($result,$i,"main") . '' . mysql_result($result2,$myvar) . 'Sub Category
 
</strong></a>'; 
 
}
 
print "<p>&nbsp;</p>\n";
print "</td>\n";
if($i%$display_columns == $display_columns-1)
 
print "</tr>\n";
}
 
if($padding!=0){
for($i=0;$i<$padding;$i++)
print "<td width=\"33%\"></td>\n";
print "</tr>\n";
}
 
print "</table>\n";
 
 
}
                
mysql_close($link);
                
?>


Thank you in advance

Re: Problem with counting rows

Posted: Tue Feb 02, 2010 6:33 pm
by JakeJ
The problem is, your second query can't loop through the first one to find an appropriate value.

Make one query as a join:

"SELECT * from maincategories JOIN subcategories on maincategories.id = subcategories.main_id"

Then, you can count, manipulate, etc, whatever you want from there.

Re: Problem with counting rows

Posted: Wed Feb 03, 2010 12:10 am
by SWorld
Thank you JakeJ, but I can see a new error now:

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 29 on MySQL result index 28


also it did not list all the main category, only main categories with sub categories was listed

do you have any idea about how to fix it?

Re: Problem with counting rows

Posted: Wed Feb 03, 2010 1:48 am
by JakeJ
Do you have phpmyadmin? Try running the query there before troubleshooting the php. If it works fine, post the php code exactly as you have it and I'll see what I can do.

Re: Problem with counting rows

Posted: Wed Feb 03, 2010 3:10 am
by SWorld
yes I have phpmyadmin, but how to run the query there?

Re: Problem with counting rows

Posted: Wed Feb 03, 2010 11:17 am
by JakeJ
SWorld wrote:yes I have phpmyadmin, but how to run the query there?
You have to click on the SQL tab and type or paste in a valid SQL statement. Perhaps a phpmyadmin tutorial would be helpful, they're easy to find. Alternatively, you could find and install MySQL Workbench.