Tables in MySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Jim
Forum Contributor
Posts: 238
Joined: Fri Apr 19, 2002 5:26 am
Location: Near Austin, Texas

Tables in MySQL

Post by Jim »

I'm very lost..

I can connect to a database, retreive data from a database, but for the life of me I can't figure out how to put information on one.

I can't figure out what varchar and stuff like that mean. Do you know of anywhere I can go to learn more about creating tables in MySQL?
User avatar
Ruiser
Forum Newbie
Posts: 8
Joined: Wed Apr 24, 2002 11:04 pm
Contact:

Post by Ruiser »

There are some nice php tools to help you administrate a mysql database. phpMyAdmin is one. That will help you add tables, and you can even add data there. Check out http://www.mysql.com and read through the documentation.
Brian
Forum Contributor
Posts: 116
Joined: Thu Apr 18, 2002 5:33 pm

Noooooooooooooooooooooooo!!! :(

Post by Brian »

Okay, so I guess administration tools have their place, but I do recommend learning some general database concepts and SQL as well. Don't just rely on tools; you will have a better understanding of how to work with your data if you better understand what is being done with it. Play around from the command line. Check out some of the documentation. Here is a good quick tutorial:

http://www.sqlcourse.com/
Jim
Forum Contributor
Posts: 238
Joined: Fri Apr 19, 2002 5:26 am
Location: Near Austin, Texas

Post by Jim »

I just ran through the beginners SQL course this morning.

Very simple stuff. It only took me about 20 minutes to finish. I'm going to go through the advanced stuff tonight and see if I can pick up a bit more.'

I have a question, though... on forums such as these, are these topics and replies written to tables in MySQL?

If so, can you have as many items in tables as you want? How is it that phpBB writes to all these tables without overwriting other stuff?

Another thing, when you delete a topic, is it actually deleting information from the MySQL database?

Thanks guys :)
Brian
Forum Contributor
Posts: 116
Joined: Thu Apr 18, 2002 5:33 pm

Yes, sort of, with internal keys, and yes.

Post by Brian »

I have a question, though... on forums such as these, are these topics and replies written to tables in MySQL?
Yes, in MySQL or mSQL or PosgreSQL or SQL Server or whatever other relational database management system you want to use.
If so, can you have as many items in tables as you want?
Sort of. There may be some limits imposed by a particular program, by the host file system, or by system resources, but they are usually so high that most people will probably not encounter them.
How is it that phpBB writes to all these tables without overwriting other stuff?
With internal keys. You can also assign your own identifiers, but they are not actually necessary for a database to keep records separate. Think of it like making a big pile of paper without having to worry about gravity. You can always add another sheet without any previously placed sheet disappearing. Of course, it would be difficult for you to find what you wanted without lots of little sticky tabs to identify each and every separate page.
Another thing, when you delete a topic, is it actually deleting information from the MySQL database?
Yes. A discussion forum system could easily mark items as deleted without actually removing the record from the database, though. It is up to the developer.
User avatar
sam
Forum Contributor
Posts: 217
Joined: Thu Apr 18, 2002 11:11 pm
Location: Northern California
Contact:

Post by sam »

Think of it like making a big pile of paper without having to worry about gravity. You can always add another sheet without any previously placed sheet disappearing.
Nice Brian :mrgreen:

Cheers Sam
Jim
Forum Contributor
Posts: 238
Joined: Fri Apr 19, 2002 5:26 am
Location: Near Austin, Texas

Post by Jim »

Thanks for the explanation. Very nice indeed :) I find it impossible to do something without understanding why it needs to be done. Thanks to explanations like yours I'm slowly but surely getting un-n00bish :)

A few questions about functions and MySQL DBs.

If you made a function like this:

Say I create a form which, when submitted, sends information correspondent to the variables $name, $email, $password to a page with something like this on it:

Code: Select all

insert into table1
(name, email, password)
values('$name' , '$email' , '$password')
);
Would that write to the MySQL db all the information I need? Also, how would I make sure it didn't overwrite something else every time I inserted data. How do I make sure it gets put on a separate part of the table?

Gracias!

*EDIT*

I know that exact code won't work, but will the general idea work? I'm learning how to create and insert into tables using PHP right now. Thanks!
Brian
Forum Contributor
Posts: 116
Joined: Thu Apr 18, 2002 5:33 pm

More Database Basics

Post by Brian »

Would that write to the MySQL db all the information I need?
First of all, as you may know, that is SQL--not PHP--so you would need to make sure to encapsulate it in an appropriate PHP function. I recommend reading the PHP manual for its handy MySQL functions:

http://www.php.net/manual/en/ref.mysql.php

The PHP manual is a great resource for learning how various things work. Of course, as with most or perhaps even all such large documents, there are some errors, but overall the information is very useful.

Before I go any further, I would like to offer some advice: Never trust the client! Validate everything and try to catch all possible errors. Doing so will make coding take longer and it will have an ever so slight effect on execution time (probably unnoticeable in most cases), but the benefits far outweigh the potential consequences.
Also, how would I make sure it didn't overwrite something else every time I inserted data. How do I make sure it gets put on a separate part of the table?
I do not think you understood my analogy in my previous post. How do you know the hostess at a restaurant is not going to overwrite your name on her list with the name of the next person who walks in? She just knows to put it on the next line. Even if you had the same name and party size as the person, she could write their information on a blank line without making yours disappear. Then she would have to ask who arrived first, but do you understand where I am going with this?

Take a handful of coins. Put one in your pocket. Now put another one in your pocket. Did the first one disappear?

Just as you do not need to worry about your name being overwritten on a restaurant waiting list and you do not have to worry about coins overwriting coins in your pocket (or worse--overwiring your keys), you do not have to worry about overwriting data in a database with an insert command. The "insert" command is for inserting new data only; you only have to worry about overwriting data if you are updating records with the appropriately named "update" command.

Now that you understand this (I hope), I can tell you about preventing yourself from entering duplicate records. This is done with what is called a "primary key", which is just a unique value in a particular column. There is a lot more to learn than I can teach you in these little posts; I definitely recommend reading up on SQL in general and MySQL in particular. However, to be honest, I would forget about PHP for a few days if I were you. You should try designing some databases then inserting, selecting, updating, and deleting data. Once you are comfortable with SQL, figuring out how to run queries through PHP is fairly trivial.
Jim
Forum Contributor
Posts: 238
Joined: Fri Apr 19, 2002 5:26 am
Location: Near Austin, Texas

Post by Jim »

I've been having an interesting day today. I've spent about 4 hours learning basic SQL and how to use PHP and MySQL in conjunction.

I now understand your analogies. They make perfect sense :)

Now I even know how to create tables and insert information into them using php :)

Here's my first attempt at a registration script. Right now it sends the info you input to the mysql DB, so that part works. Now I need to create a login script...

http://www.maxxxtorque.com/prodigy/php/ ... gister.php

I'm not sure what sorts of things can be exploited with this script. If you find anything, please tell me :)
User avatar
sam
Forum Contributor
Posts: 217
Joined: Thu Apr 18, 2002 11:11 pm
Location: Northern California
Contact:

Post by sam »

Man Brian your like a walking analogies machine :mrgreen:

Cheers Sam
Brian
Forum Contributor
Posts: 116
Joined: Thu Apr 18, 2002 5:33 pm

Being A Walking Analogies Machine

Post by Brian »

Man Brian your like a walking analogies machine
Analogies can be very useful tools in expressing logical assertions so people can more easily understand them. :)
Post Reply