hello there!

matt5409

I'm new to the forum and new to PHP. after hating it's guts for some time, i am slowly (and reluctantly!) coming around to it's way of thinking.

now, onto my problem...

i am wanting to enfore referential integrity between two mysql tables (both of which are innodb).

so far, i have:

users:

id *
name
pass
email

songs:

song_id
id
title
lyrics

i am wanting to create a small site where each user can store songs they have written, in a 1:many fashion. i've achieved this in about 2 minutes using Access but am struggling with mysql. any help would be very much appreciated! 🙂

thanks, Matt

Wynder

A new person to PHP in the newbie forums?!?!1one Unheard of! 😃

Add a third table:

users_x_songs
user_id
song_id

Both being the primary key to prevent duplicate entries.

So you can store my user id (240 for example) for Song 1, 3 4 (in separate rows: 240 1, 240 3, 240 4) and do like:

SELECT songs.title
FROM songs, users_x_songs
WHERE users_x_songs.user_id = 'my_id'
AND songs.song_id = users_x_songs.song_id;

Which would return the song names of songs 1, 3 and 4.

Something along those lines should work.

bpat1434

Or, just add a column in the songs table "writer" and populate that with the id from the users table.

Then join the tables together using the User ID.

Table: users
[indent]Fields:
[indent] id (Primary Key, auto-increment, Not-Null)
name
pass
email[/indent][/indent]

Table: songs
[indent]Fields:
[indent]song_id (Primary Key, auto-increment, Not-Null)
title
lyrics
writer (int(11))[/indent][/indent]

SELECT a.*, b.*
FROM songs a
INNER JOIN users b
ON a.writer = b.id

I see you have the "id" and "song_id" field in the songs table, I think my suggestion just solidifies what you were thinking....

Wynder

Though it's quite common to have songs with multiple composers/writers -- assuming they were all a part of your database, you could account for that with a third table. Brett's option kind of limits you to 1:1 for song author but a 1:Many for authors to songs.

Completely depends on how you're setting up to record stuff like that. If you're just getting the primary author/composer, I'd go with Brett's way.

frost110

I would change the name of the "id" fields to be unique. IE: userid songid etc.

It is bad practice to make them all one id, and if you have a table joining the 2 ids it can make your SQL a ton more complicated then it needs to be.

--FrosT

Wynder

Yeah.. Usually for my PK ID's, I'll use caps for that... UID, SID, CID, TID, etc... and lower case for everything else.

frost110 wrote:
I would change the name of the "id" fields to be unique. IE: userid songid etc.

It is bad practice to make them all one id, and if you have a table joining the 2 ids it can make your SQL a ton more complicated then it needs to be.

--FrosT

bpat1434

I honestly disagree with that statement. I've never really worried about what they're called (as long as they're not key/reserved words in the D😎. Just because you use the same column name doesn't mean it's confusing. You can use aliases to reference them after the query, and using "table.column" structure is best when dealing with more than one table. It ensures that data is coming from that specific area.

Using the same column name has its advantages. For example, these are all the same:

SELECT a.*, b.*
FROM table a
INNER JOIN table1 b
  ON a.id = b.id

SELECT a.*, b.*
FROM table a, table1 b
WHERE a.id = b.id

SELECT a.*, b.*
FROM table a
INNER JOIN table1 b
  USING(id)

Using the same column name makes it easier for joins. And if you write your queries right, you won't have to worry about confusing yourself. But it's a preference thing. I've seen plenty of good apps that use the same column name, and others that don't.

matt5409

wow! too much for my small brain to swallow in one go, but thanks very much for the prompt replies, i'll get on it and hopefully post my successful results some time 🙂