MySQL and PHP for Dummies

Any tutorials (or useful resources) should go in here.
Post Reply
User avatar
Kamal
Posts: 123
Joined: Fri May 06, 2011 10:45 am
Contact:

MySQL and PHP for Dummies

Post by 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
Last edited by jacek on Sat May 07, 2011 6:58 pm, edited 1 time in total.
Reason: removed duplicated line.
User avatar
ta2shop
Posts: 179
Joined: Sat May 07, 2011 9:07 am
Location: madrid, Spain
Contact:

Re: MySQL and PHP for Dummies

Post by ta2shop »

wow, thanks this will by weryyy helpful for all the new users!!! ;)
Image
User avatar
Kamal
Posts: 123
Joined: Fri May 06, 2011 10:45 am
Contact:

Re: MySQL and PHP for Dummies

Post by Kamal »

ta2shop wrote:wow, thanks this will by weryyy helpful for all the new users!!! ;)
you are welcome :)
User avatar
jacek
Site Admin
Posts: 3262
Joined: Thu May 05, 2011 1:45 pm
Location: UK
Contact:

Re: MySQL and PHP for Dummies

Post by jacek »

Thanks for contributing this, looks good :)

I guess what MySQL actually is is often overlooked.
Image
Tino
Posts: 360
Joined: Thu May 05, 2011 8:55 pm
Location: The Netherlands

Re: MySQL and PHP for Dummies

Post by 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.
Please check out my CodeCanyon items.
User avatar
Kamal
Posts: 123
Joined: Fri May 06, 2011 10:45 am
Contact:

Re: MySQL and PHP for Dummies

Post by Kamal »

jacek wrote:Thanks for contributing this, looks good :)
Thanks
User avatar
EcazS
Posts: 789
Joined: Fri May 06, 2011 5:29 pm

Re: MySQL and PHP for Dummies

Post by EcazS »

Tino wrote: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 :lol: Well, something along those line :P
This looks good, I didn't know what the "NOT NULL" was before I read this, so thanks for that :lol:
User avatar
Dylan
Posts: 150
Joined: Fri May 06, 2011 7:14 pm

Re: MySQL and PHP for Dummies

Post by 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.
User avatar
Kamal
Posts: 123
Joined: Fri May 06, 2011 10:45 am
Contact:

Re: MySQL and PHP for Dummies

Post by Kamal »

Never thought this tutorial would be that helpful to anybody.
You're welcome and thanks for the compliments.
User avatar
GenSwat
Posts: 74
Joined: Sat May 07, 2011 3:37 pm

Re: MySQL and PHP for Dummies

Post by GenSwat »

very nice break down, excellant.
One of my Favorites
Image
User avatar
pasqo83
Posts: 106
Joined: Sat May 14, 2011 6:23 am

Re: MySQL and PHP for Dummies

Post by pasqo83 »

Thanks for the dummies guide, really informative. :D
  • Empty your mind, be formless like water. If you put water into a cup, it becomes the cup. You put water into a bottle and it becomes the bottle. You put it in a teapot it becomes the teapot. Now, water can flow or it can crash. Be water my friend.
Post Reply