array wrangling

[satre]

Hi, It's been a while since I've done this, sorry. I have a huge 3 column, 3600+ row MySQL table (sorry, has to be that way because of interface with another system) that I only want a little data from in snippets here and there and I want to output it to a smaller 12 column table with the most useful info in it (rows are determined by "SERIESNUM" in the MySQL data. I can get the info for each column quite easily, but can't figure out how to put the columns into a table. Obviously, html outputs tables in rows, so I have to take the first value of each array and put them into the first row, the second value of each array and put them into the second row, and so on. Here's where I am so far but it's not working at all, I'm sure someone has a better (working  ) idea:

 

// get number of rows (series) from the table
$query = mysql_query("SELECT value FROM test WHERE type='SET' AND command='SERIESNUM'");

$queryfetch = mysql_fetch_array($query);

echo $queryfetch[0];

// get data for table
$qseries = mysql_query("SELECT value FROM test WHERE type='SET' AND command='SEDESC'");
$qpulseseq = mysql_query("SELECT value FROM test WHERE type='SET' AND command='PSEQ'");
$qimgopt = mysql_query("SELECT value FROM test WHERE type='SET' AND command='IOPT'");
$qplane = mysql_query("SELECT value FROM test WHERE type='SET' AND command='PLANE'");
$qfov = mysql_query("SELECT value FROM test WHERE type='SET' AND command='FOV'");
$qslthick = mysql_query("SELECT value FROM test WHERE type='SET' AND command='SLTHICK'");
$qslspace = mysql_query("SELECT value FROM test WHERE type='SET' AND command='SPC'");
$qfreq = mysql_query("SELECT value FROM test WHERE type='SET' AND command='MATRIXX'");
$qphase = mysql_query("SELECT value FROM test WHERE type='SET' AND command='MATRIXY'");
$qtr = mysql_query("SELECT value FROM test WHERE type='SET' AND command='TR'");
$qte = mysql_query("SELECT value FROM test WHERE type='SET' AND command='TE'");
$qflipang = mysql_query("SELECT value FROM test WHERE type='SET' AND command='FLIPANG'");

// double check to make sure the number of rows will equal the number of series

$check = mysql_numrows($series);
if ($query != $check){
exit "rows not equal to data length";
    'Error: ' . mysql_error() . '</p>');
}

// build table with loop set to number of results (series) there are
echo ('<table>');
$i=0;
while ($i<$query){
$series = mysql_fetch_array($qseries);
$pulseseq = mysql_fetch_array($qpulseseq);
$imgopt = mysql_fetch_array($qimgopt);
$plane = mysql_fetch_array($qplane);
$fov = mysql_fetch_array($qfov);
$slthick = mysql_fetch_array($qslthick);
$slspace = mysql_fetch_array($qslspace);
$freq = mysql_fetch_array($qfreq);
$phase = mysql_fetch_array($qphase);
$tr = mysql_fetch_array($qtr);
$te = mysql_fetch_array($qte);
$flipang = mysql_fetch_array($qflipang);
echo ('<tr>');
echo ('<td>');
echo mysql_result($series, $i);
echo ('</td>');
echo ('<td>');
echo mysql_result($pulseseq, $i);
echo ('</td>');
echo ('<td>');
echo mysql_result($imgopt, $i);
echo ('</td>');
echo ('<td>');
echo mysql_result($plane, $i);
echo ('</td>');
echo ('<td>');
echo mysql_result($fov, $i);
echo ('</td>');
echo ('<td>');
echo mysql_result($slthick, $i);
echo ('</td>');
echo ('<td>');
echo mysql_result($slspace, $i);
echo ('</td>');
echo ('<td>');
echo mysql_result($freq, $i);
echo ('</td>');
echo ('<td>');
echo mysql_result($phase, $i);
echo ('</td>');
echo ('<td>');
echo mysql_result($tr, $i);
echo ('</td>');
echo ('<td>');
echo mysql_result($te, $i);
echo ('</td>');
echo ('<td>');
echo mysql_result($flipang, $i);
echo ('</td>');
echo ('</tr>');

$i=$i++;
}

echo ('</table>');
?>

[Dennis1986]

$check = mysql_numrows($series);

At what point is $series set?

Shouldn't it be $qseries?

[Psycho]

Something is missing from that code. You are using the variable $series as a database result, but it is not set any where.

 

