• 0

MySQL help


Question

Hi guys,

I've been learning MySQL on my own and just have a few questions on how to go about thinking in SQL

I want to have this scenario

The user registers to a test site I have, from there they fill out the usual data and it stores it to the `users` table with the `id` auto_increment.

so then they fill out a contact form.

saves the data to my `contact` table with an id of `contact_id` in my limited logic on how i would go about getting the contact data to match up with the user data so

that it outputs in the about_me.php page is to just set the `contact_id` = `id` from the `users` table and when ever i want the output data from the user i can just

tell the SQL queries to look for all tables that match the `id` from the `users` table right?. This way contact data isn't accessed or accidentally shown to another user right?

I'm thinking small and simple right here, I was hoping to have at least another 4 tables with different information about a single user. Its simple with only one user.

But if i have say 5 users then I was wondering how to create the SQL to match all data with its correct owner.

I'm not asking for someone to code for me, but just some insight on how to tackle this problem. I can research on my. But if you would like to show

me examples or working code that I can learn from then I would really appreciate it.

Thx guys

Link to comment
Share on other sites

5 answers to this question

Recommended Posts

  • 0

If you use the InnoDB storage engine you can set up 'foreign keys' between the tables, so the 'contact' table would have a field 'user_id' which is a foreign key of the user ID. InnoDB would enforce this relationship for you, but if you are using MyISAM you would have to do this yourself in the code.

You can then use JOINs on the user_id between the tables to get all the data you require :)

Link to comment
Share on other sites

  • 0

Hey, can someone give me an example. I have looked online but for some reason its just not sticking. I've picked up a book on this too but the example just somewhat trail off what I really want.

How can I make these two table connect with each other?

(example tables)

==users==

id (auto inc)

name

username

email

==contact_users==

ID

contact_id

address

phone

I know that if i made just one table and threw in ALL the information a user needs such as the contacts table and maybe even a settings table then I would find it easy to pull data out and not be afraid that another user would accidentally access another users information.

I looked into JOINS (which just left me with more questions than answers) and the FOREIGN key thing, which um....not 100% sure on how to use that yet.

if anyone could help I would really appreciate it!

Oh, and my host doesn't support InnoDB so I won't be able to use it

Link to comment
Share on other sites

  • 0

==users==

user_id

name

username

email

==contact_users==

contact_id

user_id

address

phone

SELECT * FROM users JOIN contact_users ON users.user_id = contact_users.user_id

This is a very rudimentary implementation.

Link to comment
Share on other sites

  • 0

hmm, will my PHP script will need to look like this?

//set session from login form

$session_user_id = $_SESSION['user_id'];

$user_id = mysql_query(SELECT $session_user_id FROM users)

then just make the $user_id equal all the related id's per table that hold the users information?

the script is not perfect, but just off the top of my head.

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.