PHP and mySQL - not sure how to proceed now

[scarlson]

I am at a point now in creating my website that I am not sure how to go about making sure my mySQL tables relate with each other so I can pull data from multiple tables.

 

I have a website that allows a user to login first or create a new account (username, password twice, then email verification is needed to activate the account).  Once they login, the are directed to a page to setup their "ticket" (or edit the ticket they created already - I don't want two differnt pages for this) - this includes personal information that is stored in one table along with information about all their items they want to post that is stored in a different table. 

 

What I don't understand is how I can get my different tables to relate to each other (i will post my database tables below).

 

Here is my database along with my plans on how to use them:

 

 

cities   - table name  (this is for a drop down box that pre-loads cities based off of state choosen)
id    auto
state_id
city


garage_sale   - table name  (used to set the start date and end date of the ticket, should this be combined with seller also?)
sale_id    auto
seller_id
start_date
end_date
payment_type


items   - table name (what do I need to do to make sure this item table relates with login and seller table)
item_id   auto
sale_id
login_id
(34 individual fields to store different types of items, didn't want to post them all here)


login   - table name  (used for user authentication)
id   auto
username
password
email
verifystring
active


seller  - table name  (used to store personal information, should this be combined with login table?)
lName
fName
address
zip
phone
seller_id    auto
state_id
city_id
login_id


states   - table name (drop down box preloaded)
id    auto
state
abb

 

First off as you can see my database is probably not setup correctly or efficiently.  Any suggestions on this would be great.  I am at a lost right now.

 

Thanks,

 

Scott

[revraz]

You first need to have a design on how you want each table to relate to each other.  Example, a company inventory.

 

Item Table

itemno

itemname

 

Customer Table

customerno

customername

itemno

 

Here, in your Customer Table, you have a entry for the item no they bought.  When you do a query and JOIN these on item.itemno=customer.itemno, you are making a relation to the two tables.

 

This is all Database design and you would probably get better answers in the MySQL forum.

[scarlson]

I do understand what your saying and I think I have it designed out.

 

My problem is in how to go about updating the different id's in the other tables when the user submits all the information.

 

Would I query the database based on the username stored in SESSION and store the id there in a variable and then use that variable to update the other tables with that id number?

[revraz]

Its hard to say without knowing what you want each table to do.

 

Every table doesn't have to change, unless you are trying to do dynamic quantities and the like.

 

A city table should only change if you add a new city.

A state table should only chane if you add a new state.

 

Using an ID instead of a username would be faster, since it's indexed.

 

But I dont really understand what you mean by updating the different id's in the other tables.  I assume you mean row or record, but I don't think you want to Update it, but rather Insert a new row.  Unless someone is modifing their existing garage sale.

[scarlson]

I was thinking from the login table I have an id that is auto increment for each new user.  So I need to know when a user is sent to the setup garage sale page and fills in the other information and hits submit, how do I insert that "id" from the login table to login_id field in the seller and item tables?  This way the tables will be related.  Not sure how to code this.

[Ell20]

how do I insert that "id" from the login table to login_id field in the seller and item tables? 

 

Sounds like you need to set up a session when a user logs in which would hold there user_id

[scarlson]

how do I insert that "id" from the login table to login_id field in the seller and item tables? 

 

Sounds like you need to set up a session when a user logs in which would hold there user_id

 

I have SESSIONS setup but it stores the username.  How do I store the id that is auto generated from the database?  So when they hit the login button I should store the id from the database in a SESSION then, if so, how?

[scarlson]

Here is what I am trying to do now but I am getting an error.

 

 

<?php
session_start();

if(isset($_SESSION['myusername'])){

// logged in


}
else
{

header("location:account_setup.php"); // REDIRECT TO LOGIN PAGE

}
?>

<?php
include "dbconnect.php";

$logid = "SELECT id FROM login WHERE username = "$_SESSION['myusername']";"  //ERROR COMING FROM THIS LINE
$resultid = mysql_query($logid);

$sellerid = "SELECT * FROM seller WHERE seller_id = '$resultid'";
$sellerid_result = mysql_query($sellerid);

$count = mysql_num_rows($sellerid_result);

if ($count == 1) {
echo "Count == 1, it worked";
//insert code to update form with all the data, if count is zero then it's a new user

}

else {
//need to set login_id from seller table with id from login table and also set the login_id from the items table
echo "DID NOT WORK";
mysql_query = ("INSERT INTO seller (login_id) VALUES ($resultid)");   //WILL THIS WORK?
mysql_query = ("INSERT INTO items (login_id) VALUES ($resultid)");   //WILL THIS WORK?


}

?>

 

Here is the error:  Parse error: parse error, unexpected T_VARIABLE in /home/content/t/u/r/turnkeywebplan/html/garagesalelister/ad_setup.php on line 21

[scarlson]

$logid = "SELECT id FROM login WHERE username = "$_SESSION['myusername']";"  //ERROR COMING FROM THIS LINE

 

Why am I getting an error here?  Not sure how to use $_SESSION.