Reputation: 211

php query not excuting correctly

how can i execute a query, with one of the paramters having no surrounding quotes like so -

select * from table where column1 = 'value1' and column2 = value2 //value2 has no ''

right now, in php im using something like

query("select * from table where column1 = ? and column2 = ?",array($value1,$value2)

that being an example. and excuting in sql like so

 select * from table where column1 = 'value1' and column2 = 'value2' //value2 has quotes, this is what i want to ignore / remove

how can that be done in php?

Upvotes: 0

Answers (4)

Wil Moore III

Reputation: 7214

Here is a usable boilerplate that you can use which also includes a scratch database setup for testing this scenario. The code inside the try construct is what performs the actual action of connecting and querying.

<?php

/**********************************************************
* CREATES THE SAMPLE DATABASE/TABLE/DATA
/*********************************************************/
/*********************************************************

CREATE DATABASE IF NOT EXISTS scratch
  CHARACTER SET = 'utf8' COLLATE = 'utf8_general_ci';

CREATE TABLE `scratch`.`table1` (
  `column1` varchar(40) NOT NULL DEFAULT '',
  `column2` varchar(40) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `scratch`.`table1` (`column1`, `column2`)
  VALUES ('test1', 'test2');

/*********************************************************/

// connection parameters
$hostname = '127.0.0.1';
$database = 'scratch';
$username = 'root';
$password = 'rootpass';

// connection parameters (mysql specific)
$connectionString  = "mysql:host=${hostname};dbname=${database}";
$connectionOptions = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8');

// query bound parameters
$column1  = 'test1';
$column2  = 'test2';

// sql statement(s)
$sql      = 'SELECT
               `column1`, `column2`
             FROM
               table1
             WHERE
                   `column1` = :column1
               and `column2` = :column2;
';

// tl;dr
try {
  $pdo = new PDO($connectionString, $username, $password, $connectionOptions);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

  $statement = $pdo->prepare($sql);
  $statement->execute(compact('column1', 'column2'));
  $results   = $statement->fetchAll();

  var_dump($results);

  $pdo = $statement = null;
} catch(PDOException $e) {
  echo $e->getMessage();
}

Upvotes: 0

enbits

Reputation: 66

You can do that using PDO's prepared statements:

<?php
    $sth = $db->prepare('SELECT * FROM users WHERE username = ? AND pass = ?');
    $sth->execute(array('john', '1234'));
    $result = $sth->fetchAll();
?>

Upvotes: 0