Help with presenting data from an sql table

[thowe26]

Hello

I am a complete newbie. I have tried a few snippets without success.

I am trying to present the results from an sql query into a web page. The query has multiple columns and multiple rows

I can print my headers with:

// printing table headers

for($i=0; $i<$fields_num; $i++)

{

$field = mysqli_fetch_field($result);

echo "<td>{$field->name}</td>";

}

echo "</tr>\n";

 

But having problems working out how to print my data

This is what I have from this forum but its only for one column and I cannot work out how to make work for multiple columns

 

 

<table cellspacing="3" cellpadding="3">

<?php

$query = "SELECT MachineName ,BedSizeX, Weight FROM machines";

$result = mysql_query($link, $query) or die("There was a problem with the SQL query: " . mysql_error()); 

if($result && mysql_num_rows($result) > 0)

{

    $i = 0;

    $max_columns = 3;

    for($row = mysql_fetch_array($result))        

   {

       // make the variables easy to deal with

       extract($row);

 

       // open row if counter is zero

       if($i == 0)

          echo "<tr>";

 

       // make sure we have a valid product

       if($product != "" && $product != null)

          echo "<td>$product</td>";

    

       // increment counter - if counter = max columns, reset counter and close row

       if(++$i == $max_columns) 

       {

           echo "</tr>";

           $i=0;

       }  // end if 

   } // end for

} // end if results

 

// clean up table - makes your code valid!

if($i > 0)

{

    for($j=$i; $j<$max_columns;$j++) echo "<td> </td>";

   echo '</tr>';

}

 ?>

</table>

 

Thanks in advance

[jcbones]

First, lets migrate you over to PDO.  It is faster, and more secure than the old mysql functions.

First Set up the connection: I like to store this in a script to include, so if I change the connection, I will only have to change the details in one place.

config.php

 

//Change these definitions as needed for your database connection.
define('DSN','mysql:host=localhost;dbname=test'); //pdo dsn
define('USER','root'); //pdo user
define('PW',''); //pdo password
 
//connect to the database:
$pdo = new PDO(DSN,USER,PW,array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC)); //setting the default fetch mode to associative array.

Edited May 31, 2015 by jcbones

[jcbones]

Now if you need to add client supplied parameters to the query, in a where clause, then the process is a little different.

Also note, there is no error checking in this script.  If something fails, you will have no way of handling it.
Post back if you need more help.

 

script.php

 

<?php
require_once 'config.php';  //this gets the database connection.
 
$table = '<table cellspacing="3" cellpadding="3">'; //start our table, I usually hold the building in a variable, for output later.
 
$query = "SELECT MachineName ,BedSizeX, Weight FROM machines"; //This is the query we send to mysql.
$i = 0;  //This is a control variable.
//since we DON'T have client parameters to add to the query, we can use a standard query.
foreach($pdo->query($query) as $row) { //since we use a standard query, you can call it in a foreach to get each row.
if($i == 0) { //if our control hasn't been used.
$keys = array_keys($row); //we get the column names.
$table .= '<tr><th>' . implode('</th><th>',$keys) . '</th></tr>'; //and add them as table headers.
++$i; //then increment our control so that this block will not run again.
}
$table .= '<tr><td>' . implode('</td><td>',$row) . '</td></tr>'; //then we populate the fields of the table with our data.
}
$table .= '</table>'; //Then we end the table.
 
//since our table is in a variable, we can now move it wherever we want on our page.
 
 
 
echo $table;

 

 

[thowe26]

Hello again

Thanks for your quick response.

I have tried to use this code but the page comes back with a ...Invalid argument supplied for foreach()...

I think i have had this error when nothing is retrieved from the database?

The definitions I have plugged into your code are exactly the ones I used (and got some kind of response) with my previous code.

just fyi my Database is called machines as is the Table I am trying to query (sorry!)

is it something I have not copied correctly in the $pdo statement? although I have gone thru it with a fine toothpick.

To keep it simple I have put your connection code and the table code into one simple file.:

do you have a snippet of error checking code I could plug in to check the connection etc.

Thanks for your help - appreciated.

I set myself the task of learning php/mysql and it looks like I now need to learn pdo.

 

<?php

//definitions for the connection:

define('DSN','mysql:host=localhost;dbname=machines'); //pdo dsn

define('USER','root'); //pdo user

define('PW','xxx'); //pdo password

 

//connect to the database:

$pdo = new PDO(DSN,USER,PW,array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC)); //setting the default fetch mode to associative array.

 

$table = '<table cellspacing="3" cellpadding="3">'; //start our table, I usually hold the building in a variable, for output later.

 

$query = "SELECT MachineName ,BedSizeX, FROM machines"; //This is the query we send to mysql.

 

$i = 0;  //This is a control variable.

 

foreach($pdo->query($query) as $row) 

{ //since we use a standard query, you can call it in a foreach to get each row.

if($i == 0)

{

$keys = array_keys($row); //we get the column names.

$table .= '<tr><th>' . implode('</th><th>',$keys) . '</th></tr>'; //and add them as table headers.

++$i; //then increment our control so that this block will not run again.

}

$table .= '<tr><td>' . implode('</td><td>',$row) . '</td></tr>'; //then we populate the fields of the table with our data.

}

 

$table .= '</table>'; //Then we end the table.

 

echo $table;

?>

 

[boompa]

Good for you for moving to PDO. Now, read the manual page for PDO's query() method. Note the following in in the Return Values section"

 

 

PDO::query() returns a PDOStatement object, or FALSE on failure.

.

 

 

You do not account for the fact that your query can fail before you go trying to use it in your foreach loop. I note also that you can't necessary be blamed for this, because the example provided in the documentation does the same. Better would be something like the following, at least until you're sure you've got the query right:

 

$result = $pdo->query();
if ($result === false) {
    die(print_r($pdo->errorInfo()));
}
 
// Successful query
foreach($result as $row) {
    // Do your output
}

[mac_gyver]

if you enable pdo exceptions, you can catch and handle all the database statement errors in one place without having to add logic for each individual database statement.

[thowe26]

Hello again

The attached file PrinterMysql is my original attempt, wrong and messy but it does retrieve data, so i know that bit is right?

PrintersMysql.php

 

Using the same connection definitions in PrinterPDO I pull a blank, no data.

the errorinfo shows: ..Array ( [0] => [1] => [2] => ) 1..

PrintersPDO.php

 

Help

 

 

 

[mac_gyver]

the example that boompa posted wasn't copy/paste code for you to use. it was an example for you to look at, learn from, and modify your code to make use of. the line - $result = $pdo->query(); needs to include your $query variable as a parameter. you also need to change the foreach() statement to use $result, rather than to run the query again.