Database Processing with PDO

sunfloweremryologistData Management

Oct 31, 2013 (3 years and 9 months ago)

112 views

Database Processing with PDO

ISYS 475

PDO:

http://net.tutsplus.com/tutorials/php/why
-
you
-
should
-
be
-
using
-
phps
-
pdo
-
for
-
database
-
access/


PDO


PHP Data Objects


is a database access
layer providing a uniform method of access to
multiple databases since PHP 5.1.


Regardless
of which database you're using, you
use the same functions to issue queries and
fetch data.


Database Support


PDO_DBLIB (
FreeTDS

/ Microsoft SQL Server / Sybase )


PDO_FIREBIRD ( Firebird/
Interbase

6 )


PDO_IBM ( IBM DB2 )


PDO_INFORMIX ( IBM Informix Dynamic Server )


PDO_MYSQL ( MySQL 3.x/4.x/5.x )


PDO_OCI ( Oracle Call Interface )


PDO_ODBC ( ODBC v3 (IBM DB2,
unixODBC

and win32
ODBC) )


PDO_PGSQL (
PostgreSQL

)


PDO_SQLITE ( SQLite 3 and SQLite 2 )


PDO_4D ( 4D )


A

quick way to find out which drivers
you have:


print_r
(PDO::
getAvailableDrivers
());



Or



http://localhost/xampp
/


Phpinfo
()

Connecting

Connecting Example: To MySQL

$
dsn

= '
mysql:host
=
localhost;dbname
=
custorders
';

$username = 'root';

$password = '';

$
db

= new PDO($
dsn
, $username, $password);

$
db

