Filling table with MySQL Database

[yongj]

How does one create a dynamic table (meaning that it expands as information is automatically put in) with data coming from a MySQL database? I'm pulling information from a private school's "stock" database and need to fill in a table with information such as the date of the stock, the last sale price of the stock, the change price, change price %, and volume. I know how to pull this information manually but have no idea how to fill a table with the appropriate information.

I'm guessing loops have to come in to play? Can someone steer me into the right direction?

[Gel]

http://www.w3schools.com/php/php_mysql_select.asp

[FreddieBambino]

Dynamic table in what sense, do you mean rows or columns or both?

For example:

<?php
$db = insert your database credentials here.
query = ("select mommy, daddy, sister, brother from table");
$result = $db -> query($query);
$counter = $result -> num_rows;
if($counter > 0){

   echo "<table>";
   for($i = 0; $i < $counter; $i++){ 
       echo "<tr>";
       $row = $result -> fetch_assoc();
        echo "<td>".$row['mommy']."</td>;
echo "<td>".$row['daddy']."</td>;
echo "<td>".$row['sister']."</td>;
echo "<td>".$row['brother']."</td>;
echo "</tr>";
   }
   echo "</table>;
}
else{
  echo "No results, probably something wrong with your query";
}

[Gel]

did you get the output the way you want???

[yongj]

did you get the output the way you want???

Haven't tried the suggested code above but I will definitately give it a shot! I've been so swamped with studying for finals that I haven't had time to code this yet. Thanks for all the support! Daniweb peeps rock :)

[yongj]

Okay so I've gotten the table to display nicely--sorta. There are two problems that I'm facing: one, the table overflows and extends outside of my normal page layout and two, my queries are the same! In other words, the date/time (as well as all other values of the database) are the same! I'm trying to output a history of the stock quote but it just gives me line after line of the most recent history. What's wrong with my code?

<?php	

$strSymbol = @$_REQUEST["symbol"];

if(! empty($strSymbol))
{
	$db = $objDBUtil->Open();

	$query = "SELECT * FROM symbols LEFT OUTER JOIN quotes ON symSymbol=qSymbol WHERE symSymbol='{$strSymbol}' LIMIT 10";

	$result = @$db->query($query);

	if(!$result)
	{
		print "Invalid";
	}
	else
	{
		$row = @$result->fetch_assoc();
		extract($row);
	}

}
else
{
	$symName = "N/A";
	$symSymbol = "N/A";
	$qQuoteDateTime = "N/A";
	$qLastSalePrice = "0.00";
	$qNetChangePrice = "0.00";
	$qNetChangePct = "0.00";
	$qShareVolumeQty = "0";
}
?>
        
			<left><h2>Stock Symbol: <b><font color="yellow">
			<?php
				print $symSymbol;
			?>
            </font></b></h2></font></left>
<?php

$counter = $result -> num_rows;
if($counter > 0)
{
	print "<table width=580 border=0 cellspacing=0 cellpadding=0 align=center>";
	print "<tr class=tblHead bgcolor='blue'><td align=center colspan=2><b><font color='yellow'>$symName</font></b></td></tr>";
	print "<table class=tblHead2 width=580 border=5 cellpadding=0 cellspacing=0 rules=rows frame=below bordercolor=black bordercolordark=white align=center>";
	print "<tr><td width=10 align=right>&nbsp;</td>";
	print "<td width=75 align=center><b>Date</b></td>";
	print "<td width=85  align=right><b>Last</b></td>";
	print "<td width=85  align=right><b>Change</b></td>";
	print "<td width=85  align=right><b>% Chg</b></td>";
	print "<td width=90  align=right><b>Volume</b></td></tr></table>";
	
	print "<div STYLE='width:580;height:200;overflow:auto'>";
	print "<table class=tblStyle width=580 border=1 cellpadding=0 cellspacing=0 rules=rows frame=below bordercolor='green' bordercolordark=white align=center>";
	for($i = 0; $i < $counter; $i++)
	{
		echo "<tr>";
		$row = $result -> fetch_assoc();
		print "<td width=10>&nbsp;</td>";
		print "<td width=65 align=left>$qQuoteDateTime</td>";
		print "<td width=75 align=right>$qLastSalePrice</td>";
		print "<td width=75 align=right><font color=red>$qNetChangePrice</font></td>";
		print "<td width=75  align=right><font color=red>$qNetChangePct</font></td>";
		print "<td width=80  align=right>$qShareVolumeQty</td>";
		print "</tr>";
	}
	
	print "</table>";
}
else
{
	print "No results";
}
?>

[FreddieBambino]

Put this line under your query at line 10:
echo $query;

This will display the query on the screen. Copy them and run them through your sql interface(for example phpmyadmin) and see what results you get. If its not the same as you get on the screen then there is something wrong with how you display the results I think.

I have never used that extract function, does it create variables named after the columns they are fetching data from or something?

Try printing the values in the row array directly on the screen like I did, for example
echo "<td>".$row."</td>;

[yongj]

Put this line under your query at line 10:
echo $query;

This will display the query on the screen. Copy them and run them through your sql interface(for example phpmyadmin) and see what results you get. If its not the same as you get on the screen then there is something wrong with how you display the results I think.

I have never used that extract function, does it create variables named after the columns they are fetching data from or something?

Try printing the values in the row array directly on the screen like I did, for example
echo "<td>".$row."</td>;

Extract does it what it sounds like: extract each row of its data values. So instead of having to type $row. you can simply type the variable itself: $column_name. But I got that figured out, the only thing I'm stuck with is the table overflowing under my page. How can I code it so that it automatically creates a scrollbar-embedded table once it begins to overflow?

[FreddieBambino]

Scrollbar embedded table?

I never seen that, not unless you are printing out the table inside and html frameset. If you have fixed size design(for example background height etc), then what you would want to do is to count in the loop how many rows of table have been populated and have page counters(page 1, 2, 3, 4 etc) to deliminated between the results. Then you use the combination of page number and results per page to set a new offset in the database query.