Mysql retrieving unwanted data.

[Stephen Philbin]

Hi folks. I think I'm doing something VERY wrong with my database here. I've been tinkering with relational databases for a bit now, trying to get the swing of 'em etc, but I think I may have been quite wide of the mark on one use of them. I'm trying to run queries that rely on the foreign keys to fetch a specific row set from some tables. Only trouble is though, when I run them, I get a load of data I didn't want.

I'll show you a very scaled down version of the database I built. The example I'll show you here has been downsized to a tiny scale, but the design characteristics are the same and it still produces the same undesired output. Oh it's also done directly on the command line to avoid any problems that may be caused by php's meddling with the queries and commands.

Here's the create table instructions:


CREATE TABLE IF NOT EXISTS user (
uid INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(25) NOT NULL,
password VARCHAR(20),
PRIMARY KEY (uid),
UNIQUE(name)
) ENGINE=Innodb DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS user_prefs (
uid INT UNSIGNED NOT NULL,
theme VARCHAR(25),
items_per_block TINYINT UNSIGNED DEFAULT 3,
images_disabled TINYINT UNSIGNED DEFAULT 0,
PRIMARY KEY (uid),
FOREIGN KEY (uid) REFERENCES user(uid) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=Innodb DEFAULT CHARSET=utf8;


Then pop in some data for three users:


INSERT INTO user VALUES(FALSE,'Tim','password');
INSERT INTO user_prefs VALUES(1,'cold_blue',DEFAULT,DEFAULT);
INSERT INTO user VALUES(FALSE,'Tom','wordpass');
INSERT into user_prefs VALUES(2,'hot_red',5,1);
INSERT INTO user VALUES(FALSE,'Dom','Rubberducky');
INSERT INTO user_prefs VALUES(3,'cold_blue',4,DEFAULT);


Once the data is in, I try to fetch most of the data about a single user (and only that user) by using a single query that relies on the foreign key to find data from other tables based on the matching up of the uid of the users name given in the query.

So here's the query I use:


select user.uid,name,password,theme,items_per_block,images_disabled from user,user_prefs where name = 'tom';


Now what I'd be expectint to be returned from that query would be:

+-----+------+----------+---------+-----------------+-----------------+
| uid | name | password | theme | items_per_block | images_disabled |
+-----+------+----------+---------+-----------------+-----------------+
| 2 | Tom | wordpass | hot_red | 5 | 1 |
+-----+------+----------+---------+-----------------+-----------------+


But what I actually get is rather different. Instead of that I get the details about tom that are held in the user table prepended to the details of every single user (including Tom) held in the user_prefs table. The results look like this:


+-----+------+----------+-----------+-----------------+-----------------+
| uid | name | password | theme | items_per_block | images_disabled |
+-----+------+----------+-----------+-----------------+-----------------+
| 2 | Tom | wordpass | cold_blue | 3 | 0 |
| 2 | Tom | wordpass | hot_red | 5 | 1 |
| 2 | Tom | wordpass | cold_blue | 4 | 0 |
+-----+------+----------+-----------+-----------------+-----------------+


As you can see it gives Tom's data held in user and user_prefs, but it also mixes Tom's details with Tim's and Dom's user_prefs data too and then returns the whole lot.

So then folks, what on earth am I doing wrong here? Everything?

[NogDog]

You need to join the two tables on the 'uid' columns:

SELECT user.uid, name, password, theme, items_per_block, images_disabled
FROM user, user_prefs WHERE name = 'tom' AND user.uid = user_prefs.uid;

[Stephen Philbin]

And lo, Nog Dog did with his wisdom smite the bafflment and all was yayness and smiles in the land of Herer once more. :D

[NogDog]

And it was good.

:cool:

[russell]

one addition, if i may, you should get used to using proper join syntax:

SELECT user.uid, name, password, theme, items_per_block, images_disabled
FROM user
INNER JOIN
user_prefs
On user.uid = user_prefs.uid
WHERE name = 'tom'

this is a little more efficient (though in a couple of small tables, you won't notice any performance difference), and a lot more readable if u have several tables.

Also, you can alias the table names for shorter, more readable queries

SELECT u.uid, name, password, theme, items_per_block, images_disabled
FROM user u
INNER JOIN
user_prefs p
On u.uid = user_p.uid
WHERE name = 'tom'
no big deal in your example, but when you are fetching several fields that might have the same name in more than 1 table in your query, you'll see the advantage as obvious.

anyway, thats my 2c worth.

regards,
rb