Introduction to MySQL with PHP

Shadab · # 1 (**permalink**)

What is SQL ?

SQL , commonly expanded as Structured Query Language, is a computer language designed for the retrieval and management of data in relational database management systems, database schema creation and modification, and database object access control management.

What is MySQL ?

MySQL is a multithreaded, multi-user SQL database management system (DBMS).

Connecting to a MySQL DB in PHP :

PHP Code:

  <?php
$server = "localhost";
$user = "mysql-username";
$pass = "your-password";
$dbname = "the-database-name";
 
$result = mysql_connect("$server","$user","$pass");
if (!$result) die ("Could not connect MySQL");
mysql_select_db($dbname,$result) or die ("Could not open database");
?>

This is just a very basic example on how to connect to a MySQL database thru PHP.

The $server variable is for the MySQL server's location.
Usually the MySQL server is located right on the server where PHP is being processed. So, leaving this setting to localhost will be better.

Put your login info ie, the username and the password for the MySQL server should be put appropriately in the $user and $pass variables.

Put the database name from which you are actually retrieving the data,
in the $dbname variable.

Now the main action begins :

$result = mysql_connect("$server","$user","$pass");

this will connect to the MySQL server located at whatever the $server variable has the data of; using the username as the one put in $user variable and the password as the one put in the $pass variable.

Then the next line :

if (!$result) die ("Could not connect MySQL");

This checks if the connection was successful or not.
If it was not successful then it will display the given error and will exit.

The following line :

mysql_select_db($dbname,$result) or die ("Could not open database");

selects the database named the one in the $dbname variable,
using the connection established in the $result variable.

If the PHP is unable to select the given database, it will display the error and will exit.

Creating a Table :

PHP Code:

  <?php
 
mysql_query("CREATE TABLE User ( 
    firstname VARCHAR(30), 
    lastname VARCHAR(30), 
    country VARCHAR(25))");
 
?>

The mysql_query() function is used to perform any query on the selected database.

Here we are creating a new table named User,
with the three columns in it, namely : firstname, lastname and country.
With the maximum input input characters accepted being 30, 30 and 25 characters respectively.

Inserting Data in a Table :

PHP Code:

  <?php
 
$query = "INSERT INTO User (firstname,lastname,country) 
    VALUES ('Shadab','Ansari','India')";
    mysql_query($query) or die ("The data could not be put into the table");
 
?>

Thats a pretty simple query to our database.
This will just insert a record into the table "User", with the supplied values of "Shadab", "Ansari" and "India" as the firstname, lastname and the country.

The last line will process the query and if not successful then it will give the error and exit.

The Browser Output :

PHP Code:

  <?php 
 
// This will select everything from the table Users
$query = "SELECT * FROM users";
 
// Performing the actual query
$result = mysql_query($query);
 
// Now counting the number of rows, ie. records in our database
$numrows = mysql_num_rows($result);
 
// This will loop thru each row in our DB and display the output as follows
while($row = mysql_fetch_array($result)){
    echo "There are $numrows user(s) in the database table";
    echo "First Name : $row[firstname]";
    echo "Last Name : $row[lastname]";
    echo "Country : $row[country]";
}
 
?>

The output of the given code in our browser would be something like :

There are 1 user(s) in the database table
First Name : Shadab
Last Name : Ansari
Country : India

That's all for now.
Hope this helped the users who have just started learning PHP and MySQL out here.

Sources :
This tutorial was compiled from different sources,
mainly Wikipedia.com, PhpFreaks.com and PHP.net
And code illustrations by 'Shadab'.

thanx,
Shadab.

ISRdesign · # 2 (**permalink**)

Nice perspective of viewing php connection, dude

joyti · # 3 (**permalink**)

Hi

nice tut, I need it and i like it.
can you tell me the book for this not ebook.

thanks

adserverexpert · # 4 (**permalink**)

Google it out, you can find lots of notes.