Asked  7 Months ago    Answers:  5   Viewed   26 times

My web application currently has do execute simple queries: simple CRUD operations, counting,...

A few months ago, someone recommended me here to write a simple PDO wrapper for this (to avoid writing try/catch, prepare(), execute(), etc. each time a query should be executed). This example method was shown (I've made some changes so I could use it in my own project):

public function execute() {
    $args  = func_get_args();
    $query = array_shift($args);
    $result = false;

    try {
      $res = $this->pdo->prepare($query);
      $result = $res->execute($args);
    } catch (PDOException $e) { echo $e->getMessage(); }

    return $result;
  }

As I need to perform more operations (executing queries, retrieving 1 record, retrieving multiple records, counting results) I created a method for all of these:

  public function getMultipleRecords() {
    $args  = func_get_args();
    $query = array_shift($args);
    $records = array();

    try {
      $res = $this->pdo->prepare($query);
      $res->execute($args);
      $records = $res->fetchAll();
    } catch (PDOException $e) { echo $e->getMessage(); }

    return $records;
  }

  public function getSingleRecord() {
    $args  = func_get_args();
    $query = array_shift($args);
    $record = array();

    try {
      $res = $this->pdo->prepare($query);
      $res->execute($args);
      $record = $res->fetch();
    } catch (PDOException $e) { echo $e->getMessage(); }

    return $record;
  }

  public function execute() {
    $args  = func_get_args();
    $query = array_shift($args);
    $result = false;

    try {
      $res = $this->pdo->prepare($query);
      $result = $res->execute($args);
    } catch (PDOException $e) { echo $e->getMessage(); }

    return $result;
  }

  public function count() {
    $args  = func_get_args();
    $query = array_shift($args);
    $result = -1;

    try {
      $res = $this->pdo->prepare($query);
      $res->execute($args);
      $result = $res->fetchColumn();
    } catch(PDOException $e) { echo $e->getMessage(); }

    return $result;
  }

As you see, most of the code is the same. Only 2 lines of code are different for each method: the initialisation of $result (I always want to return a value, even if the query fails) and the fetching. Instead of using 4 methods, I could write just one of them and pass an extra parameter with the type of action. That way, I could use a bunch of if/else statements of a switch statement. However, I think the code can get messy. Is this a good way for solving this problem? If not, what would be a good solution to it?

The second problem I have (which is why I'm working on this class right now) is that I want to use prepared statements with the LIMIT SQL statement. However, it is not possible to do this:

$res = $pdo->prepare("SELECT * FROM table LIMIT ?");
$res->execute(array($int));

The variabele will be quoted for some reason (and so the query will fail), as explained here: https://bugs.php.net/bug.php?id=40740

The solution seems to use bindValue() and use the int datatype as a parameter: http://www.php.net/manual/de/pdostatement.bindvalue.php

I could rewrite the method(s) to support this, but I would also need to use an extra parameter. I can't just use $db->execute($sql, $variable1, $variable2); anymore as I need to know the data type.

What's the best way to solve this?

Thanks

 Answers

91

How about creating a class with methods that you can chain (for clarity, I've removed error checking):

class DB {

    private $dbh;
    private $stmt;

    public function __construct($user, $pass, $dbname) {
        $this->dbh = new PDO(
            "mysql:host=localhost;dbname=$dbname",
            $user,
            $pass,
            array( PDO::ATTR_PERSISTENT => true )
        );
    }

    public function query($query) {
        $this->stmt = $this->dbh->prepare($query);
        return $this;
    }

    public function bind($pos, $value, $type = null) {

        if( is_null($type) ) {
            switch( true ) {
                case is_int($value):
                    $type = PDO::PARAM_INT;
                    break;
                case is_bool($value):
                    $type = PDO::PARAM_BOOL;
                    break;
                case is_null($value):
                    $type = PDO::PARAM_NULL;
                    break;
                default:
                    $type = PDO::PARAM_STR;
            }
        }

        $this->stmt->bindValue($pos, $value, $type);
        return $this;
    }

    public function execute() {
        return $this->stmt->execute();
    }

    public function resultset() {
        $this->execute();
        return $this->stmt->fetchAll();
    }

    public function single() {
        $this->execute();
        return $this->stmt->fetch();
    }
}

You can then use it like this:

// Establish a connection.
$db = new DB('user', 'password', 'database');

// Create query, bind values and return a single row.
$row = $db->query('SELECT col1, col2, col3 FROM mytable WHERE id > ? LIMIT ?')
   ->bind(1, 2)
   ->bind(2, 1)
   ->single();

// Update the LIMIT and get a resultset.
$db->bind(2,2);
$rs = $db->resultset();

// Create a new query, bind values and return a resultset.
$rs = $db->query('SELECT col1, col2, col3 FROM mytable WHERE col2 = ?')
   ->bind(1, 'abc')
   ->resultset();

// Update WHERE clause and return a resultset.
$db->bind(1, 'def');
$rs = $db->resultset();

You could alter the bind method to accept an array or associative array if you prefer, but I find this syntax quite clear - it avoids having to build an array. The parameter type checking is optional, as PDO::PARAM_STR works for most values, but be aware of potential issues when passing null values (see comment in PDOStatement->bindValue documentation).

Wednesday, March 31, 2021
 
Keat
answered 7 Months ago
19

Very short answer: Yes it will.

class Foo
{
    private $id;
    public function echoID()
    {
        echo $this->id;
    }
}
$result = $statement->fetchAll(PDO::FETCH_CLASS, "Foo");
$result[0]->echoID(); // your ID

Aside:

This will cause syntax errors $statement->fetchAll(PDO::FETCH_INTO, $User);. You can't use FETCH_INTO with the fetchAll method.

Wednesday, March 31, 2021
 
ritch
answered 7 Months ago
84

Instead of:

$query = $db->query("SELECT articles . title FROM articles");

Try:

$query = $db->query("SELECT title FROM articles");

edit

Try:

while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
    echo $row['title'];
}
Wednesday, March 31, 2021
 
Puneet
answered 7 Months ago
80

Run this code before running a query

$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

Further reading Buffered and Unbuffered queries

I tried to increase the memory size in php.ini and with ini_set('memory_limit', '512M');, but it doesn´t work.

It worked, actually.

The error message says that this exact amount were exhausted.

Wednesday, March 31, 2021
 
Bono
answered 7 Months ago
39

Summary:

I had done something similar to this before by creating a custom input that inherits from SimpleForm::Inputs::CollectionRadioButtonsInput and overloading just a few methods. For more on custom input components, see https://github.com/plataformatec/simple_form/wiki/Adding-custom-input-components.

In any case, the code below produces your desired html markup almost exactly using simple_form v2.1.0 and rails v3.2.15.

Code:

# File: app/inputs/semantic_ui_radio_buttons_input.rb

class SemanticUiRadioButtonsInput < SimpleForm::Inputs::CollectionRadioButtonsInput

  # Creates a radio button set for use with Semantic UI

  def input
    label_method, value_method = detect_collection_methods
    iopts = { 
      :checked => 1,
      :item_wrapper_tag => 'div',
      :item_wrapper_class => 'field',
      :collection_wrapper_tag => 'div',
      :collection_wrapper_class => 'grouped inline fields'
     }
    return @builder.send(
      "collection_radio_buttons",
      attribute_name,
      collection,
      value_method,
      label_method,
      iopts,
      input_html_options,
      &collection_block_for_nested_boolean_style
    )
  end # method

  protected

  def build_nested_boolean_style_item_tag(collection_builder)
    tag = String.new
    tag << '<div class="ui radio checkbox">'.html_safe
    tag << collection_builder.radio_button + collection_builder.label
    tag << '</div>'.html_safe
    return tag.html_safe
  end # method

end # class

Then, in your form, just do:

-# File: app/views/<resource>/_form.html.haml

-# Define the collection
- child_care_coll = %w( Infant Toddler Preschool Kindergarten ).map!.with_index(1).to_a

-# Render the radio inputs
= f.input :child_care_type,
  :collection    => child_care_coll,
  :label_method  => :first,
  :value_method  => :last,
  :as            => :semantic_ui_radio_buttons

Results:

<div class="input semantic_ui_radio_buttons optional childcare_child_care_type">

  <label class="semantic_ui_radio_buttons optional control-label">
    Child care type
  </label>

  <div class="grouped inline fields">

    <div class="field">
      <div class="ui radio checkbox">
        <input checked="checked" class="semantic_ui_radio_buttons optional" id="childcare_child_care_type_1" name="childcare[child_care_type]" type="radio" value="1">
        <label for="childcare_child_care_type_1">Infant</label>
      </div>
    </div>

    ...

    <div class="field">
      <div class="ui radio checkbox">
        <input class="semantic_ui_radio_buttons optional" id="childcare_child_care_type_4" name="childcare[child_care_type]" type="radio" value="4">
        <label for="childcare_child_care_type_4">Kindergarten</label>
      </div>
    </div>

  </div>

</div>

enter image description here

Wednesday, September 29, 2021
 
pirtle
answered 4 Weeks ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :