Publishing System Settings Logout Login Register
Different MySQL Tid-Bits
TutorialCommentsThe AuthorReport Tutorial
Tutorial Avatar
Rating
Add to Favorites
Posted on February 26th, 2007
2942 views
PHP Coding
What is MySQL?
MySQL is a database application... Let's see what thea glossary has to say about it

"MySQL (pronounced "my ess cue el") is an open source relational database management system (RDBMS) that uses Structured Query Language (SQL), the most popular language for adding, accessing, and processing data in a database. Because it is open source, anyone can download MySQL and tailor it to their needs in accordance with the general public license. MySQL is noted mainly for its speed, reliability, and flexibility. Most agree, however, that it works best when managing content and not executing transactions."

The most widely used way of integrating MySQL is through PHP Applications, and can be used for a wide variety of applications, forums, user systems, site logging and so so much more, so it's a vital thing to know a lot of the mysql functions to use with PHP, this tutorial will show you different functions you can use with MySQL, including queries, securing injections, different fetch abilities, and more.

Creation of MySQL
In this first part, I will show you the ways of creating MySQL databases and tables.

First bit, creating a database.

CREATE DATABASE `name`;

Very simple, just creating a database with the name of name.

Creating a table for this database, when creating tables, you want to know what your naming it, and how many fields you wants (How many columns)... for this, I will just do 4 fields. Also while creating tables, you need to enter in the name of the field, the type of the field, and any other information, the code below will show the most used one in a user system.

CREATE TABLE `tablename` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 30 ) NOT NULL ,
`password` TEXT NOT NULL ,
`email` VARCHAR( 100 ) NOT NULL
) ENGINE = MYISAM ;


CREATE TABLE `tablename` ( is the name of the table. Then we move onto the fields, we have id, username, password and email.

INT( ## ) is an integer, the ## is the length that can be put in, so we can have up to 11 numbers, a lot we can have. NOT NULL means it shouldn't be null, something should always be in there, if not then it will automatically turn out as NULL. AUTO_INCREMENT means that each time a new row is put in, this row will keep on adding 1 to the last row, so 1, 2, 3, etc. PRIMARY KEY is the main field, it's always unique and can't be the same.

VARCHAR is a series of characters, or in other words, a string.

TEXT - usually wouldn't use it for password, but just an example. You can't pick a length of characters in this.
Editing a table
Quite a vital thing, editing a table.

This next bit will show you how to add a new row to a table.
ALTER TABLE `tablename` ADD `realname` VARCHAR( 80 ) NOT NULL ;

ALTER TABLE defines that we are editing the table, and of course, the name of the table. ADD the field name, and comes the varchar again.

Editing an already made field.
ALTER TABLE `tablename` CHANGE `password` `password` VARCHAR( 30 ) NOT NULL 

Of course, the ALTER TABLE again, we now use CHAGE to change the field password into password field name (the same), but we've changed TEXT to VARCHAR(30) and keep it a NOT NULL.

Inserting data
Insert data into a table.

INSERT INTO `tablename` ( `username` , `password` , `email` , `realname` )
VALUES ( 'dotSilver', 'iek', 'eidk', 'ek');

INSERT INTO tablename then we show all the field names we're inserting data into, we put these in brackets. Then comes the VALUES which is what we're putting into the fields. Notice that they need to be in the same order of what you named the fields.

Now there are things you need to know while inserting data into tables. Watch out for MySQL Injections, hackers use it to try and mess up your database, usually on user systems in order to try and get into the admin account. There are ways to prevent this though, the most secure way, and php.net strongly advises you to use this is to add your data into a function called mysql_real_escape_string

$password = mysql_real_escape_string($password);

It makes the string safe for MySQL Insertion.

Selecting Data
Selecting data from tables.

SELECT * FROM `tablename`

SELECT everything FROM table name. Very simple, you can limit the amount that comes out too.

SELECT * FROM `tablename` LIMIT 0, 10

This will get the first 10 rows of data.

SELECT * FROM `tablename` LIMIT 10, 20

This will get the rows from 10 to 20.

SELECT * FROM `tablename` WHERE `username` = 'dotSilver'

This will get the rows where the field username is dotSilver. Great for user systems of checking to see if a username or email already exists. Because of cause, if it can select it, then it exists.

You can even do more than one.
SELECT * FROM `tablename` WHERE `username` = 'dotSilver' AND `email` = 'eidk'


Even this or that.
SELECT * FROM `tablename` WHERE `username` = 'aname' OR `email` = 'eidk'


Getting only certain fields from a table.
SELECT username FROM `tablename`

This can also be used with all the other LIMITs and WHEREs.

Using the data
So how can we use this data we've got? Well every MySQL Command you do in PHP needs to be inside a certain function called mysql_query.

mysql_query(SELECT * FROM `tablename`);


To further use this data, we can use this in a variable.

$data = mysql_query(SELECT * FROM `tablename`);


First let's see how we get the number of rows we have.
$numrows = mysql_num_rows($data);

We get a variable called numrows which holds the number of rows. We can use a simple echo function to show it. Now to show the data, there are multiple ways you can do this, here are two of them.

while($thedata = mysql_fetch_array($data)){
//data
}
while($thedata = mysql_fetch_object($data)){
//data
}

Both of these use different types of ways to show the data. We will show the username and email. Remember, try one at a time, not both.

while($thedata = mysql_fetch_array($data)){
echo $thedata['username']." | ".$thedata['email'];
}

while($thedata = mysql_fetch_object($data)){
echo $thedata->username." | ".$thedata->email;
}

I find that the latter is a better one to use, since it's more clearer and easy to see. Pretty simple eh.

Deleting
Ok so not much left, this will be it... deleting stuff.

Delete a row.
DELETE FROM `tablename` WHERE `id` = '1'

Delete from the table name, where id is 1. You can use anything, not just ID. Such as username, password, etc.

Empty a table.
TRUNCATE TABLE `tablename`

Truncate is emptying the table... so all data is gone.

Delete a column.
ALTER TABLE `tablename` DROP `realname`

Very simple.

And now, deleting the whole table.
DROP TABLE `tablename`

Well that's that table gone.

For the finale... the database!
DROP DATABASE `name`


They are the main SQL Commands that you will need to know when working with MySQL.
Dig this tutorial?
Thank the author by sending him a few P2L credits!

Send
nitr0x

I am a web developer and a graphic designer experienced in HTML, XHTML, Javascript, PHP, MySQL and CSS. My graphic skills consist of using Cinema 4D, 3DS Max and Photoshop.
View Full Profile Add as Friend Send PM
Pixel2Life Home Advanced Search Search Tutorial Index Publish Tutorials Community Forums Web Hosting P2L On Facebook P2L On Twitter P2L Feeds Tutorial Index Publish Tutorials Community Forums Web Hosting P2L On Facebook P2L On Twitter P2L Feeds Pixel2life Homepage Submit a Tutorial Publish a Tutorial Join our Forums P2L Marketplace Advertise on P2L P2L Website Hosting Help and FAQ Topsites Link Exchange P2L RSS Feeds P2L Sitemap Contact Us Privacy Statement Legal P2L Facebook Fanpage Follow us on Twitter P2L Studios Portal P2L Website Hosting Back to Top