How do I get all data from a mysql table via php, and print out the contents of every cell?

Question

I've got a database table with at least three rows in it. From php, I have successfully connected to my db and extracted all table information with 'SELECT * from mytable' .

Now I want to loop through first each row, and then each cell, printing out the contents of each cell.

I know this might be a simple task for a more experienced programmer, but I can't figure it out, and I can't find any examples online and it's driving me stark raving bonkers.

How can I do this ?

Solution 1

You could use a for each loop...

//Do the query
$query = "SELECT * FROM table";
$result = mysql_query($query);
//iterate over all the rows
while($row = mysql_fetch_assoc($result)){
    //iterate over all the fields
    foreach($row as $key => $val){
        //generate output
        echo $key . ": " . $val . "<BR />";
    }
}

Haven't tested it, so there may be a syntax error in it, but this is the main idea

Solution 2

There are a couple of examples in the manual that could help you with that ; for instance, on the manual page of mysql_query (quoting, and adapting a bit) :

You first have to execute the query :

$result = mysql_query('SELECT * from mytable');
if (!$result) {
    die('Invalid query: ' . mysql_error());
}

Note that dying in case of an error, and echoing the error-message, is OK while developping -- but you shouldn't do that in a production environment !

And, then, you have to loop over the lines of results, fetching them one at a time :

while ($row = mysql_fetch_assoc($result)) {
    // Use the data in $row
}

And, inside this loop, as $row is an array, you can iterate over its content with a foreach loop :

foreach ($row as $name => $value) {
    echo "column $name contains $value<br />";
}

Note : at this point, you should really invest some time going through a couple of sections of the PHP manual : it will take some time, yes ; but it will definitely help you, and will not be wasted time :-)

Comments

[Matt Phillips]

I would suggest getting comfortable with using php.net's docs because the examples are in there for almost every method. The one example just answered below is too

[Gordon]

(tutorials) articles.sitepoint.com/category/php-tutorials

[Gordon]

(reference) de2.php.net/manual/en/mysql.examples-basic.php

[roberto]

Thanks everyone. I ended up using a modified version of Lex's solution. I did actually go through php.nets examples, but I couldn't didn't fine one that worked for me. (I've spent about half of my day on php.net going through the mysql code, which is how I've gotten this far.) The thing that really stumped me was putting together a solution in my head which artfully resolved looping logic with my ability to visualise the data structures resturned by phps mysql functions. This has been a mindbender for me.

[Matt Phillips]

Well thats good that you can! Its amazing how much simpler/more difficult an objective can be based on the data structures used.

[roberto]

Thanks Lex. This is what I ended up using. I modified it so to output everthing in paragraphs and divs.