Learn PHP for Free: MySQL

Showing posts with label MySQL. Show all posts

Monday, 5 May 2014

PHP - AJAX and MySQL

AJAX can be used for interactive communication with a database.

AJAX Database Example

The following example will demonstrate how a web page can fetch information from a database with AJAX:

Example

Person info will be listed here...

Example Explained - The MySQL Database

The database table we use in the example above looks like this:

id	FirstName	LastName	Age	Hometown	Job
1	Peter	Griffin	41	Quahog	Brewery
2	Lois	Griffin	40	Newport	Piano Teacher
3	Joseph	Swanson	39	Quahog	Police Officer
4	Glenn	Quagmire	41	Quahog	Pilot

Example Explained - The HTML Page

When a user selects a user in the dropdown list above, a function called "showUser()" is executed. The function is triggered by the "onchange" event:

<html>
<head>
<script>
function showUser(str) {
if (str=="") {
document.getElementById("txtHint").innerHTML="";
    return;
}
if (window.XMLHttpRequest) {
    // code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp=new XMLHttpRequest();
} else { // code for IE6, IE5
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.onreadystatechange=function() {
if (xmlhttp.readyState==4 && xmlhttp.status==200) {
    document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
    }
}
xmlhttp.open("GET","getuser.php?q="+str,true);
xmlhttp.send();
}
</script>
</head>
<body>

<form>
<select name="users" onchange="showUser(this.value)">
<option value="">Select a person:</option>
<option value="1">Peter Griffin</option>
<option value="2">Lois Griffin</option>
<option value="3">Glenn Quagmire</option>
<option value="4">Joseph Swanson</option>
</select>
</form>
<br>
<div id="txtHint"><b>Person info will be listed here.</b></div>

</body>
</html>

The showUser() function does the following:

Check if a person is selected
Create an XMLHttpRequest object
Create the function to be executed when the server response is ready
Send the request off to a file on the server
Notice that a parameter (q) is added to the URL (with the content of the dropdown list)

The PHP File

The page on the server called by the JavaScript above is a PHP file called "getuser.php".
The source code in "getuser.php" runs a query against a MySQL database, and returns the result in an HTML table:

<?php
$q = intval($_GET['q']);

$con = mysqli_connect('localhost','peter','abc123','my_db');
if (!$con) {
  die('Could not connect: ' . mysqli_error($con));
}

mysqli_select_db($con,"ajax_demo");
$sql="SELECT * FROM user WHERE id = '".$q."'";
$result = mysqli_query($con,$sql);

echo "<table border='1'>
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>Age</th>
<th>Hometown</th>
<th>Job</th>
</tr>";

while($row = mysqli_fetch_array($result)) {
  echo "<tr>";
  echo "<td>" . $row['FirstName'] . "</td>";
  echo "<td>" . $row['LastName'] . "</td>";
  echo "<td>" . $row['Age'] . "</td>";
  echo "<td>" . $row['Hometown'] . "</td>";
  echo "<td>" . $row['Job'] . "</td>";
  echo "</tr>";
}
echo "</table>";

mysqli_close($con);
?>

Explanation: When the query is sent from the JavaScript to the PHP file, the following happens:

PHP opens a connection to a MySQL server
The correct person is found
An HTML table is created, filled with data, and sent back to the "txtHint" placeholder

PHP MySQL Delete

The DELETE statement is used to delete records in a table.

Delete Data In a Database

The DELETE FROM statement is used to delete records from a database table.

Syntax

DELETE FROM table_name
WHERE some_column = some_value

Note: Notice the WHERE clause in the DELETE syntax. The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!
To learn more about SQL, please visit our SQL tutorial.
To get PHP to execute the statement above we must use the mysqli_query() function. This function is used to send a query or command to a MySQL connection.

Example

Look at the following "Persons" table:

FirstName	LastName	Age
Peter	Griffin	35
Glenn	Quagmire	33

The following example deletes all the records in the "Persons" table where LastName='Griffin':

After the deletion, the table will look like this:

FirstName	LastName	Age
Glenn	Quagmire	33

PHP MySQL Update

The UPDATE statement is used to modify data in a table.

Update Data In a Database

The UPDATE statement is used to update existing records in a table.

Syntax

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

Note: Notice the WHERE clause in the UPDATE syntax. The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!To learn more about SQL, please visit our SQL tutorial.
To get PHP to execute the statement above we must use the mysqli_query() function. This function is used to send a query or command to a MySQL connection.

Example

Earlier in the tutorial we created a table named "Persons". Here is how it looks:

FirstName	LastName	Age
Peter	Griffin	35
Glenn	Quagmire	33

The following example updates some data in the "Persons" table:

<?php
$con=mysqli_connect("example.com","peter","abc123","my_db");
// Check connection
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

mysqli_query($con,"UPDATE Persons SET Age=36
WHERE FirstName='Peter' AND LastName='Griffin'");

mysqli_close($con);
?>

After the update, the "Persons" table will look like this:

FirstName	LastName	Age
Peter	Griffin	36
Glenn	Quagmire	33

PHP MySQL Order By Keyword

The ORDER BY keyword is used to sort the data in a recordset.

The ORDER BY Keyword

The ORDER BY keyword is used to sort the data in a recordset.
The ORDER BY keyword sort the records in ascending order by default.
If you want to sort the records in a descending order, you can use the DESC keyword.

Syntax

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC

To learn more about SQL, please visit our SQL tutorial.

Example

The following example selects all the data stored in the "Persons" table, and sorts the result by the "Age" column:

<?php
$con=mysqli_connect("example.com","peter","abc123","my_db");
// Check connection
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con,"SELECT * FROM Persons ORDER BY age");

while($row = mysqli_fetch_array($result)) {
  echo $row['FirstName'];
  echo " " . $row['LastName'];
  echo " " . $row['Age'];
  echo "<br>";
}

mysqli_close($con);
?>

The output of the code above will be:

Glenn Quagmire 33
Peter Griffin 35

Order by Two Columns

It is also possible to order by more than one column. When ordering by more than one column, the second column is only used if the values in the first column are equal:

SELECT column_name(s)
FROM table_name
ORDER BY column1, column2

PHP MySQL The Where Clause

The WHERE clause is used to filter records.

The WHERE clause

The WHERE clause is used to extract only those records that fulfill a specified criterion.

Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name operator value

To learn more about SQL, please visit our SQL tutorial.
To get PHP to execute the statement above we must use the mysqli_query() function. This function is used to send a query or command to a MySQL connection.

Example

The following example selects all rows from the "Persons" table where "FirstName='Peter'":

<?php
$con=mysqli_connect("example.com","peter","abc123","my_db");
// Check connection
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con,"SELECT * FROM Persons
WHERE FirstName='Peter'");

while($row = mysqli_fetch_array($result)) {
echo $row['FirstName'] . " " . $row['LastName'];
echo "<br>";
}
?>

The output of the code above will be:

Peter Griffin

PHP MySQL Insert Into

The INSERT INTO statement is used to insert new records in a table.

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,...)

