What is a good way to make one table 'interact' with another?

[Anne Arbor]

My small PHP program has two tables, each with 4-5 columns. So it's about as simple a database as one could have.

There is a users table and a temps (temperatures) table. The idea is that a user fills out a form and records the current temperature. That data is then entered into the second table of the database.

The data in the second table needs to be tied in some way to the individual user who entered it. When I was setting up my tables, I wanted to somehow make a direct connection between the user_id of the first table and each row of data that would belong to that user in the second table.

Eventually I used sessions for doing this, but I've never been particularly happy with that method.

Is there another, better way of accomplishing the same goal?

[djr33]

Cross-referencing with an ID in a database is standard. It sounds like you're already doing that, so that isn't the problem.

Each row in a database can (usually) be thought of as a thing-- in a website as used cars, one row (in the "cars" table) is a car-- it's model, make, color, year, price, etc. Sometimes, though, we want to have more than just lists-- we want to relate several of these (usually real-world) things. We might want to have an employee in charge of selling a particular car, so we'd link the car_id and employee_id somehow. Probably, because the car has one employee (not the other way around) we'd include the employee_id in the information about the car, in its row.

So again, I think you're set with that, from what you've said.


The issue, instead, starts here:

Eventually I used sessions for doing this, but I've never been particularly happy with that method.

I'm confused by this transition in your topic-- the rest made sense, then this seems like a different idea.

Sessions have nothing to do with database design. They're a completely separate kind of information. Certainly you can use sessions (or various other methods) for dealing with information before or after you store it in the database.

So, am I right in understanding that the problem is how to use and access information as you are going? This may be a problem with knowing how to use sessions or variables in general, but it doesn't seem like a database problem.



Sessions work like this:
1. Use session_start() on every page where you need sessions, right at the top. (If you have a main configuration page that is included into every page, put it at the top of that shared file.)
2. You can use $_SESSION['x'] just like $x, whenever you need to. Nothing more. It's a little different because it's an array, so it's like $x['y'], which is also valid.
3. To know if a value is set, you can use isset($_SESSION['x']) and that will return TRUE or FALSE, which you can (in an if statement) do whatever you want with.
4. Sometimes if you get too far buried in certain levels of code (generally functions) you run into problems with variable scope. But session variables are global scope-- they're always variable; you will never have a problem with that. (Assuming you follow step 1.)

The only other thing to think about is overall design: how do you know which id you want to use? The computer can't access it in exactly the same way, but you can help it along by storing the information at the relevant points.

[traq]

To add to Daniel's response, what you're asking about are called FOREIGN KEYs. Databases support them natively (though MySQL's default engine -MyISAM- does not; you need to use the InnoDB engine). For a simple application, you can probably manage these relationships in your PHP code. But, for larger applications, it is very useful for your database to take care of it itself.

There are basically three types of relationships in relational databases: one to one, one to many, and many to many.

Daniel's suggestion (having a row in the table that defines the relationship, in his example, car-to-salesman) works well for the first two, because no car has more than one salesman (a salesman might have other cars, but not the other way 'round).

For a many to many relationship, you use a third table (called an "intersection table") to define the relationships: for example, cars and passengers. One column for the car_id, one for the passenger_id, and you use joins to connect all of the records.

If you want to use MySQL for this, you could define your temperature table something like so:

CREATE TABLE `temperature`(
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `temperature` INT NOT NULL,
    `user_id` INT NOT NULL,
    --assuming your user table has a primary key named "id"
    FOREIGN KEY( `user_id` ) REFERENCES user( `id` )  
)ENGINE=InnoDB;

Now, the database won't allow records unless the user id exists in the user table.

[Anne Arbor]

Daniel, thank you very much for your reply and all the information, especially about sessions.

My initial post seemed strange to you, I think, because my program is actually written in a rather strange way. What I am trying to talk about is how to "link" one table with another table, or how to "associate" the user_id of one table with that user's data to be stored in the second table. You are assuming that I'm already doing that -- but I think that assumption is wrong!

When I was trying to write this program, I went to a different forum and asked my question in terms of foreign keys. I was told the problem had nothing to do with foreign keys. Then I went to a second forum and asked how to do the necessary linking and no one seemed to know how but somehow the idea of using sessions emerged.

As currently written, my program enters user_1's data into the temps table using sessions - and sessions alone. I have a user_id column in the users table, of course. I also have a user_idm column in the temps table (where the real data is stored) but there is no necessary correlation between the two in terms of how the tables are structured.

Instead, the following query is used to store user_1's data in the temps (or data) table:

		$query = "INSERT INTO temps (user_idm, temp_rating, notes, time_entered)
                                              VALUES ('$_SESSION[user_id]', '$temp', '$notes', NOW())";

		if (@mysql_query($query)) {
			print '<p>The entry has been added!</p>';
                 }

There is no other kind of linkage between the two tables.

It's always seemed to me that this was a rather weak way of going about this. There must, surely, be some stronger way of linking the two tables other than using session variables?

[traq]

What I am trying to talk about is how to "link" one table with another table, or how to "associate" the user_id of one table with that user's data to be stored in the second table.
When I was trying to write this program, I went to a different forum and asked my question in terms of foreign keys. I was told the problem had nothing to do with foreign keys.

The issue can be solved without using foreign keys, but this is exactly what foreign keys do.

Then I went to a second forum and asked how to do the necessary linking, and somehow the idea of sessions came out of that.

As currently written, my program enters user_1's data into the temps table using sessions - and sessions alone! I have a user_id column in the users table, of course. I also have a user_idm column in the temps table (where the real data is stored) but there is no necessary correlation between the two in terms of how the tables are structure.

This seems odd, and potentially error-prone. Can you share your database schema (i.e., the output of SHOW CREATE TABLE {your table name goes here})?

