Asked  10 Months ago    Answers:  5   Viewed   5 times

I've extended the PDO class to create a simple DB class and currently use prepare + execute for all queries run to the database, even the ones that do not have parameters (e.g. SELECT * FROM table).

The question is: is there a performance benefit to actually use PDO::query for simple queries that do not have parameters, instead of prepare/execute?

 Answers

3

Yes, because when you call PDO::prepare, the server must create a query plan and meta information for that query, then there is additional overhead to bind the specified parameters when you use PDO::execute. So, to save this overhead and improve performance, you can use PDO::query for queries without parameters.

However, depending on the scale and size of your application, and your server / host configuration (shared / private), you may or may not see any performance increase at all.

Sunday, August 22, 2021
 
nomie
 
4

Not near a terminal to check, but I believe you have to type bind it to INT and send it in as an INT, not as "b010101" (or whatever):

$sql='INSERT INTO test(id,data) VALUES(:id,:bit)';
$stmt=db::db()->prepare($sql);
$stmt->bindValue('id', null, PDO::PARAM_NULL);
$stmt->bindValue('bit', (int)$value, PDO::PARAM_INT);
$stmt->execute();

Quick check on Google brought up this similar previous answer.

Thursday, April 1, 2021
 
nasty
 
5

Some good old dynamic SQL query cobbling-together...

$sql = sprintf('SELECT * FROM user WHERE name LIKE :name %s %s',
               !empty($_GET['city'])   ? 'AND city   = :city'   : null,
               !empty($_GET['gender']) ? 'AND gender = :gender' : null);

...

if (!empty($_GET['city'])) {
    $stmt->bindParam(':city', '%'.$_GET['city'].'%', PDO::PARAM_STR);
}

...

You can probably express this nicer and wrap it in helper functions etc. etc, but this is the basic idea.

Saturday, May 29, 2021
 
JakeGR
 
3

I got it working using the following code

  1. HTML and CSS:
<div class="input-group col-md-8 col-sm-8 col-xs-8">
      <input type="text" class="form-control date">
      <span class="input-group-addon calendar-date-picker">
           <i class="glyphicon glyphicon-calendar"></i>
      </span>
  </div>

(...)

this is cool :<p> <?php print_r($_POST);?> </p>
<div class="results-ajax"></div>

. It uses 'date' in the class of the input for opening the datepicker calendar.

. It has a specific div with one class 'results-ajax' where the input generated by the script is displayed at.

  1. JS and Ajax:
jQuery(function($) {

  $(".date").datepicker({
    onSelect: function(dateText) {
      display("Selected date: " + dateText + "; input's current value: " + this.value);
      $(this).change();
    }
  }).on("change", function() {
    display("Got change event from field");
    $.ajax({
      type: "POST",
      url: 'events_script.php',
      data: ({dates: this.value}),
      success: function(data) {
        $('.results-ajax').html(data);
        alert(data);
      }
  });
});
  function display(msg) {
    $("<p>").html(msg).appendTo(document.body);
  }

});

. Once new date gets picked up, the results are shown bellow under Outcomes. .

. The Ajax call is posting in events_script.php.

  1. PHP (events_script.php)
<?php
include 'config/config.php';
include 'libraries/database.php';

    if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    print_r($_POST);
    echo $_POST[dates];
    $dias= $_POST[dates];
    $mysql_date = date('Y-m-d', strtotime($dias));

//Make database query

$sql = "SELECT *
        FROM events
        left JOIN companies ON companies.companyID = events.Ref_ID AND companies.Ref_Type = events.Ref_Type
        WHERE events.Start_Date= '".$mysql_date."'
        ORDER BY events.Start_Date DESC";

$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {

// output data of each row

  while ($row = mysqli_fetch_assoc($result)) {
      echo '<p>It works!</p>';
  }

} else { echo 'No results found.'; } } ?>

Outcomes:

1. The result of the alert in Ajax (using the PHP script):

Saturday, May 29, 2021
 
Guesser
 
1

query to create database only once if it doesn't exists

            CREATE DATABASE IF NOT EXISTS DBName;

query to create table only once if it doesn't exists

            CREATE TABLE IF NOT EXISTS tablename; 
Tuesday, August 24, 2021
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 :