/    Sign up×
Community /Pin to ProfileBookmark

[RESOLVED] Advanced SQL query building.

Copy linkTweet thisAlerts:
Jul 06.2007

In the time I have been programming PHP I am yet to find a perfect catch all way of building a SQL query.

How do you guys do it?

If you don’t know what mysql_real_escape_string or typecasting is please do not reply.

Things I am looking for..
Speed – As few preg or str based function calls as possible.
Safe – All values need to be safely inserted in to a query.
Solid – No creepy bugs or fast hacks.

to post a comment
PHP

5 Comments(s) ↴

Copy linkTweet thisAlerts:
@MrCoderauthorJul 06.2007 — Here is an example of something I have been working on.

It is just a production prototype, any suggestions are more then welcome.


database.php
[code=php]
<?php
class database
{
private $link;
private $sql_values;
private $sql_query;

function __construct($hostname, $username, $password, $database)
{
$this->link = @mysql_connect($hostname, $username, $password);

if(!$this->link)
return NULL;

if(!@mysql_select_db($database, $this->link))
return NULL;
}

function __destruct()
{
if($this->link !== NULL)
@mysql_close($this->link);
}

function query($sql)
{
$this->sql_values = array();
$this->sql_query = $sql;
}

function bind_value($key, $value)
{
if(is_int($value))
{
$this->sql_values[$key] = (int)$value;
}
elseif(is_float($value))
{
$this->sql_values[$key] = (float)$value;
}
elseif(is_bool($value))
{
$this->sql_values[$key] = (bool)$value;
}
elseif(is_string($value))
{
$this->sql_values[$key] = mysql_real_escape_string($value);
}
else
{
die("Unhandled sql value : ".$key." = type of ".gettype($value));
}
}

function execute()
{
$sql_query_final = "";

foreach(preg_split("/:([^:]*):/", $this->sql_query, -1, PREG_SPLIT_DELIM_CAPTURE) as $part)
{
if(array_key_exists($part, $this->sql_values))
$sql_query_final .= $this->sql_values[$part];
else
$sql_query_final .= $part;
}

return mysql_query($sql_query_final, $this->link);
}
}
?>
[/code]

index.php
[code=php]
<?php
///////////////////////////
// CONFIG
///////////////////////////
require("config.php");

///////////////////////////
// CLASSES
///////////////////////////
require(SITE_DIR_CLASSES."database.php");

$database = new database(MYSQL_HOSTNAME, MYSQL_USERNAME, MYSQL_PASSWORD, MYSQL_DATABASE);

$database->query("SELECT * FROM test WHERE user_id = ':user_id:' AND username != ':username:'");
$database->bind_value("user_id", 1);
$database->bind_value("username", "bob's boy's");
$result = $database->execute();

// End of test
?>
[/code]
Copy linkTweet thisAlerts:
@pcthugJul 07.2007 — Try this class I made:

[code=php]
<?php

/**
* Db_Exception
*/
class Db_Exception extends Exception
{}

/**
* Db
*/
class Db extends Mysqli
{
/**
* MySQL Hostname
*/
const HOSTNAME = NULL;

/**
* MySQL Username
*/
const USERNAME = 'root';

/**
* MySQL Password
*/
const PASSWORD = NULL;

/**
* MySQL Database
*/
const DATABASE = 'my_db';

/**
* Singleton object instance
*
* @var object
*/
private static $instance;

/**
* Constructor
*
* Private constructor suppresses generation of a (public)
* default constructor. Thus direct instantiation of object is
* forfeited and instantiation shall be done via the getInstance
* method. Makes call to parent constructor (Mysqli) and
* initiates Mysql connection
*
* @return void
* @throws Db_Exception
*/
private function __construct()
{
parent::__construct(self::HOSTNAME, self::USERNAME, self::PASSWORD, self::DATABASE);

if (mysqli_connect_errno())
{
throw new Db_Exception('Could not connect to Database: #' . mysqli_connect_errno() . ' : ' . mysqli_connect_error());
}
}

/**
* Gets object instance. If no instance exists, a new
* object instance is created and then returned
*
* @return object
*/
public static function getInstance()
{
if (!Db::$instance)
{
Db::$instance = new Db;
}

return Db::$instance;
}

/**
* Destructor
*
* Close database connection
*
* @return void
*/
public function __destruct()
{
parent::close();
}
}[/code]

