PHP MYSQL List Data inside Table issue

Question

below is my PHP code to display all the data in my database in a table..

if I use this code below to login and select DB rather than using PDO:

 mysql_connect("localhost","root","12345678") or die(mysql_error());
   mysql_select_db("clubresults") or die(mysql_error());
   $query=mysql_query("SELECT * from events ORDER By EventID ASC") or die(mysql_error());

The output gives me a table with all the data listed correctly.

However If I use PDO.. it gives me this Error:

Warning: mysql_num_fields() expects parameter 1 to be resource, object given in C:\xampp\htdocs\clubresults\listevents.php on line 52

Warning: mysql_fetch_row() expects parameter 1 to be resource, object given in C:\xampp\htdocs\clubresults\listevents.php on line 58

Below is the full php code to grab data to and place in a table (Doesn't work) anyone able to point out where i have a mistake? because I can't seem to figure it out myself.. All suggestions are really appreciated! Thanks!

$pdo = new PDO('mysql:host=localhost;dbname=clubresults', 'root', '12345678');
        #Set Error Mode to ERRMODE_EXCEPTION.
        $pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  


   $query=$pdo->query('SELECT * from events ORDER By EventID ASC');


   <<<<<<<<<<<<<<<<THIS BIT WORKS IF NOT USING PDO>>>>>>>>>>
$numfields = mysql_num_fields($query);
       print("<table border=\"1\">\n<tr>\n");
       for ($i=0; $i<$numfields; $i++) {
          printf("<th>%s</th>\n", mysql_field_name($query,$i));
       }
       print("</tr>\n");
       while ($row = mysql_fetch_row($query)) {
          print("<tr>\n");
          for ($i=0; $i<sizeof($row); $i++) {
             printf("<td><a href=\"index.php?ID=%s\">%s</a></td>\n", $row[0],$row[$i]);
          }
          print("</tr>\n");
       }
       print("</table>\n");

mysql_num_fields is not compatible with PDO

Pekka
– Pekka

2012-05-31 01:05:30 +00:00
Commented May 31, 2012 at 1:05 — Pekka
– Pekka, Commented May 31, 2012 at 1:05
oh... what should I use then?

Tuzki
– Tuzki

2012-05-31 01:10:15 +00:00
Commented May 31, 2012 at 1:10 — Tuzki
– Tuzki, Commented May 31, 2012 at 1:10

Michael Berkowski · Accepted Answer · 2012-05-31 02:19:00Z

1

You won't be able to use mysql_num_fields() or mysql_fetch_row() with PDO -- they're part of a different API, the old MySQL extension.

Instead, since you have called PDO::query(), call $query->fetch(). Use the first row's array keys to build your table heading.

$query = $pdo->query('SELECT * from events ORDER By EventID ASC');
$rowset = array();

if ($query) {
  while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
    // Build array of rows
    $rowset[] = $row;
  }    

  // Output header first
  $headrow = $rowset[0];
  print("<table border=\"1\">\n<tr>\n");
  // Use $rowset[0] to write the table heading
  foreach ($headrow as $col => $val) {
    printf("<th>%s</th>\n", $col);
  }
  print("</tr>");

  // Then output table rows.
  // Outer loop iterates over row
  foreach ($rowset as $row) {
     print("<tr>");
     // Inner loop iterates over columns using $col => $val
     foreach ($row as $col => $val) {
        // We don't know your column names, but substitute the first column (the ID) for FIRSTCOL here
        printf("<td><a href=\"index.php?ID=%s\">%s</a></td>\n", $row['FRISTCOL'],$val);
     }
     print("</tr>");
  }
}
print("</table>");

Addendum

Generally, I would recommend against using SELECT * and dynamically writing out your column headings. Instead, be explicit about the columns you request in your SELECT statement, and be explicit about them in the table you write out. That way, you can be deterministic about the order the columns appear in, and you won't ever get caught by having added columns to the table which should not have appeared in output on screen.

edited May 31, 2012 at 2:19

answered May 31, 2012 at 1:12

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

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

6 Comments

Tuzki Over a year ago

Thanks for you help Michael! I've tried your code.. the errors in my original post is gone, but I have a new error: ("Fatal error: Cannot use object of type PDOStatement as array in C:\xampp\htdocs\clubresults\listevents.php on line 53") where Line 53 is $headrow = $query[0];

Michael Berkowski Over a year ago

@BillChen Sorry, I misread docs. You'll need to fetch() first. I modified the above to build an associative array via fetch() then use that to output your table

Tuzki Over a year ago

Thanks so much michael! Your code works but.. erm everything is in 1 row.. Where should i put <br> in order to make each entry in 1 row?

Michael Berkowski Over a year ago

@BillChen Sorry, I was missing the <tr> tags. Added above.

Tuzki Over a year ago

You are a legend Michael Thanks so much! but sorry one last question from me.. in the table I have a "EventID, EventName, EventDate and Location. "SELECT * from events ORDER By EventID ASC" Would I be able to select all of those above EXCEPT FOR EVENTID coz i dont want users to see the ID.. is there a way to hide it.. but still order it by the event ID?

|

Collectives™ on Stack Overflow

PHP MYSQL List Data inside Table issue

1 Answer 1

Addendum

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Addendum

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related