MySQL Confusion

[Okapi]

Hey guys, haven't been here in awhile because I have been busy. Have some time off now so I'm looking at PHP. It's going fine, nice and easy to pick up but I am a bit confused with MySQL.

What I want to do:
Have a form where people can type in what they want and it will appear on a page.
So like a guestbook type thing (no need to register)

Problem:
I realise I have to save their posts in a database. So i create a database and a table but not sure how I store the posts. In a row each? Should I give the posts a unique ID for getting them too?

Any links or help would be appriciated! Thanks

[Vertico]

You will definitely want to go look up databases fundamentals. Entire jobs are based of how to set up databases for maximum potential.

I dont understand what you are trying to do enough to offer an example. Your first step is to list everything you will want to do and what you will want to save. Everything.

Then determine what is required, what isn't and what is completely useless.


On that note, each row/record in a database needs to have some sort of unique ID. Either its established through an autonumber, timestamps or a combination key, no two rows should be the exact same.

Also go read up on normalization.

[Okapi]

Basically I wanted to know how to insert and take out information of a MySQL database. I've worked it out now I think. Well, I can get information in and am working on how to get it out now

[CyberGeek]

Hey,

This is simply just an insert statement. Depending on how your database table is setup depending on a couple of things.

You will have to submit data - validate it, etc. However, take a look at:

http://dev.mysql.com/doc/refman/5.1/en/insert.html

For information on INSERT.

Now, to retrieve this data, you will need to SELECT data and build a rowset.

http://dev.mysql.com/doc/refman/5.0/en/select.html

For reference to PHP's MySQL functions - take a look here: http://ca2.php.net/manual/en/book.mysql.php

[Okapi]

Thanks. I made a simple blog type thing using php and MySQL so I think I have an ok understanding now.

Another question related to MySQL though...

You make a table and then it's columns. For example, a member system would have at least:
id, name, email

The user is able to enter more than one email address. Maybe infinite. Is the solution just to make the column WHEN the user adds in another email? Is that possible?

[rdrake]

Okapi @ Sun Mar 15, 2009 10:21 am wrote:

The user is able to enter more than one email address. Maybe infinite. Is the solution just to make the column WHEN the user adds in another email? Is that possible?

No. This brings up foreign keys and join. For a 1:n relationship like the one you describe you'd need two tables.

users:
id
name

emails:
user_id
email

So let's say you had a user like so:
1
Okapi

You'd have entries in your e-mails table like this:
1
email1@example.com
-----
1
email2@example.com

Notice how the two entries in the e-mail table both point to the same user. This means the user has two e-mail addresses.

[Okapi]

I see, thanks!

[jeffgreco13]

there's also a way to just add a column to an existing table. it is much sloppier this way tho so i still suggest havin the multiple tables with id reference.

code:

ALTER TABLE contacts ADD email VARCHAR(60) AFTER name;

[Okapi]

I am still having trouble getting my head around relationships.

Say I have a website which has lots of users and lots of items. A user can associate itself with as many items as it likes.

So could have 10 users attached to 1 item. That is a many-to-one relationship just like a user with more than 1 email. This is an item with more than one user.

So item might be:
item ID
title
user ID

user might be:
User ID
name
email
password

This would make a copy of the same item (same item ID) for each user. Would this be the most effiecient way of doing it?

[jbking]

Okapi @ Thu Mar 19, 2009 6:34 pm wrote:

So could have 10 users attached to 1 item. That is a many-to-one relationship just like a user with more than 1 email. This is an item with more than one user.

So item might be:
item ID
title
user ID

user might be:
User ID
name
email
password

This would make a copy of the same item (same item ID) for each user. Would this be the most effiecient way of doing it?

No, because there is the possibility of duplicating entries in the item table. What you want to have are 3 tables as follows I think for most efficient:

item:
item ID
name
.... other fields defining an item.

user:
user ID
name
.... other fields defining a user.

useritem(or the relationship however you want to call it):
ID
user ID
item ID

The last table has IDs from the first two tables and an ID column to allow for removal of duplicates. This is a form of normalization I believe.

[rdrake]

I'd use the above except the UserItem.ID column. There's no need for it.

Compound primary key (MySQL supports that, right?) with UserID and ItemID.

[Amailer]

For "So could have 10 users attached to 1 item"

If you have your Item Table setup as

ID | USER_IDS | ITEM
--------------------------
you can do

1 | 1,2,3,4 | ITEM

Use php to split the USER_IDS into an array (explode()) and thats how you get your individual user. If you want to do a select for a specific user. Use the WHERE USER_IDS LIKE '1', should get you all items that USER 1 is associated with.

[Tony]

speed issues of text-search vs. indexed key aside...

Amailer @ Fri Mar 20, 2009 12:56 pm wrote:

Use the WHERE USER_IDS LIKE '1', should get you all items that USER 1

as well as all items of USER 10, 11, ... etc.