DaniWeb Forum Index > Web Development > PHP

Displaying query results dynamically in table

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Sep 2007

Posts: 4

Reputation: hsmom314 is an unknown quantity at this point

Solved Threads: 0

hsmom314

Offline

Newbie Poster

Displaying query results dynamically in table

Sep 20th, 2007

I am trying to display MySQL query results dynamically in a table. Based on the query, the table will have a maximum of 3 columns and 15 rows. I would like to have the results display as follows:

Record 1 Record 16 Record 31
Record 2 Record 17 Record 32

Record 15 Record 30 Record 45

If the query returns less than 16 records, everything should display in the first column; if more than 15 but less than 31 - 2 columns, et al.

I am fairly new to PHP and am trying to learn all I can as I go along. Any books, websites, or online classes you can suggest to increase my knowledge of PHP would be greatly appreciated.

Thanks,

Deb

•

Join Date: Nov 2004

Posts: 257

Reputation: sam1 is an unknown quantity at this point

Solved Threads: 1

sam1

Offline

Posting Whiz in Training

Re: Displaying query results dynamically in table

Sep 20th, 2007

ok i will give you an example:

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
<html>
<head>
</head>
<body>
 <?php
$host=""; // Host name
$username=""; // Mysql username
$password=""; // Mysql password
$db_name=""; // Database name
$tbl_name=""; // Table name
 
// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");  
 
 $user_result = "select * from table;";
$qry = mysql_query($user_result) OR die(mysql_error());
$user_array = mysql_fetch_assoc($qry);
echo "<center>";
echo "<table CELLPADDING=10 border =1 >";
echo "<tr>";
echo "<td>".$user_array['field 1']."</td>";
echo "<td>".$user_array['field 2']."</td>";
echo "<td>".$user_array['field 3']."</td>";
echo "</tr>";
echo "</table>";
    }
mysql_close();
?>
 
</body>
</html>
<html>
<head>
</head>
<body>
 <?php
$host=""; // Host name
$username=""; // Mysql username
$password=""; // Mysql password
$db_name=""; // Database name
$tbl_name=""; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");  
    
 $user_result = "select * from table;";
$qry = mysql_query($user_result) OR die(mysql_error());
$user_array = mysql_fetch_assoc($qry);
echo "<center>";
echo "<table CELLPADDING=10 border =1 >";
echo "<tr>";
echo "<td>".$user_array['field 1']."</td>";
echo "<td>".$user_array['field 2']."</td>";
echo "<td>".$user_array['field 3']."</td>";
echo "</tr>";
echo "</table>";
    }
mysql_close();
?>

</body>
</html>

hope that helps you

Last edited by sam1; Sep 20th, 2007 at 10:52 am.

•

Join Date: Sep 2007

Posts: 4

Reputation: hsmom314 is an unknown quantity at this point

Solved Threads: 0

hsmom314

Offline

Newbie Poster

Re: Displaying query results dynamically in table

Sep 20th, 2007

Thank you for your suggestion. However, I don't think I explained what I'm trying to do well enough. I am trying to display one field from my database in this table. The results from the one field should display in a table vertically (max. 15 rows) then start a new column. I've written some code (using a while loop and count function) that will display it horizontally but everything that I try to get it to display vertically ends up duplicating the same record.

•

Join Date: Jul 2006

Posts: 155

Reputation: mostafadotnet is on a distinguished road

Solved Threads: 10

mostafadotnet mostafadotnet is offline

Offline

Junior Poster

Re: Displaying query results dynamically in table

Sep 20th, 2007

try using the SQL limit instruction.for example:

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
SELECT * FROM 'users' LIMIT 0 10;
SELECT * FROM 'users' LIMIT 10 10;
SELECT * FROM 'users' LIMIT 0 10;
SELECT * FROM 'users' LIMIT 10 10;

the first number tell you the index in which the SQL DB needs to start from and the second number tell you the number of rows to be selected.
I think you can easily figure it out.
Also, try using MySQL manual if you use MySQL as your DBMS.

Good luck.

•

Join Date: May 2007

Posts: 4,444

Reputation: Ezzaral has much to be proud of

Solved Threads: 510

Ezzaral

Offline

Industrious Poster

Re: Displaying query results dynamically in table

Sep 20th, 2007

You will of course have to implement a paging mechanism if your results exceed rows*columns and you will need to get all of the items into a single array before you generate the <tr> and <td> elements. The following example code shows how you can generate the table you want

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
<?php
$data = array("a","b","c","d","e","f","g","h","i","j");
$rows = 4;
$itemCount=count($data);
echo "<table>";
for ($row=0; $row < $rows; $row++){
    echo "<tr>";
    for ($itemIndex=$row; $itemIndex < $itemCount; $itemIndex+=$rows){
        echo "<td>".$data[$itemIndex]."</td>";
    } 
}
echo "</table>";
?>
<?php
$data = array("a","b","c","d","e","f","g","h","i","j");
$rows = 4;
$itemCount=count($data);
echo "<table>";
for ($row=0; $row < $rows; $row++){
    echo "<tr>";
    for ($itemIndex=$row; $itemIndex < $itemCount; $itemIndex+=$rows){
        echo "<td>".$data[$itemIndex]."</td>";
    } 
}
echo "</table>";
?>

•

Join Date: Sep 2007

Posts: 6

Reputation: schizoman is an unknown quantity at this point

Solved Threads: 0

schizoman

Offline

Newbie Poster

Re: Displaying query results dynamically in table

Sep 23rd, 2007

Here's a quick and dirty way to do it with nested "for" loops:

Help with Code Tags

(Toggle Plain Text)

$strQS="select field from table order by index";
$refItems=mysql_query($strQS) or die (mysql_error());
$cols=ceil(mysql_num_rows($refItems)/15);
$tableData=array();
for($x=0;$x<$cols;$x++)
{
	for($y=0;$y<15;$y++)
	{
		$tableData[$y][$x]=@mysql_fetch_assoc($refItems);
	}
}
echo <<<TABLESTART
<table>
<tr>
<th colspan={$cols}>
Your Header Here
</th>
</tr>
TABLESTART;
for($x=0;$x<15;$x++)
{
	echo "<tr>\n";
	for($y=0;$y<$cols;$y++)
	{
		echo "<td>".$tableData[$x][$y][field]."</td>\n";
	}
	echo "</tr>\n";
}
echo "</table>"