PHP, Select, Print Table: i have an issue

[Archangelos]

Hi guys,

 

i have a db in mysql and a script in php gets data from the db and prints a table. The script is the following

 

$connection = mysql_connect("localhost", "Litsa", "integratorgold");
echo $connection;

 mysql_select_db("mattrix");
$data = mysql_query("SELECT fNo, FieldA, FieldB FROM matin ORDER BY fNo DESC");


 Print "<table border cellpadding=3>"; 
 Print "<tr><th>No</th>";
 Print     "<th>Field A</th>";
 Print     "<th>Field B</th>";
 Print "</tr>";

 while($info = mysql_fetch_array( $data )) 
 {  Print "<tr>"; 
    Print "<td>".$info['fNo'] . "</td> ";
    Print "<td>".$info['FieldA'] . "</td> "; 
    Print "<td>".$info['FieldB'] . "</td> "; 
    Print "</tr>";     } 
 Print "</table>"; 

Obviously, it's not my invention. The above code appears in many tutorials and although being a rookie i made it work. The file "pict-single.JPG" shows the output of the script. Nothing special, it works as expected.

 

 

The file "pict-double.JPG" shows what i need to do. The $data variable contains the results of the query. It is an array containing the output records sorted as requested. The while part of the code navigates from the first record of the #data variable to the last.Is it possible to address a specific record in $data?

 

 

 

[vectorialpx]

You should have a primary key and that should be your identifier.

If you don't, create one

[Archangelos]

It seems that the code above is too simple and cannot help me. It would be better if i store the data in an array. Then i can navigate through the array records. This is what i came up to but i have a problem reading the contents of the array.

$tempVar = array();
while ($row = mysql_fetch_array($queryperTV))
      {
       $tempVar[] = array($row['minNo']);
       echo $row['minNo'];//Used for debugging only. It works fine.
       echo'<br/>';
       $tempVar[] = array($row['minField1']);
       echo $row['minField1'];//Used for debugging only. It works fine.
       echo'<br/>';
       $tempVar[] = array($row['minField2']);
       echo $row['minField2'];//Used for debugging only. It works fine.
       echo'<br/>';
      }

echo 'Test'; //echoes for debugging purposes
echo'<br/>';
echo $tempVar[0];//DOES NOT WORK.
echo'<br/>';
echo $tempVar[20];//DOES NOT WORK.
echo'<br/>';

 

I check the data before i store them in the array ($tempVar) but when i attempt to retrieve them i get ArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArray.

 

Any idea?

Edited March 22, 2013 by Archangelos

[Archangelos]

Well, i found it.

The way i tried to enter the data in the array $tempVar was wrong. I changed a part of the code.

$TempPointer=0;
//store query results in array
$tempVar = array();
while ($row = mysql_fetch_array($queryperTV))
      {
       $tempVar[$TempPointer] = $row['minName'];
       $TempPointer++;
       $tempVar[$TempPointer] = $row['minTV'];
       $TempPointer++;
       $tempVar[$TempPointer] = $row['minRadio'];
       $TempPointer++;
      }

 

Now it's working. The debugging echoes are working.

echo $tempVar[0];
echo'<br/>';
echo $tempVar[20];
echo'<br/>';

 

 

They echoes return the values that originated from the query and not the annoying "Array".

[moltm4785]

So then you are good?

[Archangelos]

So far yes. The project is still in progress. I'm sure a new obstacle will appear soon.

[trq]

Maybe if you explained "what" it is your trying to do rather than "how" your trying to do it someone who knows what they are doing could offer a better solution.

 

As it stands, your code is pretty bad I'm sorry to say.

[Archangelos]

 

 

As it stands, your code is pretty bad I'm sorry to say.

I expected to receive such a comment. It's the first time I write code in php.

 

 

 

Maybe if you explained "what" it is your trying to do rather than "how" your trying to do it someone who knows what they are doing could offer a better solution.

I think i have explained what i wanted to do. However, i can write it again.

 

What do i want to do?

I want to get some data from a mySQL database and print a table.

 

It doesn't seem to be difficult, what's the problem?

Check the photos to see how i want to present the data.

 

 

The first line (after the header) has the first and the fifth record (returned by the query). The second line has the second and the sixth record. I need a way to "jump" back and forth in the records that are returned by the array.

 

The solution

Firstly, i stored the data in an array and then i accessed the data using a pointer that moved back and forth. The following picture shows the array where I stored the data returned by the query.

 

 

The next picture shows how the array pointer "jumps" back and forth in the array.

 

 

 

The code

 <?php 


