how to create a dynamic html table populated by a MySQL table?

hakaii

Hi - I'm stuck with this problem and would be grateful for some pointers:

I would like to create a dynamic HTML table populated by data from a MySQL table. Two of the columns in the MySQL table are labeled “country” and “category”. Currently there are 12 different countries and 5 different categories. I would like to construct the HTML table so that its columns correspond to the 5 categories and its rows to the 12 countries. Then I would like to populate the fields in the table with the number of rows in the MySQL table that contain a given combination of “country” and “category”.

Many thanks!

dalecosp

hakaii wrote:
Hi - I'm stuck with this problem and would be grateful for some pointers:

I would like to create a dynamic HTML table populated by data from a MySQL table.

See the MySQL functions section of the manual. General outline: specify the db host, username, and password; call the connection (mysql_connect), construct the query string, and then run it.

Two of the columns in the MySQL table are labeled “country” and “category”.

See, for example, [man]mysql_field_name/man --- assuming you name your fields something meaningful, you can use this function to read the field headers for the HTML table directly from the db.

Currently there are 12 different countries and 5 different categories. I would like to construct the HTML table so that its columns correspond to the 5 categories and its rows to the 12 countries.

See above. Also, [man]mysql_fetch_array/man, [man]while/man, etc.

Then I would like to populate the fields in the table with the number of rows in the MySQL table that contain a given combination of “country” and “category”.

All in the query. "Select from table_name where country = 'foo' and category LIKE 'bar'" ...

Show us some code; I'm sure somebody will be willing to give some aid when you hit the inevitable snag ...

hakaii

Ok, I got some basic code now that works - see below (any suggestions as to improving it are appreciated) It gives me a table with "countries" as rows and "categories" as columns and populated with the sum of occurences of each combination country/category. Now I would like to do the following: make the number in each field clickable so that it lists the results, i.e. respective rows from my database. So for instance, the sum of rows in my database that have "USA" as "country" and "university" as "category" in common is 30. When clicking "30" in my table, I would like to show the content of these 30 rows from my database.

Also, how could I avoid showing a "0" in my table if there are no results?

Code:
<?
$connection=mysql_connect($hostName,$username,$password);
mysql_select_db($databaseName,$connection);
$num_cat = 0;
$cat_arr = array();
$sel_cat = "SELECT DISTINCT(category) FROM labs ORDER BY category DESC";
$res_cat = mysql_query($sel_cat) or die(mysql_error());
$num_cat = mysql_num_rows($res_cat);
for($i=0;$i<$num_cat;$i++)
{
$arr_cat = mysql_fetch_array($res_cat);
@extract($arr_cat);
$cat_arr[$i] = stripslashes($category);
}
?>
<table align='center' width='400' cellpadding='0' cellspacing='0'>
<tr>
<td width='90' align='left'> </td>
<td width='40' align='center'>Universities</td>
<td width='40' align='center'>Researchers</td>
<td width='40' align='center'>Labs</td>
<td width='40' align='center'>Initiatives</td>
</tr>
</table>
<?
$num_cnt = 0;
$sel_cnt = "SELECT DISTINCT(country) FROM labs";
$res_cnt = mysql_query($sel_cnt) or die(mysql_error());
$num_cnt = mysql_num_rows($res_cnt);
echo "<table align='center' width='400' cellpadding='0' cellspacing='0'>";
for($j=0;$j<$num_cnt;$j++)
{
$arr_cnt = mysql_fetch_array($res_cnt);
@extract($arr_cnt);
echo "<tr><td width='90' align='left'>".stripslashes($country)."</td>";
for($i=0;$i<$num_cat;$i++)
{
$retres = 0;
$sel_n = "SELECT title, urllink, description FROM labs WHERE country LIKE '$country' AND category LIKE '$cat_arr[$i]'";
$res_n = mysql_query($sel_n) or die(mysql_error());
$retres = mysql_num_rows($res_n);
echo "<td align='center' width='40'>";
echo stripslashes($retres);
echo "</td>";
}
echo "</tr>";
}
echo "</tr></table>";
?>