And then make use of Mysqli Prepared statements and the Singleton object design:

[code=php]
<?php

/**
* Db
*/
require_once 'db.php';

$db = Db::getInstance();

$user_id = 1;
$username = "bob's boy's";

$stmt = $db->prepare("SELECT * FROM test WHERE user_id = ? AND username != ?");
$stmt->bind_param('is', $user_id, $username);
$stmt->execute();
$stmt->close();
[/code]

Some further reading:

http://au.php.net/manual/en/function.mysqli-prepare.php

http://en.wikipedia.org/wiki/Singleton_pattern
Copy linkTweet thisAlerts:
@temp_user123Jul 07.2007 — Type on the last statement there, thug.
Copy linkTweet thisAlerts:
@MrCoderauthorJul 07.2007 — Try this class I made:

[code=php]
<?php

/**
* Db_Exception
*/
class Db_Exception extends Exception
{}

/**
* Db
*/
class Db extends Mysqli
{
/**
* MySQL Hostname
*/
const HOSTNAME = NULL;

/**
* MySQL Username
*/
const USERNAME = 'root';

/**
* MySQL Password
*/
const PASSWORD = NULL;

/**
* MySQL Database
*/
const DATABASE = 'my_db';

/**
* Singleton object instance
*
* @var object
*/
private static $instance;

/**
* Constructor
*
* Private constructor suppresses generation of a (public)
* default constructor. Thus direct instantiation of object is
* forfeited and instantiation shall be done via the getInstance
* method. Makes call to parent constructor (Mysqli) and
* initiates Mysql connection
*
* @return void
* @throws Db_Exception
*/
private function __construct()
{
parent::__construct(self::HOSTNAME, self::USERNAME, self::PASSWORD, self::DATABASE);

if (mysqli_connect_errno())
{
throw new Db_Exception('Could not connect to Database: #' . mysqli_connect_errno() . ' : ' . mysqli_connect_error());
}
}

/**
* Gets object instance. If no instance exists, a new
* object instance is created and then returned
*
* @return object
*/
public static function getInstance()
{
if (!Db::$instance)
{
Db::$instance = new Db;
}

return Db::$instance;
}

/**
* Destructor
*
* Close database connection
*
* @return void
*/
public function __destruct()
{
parent::close();
}
}[/code]

And then make use of Mysqli Prepared statements and the Singleton object design:

[code=php]
<?php

/**
* Db
*/
require_once 'db.php';

$db = Db::getInstance();

$user_id = 1;
$username = "bob's boy's";

$stmt = $db->prepare("SELECT * FROM test WHERE user_id = ? AND username != ?");
$stmt->bind_param('is', $user_id, $username);
$stmt->execute();
$stmt->close();
[/code]

Some further reading:

http://au.php.net/manual/en/function.mysqli-prepare.php

http://en.wikipedia.org/wiki/Singleton_pattern[/QUOTE]


I have never used Mysqli. Is bind_param safe when inserting strings or do you still have to pass the string through something like mysql_real_escape_string. How much more overhead is required by mysqli vs basic mysql functions?
Copy linkTweet thisAlerts:
@pcthugJul 08.2007 — As bind_param knows exactly what types of data it's expecting it does not require sanitized data. Generally speaking, prepared statements can result in noticeable performance increases, especially when executing the same query with different parameters in say a loop. The difference in between the Mysqli and Mysql overhead footprint is insignificant. However, Mysqli does not natively support persistent database connections
×

Success!

Help @MrCoder spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.19,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...