MySQL and PHP for Dummies

[Kamal]

This is a tutorial I wrote ages ago for my non-active 2 member forums. Here it is:

What is MySQL:
MySQL is "powered by" SQL, there is MSSQL, MySQL, etc.. each of them is for a language (programming languages), MySQL is mostly used with PHP.
Wikipedia Definition:

MySQL is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. MySQL is officially pronounced ("My S-Q-L"), but is often pronounced ("My Sequel"). It is named for original developer Michael Widenius's daughter My.

What is MySQL used for?
MySQL is used to store information in databases for use with websites(mainly) such as usernames and passwords etc..

How to get MySQL ?
Well MySQL is now almost installed in every webhost. if you dont want to pay, there are plenty other free webhosts but with lots of limits. I recommend using a paid webhost, if you dont want any of the previous ways, you could turn your computer into a webserver! If you want to do that and you are using Windows Vista/7, look at my signature.

What are the requirements to use MySQL with PHP?
A MySQL host, username, password, database.

How to get a database?
Most webhosts have a software called phpMyAdmin installed (XAMPP installs it too) you can use to create the database easier, but you can create it using a SQL code in PHP.

What is a SQL code?
A SQL Code is a piece of code used to perform actions in the MySQL connection.

My first MySQL code!
First you need to create a database.

Second you need to connect to your database.
You need your mysql host, mysql username, mysql password and a database. Those are the defaults in XAMPP:
Host: localhost or 127.0.0.1
Username: root
Password: (nothing).
To connect, you must start a php page and use the mysql_connect() function in PHP, as follows:

<?php
// Assigning variables to make it easier:
$host = "localhost";
$user = "root";
$pass = "";
//Im using XAMPP's defaults.

//Connecting:
mysql_connnect($host, $user, $pass) or die("Error connecting to MySQL: <br />".mysql_error());
?>

Definitions:

$host = "localhost"; assigns the host into a variable.
$user = "root"; assigns the username into a variable.
$pass = ""; assigns the password into a variable.
mysql_connnect($host, $user, $pass) connects to MySQL.
or die("Error connecting to database: <br />".mysql_error()); if the connection fails, it stop the execution of the page and writes: Error connection to MySQL: (newline) -error- the mysql_error() is the error returned, its blank if there wasnt any errors.

Now you need to select your database, you need to use the mysql_select_db() function as follows:

<?php
// Assigning variables to make it easier:
$host = "localhost";
$user = "root";
$pass = "";
//Im using XAMPP's defaults.

//Connecting:
mysql_connnect($host, $user, $pass) or die("Error connecting to MySQL: <br />".mysql_error());

//Assigning the database into a variable:
$db = "myNEWdatabase";
mysql_select_db($db) or die("Error selecting the database: <br />".mysql_error());;
?>

Definitions:

$db = "myNEWdatabase"; assigns the database name into a variable.
mysql_select_db($db) selects the database.
or die("Error connecting to database: <br />".mysql_error()); if the selection fails, it stop the execution of the page and writes: Error connecting to database: (newline) -error- the mysql_error() is the error returned, its blank if there wasnt any errors.


Now you successfully connected to your connection and selected the database!

Creating a database using PHP code!
Ok to perform actions on your MySQL Connection you must use the mysql_query() function, like so:

<?php

//connection
//select db

mysql_query("CODE HERE");

?>

We need to use that to make a database so in SQL Code we use CREATE DATABASE as follows:

<?php

//assuming you know what or die(etc).. means
mysql_query("CREATE DATABASE `example` ;") or die("Error: ".mysql_error());
echo "success";

?>

Now we have created our own database!!!

Creating a table using PHP Code!
As we said, we must use the mysql_query() function to perform actions, or to perform ANYTHING!
Sample table:
Make a php file, call it whatever you want then put the mysql connection in it then put this code:

<?php

//connect here
//select the database
// <-- those mean comments
//assuming you know php
//and learned how to connect in the last tutorial 

$sql = "CREATE TABLE  `example` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 32 ) NOT NULL ,
`email` VARCHAR( 75 ) NOT NULL
)";
mysql_query($sql) or die(mysql_error());
echo "success.";
?>

Explanation:

CREATE TABLE `example` ( create a table called example and the ( tells MySQL that the following is the content of the table.
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,:

• `id` specifies the name of the COLUMN
• INT makes it an integer (number)
• NOT NULL makes it give an error when its empty
• AUTO_INCREMENT makes it generate sequential numbers (1, 2, 3, 4, etc...)
• PRIMARY KEY makes it identify the table and if there are two ROWS that have ID the same it gives error

`name` VARCHAR( 32 ) NOT NULL ,:

• `name` specifies the name of the COLUMN
• `VARCHAR ( 32 )` makes it a character variable that holds up to 32 characters
• NOT NULL makes it give an error when empty

`email` VARCHAR( 75 ) NOT NULL:

• `email` specifies the name of the COLUMN
• `VARCHAR ( 75 )` makes it a character variable that holds up to 75 characters
• NOT NULL makes it give an error when empty

[ta2shop]

wow, thanks this will by weryyy helpful for all the new users!!!

[Kamal]

wow, thanks this will by weryyy helpful for all the new users!!!

you are welcome

[jacek]

Thanks for contributing this, looks good

I guess what MySQL actually is is often overlooked.

[Tino]

From what I have seen and heard, most people think MySQL is just a group of functions with which you can do database stuff. Obviously when thinking that, they don't realize MySQL is actually the database server as well as the system used to manage it.

I do think it's quite vital to actually know what you're working with. Since many people just don't know this, it might just be why people keep asking the same questions about MySQL over and over again.

[Kamal]

Thanks for contributing this, looks good

Thanks

[EcazS]

they don't realize MySQL is actually the database server as well as the system used to manage it.

That's the first thing I learned Well, something along those line
This looks good, I didn't know what the "NOT NULL" was before I read this, so thanks for that

[Dylan]

Pretty nice, indepth tutorial. For sure useful for those getting started!

MySQL is such a powerful method of storing information, and the true capabilities are frequently over looked.

[Kamal]

Never thought this tutorial would be that helpful to anybody.
You're welcome and thanks for the compliments.

[GenSwat]

very nice break down, excellant.

[pasqo83]

Thanks for the dummies guide, really informative.