GrumpyGeek
-
Posts
10 -
Joined
-
Last visited
Content Type
Profiles
Forums
Events
Posts posted by 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
-
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 usersWHERE 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? -
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?
-
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
-
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?
-
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
-
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
-
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
-
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
Link information in tables
in SQL
Posted
Thanks everybody for your help.
Special thanks to "justsomeguy" for helping me lots on this simple problem.
Cheers!
GG