DB Access using PDO: Query MySQL tables with prepared queries using PDO

Recommend this page to a friend!

Download .zip

Info

Example

View files (10)

Download .zip

Reputation

Support forum

Blog

Links

Ratings				Unique User Downloads		Download Rankings
Not yet rated by the users				Total: 203		All time: 8,449 This week: 455

Version		License		PHP version		Categories
`db-access-wrapper` 1.0		The PHP License		5.4		PHP 5, Databases

Description

Author

Dustin Ruckman

This class can query MySQL tables with prepared queries using PDO.

It can connect to a given MySQL server using PDO.

The class can execute prepared queries binding given parameter variables.

It can also execute table update queries given the identifier of the record to update and an associative array with values for each field.

Dustin Ruckman

Performance

Level

Name:	Dustin Ruckman `<contact>`
Classes:	1 package by Dustin Ruckman
Country:	United States
Age:	48
All time rank:	4128	525 in United States
Week rank:	321	31 in United States

Level 2

Example


<?php





if (!isset($_POST['Action']))


    ShowFormAndDie();





// Require the class and create an instance


require('path/to/DatabaseAccess.Class.php');


$DbAccess = new DatabaseAccess("yourHost", "dbName", "dbUser", "dbUserPassword");











// Create a new user from the data supplied by a form


// Create a blank row of data to create an ID


$numberOfRowsUpdated = $DbAccess->SimpleNon("INSERT INTO `UserAccounts` (`FirstName`) VALUES ('')");


// Clean up anything in the $_POST that is not account data.


unset($_POST['Action']);


// Now add the new ID to the _POST array


$_POST['ID'] = $DbAccess->LastInsertID();


// Use the Post array to update the database


$numberOfRowsUpdated = $DbAccess->UpdateTableViaArrayByID("UserAccounts", "ID", $_POST);











// Select a user from the Database


$DbAccess->PrepareStatement("SELECT * FROM `UserAccounts` WHERE `ID` = :ID"); 


$DbAccess->BindParameter(":ID", $ID); 


$userData = $DbAccess->ExecuteQuery_Get(); 


// Note that data is always returned as an array of arrays 


var_dump($userData[0]); 











// Select multiple rows from the Database


$DbAccess->PrepareStatement("SELECT * FROM `UserAccounts`"); 


$userDatas = $DbAccess->ExecuteQuery_Get(); 


foreach ($userDatas as $userData) 


    var_dump($userData); 











// Loop the results ($userDatas) change the FirstName with ucfirst and update


// Create some placeholders for the data 


$ID = ""; 


$newFirstName= ""; 





// Here we prepare the query that we will reuse 


$DbAccess->PrepareStatement("UPDATE `UserAccounts` SET `FirstName` = :FirstName WHERE `ID` = :ID"); 


// Now bind the parameters that will later hold the data to use in the query 


$DbAccess->BindParameter(":ID", $ID); 


$DbAccess->BindParameter(":FirstName", $newFirstName); 





// We want to use the key to access the array values 


foreach (array_keys($userDatas) as $key) 


{ 


    // Update the placeholders 


    $ID = $userDatas[$key]['ID']; 


    $newFirstName= ucfirst($userDatas[$key]['FirstName']); 


    // Run the query, the parameters are automaticly updated with the placeholders 


    $numberOfRowsUpdated = $GLOBALS['DatabaseAccess']->ExecuteQuery_Non(); 


} 











// Used to show a form for creating an UserAccount


function ShowFormAndDie() {


    // Note that the name of each input is the same as the Table Column names


    ?>





<form method="post" action="./">


    <input type="hidden" name="Action" value="CreateUser" />


    <label for="FirstName">First Name</label>


        <input type="text" id="FirstName" name="FirstName" value="" />


    <label for="LastName" class="col-sm-2 control-label">Last Name</label>


        <input type="text" id="LastName" name="LastName" value="" />


    <label for="EMail">Email</label>


        <input type="email" id="EMail" name="EMail" value="" />


    <label for="ZipCode">ZipCode</label>


        <input type="text" id="ZipCode" name="ZipCode" value="" />


    <input type="submit">


</form>





    <?php


    die();


}

Files

File	Role	Description
`DatabaseAccess.Class.php`	Class	DatabaseAccess Class
`Example.Consolidated.php`	Example	Example showing a few of the most commonly used functionality of the class
`Example.InsertAndGetID.Simple.php`	Example	Use the simple nonQuery method to do an insert and get the Inserted ID
`Example.SelectMultipleRows.php`	Example	Use the class to select multiple rows and cycle them.
`Example.SelectMultipleRows.Simple.php`	Example	Same as SelectMulitpleRows example but uses the single method call.
`Example.SelectSingleRow.php`	Example	Example showing how to select a single row of data.
`Example.UpdateByReusingQuery.php`	Example	An example using variable data with a static query to update multiple records
`Example.UpdateSingleRow.php`	Example	Update a single row by ID using parameters.
`Example.UpdateTableWithArray.php`	Example	Update a table using an array of values by a unique id
`Example.UseReturnO...mToCreateRecord.php`	Example	Create a new user record from a form.