PHP MySql Tutorial -> Update and Delete data

PHP MySQL Update

The UPDATE statement is used to modify data in a table

The MySQL UPDATE Statement

In this chapter we will show you how to change data in a table.
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.

Updating Data In a Database

Earlier in the tutorial we created a table named "Person", with three columns. We will use the same table in this example.

FirstName	LastName	Age
Peter	Griffin	35
Glenn	Quagmire	33

MySQL Syntax

UPDATE table_name SET column_name = new_value WHERE column_name = some_value

Now we use this together with the mysql_query() function.

Example

This code updates data in 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("UPDATE Person SET Age = '40' WHERE LastName = 'Griffin'"); ?>

After the update, the table will look like this:

FirstName	LastName	Age
Peter	Griffin	40
Glenn	Quagmire

PHP MySQL Delete

The DELETE statement is used delete rows in a table

The MySQL DELETE Statement

In this chapter we will show you how to delete rows from a table.
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.

Deleting Data In a Database

Earlier in the tutorial we created a table named "Person", with three columns. We will use the same table in this example.

FirstName	LastName	Age
Peter	Griffin	35
Glenn	Quagmire	33

MySQL Syntax

DELETE FROM table_name WHERE column_name = some_value

Now we use this together with the mysql_query() function.

Example

This code updates data in 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("DELETE FROM Person WHERE LastName = 'Griffin'"); ?>

After the delete, the table will look like this:

FirstName	LastName	Age
Glenn	Quagmire	33

PHP Database ODBC

Create an ODBC Connection

With an ODBC connection, you can connect to any database, on any computer in your network, as long as an ODBC connection is available.
Here is how to create an ODBC connection to a MS Access Database: 

1. Open the Administrative Tools icon in your Control Panel.
2. Double-click on the Data Sources (ODBC) icon inside.
3. Choose the System DSN tab.
4. Click on Add in the System DSN tab.
5. Select the Microsoft Access Driver. Click Finish.
6. In the next screen, click Select to locate the database.
7. Give the database a Data Source Name (DSN).
8. Click OK.

Note that this configuration has to be done on the computer where your web site is located. If you are running Internet Information Server (IIS) on your own computer, the instructions above will work, but if your web site is located on a remote server, you have to have physical access to that server, or ask your web host to to set up a DSN for you to use.

Connecting to an ODBC

The odbc_connect() function is used to connect to an ODBC data source. The function takes four parameters: the data source name, username, password, and an optional cursor type.
The odbc_exec() function is used to execute an SQL statement.

Example

The following example creates a connection to a DSN called northwind, with no username and no password. It then creates an SQL and executes it:

$conn=odbc_connect('northwind','',''); $sql="SELECT * FROM customers"; $rs=odbc_exec($conn,$sql);

Retrieving Records

The odbc_fetch_rows() function is used to return records from the result-set. This function returns true if it is able to return rows, otherwise false.

The function takes two parameters: the ODBC result identifier and an optional row number:

odbc_fetch_row($rs)

Retrieving Fields from a Record

The odbc_result() function is used to read fields from a record. This function takes two parameters: the ODBC result identifier and a field number or name.

The code line below returns the value of the first field from the record:

$compname=odbc_result($rs,1);

The code line below returns the value of a field called "CompanyName": 

$compname=odbc_result($rs,"CompanyName");

Closing an ODBC Connection

The odbc_close() function is used to close an ODBC connection.

odbc_close($conn);

An ODBC Example

The following example shows how to first create a database connection, then a result-set, and then display the data in an HTML table.

<html> <body> <?php $conn=odbc_connect('northwind','',''); if (!$conn)   {exit("Connection Failed: " . $conn);} $sql="SELECT * FROM customers"; $rs=odbc_exec($conn,$sql); if (!$rs)   {exit("Error in SQL");} echo "<table><tr>"; echo "<th>Companyname</th>"; echo "<th>Contactname</th></tr>"; while (odbc_fetch_row($rs)) {   $compname=odbc_result($rs,"CompanyName");   $conname=odbc_result($rs,"ContactName");   echo "<tr><td>$compname</td>";   echo "<td>$conname</td></tr>"; } odbc_close($conn); echo "</table>"; ?> </body> </html>

You may like:

PHP MySql Tutorial Update and Delete

PHP MySql Tutorial  Select Data

PHP MySql Tutorial Create and Insert

PHP MySql Tutorial  Introduction

PHP Tutorial Sending Mails

PHP Tutorial File Handling

PHP Tutorial Forms Input and Methods

PHP Tutorial Functions

PHP Tutorial Loops

PHP Tutorial Arrays

Decisions Making

PHP Tutorial Operators

Variables

PHP Tutorial Installation

PHP Tutorial Introduction

Read More

PHP MySql Tutorial -> Select Data

PHP MySQL Select

The MySQL SELECT Statement

In this chapter we will show you how to get data from a database using PHP

In PHP this is done the same way a table is created or data inserted. The MySQL syntax is sent to the server with the mysql_query() function.

The SELECT statement is used to select data from a table.

MySQL Syntax

SELECT column_name(s) FROM table_name

Note: MySQL statements are not case sensitive. SELECT is the same as select.

PHP MySQL SELECT Example

