Link information in tables

[GrumpyGeek]

Hello W3School,

 

I have been programming for a while now but i haven't done much with SQL databases.

 

I am writing a website which is fine. My website needs a database. I am using phpmyadmin to management my database.

 

Right now i have one database and within that database i have 2 tables one for the user information and the second for the hardware information. Where I am stuck is in the user table there will be one user per person which is fine. In the hardware table there could be multiple things per user.. like laptop, monitor docking station.

 

What I am looking to do is when I display a user i want all the pieces of hardware for that user to display as well.. i don't know how to link all the hardware in the hardware table with the user

 

Any help would be greatly appreciated.

 

Thanks

GG

 

 

[justsomeguy]

Have a column in the hardware table that contains the user ID that it belongs to.

[GrumpyGeek]

Have a column in the hardware table that contains the user ID that it belongs to.

 

where do i get the user ID from in the user table? How would I display the user and hardware information when i search for a user?

 

Sorry really new to SQL... although i am reading as much as i can

[justsomeguy]

You should probably read up on database design rather than SQL specifically. SQL is just a language that databases understand, learning SQL won't give you any information about how to efficiently design a database.

 

Most tables will need a unique ID. The easiest way to do that is to make an integer column that automatically increments. Various databases have various names for that. When you insert a new record, there are ways to get the auto-increment ID of the new record. You would use that ID as the foreign key for any other table that needs to refer to a user.

 

When you search for the user you'll get the user ID along with the rest of their record. Since you have the user ID, you look up the hardware with that user ID.

 

This is the structure for database objects that have a one-to-many relationship (e.g., each one user can have many pieces of hardware, but each one piece of hardware is only owned by a single user). For objects that have a many-to-many relationship you need to use a different structure. A many-to-many relationship might be something like the relationship between schools and students. A school can have many students, and a student can attend many schools. For that kind of relationship you need to use a third table, a lookup table. You would have a table for students that includes the student ID, a table for schools that includes the school ID, and a table that only lists school IDs and student IDs, to represent which students attend which schools.

[GrumpyGeek]

this is bring back memories of the database course that i took in university lol

 

ok, i am starting to understand..

 

Here is what i am thinking

 

user table

 

user_id ------ primary key

first_name

last_name

 

hardware table

 

hardware_id ---- primary key

serial

model

type

user_id ---- foreign key (link to the user table)

 

example

 

user table

 

user_id first_name last_name

01 John Doe

02 Jane Doe

 

hardware table

 

 

hardware_id serial model type user_id

01 cac21 Dell Desktop 01

02 cac22 HP Laptop 01

03 cac24 Apple Laptop 02

 

Does this work?

 

Thanks

GG

Edited December 3, 2013 by GrumpyGeek

[davej]

Or you could create a third table...

 

entry_no auto-increment pk

hardware_id fk

user_id fk

date_assigned

date_returned

return_condition

[GrumpyGeek]

Or you could create a third table...

 

entry_no auto-increment pk

hardware_id fk

user_id fk

date_assigned

date_returned

return_condition

not only is the third table a good idea but some of those fields are thing i didn't even think about thank you.

 

I am just not how to do my select statement. I mean to display the user information is easy and to display the hardware wouldn't be that hard i guess what i don't know who to do is a select statement that say here is the user, and here is the corresponding hardware to that user. I know it clearly has to do if user_id but I just can't get my mind around it

[justsomeguy]

You would want one query to get the user data, and another query to get the hardware. You could get them both if you use a join query but since all records in a result set need the same fields then you would end up with a result set of hardware records with the user data duplicated in each row.

[GrumpyGeek]

so a query to get the user data is no problem..

 

What i am having trouble getting my head around is the query that links multiple hardware with that user. Could you give me an example when you get a second?

Edited December 4, 2013 by GrumpyGeek

[GrumpyGeek]

Would something like this work?

 

select u.user_id, u.first_name, u.last_name, h.hardware_id, h.serial, h.model, h.typefrom user u <-- the "u" is the alias to user table... JOIN hardware h <-- "h" is the alias for hardware table on u.user_id = h.user_idorder by u.first_name, u.last_name, u.user_id

[justsomeguy]

I would just do this:

 

SELECT * FROM hardware WHERE user_id = 123

 

You can get the user ID from the other query result where you get the user information.

[GrumpyGeek]

I would just do this:

 

SELECT * FROM hardware WHERE user_id = 123

 

You can get the user ID from the other query result where you get the user information.

Yea the other query would be getting the user ID i am just not displaying it.

 

So when i do a search for a user the first query will go out and get all the information. How does the second query know the information from the first query?

Edited December 4, 2013 by GrumpyGeek

[justsomeguy]

You add the data to the query with PHP.

[GrumpyGeek]

You add the data to the query with PHP.

so this is what i am using to get the user information

 

$userinfo = mysqli_query($connection,"SELECT * FROM users

WHERE first_name=$_POST["firstname"] OR last_name=$_POST["lastname"] ");

while($row = mysqli_fetch_array($userinfo))

{

echo $row['first_name'] . " " . $row['last_name'];

echo "<br>";

}

?>

so $row would contain the user_id correct?

Edited December 5, 2013 by GrumpyGeek

[justsomeguy]

Yes, although if the values in $_POST are strings then you will need to surround them with quotes in the query. You also have some double quotes in there that are messing up the string.

 

$userinfo = mysqli_query($connection,"SELECT * FROM usersWHERE first_name='" . $_POST["firstname"] . "' OR last_name= '" . $_POST["lastname"] . "'");

You should also escape the data in $_POST, if those values contain a single quote then the query will fail. You can stop that by using something like mysqli_real_escape_string to escape any characters that would break the query.

[GrumpyGeek]

 

Yes, although if the values in $_POST are strings then you will need to surround them with quotes in the query. You also have some double quotes in there that are messing up the string.

 

$userinfo = mysqli_query($connection,"SELECT * FROM usersWHERE first_name='" . $_POST["firstname"] . "' OR last_name= '" . $_POST["lastname"] . "'");

You should also escape the data in $_POST, if those values contain a single quote then the query will fail. You can stop that by using something like mysqli_real_escape_string to escape any characters that would break the query.

 

 

Thank you for the great advice. I made all the changes.

 

based on that code would you be able to give me query for the hardware. I have tired a couple of things and i just can't get it to work

[justsomeguy]

Something like this, I imagine:

 

 

mysqli_query($connection,"SELECT * FROM hardware WHERE userID=" . $row['id']);

[GrumpyGeek]

Thanks everybody for your help.

 

Special thanks to "justsomeguy" for helping me lots on this simple problem.

 

Cheers!

GG