[RESOLVED] JOIN is fetching wrong id

retro

I am sure this has a very easy answer, sorry for that but I am still learning MySQL stuff!

I have the following query:

$id=$_GET['id'];
$result = mysql_query ("SELECT * FROM orders LEFT JOIN customers ON (customers.id = orders.customer) WHERE customer = $id");

This is used to take the id from the referring page, and display all orders made by that customer. This works fine.

I then want to link to each order by order id. This is found in the table orders as id (auto incrementing field). So I used:

$row["id"]

However, this gives me the customer id (id in the customers table), I assume because of the join.

I tried:

$row["orders.id"]

but this gave nothing at all.

What do I need to do to get the order id?

Thanks in advance for any assistance, it is greatly appreciated!

lazzerous

// This removes any non-numeric characters from the passed 'id'
// NEVER trust user input - you're setting yourself up for SQL injection attacks here
$id = ereg_replace( '[^0-9]+', '', $_GET['id'] );

// Be sure to specify what fields you want returned.
// you can use the 'AS' operator to rename the values on output
$sql = "SELECT ";
$sql .= " orders.id AS order_id, ";
$sql .= " orders.otherfield, ";
$sql .= " customers.id AS customer_id, ";
$sql .= " customers.otherfield as other_field ";
$sql .= "FROM ";
$sql .= " orders ";
$sql .= " LEFT JOIN customers ON (customers.id = orders.customer) ";
$sql .= "WHERE ";
$sql .= " customer = {$id}";

$result = mysql_query ($sql);

echo $row['customer_id'];

retro

That's great, thanks!

In this case, I don't think the numeric issue is a problem, as the id was passed by PHP script (clicking on a user name in a list), not from a user input. I guess it is still worth doing, though, in case someone tries to manually input something else in the URL. I've dealt with trapping like this in vb.NET but hadn't thought to look into it in PHP! I'll certainly remember it now!