?
Solved

PHP and MySQL select statement. Windows, mySql. php5

Posted on 2009-02-18
13
Medium Priority
?
234 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
0
Comment
Question by:MHenry
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 23678521
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
 
LVL 7

Author Comment

by:MHenry
ID: 23678586
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 23678605
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 14

Expert Comment

by:Ionut A. Tudor
ID: 23678647
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
 
LVL 19

Expert Comment

by:v2Media
ID: 23681596
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
 
LVL 7

Author Comment

by:MHenry
ID: 23681827
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
 
LVL 7

Author Comment

by:MHenry
ID: 23688615
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
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 2000 total points
ID: 23688896
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
 
LVL 19

Expert Comment

by:v2Media
ID: 23688953
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
 
LVL 7

Author Comment

by:MHenry
ID: 23688956
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
 
LVL 19

Expert Comment

by:v2Media
ID: 23688960
srries pratima, your post wasnt there when I started composing my reply.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 23689008
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
 
LVL 7

Author Comment

by:MHenry
ID: 23689022
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses
Course of the Month15 days, 23 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question