Creating MySQL table populating With PHP

Next we are going to create a database table and fill it with data.

<?php

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "mydb"; 

function execute_query($query) {

    $r = mysql_query($query);

    if (!$r) {
        echo "Cannot execute query: $query\n";
        trigger_error(mysql_error()); 
    } else {
        echo "Query: $query executed\n"; 
    }
}

$r = mysql_connect($host, $user, $pass);

if (!$r) {
    echo "Could not connect to server\n";
    trigger_error(mysql_error(), E_USER_ERROR);
} else {
    echo "Connection established\n"; 
}

$r2 = mysql_select_db($db);

if (!$r2) {
    echo "Cannot select database\n";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Database selected\n";
}

$query = "DROP TABLE IF EXISTS Cars"; 
execute_query($query);

$query = "CREATE TABLE Cars(Id INT PRIMARY KEY, Name TEXT, 
    Price INT) ENGINE=InnoDB";
execute_query($query);

$query = "INSERT INTO Cars VALUES(1,'Audi',52642)";
execute_query($query);

$query = "INSERT INTO Cars VALUES(2,'Mercedes',57127)";
execute_query($query);

$query = "INSERT INTO Cars VALUES(3,'Skoda',9000)";
execute_query($query);

$query = "INSERT INTO Cars VALUES(4,'Volvo',29000)";
execute_query($query);

$query = "INSERT INTO Cars VALUES(5,'Bentley',350000)";
execute_query($query);

$query = "INSERT INTO Cars VALUES(6,'Citroen',21000)";
execute_query($query);

$query = "INSERT INTO Cars VALUES(7,'Hummer',41400)";
execute_query($query);

$query = "INSERT INTO Cars VALUES(8,'Volkswagen',21600)";
execute_query($query);

mysql_close();

?>

In the above code example, we create a Cars table with 8 rows.

function execute_query($query) {

    $r = mysql_query($query);

    if (!$r) {
        echo "Cannot execute query: $query\n";
        trigger_error(mysql_error()); 
    } else {
        echo "Query: $query executed\n"; 
    }
}

We have created a custom execute_query() function which will be called for each INSERT statement.

$r2 = mysql_select_db($db);

Before we can work with database tables, we must select a database. A database is selected with a mysql_select_db() function.

if (!$r2) {
    echo "Cannot select database\n";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Database selected\n";
}

Error handling for the database selection process.

$query = "DROP TABLE IF EXISTS Cars"; 
execute_query($query);

The first query drops a Cars table, if it already exists.

$query = "CREATE TABLE Cars(Id INT PRIMARY KEY, Name TEXT, 
    Price INT) ENGINE=InnoDB";
execute_query($query);

This is the SQL statement to create the Cars table.

$query = "INSERT INTO Cars VALUES(1,'Audi',52642)";
execute_query($query);

A car is inserted into the table.

if (!$ok) {
   echo mysql_error();
   die("Cannot execute query. \n");
}

In case of an error, we print the error message and terminate the script.

$ php create_fill.php
Connection established
Database selected
Query: DROP TABLE IF EXISTS Cars executed
Query: CREATE TABLE Cars(Id INT PRIMARY KEY, Name TEXT, 
    Price INT) ENGINE=InnoDB executed
Query: INSERT INTO Cars VALUES(1,'Audi',52642) executed
Query: INSERT INTO Cars VALUES(2,'Mercedes',57127) executed
Query: INSERT INTO Cars VALUES(3,'Skoda',9000) executed
Query: INSERT INTO Cars VALUES(4,'Volvo',29000) executed
Query: INSERT INTO Cars VALUES(5,'Bentley',350000) executed
Query: INSERT INTO Cars VALUES(6,'Citroen',21000) executed
Query: INSERT INTO Cars VALUES(7,'Hummer',41400) executed
Query: INSERT INTO Cars VALUES(8,'Volkswagen',21600) executed

Executing the create_fill.php script.

mysql> SELECT * FROM Cars;
+----+------------+--------+
| Id | Name       | Price  |
+----+------------+--------+
|  1 | Audi       |  52642 |
|  2 | Mercedes   |  57127 |
|  3 | Skoda      |   9000 |
|  4 | Volvo      |  29000 |
|  5 | Bentley    | 350000 |
|  6 | Citroen    |  21000 |
|  7 | Hummer     |  41400 |
|  8 | Volkswagen |  21600 |
+----+------------+--------+
8 rows in set (0.00 sec)

The data inserted into the Cars table.

Do you like this Tutorial? Please link back to this article by copying one of the codes below.

URL: https://oneuoneu.blogspot.com/2013/04/creating-mysql-table-populating-with-php.html HTML link code: <a href="https://oneuoneu.blogspot.com/2013/04/creating-mysql-table-populating-with-php.html">Creating MySQL table populating With PHP</a> BB (forum) link code: [url=https://oneuoneu.blogspot.com/2013/04/creating-mysql-table-populating-with-php.html]Creating MySQL table populating With PHP[/url]