How should I set up my database???

[Dirt_Diver]

right now I have my table set up as 3 separate tables, <users>,<images>and <challenges>.

<users> table has these fields (in this order) Does order matter???
user_d
username
first_name
last_name
password
reg_date
email
website
location
show_email
last_login
create_ts
gender
address
city
state
zip
phone
country

<images>
image_id

<challenges>
challenge_id

What I am looking for is I want users to upload images to their portfolio and/or challenges and have it associate it with the user id.
So I need to know if my tables are ok or should I make changes? Images entered in challenges should know that they are in a challenge and images should know they belong to a user.

Anyone have any smart suggests as to how I should make this. I want to make it as simple as I can so I don't cut the same grass twice as they say.

[boogyman]

<users> table has these fields (in this order) Does order matter???
order doesn't matter


What I am looking for is I want users to upload images to their portfolio and/or challenges and have it associate it with the user id.
So I need to know if my tables are ok or should I make changes? Images entered in challenges should know that they are in a challenge and images should know they belong to a user.

you would need a unique identifier for the users first... typically its just an auto incrementing field.

then in your images you would need a unique id (which you have) for the image, BUT you also need to associate that image to the user so you would need a foreign key that contained the user id of the submitter

then in your challenges table you need the challenge id (which you have) and you would need the image id (which you do not) Now you do not need this table because you already know what user it is by looking at the image table.

so your basic structure would look something like this (without the additional field detailing specifics about each table)

<users>
user_id PK

<images>
image_id PK
user_id FK

<challenges>
challenge_id PK
image_id FK

and you would associate them somewhat like

images -> user

SELECT users.field, images.field FROM images LEFT JOIN users ON users.user_id=images.user_id WHERE condition

challenge -> image -> user

SELET challenges.field, images.field, users.field FROM challenges LEFT JOIN images ON challenges.image_id=images.image_id LEFT JOIN users.user_id=images.user_id WHERE condition

Those queries will work with using any of the tables declared first in the FROM statement, however I personally use the table that is my main focus first, then I JOIN the others as necessary. Also another syntax trend I practice that doesn't technically make a difference, but is easier for me debugging, is that whatever table I am JOINing I put first in statement and then I put the second table after... That may sound confusing, so using my first example...

FROM images LEFT JOIN users ON users.user_id=images.user_id

could technically be re-written as

FROM images LEFT JOIN users ON images.user_id=users.user_id

[Dirt_Diver]

okay I have some other questions..


Because I already have the DB set up when I go to add the new fields to the tables will they automatically match up?? For example I already have my user_id on auto_inc but when I create user_id in the images table will the numbers match?

I have 11 names in my users table already...

And the codes, i will put in my php pages not in MySQL area somewhere right?
anytime I write data to the db I use the codes above to write to all the fields??

[Dirt_Diver]

Does "UNIQUE" and foreign key mean the same thing?

[Dirt_Diver]

I don't know how to get the tables to see each other, please someone help me

[boogyman]

Does "UNIQUE" and foreign key mean the same thing?
No,
UNIQUE means that the value contained within this field cannot appear anywhere else in this table.
foreign key is a reference to a primary key; the primary key field must be a unique value, however its possible to have multiple fields in a table that have the same foreign key.

EG
<users>
id|fname |lname
1 |John |Smith
2 |Jane |Smith
3 |Michael |Jordan
4 |William |Clinton
5 |Monica |Lewinski

<house>
house_id |address |phone
10 |One Maple Dr. |(555)555-5555
20 |1600 Penn Ave. |(111)111-1111

<house_occupants>
house_id|member_id|status
10 |1 |father
10 |2 |mother
20 |3 |landlord
20 |4 |rentee
20 |5 |girlfriend

house id is a foreign key referencing the unique identifier of the specific house, and the member id is the unique identifier of the specific occupant

so the house id and the member id are foreign keys in the house occupants table, but they are both primary keys in their respective table.

Setting up the Keys!
when you set up a uniquely identified database field, set it up so that its an not null unsigned auto-increment field... if you have a front end client, usually there are check boxes or drop downs, but if you are doing it in a command line type environment you would run this statement



DROP TABLE IF EXISTS table;
CREATE TABLE table (
unique_field INT(16) NOT NULL AUTO-INCREMENT,
field OPTION(S),
field OPTION(S),
field OPTION(S)
);


or if you already have a table, and just wanted to add the unique field by command line interface (cli) you could execute this statement



