[RESOLVED] How to link data in one table to another table??

[Francoise]

I've finally succeeded, at least for the moment, in setting up the registration and login scripts for my proposed membership site. Now I'd like to enable potential members to enter some data, using a form that is provided.

My database has two tables -- users and entries. In the users table, each user has a user_id.

My question is - how do I link the correct user_id (in the users table) to that user's data (in the entries table)? Right now, there is no link at all!

It seems like there must be some very obvious answer to this question, but so far I haven't been able to find it. 😕

Please help! :queasy:

---

ETA: Tables are linked by keys, right? So I tried using keys, but that has not worked.

Initially, I was using MyISAM tables, in phpMyAdmin. MyISAM doesn't support foreign keys. phpMyAdmin offers primary, unique, and index keys - but which one of those would I use??

So I switched to InnoDB tables, thinking that I would use a foreign key. I think I set it up correctly, so that user_id, the primary key in the users table, is a foreign key in the entries table.

However, all I have gotten for my trouble is an error message --

Could not add the entry because:
Cannot add or update a child row: a foreign key constraint fails (/entries, CONSTRAINT entries_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (user_id)).

Does anyone know what that means, and how to correct it?

Please help, if you can. Earlier this week it looked like I was making progress, but now it feels like the same old obstacle course. 🙁

[Bonesnap]

Include a user_id column in the entries table. Each entry has to "belong" to a user. Then when you want to retrieve the entries by a particular user, you can do something like this:

SELECT * FROM entries WHERE user_id = 42

The user_id will be provided in the code. You could do something like this:

<?php
function GetEntry($id)
{
	$row = mysql_fetch_array(mysql_query("SELECT * FROM entries WHERE user_id = ".$id));
	return $row;
}
?>

When the user logs in, store their user_id in a session variable, so every time they make a new entry, it gets inserted into the database in the entries table. Hope this helps!

[Francoise]

Bonesnap, thank you for your reply. I really appreciate it. 🙂

It might be worth mentioning that I am a senior citizen (or almost), and that all of this is quite a challenge for me. Sometimes, like today, it is also a severe trial. 🙁

I have to say, I'm still confused. My question is -- how do I link "user_id" in table1 to "user_id" in table2 ???

When user42 registers, he or she is assigned user_id '42' in the users table. So far, so good. However, he has no presence of any kind in table2.

