how to read from a MySQL query

[lives4him06]

I have the following code:

 $MAX_COLS=3;

$id = current ($HTTP_GET_VARS);

$query = "Select picture FROM pictures where id='jklassen'";
$result = mysql_query($query);

$query = "Select username FROM profiles where id='jklassen'";
$result = mysql_query($query);


echo "<table border ='0'>\n<tr>\n";
$i=0;
while($picture = mysql_fetch_array($result))
{
   if ($i % $MAX_COLS==0) 
	{
	   echo "<tr>\n";
	}
   extract($picture);
   echo "<td><a href='$username/$picture'><img src='$username/td_$picture'></a></td>\n";
   if (($i+1) % $MAX_COLS==0) 
   {
	   echo "</tr>\n";
	}
	$i++;
}
echo "</tr>\n</table>\n"; 

and the following contents of the database:

+-------+-------------------+----------+
| picID | picture | id |
+-------+-------------------+----------+
| 1 | homecoming(1).JPG | jklassen |
| 2 | homecoming(2).JPG | jklassen |
| 3 | 1.jpg | jklassen |
| 4 | beetle2.jpg | jklassen |
+-------+-------------------+----------+

the output is the following:

<table border ='0'>
<tr>
<tr>
<td><a href='lives4him06/Array'><img src='lives4him06/td_Array'></a></td>
</tr>
</table>

I want to place each picture in a table with 3 columns and as many rows as needed. But the pictures are not being read from the mysql array correctly. How do i fix this?

[tomhath]

You are running two queries and storing the result in $result both times. The second one will replace the result from the first. I didn't test this, but it should get you started:

$query = "Select pictures.picture, profiles.username " .
         "FROM pictures INNER JOIN profiles " .
         "ON pictures.id = profiles.id " .
         "where pictures.id='jklassen'";

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

echo "<table border ='0'>\n<tr>\n";
$i=0;
while( $row = mysql_fetch_array($result))
{
  $picture = $row[0];
  $username = $row[1];
 ...

[lives4him06]

Thanks, looks like its working!

if you could.. explain what the MySQL query is doing.

[tomhath]

MySQL is an RDBMS, Relational Data Base Management System.

The term Relational is used because the data is stored such that rows in one table are related to rows in other tables.

In this case, rows in the pictures table are related to rows in the profiles table when the id column has the same value in both tables. So the JOIN is used to match the rows up and return you a single set of data from the combination, in this case a set of (picture, username) pairs.

SQL is very powerful, it's worth putting some time into learning. There are plenty of books and other sources online.