PHP and MySQL select statement. Windows, mySql. php5

Hi all,

I know this is probably really easy, but I can't seem to figure it out. Here's my query:
$query = "SELECT tbl_orderskus.orderSKU_SKU, tbl_orderskus.orderSKU_Quantity, tbl_customers.cst_ID FROM tbl_orderskus , tbl_customers WHERE tbl_customers.cst_ID =" .  $_SESSION["customerID"];

I think I'm missing a join statement, but I've tried everything I can think of. Can someone tell me what I'm missing?

Thanks,
MH
LVL 7
MHenryAsked:
Who is Participating?
 
Pratima PharandeCommented:
Try like this

$query = "SELECT tbl_orderskus.orderSKU_SKU, tbl_orderskus.orderSKU_Quantity, tbl_customers.cst_ID FROM tbl_orders Inner Join tbl_customers ON tbl_orders.order_CustomerID= tbl_customers.cst_ID
Inner join tbl_orderskus ON tbl_orders.orderID = tbl_orderskus.orderSKU_OrderID
WHERE tbl_customers.cst_ID =" .  $_SESSION["customerID"];
0
 
Pratima PharandeCommented:
Try like this

$query = "SELECT tbl_orderskus.orderSKU_SKU, tbl_orderskus.orderSKU_Quantity, tbl_customers.cst_ID FROM tbl_orderskus Inner Join tbl_customers ON tbl_orderskus.cst_ID = tbl_customers.cst_ID WHERE tbl_customers.cst_ID =" .  $_SESSION["customerID"];



If your fields are differnt to map two tables replace here
tbl_orderskus.cst_ID = tbl_customers.cst_ID

what problem or Error you r getting ?
0
 
MHenryAuthor Commented:
Part of the problem is I don't know what the error is. All I'm getting is a 500 server error and no other message.

The other part is, there isn't a common field between the two tables, so this doesn't work:
tbl_orderskus.cst_ID = tbl_customers.cst_ID
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Pratima PharandeCommented:
There must be some comman field to map the table , On what basis you want to get tha data
try this simple query
is it working ?
$query = "SELECT tbl_customers.*  FROM  tbl_customers"
0
 
Ionut A. TudorPHP ProgrammerCommented:
if you get 500 http error means your script times out or there is a server configuration broken. But i guess its a timeout problem, so your query look fine just put LIMIT 1 at the end of it and see if you still get the error.
Also check if you are connected by putting a mysql_error() in case of mysql_connect returns false: mysql_connect($address,$user,$pass) or die("Can't connect to mysql server");
And if this doens't work can you post your script here, maybe some of us will figure it out whats causing the 500 error.
Cheers
0
 
v2MediaCommented:
You need to rethink the structure of your database, not the query. That query is going to return and exponential number of rows because of the lack of a column mapping the relationship. If you have a decent number of records in each table, this will quickly timeout the connection.

A third table is required to maintain the relationship between orders, order items, and customers.

 - table_orders would contain an orderID, customerID, along with other order details like date, total, tax etc.
 - table_order_items would contain orderItemID, orderID, sku, and order item details such as qty etc.
 - table_customers is self explanatory.

When selecting orders for a customer, table_orders is used to map orders to customers and order_items. The query qould look along these lines: -

SELECT t1.orderID, t2.*
FROM table_orders as t1, table_order_items as t2
WHERE t1.orderID = t2.orderID AND t1.customerID = $_SESSION['customerID']

0
 
MHenryAuthor Commented:
Thanks all for the help. Off to the day job...

I think pratima_mcs & v2Media are on the right track. I was beginning to think I need to map to another table but wasn't sure. It's been a while since I had to do any SQL.

I'll give that a shot this evening.

Best,
MH
0
 
MHenryAuthor Commented:
Ok, here are the relationships:
tbl_orders.order_CustomerID maps to tbl_customers.cst_ID
tbl_orderskus.orderSKU_OrderID maps to tbl_orders.orderID
tbl_customer.cst_ID

Can someone decipher this for me? Now I'm so confused, I don't even remember what the heck I'm searching for!

I'm trying to get the orderSKU_Quantity where customerID = $_SESSION["customerID"]

Thanks,
MH
0
 
v2MediaCommented:
It's confusing because you've now introduced what's called redundant data to tbl_orders and tbl_orderskus. It's even straining my brain. Anyway, the query should now look along these lines: -

$query = "SELECT t1.orderSKU_SKU as sku, t1.orderSKU_Quantity as qty, t2.orderID, t2.order_CustomerID as customerID
FROM tbl_orderskus as t1, tbl_orders as t2
WHERE t1.orderSKU_OrderID = t2.orderID
AND t2.order_CustomerID = ".$_SESSION["customerID"];

You really need to read up on database design, especially the topics 'relational databases' and 'normalization'.
0
 
MHenryAuthor Commented:
pratima_mcs,

I tried it in Dreamweaver and got this. I can't check it in the browser, all I'm getting is a 500 error still.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'customerID"]' at line 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'customerID"]' at line 1
0
 
v2MediaCommented:
srries pratima, your post wasnt there when I started composing my reply.
0
 
Pratima PharandeCommented:
just check

whether its working

$query = "SELECT tbl_orderskus.orderSKU_SKU, tbl_orderskus.orderSKU_Quantity, tbl_customers.cst_ID FROM tbl_orders Inner Join tbl_customers ON tbl_orders.order_CustomerID= tbl_customers.cst_ID
Inner join tbl_orderskus ON tbl_orders.orderID = tbl_orderskus.orderSKU_OrderID ";
0
 
MHenryAuthor Commented:
Sorry guys, this is the database I'm stuck with, it's Cartweaver, a shopping cart plugin to Dreamweaver. I'm just trying to add a page to get registrations. I imagine they know a little about databases, a lot more than me anyway...

But pratima_mcs, your query is working when I pull out the session stuff and just plug in a number from the ID field in the database. So I guess I got other problems too.

But that's for a different question, I guess.  Hey, more points for someone, right?

Thanks for all the help!

Best,
MH
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.