PHP Basic information tutorials and reference. How to make an if loop.

[Home] [Welcome to PHP] [Data & Dynamic] [File system]

Openning a connection

Opening a connection to MySQL database from PHP is easy. Just use the mysql_connect() function like this

<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$dbname = 'petstore';
mysql_select_db($dbname);
?>

$dbhost is the name of MySQL server. When your webserver is on the same machine with the MySQL server you can use localhost or 127.0.0.1 as the value of $dbhost. The $dbuser and $dbpass are valid MySQL user name and password. Don't forget to select a database using mysql_select_db() after connecting to mysql. If no database selected your query to select or update a table will not work.

Closing the Connection

The connection opened in a script will be closed as soon as the execution of the script ends. But it's better if you close it explicitly by calling mysql_close() function.

<?php
mysql_close($conn);
?>

To create a database use the mysql_query() function to execute an SQL query like this

<?php
$query  = "CREATE DATABASE phpcake";
$result = mysql_query($query);
?>

Queries

Please note that the query should not end with a semicolon. To create tables in the new database you need to do the same thing as creating the database. First create the SQL query to create the tables then execute the query using mysql_query() function.

<?php

$query  = 'CREATE DATABASE phpcake';
$result = mysql_query($query);

mysql_select_db('phpcake') or die('Cannot select database'); 

$query = 'CREATE TABLE contact( '.
         'cid INT NOT NULL AUTO_INCREMENT, '.
         'cname VARCHAR(20) NOT NULL, '.
         'cemail VARCHAR(50) NOT NULL, '.
         'csubject VARCHAR(30) NOT NULL, '.
         'cmessage TEXT NOT NULL, '.
         'PRIMARY KEY(cid))';

$result = mysql_query($query);
?>

Inserting data to MySQL is done by using mysql_query() to execute INSERT query. Note that the query string should not end with a semicolon. Below is an example of adding a new MySQL user by inserting a new row into table user in database mysql :

<?php
mysql_select_db($mysql);
$query = "INSERT INTO user (host, user, password, select_priv, insert_priv, update_ priv) VALUES ('localhost', 'phpcake', PASSWORD('mypass'), 'Y', 'Y', 'Y')";

mysql_query($query) or die('Error, insert query failed');

$query = "FLUSH PRIVILEGES";
mysql_query($query) or die('Error, insert query failed');
?>

Using PHP you can run a MySQL SELECT query to fetch the data out of the database. You have several options in fetching information from MySQL. PHP provide several functions for this. The first one is mysql_fetch_array()which fetch a result row as an associative array, a numeric array, or both.

Below is an example of fetching data from MySQL, the table contact have three columns, name, subject and message.

<?php
$query  = "SELECT name, subject, message FROM contact";
$result = mysql_query($query);

while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
    echo "Name :{$row['name']} <br>" .
         "Subject : {$row['subject']} <br>" . 
         "Message : {$row['message']} <br><br>";
} 
?>

Of course you can also do it like this

<?php
$query  = "SELECT name, subject, message FROM contact";
$result = mysql_query($query);

while($row = mysql_fetch_row($result))
{
    $name    = $row[0];
    $subject = $row[1];
    $message = $row[2];


    echo "Name :$name <br>" .
         "Subject : $subject <br>" . 
         "Message : $row <br><br>";
} 
?>

Other sql queries can also be used, some examples are:

INSERT into table1 (field1,field2,field3,field4) values (25,field1+1,'value3',field2*3);

UPDATE table1 SET field1='value1', field2+1, field3*2 WHERE field4=10;

DELETE FROM table1 WHERE field2>field4 LIMIT 5;