We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

PHP and MySQL select statement. Windows, mySql. php5

MHenry
MHenry asked
on
Medium Priority
245 Views
Last Modified: 2013-12-13
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
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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 ?

Author

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
CERTIFIED EXPERT

Commented:
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"
Ionut A. TudorPHP Programmer
CERTIFIED EXPERT

Commented:
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

Commented:
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']

Author

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

Author

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
CERTIFIED EXPERT
Commented:
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"];

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
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'.

Author

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

Commented:
srries pratima, your post wasnt there when I started composing my reply.
CERTIFIED EXPERT

Commented:
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 ";

Author

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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.