Computer Science Canada MySQL Confusion |
| Author: | Okapi [ Sun Mar 08, 2009 8:22 am ] |
| Post subject: | MySQL Confusion |
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 |
|
| Author: | Vertico [ Sun Mar 08, 2009 7:27 pm ] |
| Post subject: | RE:MySQL Confusion |
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. |
|
| Author: | Okapi [ Tue Mar 10, 2009 11:42 pm ] |
| Post subject: | RE:MySQL Confusion |
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 |
|
| Author: | CyberGeek [ Sun Mar 15, 2009 5:58 am ] |
| Post subject: | RE:MySQL Confusion |
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 |
|
| Author: | Okapi [ Sun Mar 15, 2009 10:21 am ] |
| Post subject: | RE:MySQL Confusion |
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? |
|
| Author: | rdrake [ Sun Mar 15, 2009 11:01 am ] |
| Post subject: | Re: RE:MySQL Confusion |
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 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. |
|
| Author: | Okapi [ Sun Mar 15, 2009 11:21 am ] |
| Post subject: | RE:MySQL Confusion |
I see, thanks! |
|
| Author: | jeffgreco13 [ Sun Mar 15, 2009 7:46 pm ] | ||
| Post subject: | Re: MySQL Confusion | ||
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.
|
|||
| Author: | Okapi [ Thu Mar 19, 2009 8:34 pm ] |
| Post subject: | RE:MySQL Confusion |
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 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? |
|
| Author: | jbking [ Thu Mar 19, 2009 10:02 pm ] |
| Post subject: | Re: RE:MySQL Confusion |
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 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. |
|
| Author: | rdrake [ Thu Mar 19, 2009 10:54 pm ] |
| Post subject: | RE:MySQL Confusion |
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. |
|
| Author: | Amailer [ Fri Mar 20, 2009 12:56 pm ] |
| Post subject: | RE:MySQL Confusion |
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. |
|
| Author: | Tony [ Fri Mar 20, 2009 1:08 pm ] |
| Post subject: | Re: RE:MySQL Confusion |
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. |
|