PHP Classes

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

Recommend this page to a friend!
     
  Info   Example   View files Files   Install with Composer Install with Composer   Download Download   Reputation   Support forum   Blog    
Ratings Unique User Downloads Download Rankings
Not enough user ratingsTotal: 203 All time: 8,457 This week: 455Up
Version License PHP version Categories
db-access-wrapper 1.0The PHP License5.4PHP 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.

Picture of Dustin Ruckman
  Performance   Level  
Name: Dustin Ruckman <contact>
Classes: 1 package by
Country: United States United States
Age: 49
All time rank: 4137529 in United States United States
Week rank: 195 Up19 in United States United States Up

Level 2 StarStar

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 folder image Files (10)  
File Role Description
Plain text file DatabaseAccess.Class.php Class DatabaseAccess Class
Accessible without login Plain text file Example.Consolidated.php Example Example showing a few of the most commonly used functionality of the class
Accessible without login Plain text file Example.InsertAndGetID.Simple.php Example Use the simple nonQuery method to do an insert and get the Inserted ID
Accessible without login Plain text file Example.SelectMultipleRows.php Example Use the class to select multiple rows and cycle them.
Accessible without login Plain text file Example.SelectMultipleRows.Simple.php Example Same as SelectMulitpleRows example but uses the single method call.
Accessible without login Plain text file Example.SelectSingleRow.php Example Example showing how to select a single row of data.
Accessible without login Plain text file Example.UpdateByReusingQuery.php Example An example using variable data with a static query to update multiple records
Accessible without login Plain text file Example.UpdateSingleRow.php Example Update a single row by ID using parameters.
Accessible without login Plain text file Example.UpdateTableWithArray.php Example Update a table using an array of values by a unique id
Accessible without login Plain text file Example.UseReturnO...mToCreateRecord.php Example Create a new user record from a form.

The PHP Classes site has supported package installation using the Composer tool since 2013, as you may verify by reading this instructions page.
Install with Composer Install with Composer
Downloaddb-access-wrapper-2015-02-27.zip 6KB
Downloaddb-access-wrapper-2015-02-27.tar.gz 3KB
Install with ComposerInstall with Composer
 Version Control Unique User Downloads Download Rankings  
 0%
Total:203
This week:0
All time:8,457
This week:455Up

  Applications that use this package  
No pages of applications that use this class were specified.

Add link image If you know an application of this package, send a message to the author to add a link here.