= new PDO('
mysql:host
=
localhost;dbname
=
salesdb
', 'root', '');

Or use one statement as:

Handle Exception


If there are any connection errors, a
PDOException

object will be thrown. You may
catch the exception if you want to handle the
error
condition.


catch (
PDOException

$e) {


print "Error!: " . $e
-
>
getMessage
() . "<
br
/>";


die();

}

catch (Exception $e) {


$
error_message

= $e
-
>
getMessage
();


echo "<p>Error message: $
error_message

</p
>";

Exit();


}

Running SQL Statements with PDO


SQL Select statement:


PDO
::query


Executes an SQL
Select statement
,
returning a result set
as a
PDOStatement

object


SQL Insert, Delete and Update statements:


PDO
::exec


Execute an SQL statement and
return the number of affected
rows


Parameterized (Prepared) statement:


PDO
::prepare


Prepares a statement for
execution and returns a
PDOstatement

object

Selecting Data with PDO query


PDO::query


Executes an SQL Select
statement, returning a result set as a
PDOStatement

object


A nice feature of PDO::query() is that it
enables you to iterate over the
rowset

returned by a successfully executed SELECT
statement.

The
PDOStatement

class

Represents a prepared statement and, after the
statement is executed, an associated result
set


PDOStatement
::fetch


Fetches the next row from a result set


PDOStatement
::
fetchAll



Returns an array containing all of the
result set
rows


PDOStatement
::
rowCount



Returns the number of rows
affected by the last SQL statement


Using
PDOStatement’s

fetch() method


Query Returns a
PDOStatement

object, we can
use
PDOStatement

object’s fetch() method to
retrieve a row from the result set.


Each row
is
represented
as an array indexed
by both column name and
number.


The return value of this function on success
depends on the fetch type. In all cases, FALSE
is returned on failure.


Types of arrays


•Indexed arrays
-

Arrays with numeric index


•Associative arrays
-

Arrays with named keys


Example of indexed array:


<?
php


$cars=array("
Volvo","BMW","Toyota
");

echo "I like " . $cars[0] . ", " . $cars[1] . " and " . $cars[2] . ".";


?>

The index can be assigned automatically (index
always starts at 0):

or the index can be assigned manually
:

$cars[0]="Volvo";


$cars[1]="BMW";


$cars[2]="Toyota";

Example: Adding a new item

<?
php


$cars=array("
Volvo","BMW","Toyota
");


echo "I like " . $cars[0] . ", " . $cars[1] . " and " . $cars[2] . ".";

$cars[3]="Ford";

echo $cars[3];


?>

Loop through an indexed array:

Note: The count() function is used to return the
length (the number of elements) of an array:

<?
php


$cars=array("
Volvo","BMW","Toyota
");


$
arrlength
=count($cars);


for($x=0;$x<$
arrlength
;$x++)


{


echo $cars[$x];


echo "<
br
>";


}

?>

Loop through indexed array using
foreach

<?
php


$cars=array("
Volvo","BMW","Toyota
");


foreach
($cars as $car)


{


echo $car;


echo "<
br
>";


}

?>

Associative Arrays


Associative arrays are arrays that use named
keys that you assign to them. They are an
ordered map that associates values to keys:



key => value


A value may be an array itself.

Arrays and objects can not be used as keys.


Examples of creating an associative array:

$age=array("Peter"=>"35","Ben"=>"37","Joe"=>"43");

$age['Peter']="35";


$age['Ben']="37";


$age['Joe']="43";

Or:

The named keys can then be used in a script:

<?
php


$age=array("Peter"=>"35","Ben"=>"37","Joe"=>"43");


echo "Peter is " . $age['Peter'] . " years old.";


?>

Loop through associative array using
foreach

(access value only)

<?
php


$
age=array("Peter"=>"35","Ben"=>"37","Joe"=>"43");


foreach
($age as $x)


{


echo $x ;


echo "<
br
>";


}

?>

Loop
t
hrough an associative array and
access the key and its value

<?
php

$age=array("Peter"=>"35","Ben"=>"37","Joe"=>"43");


foreach
($age as $x=>$
x_value
)


{


echo "Key=" . $x . ", Value=" . $
x_value
;


echo "<
br
>";


}

?>

Using
while loop with fetch
()
to iterate over the result set

<?
php

t
ry {

$
db

= new PDO('
mysql:host
=
localhost;dbname
=
salesdb
', 'root', '');


$query="select * from customers";


$customers = $
db
-
>query($query
);

echo $customers
-
>
rowCount
() . " rows returned.";


echo "<table border=1><
tr
>" . "<
th
>CID</
th
>" . "<
th
>
CName
</
th
>" . "<
th
>City</
th
>" .
"<
th
>Rating</
th
></
tr
>";


while ($customer = $customers
-
>fetch
()){


$
cid
=$customer["
cid
"];
//
$customer is an array; field
name is case
sensitive or use index


$
Cname
=$customer["
cname
"];
Or
$customer[1]


$City=$customer["city"]; $
customer[2]


$Rating=$customer["rating"]; $
customer[3]


echo "<
tr
><td>$
cid
</td>" . "<td>$
Cname
</td>" . "<td>$City</td>" .
"<td>$Rating</td></
tr
>";

}

echo "</table>";

}

catch (Exception $e) {


$
error_message

= $e
-
>
getMessage
();


echo "<p>Error message: $
error_message

</p>";


}

?>

PDOStatement
::
rowCount
()


PDOStatement
::
rowCount
() returns the number of
rows affected by the last DELETE, INSERT, or UPDATE
statement executed by the corresponding
PDOStatement

object.



If the last SQL statement executed by the associated
PDOStatement

was a SELECT statement, some
databases may return the number of rows returned by
that statement. However, this
behaviour

is not
guaranteed for all databases and should not be relied
on for portable applications.


Example of Using the query method and iterate
over the result set using the
foreach

statement

<?
php

Try {

$
db

= new PDO('
mysql:host
=
localhost;dbname
=
salesdb
', 'root', '');


$query="select * from customers";


$customers = $
db
-
>query($query
);
//$customers is the result set of the
PDOStatement


echo "<table border=1><
tr
>" . "<
th
>CID</
th
>" . "<
th
>
CName
</
th
>" . "<
th
>City</
th
>" .
"<
th
>Rating</
th
></
tr
>";


foreach

($customers as $customer){


$
cid
=$customer["
cid
"];
//field name is case sensitive


$
Cname
=$customer["
cname
"];


$City=$customer["city"];


$Rating=$customer["rating"];


echo "<
tr
><td>$
cid
</td>" . "<td>$
Cname
</td>" . "<td>$City</td>" . "<td>$Rating</td></
tr
>";

}

echo "</table>";

}


catch (Exception $e) {


$
error_message

= $e
-
>
getMessage
();


echo "<p>Error message: $
error_message

</p>";


}

?>

Example of Using
fetchAll
()

<?
php


try

{

$
db

= new PDO('
mysql:host
=
localhost;dbname
=
salesdb
', 'root', '');


$query="select * from customers";


$customers = $
db
-
>query($query);


$
AllCustomers
=$customers
-
>
fetchAll
();


echo "<table border=1><
tr
>" . "<
th
>CID</
th
>" . "<
th
>
CName
</
th
>" . "<
th
>City</
th
>" .
"<
th
>Rating</
th
></
tr
>";


foreach

($
AllCustomers

as $customer)


{


$
cid
=$customer["
cid
"]; //$customer is an array; field name is case sensitive


$
Cname
=$customer[1];


$City=$customer[2];


$Rating=$customer[3];


echo "<
tr
><td>$
cid
</td>" . "<td>$
Cname
</td>" . "<td>$City</td>" . "<td>$Rating</td></
tr
>";

}

echo "</table>";

}


catch (Exception $e) {


$
error_message

= $e
-
>
getMessage
();


echo "<p>Error message: $
error_message

</p>";


}

?>

Retrieve Record Based on Key:

This form is used to enter CID


<body>


<form name="
getData
" method="post" action="
getCustomerData.php
">


Enter CID: <input type="text" name="CID" value="" /><
br
><
br
>


<input type="submit" value="Get Customer Data" name="
btnSubmit
"
/>


</form>


</body>

Note: the use of
foreach

loop even though the statement returns at
most one record
<?
php


try {

$
db

= new PDO('
mysql:host
=
localhost;dbname
=
salesdb
', 'root', '');

$
cid
=$_POST['CID'];

$query = "SELECT * FROM customers WHERE CID= '$
cid
'";

$customers = $
db
-
>query($query);

if ($customers
-
>
rowCount
()==0)echo "Record does not exist!";

else {

foreach

($customers as $customer){

$
cid
=$customer['
cid
'];

$
Cname
=$customer['
cname
'];

$City=$customer['city'];

$Rating=$customer['rating'];

echo "<p>CID: $
cid

</P>";


echo "<p>
Cname
: $
Cname

</P>";


echo "<p>City: $City </P>";


echo "<p>Rating: $Rating </P>" ;

} } }

catch (Exception $e) {


$
error_message

= $e
-
>
getMessage
();


echo "<p>Error message: $
error_message

</p>";


}
?>

Using fetch()

<?
php


try {

$
db

= new PDO('
mysql:host
=
localhost;dbname
=
salesdb
', 'root', '');

$
cid
=$_POST['CID'];

$query = "SELECT * FROM customers WHERE CID= '$
cid
'";

$customers = $
db
-
>query($query);

if ($customers
-
>
rowCount
()==0)echo "Record does not exist!";

else {

$customer=$customers
-
>fetch();

$
cid
=$customer['
cid
'];

$
Cname
=$customer['
cname
'];

$City=$customer['city'];

$Rating=$customer['rating'];

echo "<p>CID: $
cid

</P>";


echo "<p>
Cname
: $
Cname

</P>";


echo "<p>City: $City </P>";


echo "<p>Rating: $Rating </P>" ;

} }

catch (Exception $e) {


$
error_message

= $e
-
>
getMessage
();


echo "<p>Error message: $
error_message

</p>";


}


?>

Use One PHP Page for input and output

<?
php


if (!empty($_POST))



{
$
db

= new PDO('
mysql:host
=
localhost;dbname
=
salesdb
', 'root', '');

$
cid
=$_POST['CID'];

$query = "SELECT * FROM customers WHERE CID= '$
cid
'";

$customers = $
db
-
>query($query);

if ($customers
-
>
rowCount
()==0)echo "Record does not exist!";

else {

foreach

($customers as $customer){

$
cid
=$customer['
cid
'];

$
Cname
=$customer['
cname
'];

$City=$customer['city'];

$Rating=$customer['rating'];

echo "<p>CID: $
cid

</P>";


echo "<p>
Cname
: $
Cname

</P>";


echo "<p>City: $City </P>";


echo "<p>Rating: $Rating </P>" ;

} } }

?>

<form name="
getData
" method="post" action="
getCustOnePage.php
">

Enter CID: <input type="text" name="CID" value="" /><
br
><
br
>

<input type="submit" value="Get Customer Data" name="
btnSubmit
" />

</form>

Use PDO to create HTML controls


Exampls
:


Listbox


Link

Use PDO to create HTML
listbox


<?
php


$
db

= new PDO('
mysql:host
=
localhost;dbname
=
salesdb
', 'root', '');


$query="select
cid

from customers";


$
customerIDs

= $
db
-
>query($query);


echo "<form name='
updateRating
' method='post' action='
getCustomerData.php
'>";


echo "Select CID: <
br
>";


echo "<select name = 'CID'>";


foreach

($
customerIDs

as $
customerID
){


$
cid
=$
customerID
["
cid
"];


echo "<option value= $
cid

>$
cid
</option>";


}


echo "</select><
br
><
br
>";


echo "<input type='submit' value='Show Customer Data' name='
btnSubmit
'/>
</form>";


?>

Use PDO to create links

<?
php


$
db

= new PDO('
mysql:host
=
localhost;dbname
=
salesdb
', 'root', '');


$query="select
cid

from customers";


$
customerIDs

= $
db
-
>query($query);


foreach

($
customerIDs

as $
customerID
)


{


$
cid
=$
customerID
["
cid
"];


echo "<a
href
='
getCustomerDataGET.php?CID
=$
cid
'>CID: $
cid
</a><
br
><
br
>";


}


?>

Program handling the link


<?
php


try

{

$
db

= new PDO('
mysql:host
=
localhost;dbname
=
salesdb
', 'root', '');

$
cid
=$_GET['CID'];

$query = "SELECT * FROM customers WHERE CID= '$
cid
'";

$customers = $
db
-
>query($query);

if ($customers
-
>
rowCount
()==0)echo "Record does not exist!";

else {

$customer=$customers
-
>fetch();

$
cid
=$customer['
cid
'];

$
Cname
=$customer['
cname
'];

$City=$customer['city'];

$Rating=$customer['rating'];

echo "<p>CID: $
cid

</P>";


echo "<p>
Cname
: $
Cname

</P>";


echo "<p>City: $City </P>";


echo "<p>Rating: $Rating </P>" ;

}

}

catch (Exception $e) {


$
error_message

= $e
-
>
getMessage
();


echo "<p>Error message: $
error_message

</p>";


}


?>

PDOStatement’s

setFetchModemethod



Before calling fetch, it’s best to tell PDO how you’d like the data
to be fetched. You have the following options:


PDO::FETCH_ASSOC: returns an array indexed by column name


PDO::FETCH_BOTH (
default
): returns an array indexed by both
column name and number


PDO::FETCH_CLASS: Assigns the values of your columns to
properties of the named class. It will create the properties if
matching properties do not exist


PDO::FETCH_INTO: Updates an existing instance of the named
class


PDO::FETCH_NUM: returns an array indexed by column number


PDO::FETCH_OBJ: returns an anonymous object with property
names that correspond to the column names


Specifying a fetch mode, for example, PDO::FETCH_ASSOC

<?
php

t
ry {

$
db

= new PDO('
mysql:host
=
localhost;dbname
=
salesdb
', 'root', '');


$query="select * from customers";


$customers = $
db
-
>query($query);


$customers
-
>
setFetchMode
(PDO::FETCH_ASSOC);


echo "<table border=1><
tr
>" . "<
th
>CID</
th
>" . "<
th
>
CName
</
th
>" . "<
th
>City</
th
>" .
"<
th
>Rating</
th
></
tr
>";


while ($customer = $customers
-
>fetch()){


$
cid
=$customer["
cid
"]; //field name is case sensitive


$
Cname
=$customer["
cname
"];


$City=$customer["city"];


$Rating=$customer["rating"];


echo "<
tr
><td>$
cid
</td>" . "<td>$
Cname
</td>" . "<td>$City</td>" .
"<td>$Rating</td></
tr
>";

}

echo "</table>";

}


catch (Exception $e) {


$
error_message

= $e
-
>
getMessage
();


echo "<p>Error message: $
error_message

</p>";


}

?>

Use PDO
::exec
to run SQL
Insert/Delete/Update Statements


Execute an SQL Insert/Delete/Update
Statement
and
return the number of affected
rows
.

New Customer Data Entry Form

<body>


<form name="
newCustomerForm
" action="
AddNewCustomer.php
" method="POST">


CID: <input type="text" name="CID" value="" /><
br
><
br
>


Cname
: <input type="text" name="
Cname
" value="" /><
br
><
br
>


City: <input type="text" name="City" value="" /><
br
><
br
>


Rating: <input type="text" name="Rating" value="" /><
br
><
br
>


<input type="submit" value="Add New Customer" name="
btnSubmit
" />


</body>

Code to Add a New Customer

<?
php

try

{

$
db

= new PDO('
mysql:host
=
localhost;dbname
=
salesdb
', 'root', '');

$
cid
=$_POST["CID"];

$
cname
=$_POST["
Cname
"];

$city=$_POST["City"];

$rating=$_POST["Rating"];

$
queryINS

= "insert into customers value('$
cid
','$
cname
','$
city','$rating
')";

//$
queryINS

= "insert into customers value('" . $
cid

. "','" . $
cname

. "','" . $city

// . "','" . $rating .
"')";

if
( $
db
-
>exec($
queryINS
)==1)


echo "Adding
successful
";

else


echo "Adding not
successful
";

}


catch (Exception $e) {


$
error_message

= $e
-
>
getMessage
();


echo "<p>Error message: $
error_message

</p>";


}

?>

Using One PHP Program to Add a New Customer

<?
php


if (!empty($_POST)){


try {

$
db

= new PDO('
mysql:host
=
localhost;dbname
=
salesdb
', 'root', '');

$
cid
=$_POST["CID"];

$
cname
=$_POST["
Cname
"];

$city=$_POST["City"];

$rating=$_POST["Rating"];

$
queryINS

= "insert into customers value('$
cid
','$
cname
','$
city','$rating
')";

if
( $
db
-
>exec($
queryINS
)==1)


echo "Adding
succesful
";

else


echo "Adding not
succesful
";

}


catch (Exception $e) {


$
error_message

= $e
-
>
getMessage
();


echo "<p>Error message: $
error_message

</p>";


} }

?>


<form name="
newCustomerForm
" action="
addNewCustomer.php
" method="POST">


CID: <input type="text" name="CID" value="" /><
br
><
br
>


Cname
: <input type="text" name="
Cname
" value="" /><
br
><
br
>


City: <input type="text" name="City" value="" /><
br
><
br
>


Rating: <input type="text" name="Rating" value="" /><
br
><
br
>


<input type="submit" value="Add New Customer" name="
btnSubmit
" />

Form to enter CID for deletion

<body>


<form name="
deleteForm
" action="
deleteCustomer.php
"
method="POST">


Enter CID to delete: <input type="text" name="CID" value="" /><
br
>


<input type="submit" value="Delete Customer" name="
btnSubmit
" />


</form>


</body>

Code to delete a customer

<?
php


try

{

$
db

= new PDO('
mysql:host
=
localhost;dbname
=
salesdb
', 'root', '');

$
cid
=$_POST["CID"];

$
queryDEL

= "delete from customers where
cid
='$
cid

‘”;

if( $
db
-
>exec($
queryDEL
)==1)


echo "Deleting
succesful
";

else


echo "Deleting not
succesful
";

}


catch (Exception $e) {


$
error_message

= $e
-
>
getMessage
();


echo "<p>Error message: $
error_message

</p>";


}

?>

Example: Updating a customer’s rating field


A
php

program to create a form with CID
listbox

and three
radiobuttons

to select new
rating.


A second
php

program is called to update the
rating.

PHP program to create the form


<?
php


$
db

= new PDO('
mysql:host
=
localhost;dbname
=
salesdb
', 'root', '');


$query="select * from customers";


$customers = $
db
-
>query($query);


echo "<form name='
updateRating
' method='post' action='
execUpdateRating.php
'>";


echo "Select CID: <
br
>";


echo "<select name = '
cid
'>";


foreach

($customers as $customer){


$
cid
=$customer["
cid
"];


$
cname
=$customer["
cname
"];


$rating=$customer["rating"];


$
cidnamerating
=$
cid

. ": " . $
cname

.": " . $rating;


echo "<option value= $
cid

>$
cidnamerating
</option>";


}


echo "</select><
br
><
br
>";


echo "Select a new rating:<
br
> <input type = 'radio' name = '
newRating
' value = 'A' checked =
'checked' /> A<
br
>";


echo "<input type='radio' name='
newRating
' value='B' /> B<
br
>";


echo "<input type='radio' name='
newRating
' value='C' /> C<
br
><
br
>";


echo "<input type='submit' value='Update Customer Rating' name='
btnSubmit
'/> </form>";


?>

PHP program to update the rating

<?
php


$
db

= new PDO('
mysql:host
=
localhost;dbname
=
salesdb
', 'root', '');


$
cid
=$_POST["
cid
"];


$
newRating
=$_POST["
newRating
"];


$
queryUpd
="update customers set rating = '" . $
newRating

. "' where
cid

= '" . $
cid

. "'";


if( $
db
-
>exec($
queryUpd
)==1)


echo "Updating
succesful
";


else


echo "Updating not
succesful
";


?>

Using Prepared (Parameterized)
Statement


A prepared statement or a parameterized
statement is
a precompiled SQL statement
that can be executed multiple times by
sending just the data to the server.


It is
used to execute the same statement
repeatedly with high efficiency
.


You use a prepared statement by including
placeholders in your SQL.


Benefits of Using Prepared statements


The query only needs to be parsed (or
prepared) once, but can be executed
multiple times with the same or different
parameters.


The parameters to prepared statements
don't need to be quoted (don’t need the
quotation around the input variable); the
driver automatically handles this. Hence no
SQL injection will occur. Example:

$
stmt

= $
db
-
>prepare("SELECT * FROM Customers where CID = ?");

SQL Injection Demo


On a web page that takes customer ID entered in
a textbox as input, then displays the customer’s
data.


Example: Retrieve all records:


In the textbox, enter:




OR 1=1 OR CID = ‘





<form name="
getData
" method="post" action="
demoSQLInjection.php
">


Enter CID: <input type="text" name="CID" value="" /><
br
><
br
>


<input type="submit" value="Get Customer Data"
name="
btnSubmit
" />


</form>

<?
php

try

{

$
db

= new PDO('
mysql:host
=
localhost;dbname
=
salesdb
', 'root', '');

$
cid
=$_POST['CID'];

$query = "SELECT * FROM customers WHERE CID= '$
cid
'";

echo "<table border=1><
tr
>" . "<
th
>CID</
th
>" . "<
th
>
CName
</
th
>" . "<
th
>City</
th
>" .
"<
th
>Rating</
th
></
tr
>";

$customers = $
db
-
>query($query);

foreach

($customers as $customer)


{


$
cid
=$customer['
cid
'];


$
Cname
=$customer['
cname
'];


$City=$customer['city'];


$Rating=$customer['rating'];


echo "<
tr
><td>$
cid
</td>" . "<td>$
Cname
</td>" . "<td>$City</td>" .
"<td>$Rating</td></
tr
>";


}


echo "</table>";

}

catch (Exception $e) {


$
error_message

= $e
-
>
getMessage
();


echo "<p>Error message: $
error_message

</p>";


}

?>

Using the PDO::prepare to create a prepared
statement and returns a
PDOstatement

object


PDO::prepare


Example:



$
stmt
=$
db
-
>prepare($
queryINS
);


PDOStatement

class Methods Related to
Prepared Statement


PDOStatement
::
bindParam



Binds a parameter to the specified
variable name


PDOStatement
::
bindValue



Binds a value to a parameter


PDOStatement
::execute


Executes a prepared statement.




Returns
TRUE on success or FALSE on failure.


Unnamed Placeholders as Parameters,
?

$
queryINS

= $
db
-
>("INSERT INTO customers values (?, ?, ?, ?)”);

Example:

Using Unnamed Placeholders:

bindParam

-

Binds a parameter to the specified variable name

$stmt
-
>bindParam(1, $cid);

$stmt
-
>bindParam(2, $cname);

$stmt
-
>bindParam(3, $city);

$stmt
-
>bindParam(4, $rating);

Execute
(): Returns TRUE on success or FALSE on failure.

if($
stmt
-
>execute())


echo "Adding
succesful
";

else


echo "Adding not
succesful
";

}

Example of using prepared statement

<?
php

try

{

$
db

= new PDO('
mysql:host
=
localhost;dbname
=
salesdb
', 'root', '');

$
queryINS

= "insert into customers value
(?,?,?,?)";

$
cid
=$_POST["CID"];

$
cname
=$_POST["
Cname
"];

$city=$_POST["City"];

$rating=$_POST["Rating"];

$
stmt
=$
db
-
>prepare($
queryINS
);

$
stmt
-
>
bindParam
(1, $
cid
);

$
stmt
-
>
bindParam
(2, $
cname
);

$
stmt
-
>
bindParam
(3, $city);

$
stmt
-
>
bindParam
(4, $rating);


if($
stmt
-
>execute())


echo "Adding
succesful
";

else


echo "Adding not
succesful
";

}


catch (Exception $e) {


$
error_message

= $e
-
>
getMessage
();


echo "<p>Error message: $
error_message

</p>";


}

?>

Example of using prepared statement

<?
php

Try {

$
db

= new PDO('
mysql:host
=
localhost;dbname
=
salesdb
', 'root', '');

$query = "SELECT * FROM customers WHERE CID= ?";

$
stmt

= $
db
-
>prepare($query);

$
cid
=$_POST["CID"];

$
stmt
-
>
bindParam
(1, $
cid
);

echo "<table border=1><
tr
>" . "<
th
>CID</
th
>" . "<
th
>
CName
</
th
>" . "<
th
>City</
th
>" .
"<
th
>Rating</
th
></
tr
>";

if ($
stmt
-
>execute()) {


while ($row = $
stmt
-
>fetch()) {


$
cid
=$row["
cid
"]; //field name is case sensitive


$
Cname
=$row["
cname
"];


$City=$row["city"];


$Rating=$row["rating"];


echo "<
tr
><td>$
cid
</td>" . "<td>$
Cname
</td>" . "<td>$City</td>" .
"<td>$Rating</td></
tr
>";


} }

echo "</table>";

}


catch (Exception $e) {


$
error_message

= $e
-
>
getMessage
();


echo "<p>Error message: $
error_message

</p>";


}


?>

If data is stored in an array

$data = array($
cid
, $
cname
, $city, $rating);

$
stmt
=$
db
-
>prepare($
queryINS
);

if
($
stmt
-
>execute($data))


echo "Adding
succesful
";

else


echo "Adding not
succesful
";

}

Code Example

<?
php

try

{

$
db

= new PDO('
mysql:host
=
localhost;dbname
=
salesdb
', 'root', '');

$
queryINS

= "insert into customers value(?,?,?,?)";

$
cid
=$_POST["CID"];

$
cname
=$_POST["
Cname
"];

$city=$_POST["City"];

$rating=$_POST["Rating"];

$data = array($
cid
, $
cname
, $city, $rating);

$
stmt
=$
db
-
>prepare($
queryINS
);

if
($
stmt
-
>execute($
data
))


echo "Adding
succesful
";

else


echo "Adding not
succesful
";

}


catch (Exception $e) {


$
error_message

= $e
-
>
getMessage
();


echo "<p>Error message: $
error_message

</p>";


}

?>

N
amed Placeholders

$
queryINS

= "INSERT INTO customers values (:
cid
,:
cname
,:
city,:rating
)";

Using
N
amed Placeholders

<?
php

Try {

$
db

= new PDO('
mysql:host
=
localhost;dbname
=
salesdb
', 'root', '');

$
queryINS

= "INSERT INTO customers values (:
cid
,:
cname
,:
city,:rating
)";

$
cid
=$_POST["CID"];

$
cname
=$_POST["
Cname
"];

$city=$_POST["City"];

$rating=$_POST["Rating"];

$
stmt
=$
db
-
>prepare($
queryINS
);

$
stmt
-
>
bindParam
(':
cid
', $
cid
);

$
stmt
-
>
bindParam
(':
cname
', $
cname
);

$
stmt
-
>
bindParam
(':city', $city);

$
stmt
-
>
bindParam
(':rating', $rating);

if($
stmt
-
>execute())


echo "Adding
succesful
";

else


echo "Adding not
succesful
";

}


catch (Exception $e) {


$
error_message

= $e
-
>
getMessage
();


echo "<p>Error message: $
error_message

</p>";


}

?>