PHP and Database

Create Statement:


• Create a Database:

• The CREATE DATABASE statement is used to create a database in MySQL.

• Syntax: CREATE DATABASE database_name;

• To get PHP to execute the statement above we must use the mysql_query() function.

• mysql_query - Send a MySQL query.

• Description: resource mysql_query ( string query [, resource link_identifier] ).

• mysql_query() sends an unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

• Parameters :

• query: A SQL query (The query string should not end with a semicolon).

• link_identifier: The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() was called with no arguments. If by chance no connection is found or established, an E_WARNING level warning is generated.

• Return Values:

• For SELECT, SHOW, DESCRIBE or EXPLAIN statements, mysql_query() returns a resource on success, or FALSE error.

• For other type of SQL statements, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.


• Example: The following example creates a database called "my_db":

<?php

$con = mysql_connect("localhost","root","abc123");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

if (mysql_query("CREATE DATABASE my_db",$con))

{

echo "Database created";

}

else

{

echo "Error creating database: " . mysql_error();

}

mysql_close($con);

?>

• Create a Table:

• The CREATE TABLE statement is used to create a table in MySQL.

• Syntax: CREATE TABLE table_name

(

column_name1 data_type,

column_name2 data_type,

column_name3 data_type,

....

)

• Example: The following example creates a table named "Persons", with three columns. The column names will be "FirstName", "LastName" and "Age":

<?php

$con = mysql_connect("localhost","root","abc123");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

// Create database

if (mysql_query("CREATE DATABASE my_db",$con))

{

echo "Database created";

}

else

{

echo "Error creating database: " . mysql_error();

}

// Create table

mysql_select_db("my_db", $con);

$sql = "CREATE TABLE Persons

(

FirstName varchar(15),

LastName varchar(15),

Age int

)";

// Execute query

mysql_query($sql,$con);

mysql_close($con);

?>

• A database must be selected before a table can be created. The database is selected with the mysql_select_db() function.

• In the above example, mysql_select_db - Select a MySQL database

• Description: boolean mysql_select_db ( string database_name [, resource link_identifier] )

• Sets the current active database on the server that's associated with the specified link identifier. Every subsequent call to mysql_query() will be made on the active database.

• Parameters:

• database_name: The name of the database that is to be selected.

• link_identifier: The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() was called with no arguments. If by chance no connection is found or established, an E_WARNING level warning is generated.

• Return Values: Returns TRUE on success or FALSE on failure.

• Note: When you create a database field of type varchar, you must specify the maximum length of the field, e.g. varchar(15).

• Primary Keys and Auto Increment Fields:

• Each table should have a primary key field.

• A primary key is used to uniquely identify the rows in a table. Each primary key value must be unique within the table. Furthermore, the primary key field cannot be null because the database engine requires a value to locate the record.

• The following example sets the personID field as the primary key field. The primary key field is often an ID number, and is often used with the AUTO_INCREMENT setting. AUTO_INCREMENT automatically increases the value of the field by 1 each time a new record is added. To ensure that the primary key field cannot be null, we must add the NOT NULL setting to the field.

• Example:

$sql = "CREATE TABLE Persons

(

personID int NOT NULL AUTO_INCREMENT,

PRIMARY KEY(personID),

FirstName varchar(15),

LastName varchar(15),

Age int

)";

mysql_query($sql,$con);



PHP MySQL Insert Into:

• Insert Data Into a Database Table

• The INSERT INTO statement is used to add new records to a database table.

• Syntax:

• It is possible to write the INSERT INTO statement in two forms.

• The first form doesn't specify the column names where the data will be inserted, only their values:

INSERT INTO table_name

VALUES (value1, value2, value3,...)

• The second form specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3,...)

VALUES (value1, value2, value3,...)

• Example:

• In the previous example we created a table named "Persons", with three columns; "Firstname", "Lastname" and "Age". We will use the same table in this example. The following example adds two new records to the "Persons" table:

<?php

$con = mysql_connect("localhost","root","abc123");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

mysql_select_db("my_db", $con);

mysql_query("INSERT INTO Persons (FirstName, LastName, Age)

VALUES ('Peter', 'Griffin', '35')");

mysql_query("INSERT INTO Persons (FirstName, LastName, Age)

VALUES ('Glenn', 'Quagmire', '33')");

mysql_close($con);

?>

• Insert Data From a Form Into a Database

• Now we will create an HTML form that can be used to add new records to the "Persons" table.

• Here is the HTML form:

• <html>

<body>

<form action="insert.php" method="post">

Firstname: <input type="text" name="firstname" >

Lastname: <input type="text" name="lastname" >

Age: <input type="text" name="age" >

<input type="submit" />

</form>

</body>

</html>

• When a user clicks the submit button in the HTML form in the example above, the form data is sent to "insert.php".

• The "insert.php" file connects to a database, and retrieves the values from the form with the PHP $_POST variables.

• Then, the mysql_query() function executes the INSERT INTO statement, and a new record will be added to the "Persons" table.

• Here is the "insert.php" page:

<?php

$con = mysql_connect("localhost","root","abc123");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

mysql_select_db("my_db", $con);

$sql="INSERT INTO Persons (FirstName, LastName, Age)

VALUES

('$_POST[firstname]','$_POST[lastname]','$_POST[age]')";

if (!mysql_query($sql,$con))

{

die('Error: ' . mysql_error());

}

echo "1 record added";

mysql_close($con)

?>