(edit: I am also unsure of what you mean by "uses sessions" to enter data into your database.)

[Anne Arbor]

traq, thank you for using the term 'foreign key'! As mentioned to Daniel, it seemed to me that I needed to be using foreign keys way back when I first tried to write this program, but others shot that idea down and so I dropped it.

My database has only the two tables - one for users, one for data about temperatures. It's a one - to -many relationship, I assume, because each user records many temperatures.

The users table has a column called user_id - which should, it seems, be designated as a primary key, although I never actually did that.

As mentioned already, the temps table has a user_idm column, and this column is currently filled in by the INSERT query using the session variable ['user_id'] --

 
		$query = "INSERT INTO temps (user_idm, temp_rating, notes, time_entered)
                  VALUES ('$_SESSION[user_id]', '$temp', '$notes', NOW())";

		// Execute the query:
		if (@mysql_query($query)) {
			print '<p>The entry has been added!</p>';

This has always felt like a rather 'weak' way of tying the two tables together. My question is whether there is a better way of accomplishing the same goal -- of inserting user_1's data into the temps (data) table while making clear the association with user_1.

[Anne Arbor]

Sure, my database looks like this:

1st table - users:

Field 		Type 		        Null 	        Default     Auto_increment 

user_id 	smallint(5) 	        No		yes          auto_increment
email 		varchar(40) 	No     
password 	varchar(40) 	No     
first_name 	varchar(15) 	No     
last_name 	varchar(30) 	No     
active 	char(32) 	        Yes
registration_date datetime 	No

2nd table - temperatures:

Field 		       Type 		Null 	Default  

user_idm 	       smallint(5) 	No     
temp_rating	       char(2) 	No     
notes        	       text 	        Yes	NULL
time_entered	datetime 	No     
time_entered2	timestamp	No 
CURRENT_TIMESTAMP

When I say that I 'use sessions' to link the user_id to that user's records in the data table, I just mean that I use the following query to add user_1's records to the data table. It depends on there being a session and it uses the session data. That is, if there weren't a session, I would have no way of entering data into the temps table.

		$query = "INSERT INTO temps (user_idm, temp_rating, notes, time_entered)
                                              VALUES ('$_SESSION[user_id]', '$temp', '$notes', NOW())";

		if (@mysql_query($query)) {
			print '<p>The entry has been added!</p>';
                 }

[traq]

This has always felt like a rather 'weak' way of tying the two tables together. My question is whether there is a better way of accomplishing the same goal -- of inserting user_1's data into the temps (data) table while making clear the association with user_1.

From the database's view, the foreign key is the way to do it. However, while the database can enforce this relationship, it can't guess it: your application still needs to tell the database which user the temperature report belongs to.

Sure, my database looks like this …

In order to implement foreign key relationships, you would need to:

a) make sure the storage engine supports it

(I can't tell from your description which engine you're using, though if you didn't specify it's probably "MyISAM": it needs to be "InnoDB")

b) make users.user_id the primary key

c) make temperatures.user_idm a foreign key that references users.user_id

When I say that I 'use sessions' to link the user_id to that user's records in the data table … It depends on there being a session and it uses the session data. That is, if there weren't a session, I would have no way of entering data into the temps table.

IF your user is required to be logged in when reporting temperatures, AND logged-in users always have their user_id stored in the session, then this is a fine (and quite common) way to pass that information to the database.

If not, then you'd need some other way to get the user_id… but overall, requiring them to log in is probably the best way to do it.

[Anne Arbor]

traq, I might even understand how to do this. :-)

Does any of this shed any light on the problem of temps data sometimes being assigned to the wrong user? that is, to the non-existent user "0" instead of the actual user "1" (or "9" or whatever)?

Thank you for all of your replies. My sense is that a bunch of things that have long remained murky are starting to clear up.

[traq]

Does any of this shed any light on the problem of temps data sometimes being assigned to the wrong user? that is, to the non-existent user "0" instead of the actual user "1" (or "9" or whatever)?

Not really; the most likely explanation is still that your session is failing intermittently for some reason. When that happens, and you try to use $_SESSION["user_id"], it will be NULL - which MySQL will interpret as 0 (because user_idm is a not-null integer column, and -by default- mysql does nonsensical things like pretending certain mistakes don't happen).

Regarding that problem, you might add something like this to try and catch the error "in the wild" (no better testing than actual use):

<?php
// some point, on same page, before you try to use $_SESSION["user_id"]

if( empty( $_SESSION["user_id"] ) ){

    $info = "\n\nmissing user_id report\n======================\n\n";

    // basic info about the request
    $info .= "date/time: ".date( "r" )."\n";
    $info .= "user agent: {$_SERVER["HTTP_USER_AGENT"]}\n";
    $info .= "referer: {$_SERVER["HTTP_REFERER"]}\n";

    // is there a session at all? what info is in it?
    $info .= "\nSESSION information:\n";
    $info .= session_id()?
        var_export( $_SESSION,true )."\n":
        "no session exists";

    // did the user provide any cookies (i.e., the session cookie)?
    $info .= "\nCOOKIE information\n";
    $info .= ! empty( $_COOKIE )?
        var_export( $_COOKIE,true )."\n":
        "no cookies provided\n";

    // add anything else you want to record about the request (maybe $_POST data? something else?)

    // keep in mind that anyone might access this file if you put it in your web root.
    // it will contain potentially sensitive information about your scripts and the user in question.
    // you might want to save it above your web root and check it via ftp, or put it in a password-protected directory.
    $path = "/absolute/path/you/choose/user_id_error.txt";

    // write info to file so you can look at it later
    // (you could send yourself an email, too)
    fileputcontents( $path,$info );
}