PHP MySQL Create
MySQL CREATE
In this chapter we will show you how to create a database and a table.
This is done by using the mysql_query() function. This function is used to send a query or command to a MySQL connection.
Creating a Database
Create a database in MySQL with PHP.MySQL Syntax
CREATE DATABASE database_name
|
Now we use this together with the mysql_query() function.
All we have to do is to add the MySQL syntax to the mysql_query() function.
Example
Here we create a database called "my_db":
<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
$sql = "CREATE DATABASE my_db";
if (mysql_query($sql,$con))
{
echo "Database my_db created";
}
else
{
echo "Error creating database: " . mysql_error();
}
?>
|
Create a Table
MySQL Syntax To create a table in a database:
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
.......
)
|
Now we use this together with the mysql_query() function.
Example
This example demonstrates how you can create a table named "Person", with three columns. The column names will be "FirstName", "LastName" and "Age":mysql_select_db("my_db", $con);
$sql = "CREATE TABLE Person
(
FirstName varchar(15),
LastName varchar(15),
Age int
)";
mysql_query($sql,$con);
|
Note: While using PHP to create the varchar data type in a table, you must add the max length parameter, like shown above.
Here is the different MySQL data types that can be used:
Numbers
|
Description
|
int(size)
smallint(size) tinyint(size) mediumint(size) bigint(size) |
Hold integers only. The maximum number of digits are specified in parenthesis
|
decimal(size,d)
double(size,d) float(size,d) |
Hold numbers with fractions. The maximum number of digits are specified in "size". The maximum number of digits to the right of the decimal is specified in "d"
|
Text
|
Description
|
char(size)
|
Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis
|
varchar(size)
|
Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis
|
tinytext
|
Holds a variable string with a maximum length of 255 characters
|
text
blob |
Holds a variable string with a maximum length of 65535 characters
|
mediumtext
mediumblob |
Holds a variable string with a maximum length of 16777215 characters
|
longtext
longblob |
Holds a variable string with a maximum length of 4294967295 characters
|
Date
|
Description
|
date(yyyy-mm-dd)
datetime(yyyy-mm-dd hh:mm:ss) timestamp(yyyymmddhhmmss) time(hh:mm:ss) |
Holds date and/or time
|
Misc
|
Description
|
enum(value1,value2,ect)
|
ENUM is short for ENUMERATED list. Can store one of up to 65535 values listed within the ( ) brackets. If a value is inserted that is not in the list, a blank value will be inserted
|
set
|
SET is similar to ENUM. However, SET can have up to 64 list items and can store more than one choice
|
Primary Key and Auto increment
Each table should have an unique identifier field. This field is called a primary key.
The primary key field is often an ID number, and is often used with the AUTO_INCREMENT setting.
When used, AUTO_INCREMENT adds 1 to the value of the field each time a new entry is added.To make sure that no primary key fields can be NULL, we add the NOT NULL setting to ensure that the ID value can not be NULL.
Example
This is the same example from above, but with a primary key ID column using AUTO_INCREMENT and NOT NULL:$sql = "CREATE TABLE Person
(
id int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
FirstName varchar(15),
LastName varchar(15),
Age int
)";
mysql_query($sql,$con);
|
PHP MySQL Insert
The MySQL INSERT Statement
In this chapter we will show you how to insert data into your newly created table.We will also show you how to insert data to your database using a form.
In PHP this is done the same way as a table or database is created. The MySQL syntax is sent to the server with the mysql_query() function.
Inserting Data Into a Database
In the previous chapter we created a table named "Person", with three columns. The column names are "Firstname", "Lastname" and "Age". We will use the same database in this example.MySQL Syntax
INSERT INTO table_name
VALUES (value1, value2,....)
|
INSERT INTO table_name (column1, column2,...)
VALUES (value1, value2,....)
|
Now we use this together with the mysql_query() function.
Example
This code inserts data into the "Person" table:
<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("my_db", $con);
mysql_query("INSERT INTO person
(firstname, lastname, age)
VALUES
('Peter', 'Griffin', '35')");
mysql_query("INSERT INTO person
(firstname, lastname, age)
VALUES
('Glenn', 'Quagmire', '33')");
?>
|
Inserting Data From a Form to a Database
In PHP we can allow the users to use a form to insert or edit data in a database.
First we create a form:
<form action="insert_db.php" method="POST">
Enter your Firstname: <input type="text" name="firstname" />
Enter your Lastname: <input type="text" name="lastname" />
Enter your Age: <input type="text" name="age" />
<input type="submit" />
</form>
|
Then the "insert_db.php" page:
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("my_db", $con);
$sql="INSERT INTO person
(firstname,lastname,age)
VALUES
('$_POST[firstname]','$_POST[lastname]','$_POST[age]')";
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "Success!";
|
How does it work?
- The form result is sent to "insert_db.php" by HTTP POST when the user clicks submit
- "insert_db.php" connects to a database
- PHP uses the $_POST variables to get the values from the form
- The mysql_query function inserts the data in the database
You may like:
.jpg)
0 comments:
Post a Comment