Give this a try. I think it is a little more organized and flexible (not tested as I don't have your database)

<?php
  
$commands = array('SEDESC', 'PSEQ', 'IOPT', 'PLANE', 'FOV', 'SLTHICK',
                  'SPC', 'MATRIXX', 'MATRIXY', 'TR', 'TE', 'FLIPANG');
  
//Query ALL the records
$dataAry = array();
$query = "SELECT command, value FROM test WHERE type='SET' AND command IN ('" . implode("', '", $commands) . "')"
$result = mysql_query($query);
while($record = mysql_fetch_assoc($result))
{
    $dataAry[$record['command']][] = $record['value'];
}
   
//Determine the length of the longest list
$maxLength = 0;
foreach($commands as $command)
{
    if(count($dataAry[$command])>$maxLength)
    {
        $maxLength = count($dataAry[$command]);
    }
}
  
//create the table html
$tableHTML = "<table>\n";
//Create headers
$tableHTML .= "<tr>\n";
foreach($commands as $command)
{
    $tableHTML .= "<th>{$command}</th>\n";
}
$tableHTML .= "</tr>\n";
//Create data output
for($idx=0; $idx<$maxLength; $idx++)
{
    $tableHTML .= "<tr>\n";
    foreach($commands as $command)
    {
        $value = (isset($dataAry[$command][$idx])) ? $dataAry[$command][$idx] : '';
        $tableHTML .= "<td>{$value}</td>\n";
    }
    $tableHTML .= "</tr>\n";
}
$tableHTML .= "<table>\n";
  
?>

[Dennis1986]

Don't forget to print the HTML output

[Psycho]

Don't forget to print the HTML output

 

Which should not logically be done within the body of the script. The logic and the output should be separate.

[Dennis1986]

Don't forget to print the HTML output

 

Which should not logically be done within the body of the script. The logic and the output should be separate.

Looking at the original code provided by the OP I doubt he/she is seperating business logic from presentation

At least if you're going to do the work for the OP, you might as well make it work as intended for the OP's enviroment.

[satre]

Thank you so much for answering my post, and the hints on separating logic and output! I will try this code out this morning and see where it takes me. Obviously I have forgotten everything I learned 3+ years ago since I last used php/MySQL. Can you point me to where I can read about how to set a variable (re: comments on how $series is not set). Thanks!

[satre]

This code works and is truly elegant mjdamato! It's so elegant, as a matter of fact, it's going to take me a while to dissect

 

Thank you so much for your help!

[Psycho]

You are welcome. I have updated the script with expanded comments. I suspect the most difficult part to understand is within the for() loop at the end.

 

<?php
//Create an array of all the "commands"
//These should be in the order in which you want the columns displayed
$commands = array('SEDESC', 'PSEQ', 'IOPT', 'PLANE', 'FOV', 'SLTHICK',
                  'SPC', 'MATRIXX', 'MATRIXY', 'TR', 'TE', 'FLIPANG');
  
//Run a single query to get ALL the records for ALL commands
$dataAry = array();
$query = "SELECT command, value FROM test WHERE type='SET' AND command IN ('" . implode("', '", $commands) . "')"
$result = mysql_query($query);
//Reads the results of the query and put into a multi-dimensionals array
//With the command as the primary key and the values as subarray elements
while($record = mysql_fetch_assoc($result))
{
    $dataAry[$record['command']][] = $record['value'];
}
   
//Determine the length of the longest "command" list in the array
//and set the varaible $maxLength to that
$maxLength = 0;
foreach($commands as $command)
{
    if(count($dataAry[$command])>$maxLength)
    {
        $maxLength = count($dataAry[$command]);
    }
}
  
//create the table html
$tableHTML = "<table>\n";
//Create headers by iterating through the command list above
$tableHTML .= "<tr>\n";
foreach($commands as $command)
{
    $tableHTML .= "<th>{$command}</th>\n";
}
$tableHTML .= "</tr>\n";
  
//Create data output
//Run a loop to iterate through all the indexes for the
//values using the $maxLength value defined above to ensure
//all values are displayed (some "commands" may have
//less values. First time through loop will get the value
//at index 0 for each command, second pass will get the
//values at index 1, etc.
for($idx=0; $idx<$maxLength; $idx++)
{
    //Open the table row
    $tableHTML .= "<tr>\n";
    //Iterate through each command
    foreach($commands as $command)
    {
        //This is a ternary operator (i.e. If/Else shorthand)
        //$value is set to the current index of the current command IF
        //that value is set Otherwise, $value is set to an empty string
        $value = (isset($dataAry[$command][$idx])) ? $dataAry[$command][$idx] : '';
        //Create the table cell for the current command/index value
        $tableHTML .= "<td>{$value}</td>\n";
    }
    $tableHTML .= "</tr>\n";
}
$tableHTML .= "<table>\n";
  
?>