What's wrong with this query?

netfrugal

I am pulling data from 2 tables, and the matching column is "customer_id"

I am also pulling the customer_id from the url, but no luck so far. I am guessing it is because of how I wrote the query:

$sql = mysql_query("SELECT c.*, t.* FROM table1 c, table2 t WHERE c.customer_id = '$_GET[customer_id]' AND c.customer_id = t.customer_id");

I get nothing returned.

shu

Assuming you do have records by that customer id, try concatenate the id instead:

$sql = "SELECT c.*, t.* FROM table1 c, table2 t WHERE c.customer_id = '" . $_GET['customer_id'] . "' AND c.customer_id = t.customer_id";

$res = mysql_query($sql);

netfrugal

Weird, that format is not displaying anything:

$sql = mysql_query("SELECT c.*, t.* FROM table1 c, table2 t WHERE c.customer_id ='".$_GET['customer_id']."' AND c.customer_id = t.customer_id ORDER BY dateCreated");

How does one pull from 2 tables, while using either POST or GET data for the WHERE clause?

shu

By this query, you are doing effectively an INNER JOIN, therefore, both tables must contain a record with that customer_id. Otherwise, the result will be empty.

If you want records returned when customer_id exists in just one table or both tables, then you can either use OUTER JOIN or even a UNION; depending on how you want your result to come out.

netfrugal

Both tables do contain the customer_id record, and they coincide. Customer_id number 566 is in table 1 (which is the customers table) and also in table 2 (which is the transactions table)

The problem is not joining the 2 records. The problem is how to the query.

I found out what I did wrong:

I forgot to specify the dateCreated field from table2. Without that, the query couldn't work.

below is the correct query:

SELECT c.*, t.* FROM customers c, transactions t WHERE c.customer_id ='$id' AND c.customer_id = t.customer_id ORDER BY t.dateCreated