Outputting Mysql array in PHP

Question

I am creating a quoting system for our company and I have come into a small problem. Here's the rundown; I have two tables, one named data, the other named zips. The client enters their gender, age, if they use tobacco, zipcode, and state.

Here's the data table:

Here's the zips table:

I've queried the two tables to join based on zip lookup code. Here's the query I'm using:

SELECT data.Monthly_Rate, zips.ZIP_LOOKUP_CODE AS Expr1, zips.State, zips.County, zips.City, zips.Zipcode
FROM data INNER JOIN
zips ON data.ZIP_LOOKUP_CODE = zips.ZIP_LOOKUP_CODE
WHERE (zips.Zipcode = '$zipcode') AND 
(data.Company_Old LIKE '%Blue Cross%') AND
(data.Plan IN ('A','F','F (High)','G','N')) AND 
(data.Gender = '$gender') AND 
(data.Age = '$age') AND 
(data.Tobacco = '$tobacco') AND 
(data.State = '$state');

Now what I want to do is output this in php in a table. The problem I'm coming across is there are multiple Plan letters under the "Plan" column. I only want to return 5 different ones, but the issue is some companies don't offer all 5 of those plans, so in this instance when I'm outputting the array, the rate for a certain plan will line up in the wrong column in the table.

Basically, I don't know how to align the data correctly in the specific column it should be in. In a scenario where a plan A, F, and N are only available for the company, it will fill the first three fields, and the rate for plan N will not line up in the plan N column.

I need to figure out how to associate a given rate with the Plan letter, and output it correctly in the table. In the instance below Plan A rate is $111.40, Plan F is 135.37 and Plan N rate is $96.52. As you see, the Plan N rate is not aligning correctly. How do I go about fixing this?

Table output:

This is what I'm using to output the array in PHP:

while($row = mysql_fetch_array($PlanRates))
{   
$Plan = "$" . number_format($row['Monthly_Rate'], 2, '.', '');
echo "<td align='center'>$Plan</td>";           
}
echo "</tr>";
mysql_free_result($PlanRates);

air4x · Accepted Answer · 2012-10-05 14:51:58Z

2

Retrieve plan as well in your SQL query

SELECT data.Monthly_Rate, data.Plan ...

Then use the below code

while($row = mysql_fetch_array($PlanRates)) {   
  $Plans[$row['Plan']] = "$" . number_format($row['Monthly_Rate'], 2, '.', '');
}
echo "<td align='center'>".(isset($Plans['A']) ? $Plans['A'] : '')."</td>"; 
echo "<td align='center'>".(isset($Plans['F']) ? $Plans['F'] : '')."</td>"; 
echo "<td align='center'>".(isset($Plans['F (High)']) ? $Plans['F (High)'] : '')."</td>"; 
echo "<td align='center'>".(isset($Plans['G']) ? $Plans['G'] : '')."</td>"; 
echo "<td align='center'>".(isset($Plans['N']) ? $Plans['N'] : '')."</td>";

You shouldn't be mixing your data processing logic and your presentation this way though. You should use a function from which to run the query and return the data as an array. Then iterate over the array in the html markup.

edited Oct 5, 2012 at 14:51

answered Oct 5, 2012 at 14:46

air4x

5,6831 gold badge26 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Eric Over a year ago

Mihai, thanks for that. It worked. What should I do in the instance if none of the 5 plans are retrieved? Right now, if none are retrieved for that specific company, blank <td>s are shown. I don't want to show blank tds, just not display anything at all if all 5 plans are not available.

air4x Over a year ago

Then you must move the while loop to before you start the <tr>. Then put the <tr> and all the <td> inside something like if (sizeof($Plans)>0) { echo "<tr>"; echo "<td .... }

Collectives™ on Stack Overflow

Outputting Mysql array in PHP

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related