MySQL records into static html table

noah_count

For my first project, I'm help our Dispatch Dept. track driver and field tech assignments.
I have a table with two rows and four columns and I need to place the correct field tech values each into their specific row and column.
I can extract the MySQL data into a new row each time with a while statement and it works.

while ($row = mysql_fetch_assoc($result)) {
if ($row['Assign'] == 'troubleshoot') {
echo "<tr><th width='10%'>TROUBLE</th>";
if ($row['Ft_location'] == 'ttc'){
echo "<td>{$row['Tech']}</td><td> </td><td> </td><td> </td>";}
elseif ($row['Ft_location'] == 'otc')
{
echo "<td> </td><td>{$row['Tech']}</td><td> </td><td> </td></tr>";
}
elseif ($row['Ft_location'] == 'ovid')
{
echo "<td> </td><td> </td><td>{$row['Tech']}</td><td> </td></tr>";
}}
etc., etc.

but I don't want to build a new row for each MySQL record. What am I missing or not understanding?

Thanks,
Noah Count

CoderDan

Well, your code snippet is incomplete - missing a closing brace, but assume this is just the start.
Trying to figure out what you want, and it sounds like you want the row results to become columns, if so, your query will need tweaking.
If you can post table structures and sample data, and how you'd like the html to appear, shouldn't be too hard to figure out if it's possible and how to do it.

ixalmida

First - when you post, please align your curly braces and enclose your code in the PHP tags, as shown below:

while ($row = mysql_fetch_assoc($result))
  {
  if ($row['Assign'] == 'troubleshoot')
    {
    echo "<tr><th width='10%'>TROUBLE</th>";
    if ($row['Ft_location'] == 'ttc')
      {
      echo "<td>{$row['Tech']}</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td>";
      }
    elseif ($row['Ft_location'] == 'otc')
      {
      echo "<td>&nbsp;</td><td>{$row['Tech']}</td><td>&nbsp;</td><td>&nbsp;</td></tr>";
      }
    elseif ($row['Ft_location'] == 'ovid')
      {
      echo "<td>&nbsp;</td><td>&nbsp;</td><td>{$row['Tech']}</td><td>&nbsp;</td></tr>";
      }
    }

Secondly, you should primarily order the data using your MySQL query. Sorting via PHP isn't nearly as efficient.

Example: If you need to sort by location, you might add this to your SQL query (usually at the end):

"ORDER BY Ft_location"

-- or, if you need to sort on multiple fields, try:

"ORDER BY Ft_location, Tech_name, etc".

noah_count

Thanks for being so quick to reply.
Here's a quick example of what I'm looking to end up with:

<p><table border='1' width='100%'>
<tr>
<th></th>
<th width='25%'>TTC</th>
<th width='25%'>OTC</th>
<th width='25%'>OVID</th>
<th width='25%'>N</th>
</tr>
<tr><th width='10%'>TROUBLE</th>
<td>John</td><td> </td><td>Paul</td><td> </td>
<tr><th width='10%'>ORDERS</th>
<td> </td><td>George</td><td> </td><td>Ringo</td>

I'm not too concerned with missing brackets; I just copied the coding from my test page and missed one. The code I have on my test page works; it's the concept and the design that I'm struggling with.

CoderDan

also, html can go inside CODE tags 🙂
All we need now is your table structure and the sample data that would result in the above html

noah_count

Here's a quick sample of the data from phpMyAdmin:

Id_num Ft_date Assign Ft_location Tech
18 2009-04-30 orders n Ringo
15 2009-04-30 troubleshoot ovid Paul
16 2009-04-30 orders otc George
21 2009-04-30 troubleshoot ovid John

When I parse the data I use the Ft_date field to find only the current day's results.

CoderDan

OK, so you want to pull location, problem and tech for a day, and display the result as columns instead of rows.
I'd tend towards puling the data the same as you do, but using divs floated left to display the data and skipping tables. I find tables are best for a set number of columns and variable number of rows.
as alternate #1 (php side) you can pull all the data, load it into a temporary array with the desired structure, then use that array to draw the table.
as alternate #2 (mysql side) I don't have the time just yet to figure out your query, I'll let someone else. If noone comes up with the query, and the solutions I mentioned don't suit you, I'm sure to have time later to figure the sql out.

djjjozsi

this view could be solved with associated arrays.

$col=array("otc","aaa","bbb","ccc"); //fill with real values!

You then make the row headers:

$row=array("order", "troubleshoot");  //fill with real values!

after you query down the results, save the values into associated arrays:

$var=array();
while ($row = mysql_fetch_assoc($result))
{
$assign=$row['Assign'];
$location=$row['Ft_location'];
$var[$assign][$location][]=$row['Tech']; // fill the $var array
}

now, lets use double foreach, to print the wanted output!

<?php
print "<table border=\"1\">"; // change 

print "<tr>";
print "<td> - </td>";
foreach( $col AS $c ) {
    print "<td>$c</td>";
} 
print "</tr>";

foreach( $row as $r ) {
    print "<tr><td>$r</td>";

foreach( $col AS $c ) {
    if ( !empty( $var[$c][$r] ) )
        printf( "<td>%s</td>" , implode( "<br />" , $var[$c][$r] ) );
    else
        print "<td> - </td>";
} 
print "</tr>";
} 

print "</table>";

?>

You see implode, becouse more then one event allowed into one cell. For the final structure and its better understanding,dump its values:

<?php
print "<pre>";
print_r($var);
print "</pre>";
?>

noah_count

Thanks. I was knew associative arrays must be involved in some way. I will try out your current suggestions
Thanks again.
Noah Count

noah_count

Hi,
I've implemented the changes you suggest. For some reason I'm not getting the results inserted into the table. The print_r shows that the data is in the array. The nested foreach doesn't seem to work. I'm posting my entire test script as I don't know where I made my mistakes.
Thanks,
Noah Count

<?php
fieldtech();
function fieldtech () {
// connect to db

$server = "localhost:3306";
$username = "root";
$passwd = "p01f1ck";

$connect = mysql_connect($server, $username, $passwd);
if (!$connect)
{
die('Could not connect: ' . mysql_error());
}

//Fetch the results

mysql_select_db("Dispatch_Web", $connect)or die ('Error while connecting to database: ' . mysql_error());
$sql = "SELECT Assign, Ft_location, Tech FROM Fieldtech WHERE Ft_Date = CurDate() ORDER by Assign, Ft_location, Tech";
$result = mysql_query($sql);
if (!$result)
{
echo ("<p>CONNECTION ERROR:" . mysql_error() . "</p>");

}

$col=array("TTC","OTC","OVID","N");

$t_row=array("TROUBLE", "ORDERS");

$var=array();
while ($row = mysql_fetch_assoc($result))
{
$assign=$row['Assign'];
$location=$row['Ft_location'];
$var[$assign][$location][]=$row['Tech']; // fill the $var arrary

}
//Print the headers

print "<table border=\"1\" width=\"80%\">"; // change

print "<tr>";
print "<th> </th>";
foreach( $col AS $c )
{
print "<th width=\"25%\">$c</th>";
}
print "</tr>";

//Print the results
foreach( $t_row AS $r )
{
print "<tr><td>$r</td>";

foreach( $col AS $c )
{
    if ( !empty( $var[$c][$r] ) )
        printf( "<td>%s</td>" , implode( "<br />" , $var[$c][$r] ) );
    else
        print "<td>&nbsp</td>";
}
print "</tr>";

}
// end the table

print "</table>";

// data test
print "<pre>";
print_r($var);
print "</pre>";

}