This code gets the data stored in the "Person" table. We use the * character instead of column names because we want to select all from the table:

<?php $con = mysql_connect("localhost","peter","abc123"); if (!$con)  {   die('Could not connect: ' . mysql_error());   } mysql_select_db("my_db", $con);   $result = mysql_query("SELECT * FROM person");   while($row = mysql_fetch_array($result))  {   echo $row['FirstName'];   echo "<br />";   echo $row['LastName'];   echo "<br />";   } ?>

The output of the code above will be:

Peter Griffin Glenn Quagmire

How does it work?

• The data is returned by the mysql_query() function is stored in the $result variable
• The mysql_fetch_array function gets the next line in an array from a MySQL result
• We use the while loop to continue to get the next array until there is no next array to get
• To write the result in the way we want we use $row['FirstName'] and $row['LastName'] variable

PHP MySQL Where

The MySQL WHERE clause

In this chapter we will show you how to get specific data from a database using PHP.
In PHP this is done the same way as a table is created or data inserted. The MySQL syntax is sent to the server with the mysql_query() function.
To select only some data from a table, a WHERE clause can be added to the SELECT statement.

MySQL Syntax

SELECT column FROM table WHERE column operator value

With the WHERE clause, the following operators can be used:

Operator	Description
=	Equal
!=	Not equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
BETWEEN	Between an inclusive range
LIKE	Search for a pattern

PHP MySQL WHERE Example

This code will select all rows from the "Person" table, where the first name is Peter.
This code gets the data stored in the "Person" table. We use the * character instead of column names because we want to check the entire table:

<?php $con = mysql_connect("localhost","peter","abc123"); if (!$con)  {   die('Could not connect: ' . mysql_error());   } mysql_select_db("my_db", $con);   $result = mysql_query("SELECT * FROM person WHERE FirstName='Peter'");   while($row = mysql_fetch_array($result))  {   echo $row['FirstName'];   echo "<br />";   echo $row['LastName'];   echo "<br />";   } ?>

The output of the code above will be:

Peter Griffin

PHP MySQL Order By

The MySQL ORDER BY keyword

In this chapter we will show you how to get data in a specific order. We will also show you how to show the result in a HTML table.

MySQL Syntax

SELECT column_name(s) FROM table_name ORDER BY column_name

PHP MySQL ORDER BY Example

This code gets the data stored in the "Person" table ordered by age:

<?php $con = mysql_connect("localhost","peter","abc123"); if (!$con)   {   die('Could not connect: ' . mysql_error());   }   mysql_select_db("my_db", $con);   $result = mysql_query("SELECT * FROM person ORDER BY age");   while($row = mysql_fetch_array($result))   {   echo $row['FirstName'];   echo "<br />";   echo $row['LastName'];   echo "<br />";   echo $row['Age'];   echo "<br />";   }   ?>

The output of the code above will be:

Glenn Quagmire 33 Peter Griffin 35

Ascending or Descending

It is possible to order by more than one column, or even select if you want to the order to be ascending (Default. 1 before 9 and "a" before "p") or descending (9 before 1 and "p" before "a").

Order by two columns

When ordering by more than one column, the second column is only used if there is several identical values in the first column:

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

Ascending or Descending Order

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

Display Result in Table

This code gets the same result as the example above, but displays the data in a table:

<?php $con = mysql_connect("localhost","peter","abc123"); if (!$con)   {   die('Could not connect: ' . mysql_error());   }   mysql_select_db("my_db", $con);   $result = mysql_query("SELECT * FROM person ORDER BY age");   echo "<table>"; echo "<tr> <th>Firstname</th> <th>Lastname</th> <th>Age</th> </tr>"; while($row = mysql_fetch_array( $result ))   {   echo "<tr><td>";   echo $row['FirstName'];   echo "</td><td>";   echo $row['LastName'];   echo "</td><td>";   echo $row['Age'];   echo "</td></tr>";   } echo "</table>";   ?>

The output of the code above will be:

Firstname Lastname Age Glenn Quagmire 33 Peter Griffin 35

You may like:

PHP MySql Tutorial Update and Delete

PHP MySql Tutorial  Select Data

PHP MySql Tutorial Create and Insert

PHP MySql Tutorial  Introduction

PHP Tutorial Sending Mails

PHP Tutorial File Handling

PHP Tutorial Forms Input and Methods

PHP Tutorial Functions

PHP Tutorial Loops

PHP Tutorial Arrays

Decisions Making

PHP Tutorial Operators

Variables

PHP Tutorial Installation

PHP Tutorial Introduction

Read More

PHP MySql Tutorial -> Create and Insert

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: A database must be selected before a table can be created. This is done in the first line of the example above.
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,....)

You can also specify the columns for which you want to insert data:

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:

PHP MySql Tutorial Update and Delete

PHP MySql Tutorial  Select Data

PHP MySql Tutorial Create and Insert

PHP MySql Tutorial  Introduction

PHP Tutorial Sending Mails

PHP Tutorial File Handling

PHP Tutorial Forms Input and Methods

PHP Tutorial Functions

PHP Tutorial Loops

PHP Tutorial Arrays

Decisions Making

PHP Tutorial Operators

Variables

PHP Tutorial Installation

PHP Tutorial Introduction

Read More