Printing An Array From MYSQL

[Goober]

I want to get an array of all the rows of a table in a database, and print all of its contents. For example: If I had an SQL table with a list of peoples names, how could I print all the names in the database into an HTML code?

The HTML code would look something like this:

<li>John</li>
<li>Alex</li>
<li>George</li>

Let me know If you don't understand my question.

Thanks
Goober.

[Celtboy]

assuming a valid connection to the server,

$table = "tbl_users";
$query = "SELECT * FROM $table";

$result = mysql_query($query) or die(mysql_error());

print "<ul>";
if (mysql_num_rows($result) < 0) {
   while ($row = mysql_fetch_assoc($result)) {
      print "<li>" . $row["username"] . "</li>\n";
   }
} else {
   print "No rows were returned.";
}

print "</ul>"; 

that code isn't the greatest, but it should work.

[Goober]

Thanks CeltBoy. Could you please explain this code so I can understand whats going on with it?

Thanks Again.
Goober.

[Celtboy]

1:     $table = "tbl_users";
2:     $query = "SELECT * FROM $table";
3:     
4:     $result = mysql_query($query) or die(mysql_error());
5:     
6:     print "<ul>";
7:     if (mysql_num_rows($result) < 0) {
8:        while ($row = mysql_fetch_assoc($result)) {
9:           print "<li>" . $row["username"] . "</li>\n";
10:        }
11:     } else {
12:        print "No rows were returned.";
13:     }
14:     
15:     print "</ul>"; 

evaluating line by line (note that this code won't work...use the code without the line numbers ):

line 1: in your database, you'll have tables with data. we set the variable $table equal to the value of "tbl_users." (this is assuming that the table with the data we want is called "tbl_users".

line 2: a basic SQL query. says SELECT everything from whatever table name is stored in the $table variable. This sql query is stored in a variable called "query"

line 3: blank line

line 4: uses the 'mysql_query' function of php. mysql_query returns a result set, based on the query. if it doesn't work (or die), then it prints out the error the function gave us

line 5: blank line

line 6: prints to the standard output "<ul>". This is the html code to begin an unordered list

line 7: start a conditional. mysql_num_rows is a php function that takes the result set, and spits back the number of rows in that result set. in this case, we're selecting everything, so every row in the table is stored in the result set. The conditional just makes sure that at least one row was returned by mysql_query() in line 4.

line 8: a loop. mysql_fetch_assoc is a spiffy php function. let's say that table "tbl_users" looks like this:

+----+----------+--------------+----------+
| ID | username |     email    | password |
+----+----------+--------------+----------+
| 01 |   bob    | [email][email protected][/email] | h83AdLmn |
+----+----------+--------------+----------+
| 02 |   leon   | [email][email protected][/email]| rAng3r5  |
+----+----------+--------------+----------+

now. The first row is the field names. There are 2 rows.

Line 8 says for every row returned from the sql_query (and stored in $result), create the array $row. Assign the values of each field in a key named according to the field name....

so the first $row array would contain the following:
$row["id"] = 01
$row["name"] = bob
$row["email"] = [email protected]
$row["password"] = h83AdLmn


liine 8 tells the php interpreter to continue the code inside the {} braces, until $row doesn't equal anything else (i.e. there aren't any more rows).

Line 9: print to standard output (the browser): the "<LI>" html character, then the value currently stored in $row["username"] (bob), and then the closing "<LI>" tag.

Line 10: return to the "while" loop in line 8, and repeat with the next row

Line 11: If mysql_num_rows (of line 7) wasn't greater than 0, then execute the following code.... *in this case, mysql_num_rows($result) = 2, because 2 rows were returned by the sql query

Line 12: prints that no rows were returned by the sql query

Line 13. Done with the If-Else

Line 14: blank line

Line 15: prints the closing unordered list tag.


------------------------------


I'm sorry if that was over-simplified. I did my best to explain EVERYTHING that was going on, assuming you didn't know anything about the code. By that same token, if that wasn't a good enough explanation, by all means, let me know!



HTH,
-Celt

[raf]

7: if (mysql_num_rows($result) < 0) {
line 7: start a conditional. mysql_num_rows is a php function that takes the result set, and spits back the number of rows in that result set. in this case, we're selecting everything, so every row in the table is stored in the result set. The conditional just makes sure that at least one row was returned by mysql_query() in line 4.

should be

7: if (mysql_num_rows($result) > 0) {

or what i personally always use

7: if (mysql_num_rows($result) >= 1) {

because the 0 can give some unexpected results in PHP + >=1 is functionally closer to what you want to check for (--> 'is there at least one row returned')

[Celtboy]

LOL. that'd be funny...thanks for catching that one...lol

[Goober]

I apologize for having you do all that work of explaining the code to me CeltBoy. I am still new to PHP/Programing, and I did not completely understand the code. I really do appreciate the help, and I hope I didn't make too mad. Thanks again CeltBoy.