To learn more about SQL, please visit our SQL tutorial.
To get PHP to execute the statements above we must use the mysqli_query() function. This function is used to send a query or command to a MySQL connection.

Example

In the previous chapter 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=mysqli_connect("example.com","peter","abc123","my_db");
// Check connection
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

mysqli_query($con,"INSERT INTO Persons (FirstName, LastName, Age)
VALUES ('Peter', 'Griffin',35)");

mysqli_query($con,"INSERT INTO Persons (FirstName, LastName, Age)
VALUES ('Glenn', 'Quagmire',33)");

mysqli_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.
The mysqli_real_escape_string() function escapes special characters in a string for security against SQL injection.
Then, the mysqli_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=mysqli_connect("example.com","peter","abc123","my_db");
// Check connection
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// escape variables for security
$firstname = mysqli_real_escape_string($con, $_POST['firstname']);
$lastname = mysqli_real_escape_string($con, $_POST['lastname']);
$age = mysqli_real_escape_string($con, $_POST['age']);

$sql="INSERT INTO Persons (FirstName, LastName, Age)
VALUES ('$firstname', '$lastname', '$age')";

if (!mysqli_query($con,$sql)) {
die('Error: ' . mysqli_error($con));
}
echo "1 record added";

mysqli_close($con);
?>

PHP MySQL Introduction

With PHP, you can connect to and manipulate databases.

MySQL is the most popular database system used with PHP.

What is MySQL?

MySQL is a database system used on the web
MySQL is a database system that runs on a server
MySQL is ideal for both small and large applications
MySQL is very fast, reliable, and easy to use
MySQL supports standard SQL
MySQL compiles on a number of platforms
MySQL is free to download and use
MySQL is developed, distributed, and supported by Oracle Corporation
MySQL is named after co-founder Monty Widenius's daughter: My

The data in MySQL is stored in tables. A table is a collection of related data, and it consists of columns and rows.
Databases are useful when storing information categorically. A company may have a database with the following tables:

Employees
Products
Customers
Orders

PHP + MySQL

PHP combined with MySQL are cross-platform (you can develop in Windows and serve on a Unix platform)

Queries

A query is a question or a request.
We can query a database for specific information and have a recordset returned.
Look at the following query (using standard SQL):

SELECT LastName FROM Employees

The query above selects all the data in the "LastName" column from the "Employees" table.
To learn more about SQL, please visit our SQL tutorial.

Download MySQL Database

If you don't have a PHP server with a MySQL Database, you can download MySQL for free here: http://www.mysql.com

Facts About MySQL Database

One great thing about MySQL is that it can be scaled down to support embedded database applications. Maybe it is because of this many people think that MySQL can only handle small and medium-sized systems.
The truth is that MySQL is the de-facto standard database system for web sites with HUGE volumes of both data and end users (like Friendster, Yahoo, and Google).
Look at http://www.mysql.com/customers/ for an overview of companies using MySQL.

Learn PHP for Free

Ad

Ad

Ad

Monday, 5 May 2014

PHP - AJAX and MySQL

AJAX Database Example

Example

Example Explained - The MySQL Database

Example Explained - The HTML Page

The PHP File

PHP MySQL Delete

Delete Data In a Database

Syntax

Example

PHP MySQL Update

Update Data In a Database

Syntax

Example

PHP MySQL Order By Keyword

The ORDER BY Keyword

Syntax

Example

Order by Two Columns

PHP MySQL The Where Clause

The WHERE clause

Syntax

Example

PHP MySQL Insert Into

Insert Data Into a Database Table

Syntax

Example

Insert Data From a Form Into a Database

PHP MySQL Introduction

What is MySQL?

PHP + MySQL

Queries

Download MySQL Database

Facts About MySQL Database