PHP Database connection
MySQL is the most popular open source database server.And easily integrated with PHP.MYSQL is the most common databse use with PHP
What is MySQL
MySQL is a database. A database is a data storage area.
In a database, there are tables. database tables contain rows, columns, and cells.
Databases are useful when storing information . A School may have a database with the following tables: "student_no", "name", "address" and "phone_numbers".
Database Tables
A database most often contains one or more tables. Each table is identified by a name (e.g. "student_no" or "id"). Tables contain records (rows) with data.
Below is an example of a table called "student":
LastName FirstName Address student_number
hari bari 200 w 500 E 10
Rama goel 300 w 400 e 20
The table above contains 2 records (one for each person) and four columns (LastName, FirstName, Address, and student_number).
Queries
A query is a question or a request.
With MySQL, we can query a database for a specific part and have a result-set returned.
A query like this:
SELECT LastName FROM student
Gives a result set like this:
LastName
hari
Rama
Delete
A delete used to delete the row from the table
A query like this:
DELETE FROM student WHERE lastname = hari
Now the row which contain lastname as hari got deleted
How to use mysql with php?
Mysql to php Basic connction
<?php
$username="username of the mysql database";
$password="password of the mysql database";
$con = mysql_connect("freewebs.dot5hostingmysql.com","username","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}?>
Closing a Connection
The connection will be closed as soon as the script ends. To close the connection before, use mysql_close().
<?php
$username="username of the mysql database";
$password="password of the mysql database";
$con = mysql_connect("freewebs.dot5hostingmysql.com","username","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
mysql_close($con);
}?>
The INSERT INTO statement is used to insert new records to a database table.
This is done by using the mysql_query() function. This function is used to send a query or command to a MySQL connection.
MySQL Syntax
INSERT INTO table_name VALUES (value1, value2,....)
You can also specify the columns where you want to insert the data:
INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....)
Example
<?php
//mysql connection scripts
mysql_select_db("school", $con);
mysql_query("INSERT INTO student (firstname, lastname, address) VALUES ('hari', 'bari', '4131 s 200 w')");
mysql_query("INSERT INTO student (firstname, lastname, age) VALUES('rama', 'goel', '200 N 200 w')");
//MYSQL CLOSE
?>
How to take values form html forms to the database
Insert Data from a Form Into a Database
Now we will create an HTML form that can be used to insert data in the "student" table.
Here is the HTML form:
Then, here is the code in the "insert.php" page:
school---------->database
student--------->table
PHP MySQL Select
The SELECT statement is used to select data from a database.
This is also done by using the mysql_query() function. This function is used to send a query or command to a MySQL connection.
MySQL Syntax
SELECT column_name(s) FROM table_name
Note: MySQL statements are not case sensitive. SELECT is the same as select.
Example
The following example selects the data stored in the "student" table. We use the * character to select all of the data in the table:
The output of the code above something like this:
hari bari
rama goel
The MySQL UPDATE Statement
This is also done by using the mysql_query() function. This function is used to send a query or command to a MySQL connection.
Update Data In a Database
The UPDATE statement is used to modify data in a database table.
MySQL Syntax
UPDATE table_name SET column_name = new_value WHERE column_name = some_value
PHP MySQL Where
To select only data that matches a specified criteria, add a WHERE clause to the SELECT statement.
This is also done by using the mysql_query() function.
To select only data that matches a specified criteria, we add a WHERE clause to the SELECT statement.
MySQL Syntax
SELECT column FROM table WHERE column operator value
The following operators can be used with the WHERE clause: |