PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!
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:
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');
}
}
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
<?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');
}
?>
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):
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');
}