// Connect to database  named "mattrix"
$connectdBmattrix = mysql_connect("localhost", "LoginName", "MyPasswd");
mysql_select_db("mattrix");

//Run query
$queryperField1 = mysql_query("SELECT minNo, minField1, minField2
                           FROM matin 
                           WHERE minField1!='None' //exclude unwanted entries
                           ORDER BY minField1");



//store query results in array
$TempPointer=0;
$tempVar = array();
while ($row = mysql_fetch_array($queryperField1))
      {
       $tempVar[$TempPointer] = $row['minNo'];
       $TempPointer++;
       $tempVar[$TempPointer] = $row['minField1'];
       $TempPointer++;
       $tempVar[$TempPointer] = $row['minField2'];
       $TempPointer++;
      }
echo 'The number of array records is ' . $TempPointer . '<br/>';

//Calculate the records returned by the query
$QueryRecords=$TempPointer/3;
echo 'That means that the records returned from the query are: ' . $QueryRecords . '<br/>';

//Calculate the table lines (excluding header)
$TableLinesMax=$QueryRecords/2;
echo 'DIV result is: ' . $TableLinesMax . '<br/>';
//Check if number of records is odd
$RoundUp=$QueryRecords%2;
//If query records are odd one more line is needed in table
$TableLinesMax=$TableLinesMax + ($RoundUp/2);
echo 'The table should have ' . $TableLinesMax . ' lines.' . '<br/>';
echo '<br/>';

///////////////
//Print table//
///////////////

//Print header
Print "<table border cellpadding=3>";//Open table
Print "<tr><th colspan=7>Sorted per Field1 value</th></tr>";

Print "<tr>";            //Open header's line
Print "<th>No</th>";     //Left column group, No (minNo)
Print "<th>Field1</th>"; //Left column group, Field1 (minField1)
Print "<th>Field2</th>"; //Left column group, Field2 (minField2)
Print "<th></th>";       //Middle column, the separator
Print "<th>No</th>";     //Right column group, No (minNo)
Print "<th>Field1</th>"; //Right column group, Field1 (minField1)
Print "<th>Field2</th>"; //Right column group, Field2 (minField2)
Print "</tr>";           //Close header's line

//Print data temporarily stored in $tempVar
$TPmax = $TempPointer; //Last value of $TempPointer is maximum.
                       //Keep it stored in $TPmax
  //Initialize variables
    $TableLines=1;
    $TempPointer=0;
    $JumpLength=($TableLinesMax*3)-2;

for ($TableLines=1; $TableLines<=$TableLinesMax; $TableLines++)
    {
     $TempPointer= ($TableLines-1)*3;
     Print "<tr>";                                   //Open data line
     Print "<td>" . $tempVar[$TempPointer] . "</td>";//Left column group, No (minNo)
     $TempPointer++;
     Print "<td>" . $tempVar[$TempPointer] . "</td>";//Left column group, Field1 (minField1)
     $TempPointer++;
     Print "<td>" . $tempVar[$TempPointer] . "</td>";//Left column group, Field2 (minField2)
     $TempPointer = $TempPointer + $JumpLength;
     Print "<td>_</td>";                             //Middle column
     if ($TempPointer<$TPmax)
        {
         Print "<td>" . $tempVar[$TempPointer] . "</td>";//Right column group, No (minNo)
         $TempPointer++;
         Print "<td>" . $tempVar[$TempPointer] . "</td>";//Right column group, Field1 (minField1)
         $TempPointer++;
         Print "<td>" . $tempVar[$TempPointer] . "</td>";//Right column group, Field2 (minField2)
        }
     Print "</tr>";                                    //Close data line
    }

//Close table
Print "</table>";

// Disconnect from database  named "mattrix"
mysql_close($connectdBmattrix);
?>

 

 

You comments please.

 

[ginerjm]

why can't you "order" the query results so that you don't have to do this jumping back and forth?

[Barand]

try

while($info = mysql_fetch_row( $result )) 
{  
    $data[] = $info;
}

 $k = count($data);
 $n = ceil($k/2);
 
 echo "<table border=1>\n";
 for ($i=0; $i<$n; $i++, $k--) {
     echo "<tr>
        <th>No. $k</th>
        <td>{$data[$i][0]}</td>
        <td>{$data[$i][1]}</td>
        <td>{$data[$i][2]}</td>\n\t";
     if (isset($data[$i+$n])) {
        $k2 = $k - $n; 
        echo "<th>No. $k2</th>
        <td>{$data[$i+$n][0]}</td>
        <td>{$data[$i+$n][1]}</td>
        <td>{$data[$i+$n][2]}</td>";
     }
     echo "</tr>\n";    
} 
echo "</table>\n";