PHP/mySQL tables

[MrProtoman]

Okay, I'm working on a links page for my site, and I'm trying to divide the links into catagories. I'm trying two tables here. One is the links themselves, and the other is the catagories, set up like this.
links:
ID | Cat | Title | URL | Date | Hits
links_cat:
ID | Title

What I'm wanting to do is read the links table, match links.cat to links_cat.id, and return links_cat.title. Here's the PHP so far (no referencing)

<?php 
$db = mysql_connect("localhost","username","password"); 
mysql_select_db ("database"); 
$result = mysql_query("SELECT * FROM links"); 
echo "<table border='1' align='center'>"; 
echo "<th>ID#</th><th>Catagory</th><th>Link</th><th>Date</th><th>Hits</th>";
while ($rows = mysql_fetch_array($result)) 
{ 
echo "<tr><td>$rows[id]</td><td>$rows[cat]</td><td><a href=out.php?id=$rows[id]>$rows[title]</a></td><td>$rows[date]</td><td>$rows[hits]</td></tr>"; 
}  
echo "</table>"; 
?>

I've found some listings for JOIN and CROSS, though the documentation doesn't make a lot of sense to me for some reason. It just seams to be looking for listings that exist in both tables, not using two id's to get other info.
You'll have to forgive the links display formatting. Working code first, then pretty output.

[The Eagle]

You'll want to use a query similar to this one:

SELECT l.id, ... FROM links l LEFT JOIN links_cat c ON c.id = l.cat WHERE ...

Basically, the database server merges the corresponding category row to each link row, and you can then select from the result.

Also, the parts where you reference array elements like $rows[id] are ambiguous in PHP, and I would strongly advise that you put the indices between quotes, like $rows['id'].

[MrProtoman]

Everytime I use $rows['id'] and the like, I get this error:
Parse error: parse error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/darkpro/public_html/links/links.php on line 13

.....I'm feeling really dumb right now for not being able to get this. The only upside is that I'm doing this to learn more about PHP/MySQL. New code, minus the ['id'] change.

<?php 
$db = mysql_connect("localhost","darkpro_various","fRAbr5d2"); 
mysql_select_db ("darkpro_various"); 
$result = mysql_query("SELECT l.id, l.cat, l.url, l.title, l.date, l.hits, c.id, c.name FROM links l LEFT JOIN links_cat c ON c.id = l.cat WHERE l.cat=c.id"); 
echo "<table border='1' align='center'>"; 
echo "<th>ID#</th><th>Catagory</th><th>Link</th><th>Date</th><th>Hits</th>";
while ($rows = mysql_fetch_array($result)) 
{ 
echo "<tr><td>$rows[l.id]</td><td>$rows[l.cat]</td><td><a href=out.php?id=$rows[l.id]>$rows[l.title]</a></td><td>$rows[l.date]</td><td>$rows[l.hits]</td></tr>"; 
} 
echo "</table>"; 
?>

And the error it produces:
Parse error: parse error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting ']' in /home/darkpro/public_html/links/links.php on line 13

Line 13 is obviously the while loop echo line. I've tried both c.name and l.cat for the catagory name with no visible effect on the error.

[The Eagle]

Your problem now lies in the fact that you are trying to acces array elements inside a double quoted string. The way you do it is only allowed by php in the simplest cases. You can seperate the variables from your constants (which imo is the best way to do it), or put your array element references between braces.

Also, the l. and c. prefixes are not necessary to access your query result.

echo "<tr><td>{$rows['id']}</td>"; // ...

echo '<tr><td>' . htmlspecialchars($rows['id']) . '</td>'; // ...

^^ The latter option being the one I highly recommend to prevent attacks such as cross-site scripting.

[MrProtoman]

Sweet, that did it. Thanks Eagle.