ALTER table ADD COLUMN field OPTION(S)


to create a foreign key and associate it with another table/field use



ALTER TABLE table ADD FOREIGN KEY field REFERENCES table(field)



I don't know how to get the tables to see each other, please someone help me

that is done through the query that I showed you in my first post... you simply reference what two fields must be the same. a more generic query than the one i posted before would be



SELECT tb1.field, tbl2.field FROM table1 AS tbl1 LEFT JOIN table2 AS tbl2 ON tbl1.field=tbl2.field WHERE condition


if that is confusing... go to www.mysql.com and search for JOIN or use google and search for MYSQL JOIN and just read through some other tutorials

[Dirt_Diver]

okay I just read some how to's and still don't get it.

Here is what I tried what is wrong.


SELECT images.user_id, users.user_id
FROM users, images
WHERE images.user_id = users.user_id

and I get this message.
MySQL returned an empty result set (i.e. zero rows). (Query took 0.0005 sec)

thinking it didn't do anything I tried this


SELECT images.user_id, users.user_id
FROM images
AS user_id
LEFT JOIN users
AS user_id
ON users.user_id=images.user_id
WHERE images.user_id = users.user_id
And it errors out. This looks logical to me... Not sure what is going on.

I am using MySQLAdmin and if you could just write me a copy and paste code I would understand what you did by looking at it. I don't know how to set the FK... Please help me with this I don't understand it even after reading and pulling my hair out. ??Someone??

Here's my stuff as it is set up now

<images>
user_id (pk) UNSIGNED, Not Null, auto_increment

<images>
image_id (pk) UNSIGNED, Not Null, auto_increment
user_id

<challenges>
challenge_id (pk) UNSIGNED, Not Null, auto_increment
image_id

[boogyman]

SELECT images.user_id, users.user_id
FROM images
AS user_id
LEFT JOIN users
AS user_id
ON users.user_id=images.user_id
WHERE images.user_id = users.user_id


should be


SELECT img.user_id, usr.user_id
FROM images AS img
LEFT JOIN users AS usr
ON usr.user_id=img.user_id
WHERE img.user_id = usr.user_id


in the process of joining the tables, you were actually renaming both tables to the same name, which cannot be done. so I changed the name of the tables to a different short hand, then i carried that through the rest of the query... however you could have left out the shorthand, and you would have received this query



SELECT images.user_id, users.user_id
FROM images
LEFT JOIN users
ON users.user_id=images.user_id
WHERE images.user_id = users.user_id


that would still error out though, because both fields are named the same thing, so one of them you would need to create a shorthand.



SELECT images.user_id, users.user_id AS usr_user_id
FROM images
LEFT JOIN users
ON users.user_id=images.user_id
WHERE images.user_id = users.user_id


and the last bit of this, is that you do not need to JOIN the tables twice... I am not sure if this causes an error or not, but it definitely isnt necessary. The JOIN clause is meant to be the way to associate and "bring in" additional tables, and the WHERE clause is supposed to limit the results... so something like



... FROM tbl1 JOIN tbl2 ON tbl1.__=tbl2.__

would grab every result where the fields from table1 and table2 matched

where as


... FROM tbl1 JOIN tbl2 ON tbl1.__=tbl2.__ WHERE tbl1.id>100


would grab every result where the fields from table1 and table2 matched AND where the id on table1 was less than 100

so in the second statement, I am putting that 1 extra limitation. so to return to your query, If you would like every image in the database, then you can just delete the WHERE clause, however if you wanted to limit it to a specific user... like say grab every image this user has uploaded to display on their portfolio page or whatever, you would include the WHERE and populate it with the user id of that person

all images


[CODE]
SELECT images.user_id, users.user_id AS usr_user_id
FROM images
LEFT JOIN users
ON users.user_id=images.user_id


images from 1 person


SELECT images.user_id, users.user_id AS usr_user_id
FROM images
LEFT JOIN users
ON users.user_id=images.user_id
WHERE users.user_id=__

[Dirt_Diver]

I am getting the feeling that I need to add all this to my PHP pages now and not the SQL text box in my MYSQLAdmin right?

Sorry to keep bothering you.. And BTW I understood all that above just want to make sure I put it in the right spot to get it all to work.

[boogyman]

you might be able to create a procedure in your front-end client than just call the procedure when you want to run it, however, typically all queries are declared at the point of necessity, so I would say that yes, you need to update all of your pages that you wish to include multiple tables?