In this article, you look at how to alter the data in a MySQL database using PHP. This involves:
1. Inserting new records into tables using INSERT statements in PHP
2. Changing field values within records with UPDATE statements
3. Deleting records using DELETE statements
Inserting Records with SQL using PHP
You can insert a row of data with:
INSERT INTO table VALUES ( value1 , value2 , … );
If you want to insert only some values, leaving NULL s or other default values in the remaining fields, use:
INSERT INTO table ( field1, field2 , … ) VALUES ( value1, value2 , … );
Though the first approach is compact, and perfectly valid if you want to populate all the fields in the
row, the second approach is generally more flexible and readable. So how do you insert records using your PHP script? You pass INSERT statements to MySQL via PDO in much the same way as you pass SELECT statements. If you don ’ t want to pass data from any PHP variables, you can use the simpler PDO::query() method — for example:
< ?php
$dsn = “mysql:dbname=mydatabase”;
$username = “root”;
$password = “mypass”;
try {
$conn = new PDO( $dsn, $username, $password );
$conn- > setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
} catch ( PDOException $e ) {
echo “Connection failed: “ . $e- > getMessage();
}
$sql = “INSERT INTO members VALUES ( 8, ‘derek’, password(‘mypass’), ‘Derek’,
‘Winter’, ‘2008-06-25’, ‘m’, ‘crime’, ‘derek@example.com’, ‘Watching TV,
motor racing’ )”;
try {
$conn- > query( $sql );
} catch ( PDOException $e ) {
echo “Query failed: “ . $e- > getMessage();
}
? >
Notice that, although the call to $conn – > query() still returns a PDOStatement object, the object is
discarded in this case. There ’ s no result set to examine, so there ’ s no need to hold onto the
PDOStatement object. However, chances are that you do want to insert data that is stored in PHP variables. For example, if a member has just registered using a registration form, you ’ ll want to pass the form data to the INSERT statement to add the member record. The safest way to do this is to create a prepared statement using PDO::prepare() , as you did with SELECT queries in the previous chapter. You can then use placeholders in the query string for each of the field values that you want to insert, and pass the data into the query using calls to PDOStatement::bindValue() . For example:
< ?php
$dsn = “mysql:dbname=mydatabase”;
$username = “root”;
$password = “mypass”;
try {
$conn = new PDO( $dsn, $username, $password );
$conn- > setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
} catch ( PDOException $e ) {
echo “Connection failed: “ . $e- > getMessage();
}
$id = 8;
$username = “derek”;
$password = “mypass”;
$firstName = “Derek”;
$lastName = “Winter”;
$joinDate = “2008-06-25”;
$gender = “m”;
$favoriteGenre = “crime”;
$emailAddress = “derek@example.com”;
$otherInterests = “Watching TV, motor racing”;
$sql = “INSERT INTO members VALUES ( :id, :username, password(:password),
:firstName, :lastName, :joinDate, :gender, :favoriteGenre, :emailAddress,
:otherInterests )”;
try {
$st = $conn- > prepare( $sql );
$st- > bindValue( “:id”, $id, PDO::PARAM_INT );
$st- > bindValue( “:username”, $username, PDO::PARAM_STR );
$st- > bindValue( “:password”, $password, PDO::PARAM_STR );
$st- > bindValue( “:firstName”, $firstName, PDO::PARAM_STR );
$st- > bindValue( “:lastName”, $lastName, PDO::PARAM_STR );
$st- > bindValue( “:joinDate”, $joinDate, PDO::PARAM_STR );
$st- > bindValue( “:gender”, $gender, PDO::PARAM_STR );
$st- > bindValue( “:favoriteGenre”, $favoriteGenre, PDO::PARAM_STR );
$st- > bindValue( “:emailAddress”, $emailAddress, PDO::PARAM_STR );
$st- > bindValue( “:otherInterests”, $otherInterests, PDO::PARAM_STR );
$st- > execute();
} catch ( PDOException $e ) {
echo “Query failed: “ . $e- > getMessage();
}
? >
In this example, the variable values are hard – coded in the script. In a real – world application, you would of course receive these values from outside the script, such as via submitted form values in the $_POST superglobal array.
Updating Records in SQL with PHP
You can alter the data within an existing table row by using an SQL UPDATE
statement:
mysql > UPDATE fruit SET name = ‘grapefruit’, color = ‘yellow’ WHERE id = 2; Query OK, 1 row affected (0.29 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql > SELECT * from fruit; +----+------------+--------+ | id | name | color | +----+------------+--------+ | 1 | banana | yellow | | 2 | grapefruit | yellow | | 3 | plum | purple | +----+------------+--------+ 3 rows in set (0.00 sec)
As with inserting new records, updating records via your PHP script is simply a case of using
PDO::query() if you ’ re passing literal values in the UPDATE statement, or PDO::prepare() with
placeholders if you ’ re passing variable values. For example, the following script changes the email
address field in the “ Derek Winter ” record that was added in the previous section:
< ?php
$dsn = “mysql:dbname=mydatabase”;
$username = “root”;
$password = “mypass”;
try {
$conn = new PDO( $dsn, $username, $password );
$conn- > setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
} catch ( PDOException $e ) {
echo “Connection failed: “ . $e- > getMessage();
}
$id = 8;
$newEmailAddress = “derek.winter@example.com”;
$sql = “UPDATE members SET emailAddress = :emailAddress WHERE id = :id”;
try {
$st = $conn- > prepare( $sql );
$st- > bindValue( “:id”, $id, PDO::PARAM_INT );
$st- > bindValue( “:emailAddress”, $newEmailAddress, PDO::PARAM_STR );
$st- > execute();
} catch ( PDOException $e ) {
echo “Query failed: “ . $e- > getMessage();
}
? >
Deleting Records in SQL using PHP
Deleting rows of data via PHP is a similar process to updating. You can delete rows from a table using the SQL DELETE keyword:
mysql > DELETE FROM fruit WHERE id = 2;
Query OK, 1 row affected (0.02 sec)
To delete rows using PHP, you pass a DELETE statement directly via PDO::query() , or create the statement using PDO::prepare() with placeholders, passing in values (such as the criteria for the WHERE clause) with PDOStatement::bindValue() and running the query with PDOStatement::execute() .
The following script deletes the member record with the ID of 8 from the members table:
< ?php
$dsn = “mysql:dbname=mydatabase”;
$username = “root”;
$password = “mypass”;
try {
$conn = new PDO( $dsn, $username, $password );
$conn- > setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
} catch ( PDOException $e ) {
echo “Connection failed: “ . $e- > getMessage();
}
$id = 8;
$sql = “DELETE FROM members WHERE id = :id”;
try {
$st = $conn- > prepare( $sql );
$st- > bindValue( “:id”, $id, PDO::PARAM_INT );
$st- > execute();
} catch ( PDOException $e ) {
echo “Query failed: “ . $e- > getMessage();
}
? >
Incidentally, rather than binding the value of a variable to a placeholder with PDOStatement::
bindValue() , you can instead use PDOStatement::bindParam() to bind the variable itself. If
you then change the value of the variable after the call to bindParam() , the placeholder value is
automatically updated to the new value (in other words, the variable is bound by reference rather than by value). This can be useful if you ’ re not sure what value you ’ re going to pass in at the time you prepare the statement. Find out more on bindParam() in the online PHP manual at http://www .php.net/manual/en/pdostatement.bindparam.php .