Advice on DB Setup

[bodgekaloopie]

I am working on setting up a new database driven site using PHP and MySQL.

Aniticipated db setup is:

Table-Users
userid = PK auto-incremented
name
company
(etc.)

Table-Orders
order_no = PK auto-incremented
userid = FK
date
(etc.)


The user registers, then logs in to place an order. Each user can place multiple orders at various times, so there needs to be a foreign key in the Orders table that relates to the unique user, so I want it to be the userid.

I am familiar with my_sql_id() if you want to insert an id from the immediately previous query. But how do you insert the userid into each order as it is placed when the userid already exists and isn't part of the immediately previous query?

AND... is this the best way to structure this db?

[redcircle]

you know the userid by use of session tracking or cookies when THEY placed the order extract the userid from the session variable. Your database structure looks ok. but what about the items in the order. You'll need another table for those.

[bodgekaloopie]

Thanks, redcircle. I'll give that a shot.

The order is an intangible item - a bit different than a retail situation - so two tables will actually suffice for this application.

[bodgekaloopie]

What is the correct syntax to use a cookie for updating a db field?

I have tried:
$query = "INSERT INTO orders (order_no, userid, date)
VALUES (0, '{$_COOKIE['userid']}', '$date')";


In place of '{$_COOKIE['userid']}' I have also tried '$userid' and '{$_POST['userid']}' without success.

Nothing about this particular syntax came up when I searched PHP.net, Google, etc.

Before posting this question I also confirmed that the cookie is being properly sent by overriding automatic cookie handling in my browser so that I could SEE the cookie.

[mushroom]

Try

$query = "INSERT INTO orders ( userid, date)
VALUES ('$_COOKIE['userid']', '$date')";
order_no is auto-incremented and may be messing up your query

[bodgekaloopie]

Thanks for that tip. I knew that, and tried removing order_no before, but it was actually working OK with that field included.

However, just to make sure that isn't the issue, I have again removed order_no from the code, but I still can't get the userid to insert.

If I use
'$_COOKIE['userid']' I get a parsing error.

If I use
'{$_COOKIE['userid']}'
everything inserts properly except for the userid.

I can't be too far off!

[mushroom]

Your quotes are problem in the query
try

$userid = $_COOKIE['userid'];
$query = "INSERT INTO orders ( userid, date)
VALUES ('$userid', '$date')";

[php~pro]

This was almost correct. Concenation was incorrect.

$query = "INSERT INTO orders ( userid, date)
VALUES ('$_COOKIE['userid']', '$date')";


Try

$query = "INSERT INTO orders ( userid, date)
VALUES ('".$_COOKIE['userid']."', '".$date."')";

[bodgekaloopie]

Thanks, guys, but I did FINALLY get it last night.

This code works perfectly:


$query1 = "INSERT INTO orders (userid, date)
VALUES ('".$_COOKIE['userid']."', '$date')";


I appreciate the help!

[php~pro]

Look above. Watch your syntax and dont forget to escape.

[bodgekaloopie]

I see the difference here, oh friend-who-I-tick-off (wink).

But if they both work to update the db, what is the difference between '".$date."' and '$date'?

[php~pro]

The ." escape will always work. The ' consenation wont. Try escaping concenating superglobals or something like $_POST['your_var'] with ' and you will see exactly why. Most php programmers short code from experience of what they can do to shorten the code. When learning, to cut down development times and thinking you made grave errors try long coding everything. You'll spend less time seeking simple answers in forums as a result, as you will find the only mistake you ever made was trying to short code and finding the syntax incorrect.

Take your last 2 topics and the last 2 solutions for example. How far away were you? A couple of dots and dashes by my reckoning. And you dont tick me off, Im just a poor tutor.

Have a detention for shortening code!

[bodgekaloopie]

Thanks for the explanation php~pro. I appreciate it, and you're not a poor tutor. I can't believe how many great people like you are out there who are willing to help noobs like me.