[SOLVED] HELP!! selecting data from multiple tables

[mark1111]

Could somone please tell me whats wrong with the following? Im wanting to display data from two tables, the link is CUSTOMER_ID, so there is a where clause

WHERE ENQUIRY_RESERVATION.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID

 

This doesnt display any data, eventhough both tables are populated

 

?>

<table>

<?php

$sql = "SELECT ER_ID, ENQUIRY_RESERVATION.CUSTOMER_ID, CUSTOMER.CUSTOMER_ID FROM ENQUIRY_RESERVATION WHERE ENQUIRY_RESERVATION.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID";

$query = mysql_query($sql);

while($row = mysql_fetch_array($query)) {

echo "<tr>";

echo "<td>".$row['ER_ID']."</td>";

echo "<td>".$row['ENQUIRY_RESERVATION.CUSTOMER_ID']."</td>";

echo "<td>".$row['CUSTOMER.CUSTOMER_ID']."</td>";

 

echo "</tr>";

}

?>

 

Thanks in advance

[lee20]

Your query doesn't make any since because with what you are only selecting one unique field (ER_ID) and you have no actual where conditions.

 

Your query is equivalent to:

      "SELECT ER_ID, CUSTOMER_ID FROM `ENQUIRY_RESERVATION`"

 

(the table would be whichever able ER_ID is in)

 

Also, when you select fields in a query "SELECT table_name.field_name FROM `table_name`" the result does not include the "table_name." in the index. You don't need to select customer_id twice.

 

Nevertheless, your query should work assuming you have atleast one row in each table with equal CUSTOMER_ID's.

 

Try the following:

 

<table>
<?php
$sql = "SELECT ER_ID, CUSTOMER_ID FROM ENQUIRY_RESERVATION";
$query = mysql_query($sql);
while($row = mysql_fetch_array($query)) {
echo "<tr>";
echo "<td>".$row['ER_ID']."</td>";
echo "<td>".$row['CUSTOMER_ID']."</td>";
echo "</tr>";
}
</table>
?>

 

[mark1111]

Sorry, should have been a bi clearer in the first post, im purely trying to select data from 2 tables using

 

$sql = "SELECT ER_ID, ENQUIRY_RESERVATION.CUSTOMER_ID, CUSTOMER.CUSTOMER_ID FROM ENQUIRY_RESERVATION WHERE ENQUIRY_RESERVATION.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID";

 

I can select data from one table, but when i try and select from two tables no data is displayed. The data is in there, but the query will not work

[lee20]

I didn't notice before, but if your selecting from two tables you need to put both tables in the FROM or use a JOIN.

 

You also need to "alias" one of the customer id variables when selecting two or more fields with the same name. But in this case, you only need to select one because they're equal.

 

Try:

 

$sql = "SELECT ER_ID,
                     ENQUIRY_RESERVATION.CUSTOMER_ID
                     CUSTOMER.CUSTOMER_ID AS CUSTOMER_ID2
          FROM ENQUIRY_RESERVATION, CUSTOMER
          WHERE ENQUIRY_RESERVATION.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID";