yobo Posted March 27, 2007 Share Posted March 27, 2007 hey all, i am building a hack site for people to post there mods about phpbb and i have made a category list and put it in a table, i have 2 coloumns one called cat name and the other called hacks what i want is for the hacks coloumn to say the total number of hacks that have been added for that category the only problems is that categorys have a seperate table from where that hacks are stored however the hacks table has a FK that is linked to the category table. here is my code for the cateogy page <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> <title>Untitled Document</title> </head> <body> <table width="50%" border="0" cellspacing="0" cellpadding="3" align="left" > <tr> <th align="left">Cat Name</th> <th align="left">Hacks</th> </tr> </body> </html> <?php include("config.php"); // connect to the mysql server $link = mysql_connect($server, $db_user, $db_pass) or die ("Could not connect to mysql because ".mysql_error()); // select the database mysql_select_db($database) or die ("Could not select database because ".mysql_error()); $sql = "SELECT typeid, hackname FROM hacks "; $result = mysql_query($sql); $count = 0; //This is used to avoid that "|" is printed before the first menu item $query = mysql_query("SELECT * FROM type ORDER BY name ASC") or die(mysql_error()); echo "<tr valign='top'>\n"; while ($row = mysql_fetch_object($query)) { if ($count!=0){echo " ";} echo "<td><a href=\"$row->link\" alt=\"$row->name\">".$row->name."<br /></td>"; echo "<td>hey</td>\n"; echo "</tr>\n"; $count++; } ?> Quote Link to comment Share on other sites More sharing options...
desithugg Posted March 27, 2007 Share Posted March 27, 2007 $query = mysql_query("SELECT count(typeid) as id FROM hacks") or die(mysql_error()); $row = mysql_fetch_array( $query ); $total_rows = $row["id"]; if($total_rows == "") { $total_rows = "0"; } $total_rows = number_format($total_rows); This would count how many rows are in the table hacks and the variable which cntains the data is $total_rows and the number format function is only there to put it in a number like format for example 1332324 would be 1,332,324 after the number format function had been used on it Quote Link to comment Share on other sites More sharing options...
yobo Posted March 27, 2007 Author Share Posted March 27, 2007 ok thanks but how would i count only certain hacks for example my addons category has an id value of 1 and so anyhacks associated with that category will have a typeid of 1 shown in the hacks table and so have 9 categorys althogether so 1-9 is there unique id's which is (typeid) Quote Link to comment Share on other sites More sharing options...
desithugg Posted March 27, 2007 Share Posted March 27, 2007 umm im not sure what you mean but do you want to count the rows that have a specific category eg. id | name | category 1 |saad | 1 2 |alex | 1 3 |shaun | 4 so you want to count the fields that have the category 1 or 4 and so on? $query = mysql_query("SELECT count(typeid) as id FROM hacks where catrgory = '4'") or die(mysql_error()); $row = mysql_fetch_array( $query ); $total_rows['cat4'] = $row["id"]; if($total_rows['cat4'] == "") { $total_rows['cat4'] = "0"; } $total_rows['cat4'] = number_format($total_rows['cat4']); Quote Link to comment Share on other sites More sharing options...
yobo Posted March 27, 2007 Author Share Posted March 27, 2007 not really this is what i am looking for: hey all, i am not sure how i would do this, what i have is a page called cat.php which lists all the cats from the database in a row like this Add-On's Admin Tools BBCode Communication Cosmetic/Styles Profile Security Syndication the above categorys are in a table called type and have an id called typeid what i want to do is show the number of hacks associated with that category oh and hacks have there own table as well but have a FK linked to the type table the FK is called typeid so this is what i want for example Cat Name Number Of hacks Add-On's 20 Admin Tools 15 BBCode 5 Communication 58 Cosmetic/Styles 89 Profile 90 Security 200 Syndication 90 the above is just an example of what i would like it to look like cat.php code PHP Code: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> <title>Untitled Document</title> </head> <body> </body> </html> <?php include("config.php"); // connect to the mysql server $link = mysql_connect($server, $db_user, $db_pass) or die ("Could not connect to mysql because ".mysql_error()); // select the database mysql_select_db($database) or die ("Could not select database because ".mysql_error()); $sql = "SELECT typeid, hackname FROM hacks "; $result = mysql_query($sql); $count = 0; //This is used to avoid that "|" is printed before the first menu item $query = mysql_query("SELECT * FROM type ORDER BY name ASC") or die(mysql_error()); while ($row = mysql_fetch_object($query)) { if ($count!=0){echo " ";} echo "<a href=\"$row->link\" alt=\"$row->name\">".$row->name."<br />"; } ?> thanks all Quote Link to comment Share on other sites More sharing options...
sasa Posted March 27, 2007 Share Posted March 27, 2007 try mysql_select_db($database) or die ("Could not select database because ".mysql_error()); $sql="SELECT type.link, type.name, count(hack.id) AS co FROM type LEFT JOIN hack ON hack.typeid=type.typeid GROUP BY type.name ORDER BY type.name ASC"; mysql_query($sql) or die(mysql_error()); while ($row = mysql_fetch_object($query)) { echo "<a href=\"$row->link\" alt=\"$row->name\">$row->name $row->co</a><br />"; } Quote Link to comment Share on other sites More sharing options...
yobo Posted March 28, 2007 Author Share Posted March 28, 2007 hey, i have changed my code but i am getting this error Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Program Files\xampp\htdocs\phpbb3hacks\index.php on line 22 my database structure is as follows for the type table typeid name link my hacks is as follows hacksid name type size content typeid dateadded hackname description version approved username here is my coding <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> <title>Untitled Document</title> </head> <body> </body> </html> <?php include("config.php"); // connect to the mysql server $link = mysql_connect($server, $db_user, $db_pass) or die ("Could not connect to mysql because ".mysql_error()); mysql_select_db($database) or die ("Could not select database because ".mysql_error()); $sql="SELECT type.link, type.name, count(hacksid) AS co FROM type LEFT JOIN hacks ON hacks.typeid=type.typeid GROUP BY type.name ORDER BY type.name ASC"; mysql_query($sql) or die(mysql_error()); while ($row = mysql_fetch_object($query)) { echo "<a href=\"$row->link\" alt=\"$row->name\">$row->name $row->co</a><br />"; } ?> Quote Link to comment Share on other sites More sharing options...
sasa Posted March 29, 2007 Share Posted March 29, 2007 sorry mysql_query($sql) or die(mysql_error()); must be $query = mysql_query($sql) or die(mysql_error()); Quote Link to comment 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.