Page 1 of 1

mysql array into table issue

Posted: Tue Jan 23, 2007 12:46 am
by afbase
ok well i'm just trying to get my mysql array into a table for presentation on a webpage. However, I'm having the hardest time creating a table "foreach" array.

Don't mind the db_set_active () and the db_query(), its a simple modification of php statement for accessing another DB other than the default db for drupal coding(http://drupal.org/node/18429).

My goal is to have this array example created into an html table like so:
Array ( [0] => NX [ticker] => NX [1] => Quanex Cp [companyname] => Quanex Cp [2] => 0.14 [dividend] => 0.14 [3] => 450.61 [curass] => 450.61 [4] => 9.10 [multiplier] => 9.10 [5] => 1.82 [bookval] => 1.82 [6] => 130.68 [long_term_debt] => 130.68 [7] => 208.41 [total_cur_liabilities] => 208.41 [8] => 29.8 [past_5_earnings] => 29.8 [9] => 12.5 [future_5_earnings] => 12.5 [10] => 527.72 [annsales] => 527.72 [11] => 2007-01-21 03:01:55 [last_update_time] => 2007-01-21 03:01:55 [12] => 0 [rank] => 1 [13] => 3011 [stock_id] => 3011 [14] => [price] => [15] => 0 [score] => 0 [16] => 1 )

-----------------------------------------------------
|Company Name: | [companyname] Value|
|Ticker: | [ticker] Value |
|P/E Ratio: | [multiplier] Value |
-----------------------------------------------------
FYI I plan on using most of the entries into html my table, so don't worry about truncating the SQL statement.

Code: Select all

function coldowl_mysql_retrieve(){
db_set_active('intelligent');
$mysql_query = "SELECT * , COUNT( stock_id ) AS rank FROM curldata WHERE annsales >= '100' AND dividend >0 AND multiplier <=17 AND multiplier >0 AND bookval >0 AND multiplier * bookval <= 22.5 AND past_5_earnings >= 16.5 AND curass / total_cur_liabilities >=2 GROUP BY 'stock_id' ORDER BY 'multiplier' DESC;";
$returnval = db_query($mysql_query);
$a_record = mysql_fetch_array($returnval);
print_r($a_record);

foreach ($a_record as $key => $value) {
	print "<table><tr><td>Company Name</td><td>".$value['[companyname]']."<td></tr><tr><td>Ticker</td><td>".$value['[ticker]']."</td></tr><tr><td>Multiplier</td><td>".$value['[multiplier]']."</td></tr></table>";
}




db_set_active('default');
}
}

Posted: Tue Jan 23, 2007 1:16 am
by volka
As you can see from your print_r($a_record); mysql_fetch_array returns an array representing one record. So your foreach is iterating over the fields of this one record, not over all available records.
Take a look at the examples at http://de3.php.net/mysql_fetch_array

Posted: Tue Jan 23, 2007 1:48 am
by dibyendrah
A simple modification to your code :

Code: Select all

<?php
function coldowl_mysql_retrieve(){
	db_set_active('intelligent');
	$mysql_query = "SELECT * , COUNT( stock_id ) AS rank FROM curldata WHERE annsales >= '100' AND dividend >0 AND multiplier <=17 AND multiplier >0 AND bookval >0 AND multiplier * bookval <= 22.5 AND past_5_earnings >= 16.5 AND curass / total_cur_liabilities >=2 GROUP BY 'stock_id' ORDER BY 'multiplier' DESC;";
	$returnval = db_query($mysql_query);
	//$a_record = mysql_fetch_array($returnval);

	while($value = mysql_fetch_array($returnval)){
		print "<table><tr><td>Company Name</td><td>".$value['[companyname]']."<td></tr><tr><td>Ticker</td><td>".$value['[ticker]']."</td></tr><tr><td>Multiplier</td><td>".$value['[multiplier]']."</td></tr></table>";
	}
db_set_active('default');

}
?>

Posted: Tue Jan 23, 2007 3:37 am
by afbase
Thanks again Volka, i got it to work following one of the examples from php.net.
For any forum readers curious to the resulting code, here it is (sorry if it is messy):

Code: Select all

function coldowl_mysql_retrieve(){
db_set_active('intelligent');
$mysql_query = "SELECT * , COUNT( stock_id ) AS rank FROM curldata WHERE annsales >= '100' AND dividend >0 AND multiplier <=17 AND multiplier >0 AND bookval >0 AND multiplier * bookval <= 22.5 AND past_5_earnings >= 16.5 AND curass / total_cur_liabilities >=2 GROUP BY 'stock_id' ORDER BY 'multiplier' DESC;";
$returnval = db_query($mysql_query);
print_r($a_record);
$display = 4;
$cols = 0;
while($fetched = mysql_fetch_array($returnval)){
   if($cols == 0){
   }
       echo "<table><tr>\n";
      // put what you would like to display within each cell here
   $quick_ratio = $fetched['curass'] / $fetched['total_cur_liabilities'];
   $annual = 1000000 * $fetched['annsales'];
   echo "<td width=50% bgcolor='ffffd7'>Company Name:</td><td width=50% bgcolor='ffffd7'>".$fetched['companyname']."</td></tr><tr><td width=50% bgcolor='ffffd7'>Ticker:</td><td width=50% bgcolor='ffffd7'>".$fetched['ticker']."</td></tr><tr><td width=50% bgcolor='ffffd7'>Annual Sales:</td><td width=50% bgcolor='ffffd7'>".$annual."</td></tr><tr><td width=50% bgcolor='ffffd7'>Current Assets over Current Liabilities Ratio:</td><td width=50% bgcolor='ffffd7'>".$quick_ratio."</td></tr><tr><td width=50% bgcolor='ffffd7'>Dividend:</td><td width=50% bgcolor='ffffd7'>".$fetched['dividend']."</td></tr><tr><td width=50% bgcolor='ffffd7'>P/E Ratio:</td><td width=50% bgcolor='ffffd7'>".$fetched['multiplier']."</td></tr><tr><td width=50% bgcolor='ffffd7'>Price/Book Value:</td><td width=50% bgcolor='ffffd7'>".$fetched['bookval']."</td></tr><tr><td width=50% bgcolor='ffffd7'>Date of Data Record Update:</td><td width=50% bgcolor='ffffd7'>".$fetched['last_update_time']."</td></tr></table>\n";
  }
db_set_active('default');
}

@ dibyendrah

Posted: Tue Jan 23, 2007 5:07 pm
by afbase
thanks dibyendrah for your contribution! i didn't see your post until now

Re: @ dibyendrah

Posted: Wed Jan 24, 2007 4:30 am
by dibyendrah
afbase wrote:thanks dibyendrah for your contribution! i didn't see your post until now
You're welcome. Yes, usually in my free time at office, I rush into devnetwork forum.