Just added INNER JOIN to my SQL and it's not working now?!

[jarv]

is my code ok? I just added INNER JOIN PUBPICS ON PUBS.PUBID=PUBPICS.PUBID to my SQL string and now my page is not working?!

 

<?php
include_once("config.php");
include_once("functions.php");
// Check user logged in already:
checkLoggedIn("yes");
//doCSS();

$PUBID = $_GET['PUBID'];

$result = mysql_query("SELECT * FROM PUBS INNER JOIN PUBPICS ON PUBS.PUBID=PUBPICS.PUBID WHERE PUBID = ".$_GET["PUBID"]);
$record_set = $result && mysql_num_rows($result) == 1 ? mysql_fetch_assoc($result) : false;  
?>
<div class="panel">
<p>
<img src="pubs/<? echo $record_set['PUBPICS.RSPIC']; ?>" alt="<? echo $record_set['PUBPICS.RSPIC']; ?>" />
<br />
Pub name: <? echo $record_set['RSPUBNAME']; ?>
<br />
Address: <? echo $record_set['RSADDRESS']; ?>
<br />
Postcode: <? echo $record_set['RSPOSTCODE']; ?>
<br />
Telephone: <? echo $record_set['RSTEL']; ?>
<br />
Town: <? echo $record_set['RSTOWN']; ?>
<br />
County: <? echo $record_set['RSCOUNTY']; ?>
<br />
Latitude: <? echo $record_set['RSLAT']; ?>
<br />
longitude: <? echo $record_set['RSLONG']; ?>
</p>
</div>

 

 

[simshaun]

Try running the query in phpmyadmin (or mysql client) and see if it works.

 

Also, are you sure an INNER JOIN is what you want, and not LEFT OUTER JOIN?  Inner join only returns rows that are found in both PUBS and PUBPICS.  If the PUB row you are viewing does not contain any PUBPICS, the query will not return anything.

 

Edit: I suggest you read the Wikipedia article on joins.

[wildteen88]

Do not use Select all (SELECT * FROM) when joining tables. Always explicitly state the columns you're wanting your query to return.

 

Your query is most proabably failing because both tables have a column which is named the same, ie PUBID. This will cause MySQL to return an error like 'column name too ambiguous in where clause'