Using PHP to control MySQL Databases

Write your "How To" regarding anything you are comfortable with and you feel it will help the forum members.

NOTE :: All threads started here will appear only after the approval from Administrator
Post Reply
Tails5
Posts: 1302
Joined: Wed Mar 15, 2006 8:09 am
Contact:

Using PHP to control MySQL Databases

Post by Tails5 »

I'm going to tell everyone now how to control MySQL with PHP, I learned it a couple weeks ago and thought I should share what I know, so here goes.

Things you'll need:
A webserver that supports PHP (i.e. http://www.smokyhosts.com)
A simple text editor (notepad works fine, but I use DzSoft PHP Editor)

If you don't have a web server that supports PHP, you can download "Apache" which is a HTML webhost for your personal computer, "MySQL" which is obviously the MySQL Engine, and you'll need PHP 5.2.4. If you need help setting any of it up, drop me a line at tails5is@hotmail.com, now on with the MySQL stuff.

Put any code into your text editor and just save it with the extension ".php", you may need to set to Save As Type "All Files (*.*)" with notepad and type in the entire name manually, like index.php and remember to put <?php at the beginning of every file, and ?> at the end.

The first thing you need to do is connect to the server:
[php]mysql_connect("localhost","admin","password") or die(mysql_error());[/php]
mysql_connect takes 3 parameters, first is the server, localhost means the machine the PHP will be running on, replace admin with your username and password with your password.

die(mysql_error()) only activates if the statement doesnt succeed, and stops the script and outputs the error that MySQL gave.

At this point you'll want to create, drop, or select a database (or all of the above).
To select a database that already exists, you would do something like this:[php]mysql_select_db("myDatabase") or die(mysql_error());[/php]
Replacing myDatabase with the name of your existing database.

To create a database use the following.
[php]mysql_create_db("myDatabase") or die(mysql_error());[/php]
Of course, replacing myDatabase with the name of your desired database

To drop(delete) a database.
[php]mysql_drop_db("myDatabase") or die(mysql_error());[/php]
Replacing of course myDatabase with whatever database you want to eradicate, please note, this is irreversable.

Now, so far we've done nothing but connect to a database, which will naturally seem VERY boring, so now we can create tables! And read them too :)

Here's the code for a sample system to make a table:
[php]
<?php
mysql_connect("localhost", "admin", "password") or die(mysql_error());
mysql_create_db("myDatabase") or die(mysql_error());

mysql_query("CREATE TABLE addressBook(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
name VARCHAR(15),
phoneNumber INT)")
or die(mysql_error());

echo "Data inserted into MySQL";

?>
[/php]
Save this file as something like setupdb.php and test it out, after changing the login information at the top, and now I'll explain all the new code:
"mysql_query" sends data directly to MySQL, like typing it in from the MySQL command line, queries are like what comes next: id INT NOT NULL AUTO_INCREMENT; means we want a new field called "id" it should be an integer(a number), cannot be null, and auto increments, so it keeps track of itself.

"PRIMARY KEY(id)" sets id to be the primary key, which means by default, it will sort by that.

"name VARCHAR(15)" means to make a field called name, which accepts characters and up to 15 of them.

"phoneNumber VARCHAR(15)" says we want another string value to hold their phone number."



Now, we've created a table, but have nothing to show (except a table)

Now, to put some info into the table, put this into a PHP ****
[php]
<?php
mysql_connect("localhost","admin","password")
mysql_select_db("myDatabase")
mysql_query("INSERT INTO addressBook (name, phoneNumber) VALUES('John Smith','555-1234')") or die(mysql_error();
mysql_query("INSERT INTO addressBook (name, phoneNumber) VALUES('Joe Bloggs','555-4321')") or die(mysql_error();
mysql_query("INSERT INTO addressBook (name, phoneNumber) VALUES('Sally Smith','555-1235')") or die(mysql_error();
mysql_query("INSERT INTO addressBook (name, phoneNumber) VALUES('Beatrice Bloggs','555-5321')") or die(mysql_error();
?>
[/php]Don't forget to replace the login information! And only run this once, otherwise you'll have 2 of every entry (not that it would really harm anything)

I think INSERT INTO kind of explains itself, so I won't go into it today.

Now only one thing left in this tutorial, and that's reading the information.

[php]<?php
mysql_connect("localhost","admin","password")
mysql_select_db("myDatabase")

$result = mysql_query("SELECT * FROM addressBook") or die(mysql_error());


while($row = mysql_fetch_array($result)){
echo $row['name']. " - ". $row['phoneNumber'];
echo "
";
}
?>[/php]

The only new new MySQL statement I used here was mysql_fetch_array, which just fetches the first part of an array from the results returned by the SELECT * FROM addressBook, the rest of the new things are PHP and HTML, but this is a MySQL tutorial, so we're done here today, any help needed? Want a followup tutorial? Any problems with this one? Email me at tails5is@hotmail.com


Webmaster Yoda: You must confront the cPanel. Then, and only then, a webmaster will you be.
Julius Caesar: Veni, vidi, posti
SHAdmin
Posts: 2089
Joined: Sat Dec 18, 2004 11:28 am
Contact:

Post by SHAdmin »

Thank you 'Tails5' for creating such a nice 'How To'.

Your account has been credited with 50 points for sharing it.
Post Reply