Php pdo update where

Содержание

PDOStatement::execute
Return Values
Errors/Exceptions
Examples
Notes
See Also
User Contributed Notes 31 notes
PHP MySQL Update Data
Example (MySQLi Object-oriented)
Example (MySQLi Procedural)
Example (PDO)

PDOStatement::execute

An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as PDO::PARAM_STR .

Multiple values cannot be bound to a single parameter; for example, it is not allowed to bind two values to a single named parameter in an IN() clause.

Binding more values than specified is not possible; if more keys exist in params than in the SQL specified in the PDO::prepare() , then the statement will fail and an error is emitted.

Return Values

Returns true on success or false on failure.

Errors/Exceptions

Emits an error with level E_WARNING if the attribute PDO::ATTR_ERRMODE is set to PDO::ERRMODE_WARNING .

Throws a PDOException if the attribute PDO::ATTR_ERRMODE is set to PDO::ERRMODE_EXCEPTION .

Examples

Example #1 Execute a prepared statement with a bound variable and value

/* Execute a prepared statement by binding a variable and value */
$calories = 150 ;
$colour = ‘gre’ ;
$sth = $dbh -> prepare ( ‘SELECT name, colour, calories
FROM fruit
WHERE calories < :calories AND colour LIKE :colour' );
$sth -> bindParam ( ‘calories’ , $calories , PDO :: PARAM_INT );
/* Names can be prefixed with colons «:» too (optional) */
$sth -> bindValue ( ‘:colour’ , «% $colour %» );
$sth -> execute ();
?>

Example #2 Execute a prepared statement with an array of named values

/* Execute a prepared statement by passing an array of insert values */
$calories = 150 ;
$colour = ‘red’ ;
$sth = $dbh -> prepare ( ‘SELECT name, colour, calories
FROM fruit
WHERE calories < :calories AND colour = :colour' );
$sth -> execute (array( ‘calories’ => $calories , ‘colour’ => $colour ));
/* Array keys can be prefixed with colons «:» too (optional) */
$sth -> execute (array( ‘:calories’ => $calories , ‘:colour’ => $colour ));
?>

Example #3 Execute a prepared statement with an array of positional values

Example #4 Execute a prepared statement with variables bound to positional placeholders

/* Execute a prepared statement by binding PHP variables */
$calories = 150 ;
$colour = ‘red’ ;
$sth = $dbh -> prepare ( ‘SELECT name, colour, calories
FROM fruit
WHERE calories < ? AND colour = ?' );
$sth -> bindParam ( 1 , $calories , PDO :: PARAM_INT );
$sth -> bindParam ( 2 , $colour , PDO :: PARAM_STR , 12 );
$sth -> execute ();
?>

Example #5 Execute a prepared statement using array for IN clause

/* Execute a prepared statement using an array of values for an IN clause */
$params = array( 1 , 21 , 63 , 171 );
/* Create a string for the parameter placeholders filled to the number of params */
$place_holders = implode ( ‘,’ , array_fill ( 0 , count ( $params ), ‘?’ ));

/*
This prepares the statement with enough unnamed placeholders for every value
in our $params array. The values of the $params array are then bound to the
placeholders in the prepared statement when the statement is executed.
This is not the same thing as using PDOStatement::bindParam() since this
requires a reference to the variable. PDOStatement::execute() only binds
by value instead.
*/
$sth = $dbh -> prepare ( «SELECT id, name FROM contacts WHERE id IN ( $place_holders )» );
$sth -> execute ( $params );
?>

Notes

Note:

Some drivers require to close cursor before executing next statement.

User Contributed Notes 31 notes

Hopefully this saves time for folks: one should use $count = $stmt->rowCount() after $stmt->execute() in order to really determine if any an operation such as ‘ update ‘ or ‘ replace ‘ did succeed i.e. changed some data.

Note that you must
— EITHER pass all values to bind in an array to PDOStatement::execute()
— OR bind every value before with PDOStatement::bindValue(), then call PDOStatement::execute() with *no* parameter (not even «array()»!).
Passing an array (empty or not) to execute() will «erase» and replace any previous bindings (and can lead to, e.g. with MySQL, «SQLSTATE[HY000]: General error: 2031» (CR_PARAMS_NOT_BOUND) if you passed an empty array).

Thus the following function is incorrect in case the prepared statement has been «bound» before:

function customExecute ( PDOStatement & $sth , $params = NULL ) <
return $sth -> execute ( $params );
>
?>

and should therefore be replaced by something like:

function customExecute ( PDOStatement & $sth , array $params = array()) <
if (empty( $params ))
return $sth -> execute ();
return $sth -> execute ( $params );
>
?>

Also note that PDOStatement::execute() doesn’t require $input_parameters to be an array.

(of course, do not use it as is ^^).

An array of insert values (named parameters) don’t need the prefixed colon als key-value to work.

/* Execute a prepared statement by passing an array of insert values */
$calories = 150 ;
$colour = ‘red’ ;
$sth = $dbh -> prepare ( ‘SELECT name, colour, calories
FROM fruit
WHERE calories < :calories AND colour = :colour' );
// instead of:
// $sth->execute(array(‘:calories’ => $calories, ‘:colour’ => $colour));
// this works fine, too:
$sth -> execute (array( ‘calories’ => $calories , ‘colour’ => $colour ));
?>

This allows to use «regular» assembled hash-tables (arrays).
That realy does make sense!

When passing an array of values to execute when your query contains question marks, note that the array must be keyed numerically from zero. If it is not, run array_values() on it to force the array to be re-keyed.

$anarray = array( 42 => «foo» , 101 => «bar» );
$statement = $dbo -> prepare ( «SELECT * FROM table WHERE col1 = ? AND col2 = ?» );

//This will not work
$statement -> execute ( $anarray );

//Do this to make it work
$statement -> execute ( array_values ( $anarray ));
?>

When using a prepared statement to execute multiple inserts (such as in a loop etc), under sqlite the performance is dramatically improved by wrapping the loop in a transaction.

I have an application that routinely inserts 30-50,000 records at a time. Without the transaction it was taking over 150 seconds, and with it only 3.

This may affect other implementations as well, and I am sure it is something that affects all databases to some extent, but I can only test with PDO sqlite.

$data = array(
array( ‘name’ => ‘John’ , ‘age’ => ’25’ ),
array( ‘name’ => ‘Wendy’ , ‘age’ => ’32’ )
);

try <
$pdo = new PDO ( ‘sqlite:myfile.sqlite’ );
>

catch( PDOException $e ) <
die( ‘Unable to open database connection’ );
>

$insertStatement = $pdo -> prepare ( ‘insert into mytable (name, age) values (:name, :age)’ );

// start transaction
$pdo -> beginTransaction ();

// end transaction
$pdo -> commit ();

[EDITED BY sobak: typofixes by Pere submitted on 12-Sep-2014 01:07]

«You cannot bind more values than specified; if more keys exist in input_parameters than in the SQL specified in the PDO::prepare(), then the statement will fail and an error is emitted.» However fewer keys may not cause an error.

As long as the number of question marks in the query string variable matches the number of elements in the input_parameters, the query will be attempted.

This happens even if there is extraneous information after the end of the query string. The semicolon indicates the end of the query string; the rest of the variable is treated as a comment by the SQL engine, but counted as part of the input_parameters by PHP.

Have a look at these two query strings. The only difference is a typo in the second string, where a semicolon accidentally replaces a comma. This UPDATE query will run, will be applied to all rows, and will silently damage the table.

/**
* Query is intended to UPDATE a subset of the rows based on the WHERE clause
*/
$sql = «UPDATE my_table SET fname = ?, lname = ? WHERE > ;

/**
* Query UPDATEs all rows, ignoring everything after the semi-colon, including the WHERE clause!
*
* Expected (but not received):
*
*** Warning:
*** PDOStatement::execute():
*** SQLSTATE[HY093]:
*** Invalid parameter number: number of bound variables does not match number of tokens.
*
*/
// Typo here ———————— |
// V
$sql = «UPDATE my_table SET fname = ?; lname = ? WHERE > ; // One token in effect
$pdos = $pdo -> prepare ( $sql );
$pdos -> execute ( [ ‘foo’ , ‘bar’ , 3 ] ); // Three input_parameters
?>

PHP 5.4.45, mysqlnd 5.0.10

Источник

PHP MySQL Update Data

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

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.

Let’s look at the «MyGuests» table:

id	firstname	lastname	email	reg_date
1	John	Doe	john@example.com	2014-10-22 14:26:15
2	Mary	Moe	mary@example.com	2014-10-23 10:22:30

The following examples update the record with in the «MyGuests» table:

Example (MySQLi Object-oriented)

$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDB»;

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) die(«Connection failed: » . $conn->connect_error);
>

$sql = «UPDATE MyGuests SET lastname=’Doe’ WHERE ($conn->query($sql) === TRUE) echo «Record updated successfully»;
> else echo «Error updating record: » . $conn->error;
>

Example (MySQLi Procedural)

$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDB»;

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) die(«Connection failed: » . mysqli_connect_error());
>

$sql = «UPDATE MyGuests SET lastname=’Doe’ WHERE (mysqli_query($conn, $sql)) echo «Record updated successfully»;
> else echo «Error updating record: » . mysqli_error($conn);
>

Example (PDO)

$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDBPDO»;

try $conn = new PDO(«mysql:host=$servername;dbname=$dbname», $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = «UPDATE MyGuests SET lastname=’Doe’ WHERE // Prepare statement
$stmt = $conn->prepare($sql);

// execute the query
$stmt->execute();

// echo a message to say the UPDATE succeeded
echo $stmt->rowCount() . » records UPDATED successfully»;
> catch(PDOException $e) echo $sql . «
» . $e->getMessage();
>

After the record is updated, the table will look like this:

id	firstname	lastname	email	reg_date
1	John	Doe	john@example.com	2014-10-22 14:26:15
2	Mary	Doe	mary@example.com	2014-10-23 10:22:30