I would really, really like to be able to use a foreign key on this. (Foreign keys make sense to me, and I haven't really tackled sessions yet.)

There is, I'm sure, a simple way to use user_id in table1 as a foreign key in table2. The only question is . . . how? I thought I had done so, but I keep getting the "constraint" error message every time I try to enter data.

[jeepin81]

I'm only guessing here but I think what you're trying to do is JOIN tables together via a unique id. There are different types of JOINS ie: LEFT, RIGHT, CROSS, etc.. you'll have to search and see which type of JOIN will work best for you needs.

here's a sample

SELECT * FROM TABLE1 LEFT JOIN TABLE2 on T1ID = T2T1ID WHERE T1ID = '123';

Hope that helps point you in the right direction.
j

[Francoise]

Howdy, Jeep,

Thank you for your reply. It is very nice of you to respond.

I don't think my question was clear, though, because I don't think that my problem has to do with joins. You use a join when you want to retrieve data, right? My question has to do with a step that is much earlier in the process.

Suppose my application has to do with our favorite rock bands. User_1 writes that his favorite band is the Dave Matthews Band. User_2 writes that her favorite is the Rolling Stones. Both of those entries go into the "entries" table. Both users log out, and their sessions come to an end.

Next week, one of them wants to look at their own entry. How will the program know that the Dave Matthews Band entry is associated with User_1? and that the Stones are associated with User_2?

There has to be something that permanently links the User_1 column to his entries; and something that permanently links User_2 column to her entries. But what is it?

jeepin81;10966478 wrote:

I'm only guessing here but I think what you're trying to do is JOIN tables together via a unique id. There are different types of JOINS ie: LEFT, RIGHT, CROSS, etc.. you'll have to search and see which type of JOIN will work best for you needs.

here's a sample

SELECT * FROM TABLE1 LEFT JOIN TABLE2 on T1ID = T2T1ID WHERE T1ID = '123';

Hope that helps point you in the right direction.
j

[Francoise]

Maybe I could put it this way.

Suppose I'm User 1, and last week I entered the Dave Matthews Band as my favorite. That information went into Table2, the entries table. (Table2 has only one column, band_name. )

Next week I want to open up my account and look at what band I chose. In the meantime, ten other people have entered their own favorite bands in the "entries" table.

How can I make sure that I am looking at my own data, and not at someone else's? What is there, in the database structure, to mark that entry as mine, and not as someone else's?

[jeepin81]

okay. I see what you are looking to do.

Table1 = User Info
Table2 = Bands People like

Here's how i would set it up:

Table1
UID FNAME LNAME 
1   Jon       Smith
2   Sara     Anderson
3   Karl      Stevenson


Table2
BID  T1UID  FavBand
1     3        ACDC
2     3        Led Z.
3     3        Eric Clapton
4     1        Reel Big Fish
5     1        Less Than Jake
6     2        Kings of Leon
etc....

Than you would JOIN the tables like stated before

SELECT * FROM Table1 LEFT JOIN Table2 on UID = T1UID WHERE UID = '2' 

That result set would bring back all of Sara's favorite bands.

j

[Francoise]

Jeep, I think we are on the same page. 🙂

My question is -- how does "T1UID" get there?

Your second table clearly shows an integer, in the second column, that references the user_id in the first table. How does it get there?

I truly appreciate your help.

[jeepin81]

Basically, column T1UID in Table2 is the UID from Table1. So, All the 3's in columns T1UID in Table2 associate with User Karl.

[Francoise]

I understand that. But . . . how does the information about the user get into Table2???????????????????

In other words, I understand that the two tables are linked. But how does one bring the link about? what is the mechanism by which UID in table1 is linked to T1IUD in table2?

[jeepin81]

You'll have to write a script that inserts that data when people select their favorite band. Say, Karl is logged in and the band Right Said Fred comes up and there is a "Like" button. You would

$userid = '3';

INSERT INTO Table2(T1UID,FavBand) VALUES('$userid','Right Said Fred');

[jeepin81]

You would have to get their user id from Table1 when they log in. So, the entire process would be:

1. They Log in
2. You get their info from Table1 and get their User ID
3. They select a song they like
4. Your "Like Band" script inserts the data into Table2 with their User ID

[Francoise]

There is just something so completely missing somewhere.

I have a very simple data form on the "loggedin" page. Say user1 is logged in. He enters data into the form, and that data is entered into the entries table.

The form does not know, however, which user he is. And the form is used by all users.

What, then, is the mechanism -- where does the script go - for transferring all this information into the entries table?

[Francoise]

jeepin81;10966489 wrote:

You would have to get their user id from Table1 when they log in. So, the entire process would be:

1. They Log in
2. You get their info from Table1 and get their User ID

Yes - but how?

3. They select a song they like
4. Your "Like Band" script inserts the data into Table2 with their User ID

[jeepin81]

what is your login script?

[Francoise]

Jeep, the answer has something to do with sessions. Right?

It's becoming ever more clear to me that that has to be the answer.

Do you have any experience with sessions? It must be all too apparent that I have none. :o

It's been really great of you to hang on with me, and help me inch forward. 🙂

---

FWIW, here is my login script -

<?php 

ob_start();     // Start output buffering.


?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"

    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">

<head>

<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />

<title>Login</title>

</head>

<body>

<?php

// Check if the form has been submitted.


if (isset($_POST['submitted'])) {

   require_once ('../mysql_connect.php');   // Connect to the db.

// Check for an email address.

if (!empty($_POST['email'])) {

	$e = escape_data($_POST['email']);

} else {

	echo '<p><font color="red">You forgot to enter your email address!</font></p>';

	$e = FALSE;

}

// Check for a password.

if (!empty($_POST['password'])) {

	$p = escape_data($_POST['password']);

} else {

	echo '<p><font color="red">You forgot to enter your password!</font></p>';

	$p = FALSE;

}


if ($e && $p) { // If everything's OK.


	/* Retrieve the user_id and first name for that email/password combination. */

	$query = "SELECT user_id, first_name FROM users WHERE email='$e' AND password=SHA('$p')";

$result = @mysql_query ($query); // Run the query.


	$row = mysql_fetch_array ($result, MYSQL_NUM); // Return a record, if applicable.


	if ($row) { // A record was pulled from the database.

			// Set the session data & redirect.

		session_start();

		$_SESSION['user_id'] = $row[0];

		$_SESSION['first_name'] = $row[1];


		ob_end_clean(); // Delete the buffer.


		// Redirect the user to the loggedin.php page.


		// Start defining the URL.


[Etc.]

[jeepin81]

So, once they're logged in you're creating a session which has the user id.

session_start();

$_SESSION['user_id'] = $row[0];

$_SESSION['first_name'] = $row[1];//<- That's their user id stored into a session

So, now you're next script will be the "like" button. They click that button and you insert their choice into Table2

INSERT INTO Table2(T1UID,FavBand) VALUES('".$_SESSION['user_id'] ."','Right Said Fred');

[Francoise]

Jeep, two questions.

First, how can I embed my php code in a 'window' the way other people have? (the way it's supposed to be)

Second, I'm not sure if I know how to incorporate the user's choice ("Right Said Fred") into the statement -- how the user's choice becomes part of the code.

Once again, I'm grateful for your help.

[Francoise]

Just testing to see make sure the

 tags work -- 

[code=php]
if ($row) { // A record was pulled from the database
.

// Set the session data & redirect.

session_start();

$_SESSION['user_id'] = $row[0];

$_SESSION['first_name'] = $row[1];


ob_end_clean(); // Delete the buffer.


// Redirect the user to the loggedin.php page.


// Start defining the URL.


[Etc.]

Yep. Cool. 🙂

[Francoise]

Jeep, thanks to your patient and wonderful help, I was able to do what I wanted!!! Just now I succeeded -- it seems to be working perfectly. I'm so pleased -- thank you so very, very much for everything. 🙂 🙂 🙂