[Resolved] Tricky MySQL Question..

goaman

Hello!
I'm having difficulties on listing the categories and also show the number of tutorials on listed categories. It's kinda complicated problem, I'm not able to choose a listed category if I make the mysql selection before listing categories.

Could anyone please show me, how to do this trick? I want the result to be:

category1 (87)
category2 (72)
category3 (81)
category4 (...)

//get categories
$query = "SELECT * FROM category ORDER BY id DESC";
$result = mysql_query($query) or die("ERROR: Query failed [".$query."]<br>".mysql_error());


$xcate = 1;
echo("<table width='100%' border='1' bgcolor='#C56201'");
echo('<tr>');
for($xcate=1; $xcate<=mysql_num_rows($result); $xcate++){
$row = mysql_fetch_array($result);
$selected_c = $row['category'];

echo('<a href="index.php?cate=' . $row['category'] . '"\n>'.$row['category']. '</a></b>&nbsp;<font color="#FFBF13">(</font> <font color="#FFFFFF"><b>'

.

$query = mysql_query("SELECT Count('id') as category FROM tutorials WHERE category='$selected_c'") . $row = mysql_fetch_array($query) . 
$totalq = $row['category'];
.

'</b></font> <font color="#FFBF13">)</font>');

Weedpacket

select count(*) as tutorial_count from tutorials group by category

goaman

Originally posted by Weedpacket
select count(*) as tutorial_count from tutorials group by category

Hi!
It shows: screenshots ( Resource id #12 ) 🙁

but it suppose to show: screenshots ( 38 )

What am I doing wrong here?


//similar problem with getting the number of subcategories..

//get categories
$query = "SELECT * FROM category ORDER BY id DESC";
$result = mysql_query($query) or die("ERROR: Query failed [".$query."]<br>".mysql_error());


$xcate = 1;
echo("<table width='100%' border='1' bgcolor='#C56201'");
echo('<tr>');

for($xcate=1; $xcate<=mysql_num_rows($result); $xcate++){
    $row = mysql_fetch_array($result);
$iSelected_Category = $row['category'];

echo('<td bgcolor="#C56201" align="center"><b><a href="index.php?category=' . $row['category'] . '"\n>'.$row['category']. '</a></b>&nbsp;<font color="#FFBF13">(</font> <font color="#FFFFFF"><b>'

.	
$query = mysql_query("SELECT Count(*) as subcategory_count FROM subcategory GROUP by category" ) . 
$totalsubcategories = $row['tutorial_count']
.
'</b></font> <font color="#FFBF13">)</font>
<br>
<font color="' . $color . '"><font size="-5">' . $row['description'] . '</font></font>
</td>'); //$row['category']
    if($xcate%$category_col==0){
        echo("</tr>"."\n");

}
}


echo ' 
</table>';

rajavel

$query = mysql_query("SELECT Count(*) as subcategory_count FROM subcategory GROUP by category" ) . 
$totalsubcategories = $row['tutorial_count']

i can't understand this.... u hv missed out fetching the count value from the "subcategory" table

$row1 = mysql_fetch_row($query)

and also try using $row for first query and $row1 for second query......bcoz the second query was used with in the loop of the first query.....

and also this line

$totalsubcategories = $row['tutorial_count']

must contain $row1['subcategory_count'].....

rajavel

try using these two ways of code

1) This code will take categories from the category table

<?php
//get categories
$query = "SELECT * FROM category ORDER BY id DESC";
$result = mysql_query($query) or die("ERROR: Query failed [".$query."]<br>".mysql_error());


$xcate = 1;
echo("<table width='100%' border='1' bgcolor='#C56201'");
echo('<tr>');

for($xcate=1; $xcate<=mysql_num_rows($result); $xcate++){
    $row = mysql_fetch_array($result);
	$iSelected_Category = $row['category'];

echo('<td bgcolor="#C56201" align="center"><b><a href="index.php?category=' . $iSelected_Category . '"\n>'.$iSelected_Category. '</a></b>&nbsp;<font color="#FFBF13">(</font> <font color="#FFFFFF"><b>'.    
$query = mysql_query("SELECT Count(*) as subcategory_count FROM subcategory where category = '$iSelected_Category'" ) .$row1=mysql_fetch_row($query).$totalsubcategories = $row1['subcategory_count'].'</b></font> <font color="#FFBF13"> )</font><br><font color="'.$color.'"><font size="-5">' . $row['description'] . '</font></font></td>'); //$row['category']

if($xcate%$category_col==0){
    echo("</tr>"."\n");

}
}


echo '</table>';
?>

and....

2) this code will list categories from the sub-category table.....

<?PHP
//get categories
$query = "SELECT category,Count(*) as subcategory_count FROM subcategory GROUP by category";
$result = mysql_query($query) or die("ERROR: Query failed [".$query."]<br>".mysql_error());


$xcate = 1;
echo("<table width='100%' border='1' bgcolor='#C56201'");
echo('<tr>');

for($xcate=1; $xcate<=mysql_num_rows($result); $xcate++){
    $row = mysql_fetch_array($result);
	$iSelected_Category = $row['category'];

echo('<td bgcolor="#C56201" align="center"><b><a href="index.php?category=' . $iSelected_Category . '"\n>'.$iSelected_Category. '</a></b>&nbsp;<font color="#FFBF13">(</font> <font color="#FFFFFF"><b>'.    
$row['subcategory_count'].
'</b></font> <font color="#FFBF13"> )</font><br><font color="' . $color . '"><font size="-5">' . $row['description'] . '</font></font></td>'); //$row['category']

if($xcate%$category_col==0){
    echo("</tr>"."\n");

}
}


echo '</table>';
?>

goaman

Hi Rajavel,
thanks for your reply🙂, I didn't use "$row1 = mysql_fetch_row($query)" function because it gave me warnings..

This line is causing lotz of warnings..

 .$row1=mysql_fetch_row($query)

the result of your code is attached as picture, please take a look at it, thanks!

goaman

now there are no warnings but resource IDs are showing instead of numbers of tutorials inside of it. Picture is attached below.

rajavel

i think the name of the variable $query is the problem......

instead of using this code......

echo('<td bgcolor="#C56201" align="center"><b><a href="index.php?category=' . $iSelected_Category . '"\n>'.$iSelected_Category. '</a></b>&nbsp;<font color="#FFBF13">(</font> <font color="#FFFFFF"><b>'.    

    $query = mysql_query("SELECT Count(*) as subcategory_count FROM subcategory where category = '$iSelected_Category'" ) .$row1=mysql_fetch_row($query).$totalsubcategories = $row1['subcategory_count'].'</b></font> <font color="#FFBF13"> )</font><br><font color="'.$color.'"><font size="-5">' . $row['description'] . '</font></font></td>'); //$row['category']

use this code......

echo('<td bgcolor="#C56201" align="center"><b><a href="index.php?category=' . $iSelected_Category . '"\n>'.$iSelected_Category. '</a></b>&nbsp;<font color="#FFBF13">(</font> <font color="#FFFFFF"><b>');
$query1 = mysql_query("SELECT Count(*) as subcategory_count FROM subcategory where category = '".$iSelected_Category."'");
$row1=mysql_fetch_row($query1);
echo $totalsubcategories = $row1[0];
echo ('</b></font> <font color="#FFBF13"> )</font><br><font color="'.$color.'"><font size="-5">' . $row['description'] . '</font></font></td>'); //$row['category']

goaman

Thankyou Rajavel I really appreciate your help, it's working, now it's showing the numbers of tutorials.. 😃😃😃

Thanks again, screenshot is attached below... 😃

rajavel

try printing the query in the page and copy & execute the query in mysql.......tell me what is the resultset u r getting in mysql and also the query u r getting....

if nothing is going well.......

give me the latest code u r having.......so that i can able to trace what u r doing.......

rajavel

welcome.....