MySQL Tutorials: 2013

Insert Data Into a Database Table

1.The INSERT INTO statement is used to add new records to a database table.
2.To execute INSERT INTO statement, we must use the mysql_query() function.

Syntax
INSERT INTO table_name
VALUES (value1, value2, value3,...)
Or
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

The first form doesn't specify the column names.
The second form specifies both the column names and the values to be inserted.

Example: 1

<?php

//Step 1: Create a Connection

$con = mysql_connect("localhost","root","");
if (!$con)
{
die("Could not connect: " . mysql_error());
}//Step 2: Create Database

$sql = "CREATE DATABASE Aspell";

$retval = mysql_query( $sql, $con);
if($retval )
echo "Database Aspell created successfully<br>";
else
die("Could not create database: " . mysql_error());//Step 3: Select Current db

$db_selected = mysql_select_db("Aspell", $con);
if (!$db_selected) {
die ("Can't select : " . mysql_error());
}//Step 4: Create Table

$sql = "CREATE TABLE people
(
FirstName varchar(15) NOT NULL,
LastName varchar(15) NOT NULL,
Age int NOT NULL
)";//Step 5: Execute Query

$retval = mysql_query($sql,$con);
if(! $retval )
{
die("Could not create table: " . mysql_error());
}
echo "Table created successfully";//Step 6: Insert Data Into a Database Table

mysql_query("INSERT INTO people (FirstName, LastName, Age)
VALUES ('Oliva', 'Williamson',20)");mysql_query("INSERT INTO people (FirstName, LastName, Age)
VALUES ('Nicole', 'Cantwell',25)");mysql_close($con);

?>

Insert Data From a Form Into a Database

Example: 2

index.php
<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>

insert.php
<?php
$con = mysql_connect("localhost","root","");
if (!$con)
{
die("Could not connect: " . mysql_error());
}mysql_select_db("Aspell", $con);$sql="INSERT INTO people (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);?>

Create a Table

1.The CREATE TABLE statement is used to create a table in MySQL.
2.The CREATE TABLE statement has to add to the mysql_query() function to execute the command.
3.A database must be selected before a table can be created.
The database is selected with the mysql_select_db() function.

Syntax
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
)

Tables consists rows and columns and each table must have a name.

Example

<?php
$hostname = "localhost";
$username = "root";
$password = "";
$con = mysql_connect("$hostname", "$username", "$password");
//$con = mysql_connect("localhost","root","");
if (!$con)
{
die("Could not connect: " . mysql_error());
}
// Create database
if (mysql_query("CREATE DATABASE Aspell",$con))
{
echo "Database created<br>";
}
else
{
echo "Error creating database: " . mysql_error();
}
// Create table
mysql_select_db("Aspell", $con);
$sql = "CREATE TABLE Persons
(
FirstName varchar(15) NOT NULL,
LastName varchar(15) NOT NULL,
Age int NOT NULL
)";
// Execute query
$retval = mysql_query($sql,$con);
if(! $retval )
{
die("Could not create table: " . mysql_error());
}
echo "Table created successfully";
mysql_close($con);
?>

Output

Database created
Table created successfully

mysql_select_db() Function

1.mysql_select_db() function is used to select existing database on the server associated with the specified link identifier.
2.If no link identifier is specified, the last opened link is assumed.
3.mysql_select_db() function returns TRUE on success or FALSE on failure.

Syntax
bool mysql_select_db(database_name,connection)

Here, database_name specifies the database to select.
connection is optional. If not specified, the last connection opened by mysql_connect().

Example

<?php
$hostname = "localhost";
$username = "root";
$password = "";
$con = mysql_connect("$hostname", "$username", "$password");
if(! $con )
{
die("Could not connect: " . mysql_error());
}
echo "Connected successfully";
// select current db
$db_selected = mysql_select_db("Aspell", $con);
if (!$db_selected) {
die ("Can\'t select : " . mysql_error());
}
mysql_close($con);
?>

Output

Connected successfully

DROP DATABASE Function

1.The DROP DATABASE statement is used to create a database.

Syntax
DROP DATABASE dbname;

Example: 1

<?php
$hostname = "localhost";
$username = "root";
$password = "";
$con = mysql_connect("$hostname", "$username", "$password");
if(!$con)
{
die("Could not connect: " . mysql_error());
}
echo "Connected successfully<br />";
$sql = "DROP DATABASE Aspell";
$retval = mysql_query( $sql, $con);
if(!$retval )
{
die("Could not delete database: " . mysql_error());
}
echo "Database Aspell deleted successfully\n";
mysql_close($con);
?>

Output

Connected successfully
Database Aspell deleted successfully

CREATE DATABASE Function

1.The CREATE DATABASE statement is used to create a database.
2.We can create MySQL database with the use of mysql_create_db("Database Name").
mysql_create_db("Database Name") function is deprecated as of PHP 5.5.0, and will be removed in the future.

Syntax
CREATE DATABASE dbname;

Example: 1

<?php
$hostname = "localhost";
$username = "root";
$password = "";
$con = mysql_connect("$hostname", "$username", "$password");
if ($con)
  echo "Connection is succeed<br>";
else
  die("Could not connect: " . mysql_error());
$sql = "CREATE DATABASE Aspell";
$retval = mysql_query( $sql, $con);

if($retval )
  echo "Database Aspell created successfully";
else
  die("Could not create database: " . mysql_error());
mysql_close($con);
?>

Output

Connection is succeed
Database Aspell created successfully

Create a Connection to a MySQL Database

1.The mysql_connect()function is used to do the connection to MySQL database program.

2.The mysql_connect()function returns a resource which is a pointer to the database connection.

Syntax
mysql_connect(servername/hostname,username,password);

Here, servername/hostname: Optional. Specifies the server to connect to. Default value is "localhost:3306".

Username:Optional. Specifies the username to log in with. Default value is the name of the user who owns the server process.

Password:Optional. Specifies the password to log in with. Default is "".

Create a file named “connect.php” within /xampp/htdocs.
Point your browser to http://localhost/connect.php. May you see like this:

Example: 1

connect.php
<?php
$con = mysql_connect("localhost","","");
if ($con){
echo "Connection is succeed";
}
else
{
die("Could not connect: " . mysql_error());
}
?>

Output

Connection is succeed

Example: 2

connect.php
<?php
$hostname = "localhost";
$username = "root";
$password = "";
$con = mysql_connect("$hostname", "$username", "$password");
if ($con){
echo "Connection is succeed";
}
else
{
die("Could not connect: " . mysql_error());
}
?>

Output

Connection is succeed

MySQL Tutorials

Adscend

Tuesday 3 September 2013

Insert Data Into a Database Table

Insert Data Into a Database Table

Create a Table

Create a Table

mysql_select_db() Function

mysql_select_db() Function

DROP DATABASE Function

DROP DATABASE Function

CREATE DATABASE Function

CREATE DATABASE Function

Create a Connection to a MySQL Database