note 100626 added to ref.mysql
| From: | VladimirKosmala at osu1 dot php dot net | Date: | Wed, 27 Oct 2010 14:57:41 +0000 |
| Subject: | note 100626 added to ref.mysql | ||
| Groups: | php.notes | ||
| Request: | Send a blank email to php-notes+get-173505@lists.php.net to get a copy of this message | ||
Here a mysql helper containing the main functions of the mysql extension. It's easy to
understand for a beginner and quite useful because queries are secure. It understands what you want,
just write your sql query. I called it mysql_magic.
<?php
// Examples
$nb_affected = mysql_magic('delete from users');
$nb = mysql_magic('select count(*) from users');
$one_row = mysql_magic('select * from users limit 1');
$all_rows = mysql_magic('select * from users where name = ?', 'John');
$id = mysql_magic('insert into users(name,rank) values(?,?)', 'Vincent', 3);
?>
<?php
// Usage: mysql_magic($query [, $arg...]);
function mysql_magic()
{
global $dblink, $sqlhost, $sqluser, $sqlpass, $sqlbase;
$narg = func_num_args();
$args = func_get_args();
if (!$dblink)
{
$dblink = mysql_connect( $sqlhost, $sqluser, $sqlpass );
mysql_select_db( $sqlbase, $dblink );
}
$req_sql = array_shift($args);
$req_args = $args;
$req_query = mysql_bind($req_sql, $req_args);
$req_result = mysql_query($req_query);
if (!$req_result)
{
trigger_error(mysql_error());
return false;
}
if (startsWith($req_sql, 'delete') || startsWith($req_sql, 'update'))
{
return mysql_affected_rows(); // -1 || N
}
else if (startsWith($req_sql, 'insert'))
{
return mysql_insert_id(); // ID || 0 || FALSE
}
else if (endsWith($req_sql, 'limit 1'))
{
return mysql_fetch_assoc($req_result); // [] || FALSE
}
else if (startsWith($req_sql, 'select count(*)'))
{
$line = mysql_fetch_row($req_result);
return $line[0]; // N
}
else
{
return mysql_fetch_all($req_result); // [][]
}
}
function mysql_bind($sql, $values=array())
{
foreach ($values as &$value) $value = mysql_real_escape_string($value);
$sql = vsprintf( str_replace('?', "'%s'", $sql), $values);
return $sql;
}
function mysql_fetch_all($result)
{
$resultArray = array();
while(($resultArray[] = mysql_fetch_assoc($result)) || array_pop($resultArray));
return $resultArray;
}
function startsWith($haystack,$needle,$case=false) {
if($case){return (strcmp(substr($haystack, 0, strlen($needle)),$needle)===0);}
return (strcasecmp(substr($haystack, 0, strlen($needle)),$needle)===0);
}
function endsWith($haystack,$needle,$case=false) {
if($case){return (strcmp(substr($haystack, strlen($haystack) - strlen($needle)),$needle)===0);}
return (strcasecmp(substr($haystack, strlen($haystack) - strlen($needle)),$needle)===0);
}
?>
Don't forget to set $sqlhost, $sqluser, $sqlpass and $sqlbase.
With help of :
mysql_bind : http://php.net/manual/en/function.mysql-real-escape-string.php#96391
mysql_fetch_all : http://php.net/manual/en/function.mysql-fetch-assoc.php#90030
trigger_error may be enhanced with this tip : http://php.net/manual/en/function.trigger-error.php#98910
----
Server IP: 69.147.83.197
Probable Submitter: 193.57.67.241
----
Manual Page -- http://www.php.net/manual/en/ref.mysql.php
Edit -- https://master.php.net/note/edit/100626
Del: integrated -- https://master.php.net/note/delete/100626/integrated
Del: useless -- https://master.php.net/note/delete/100626/useless
Del: bad code -- https://master.php.net/note/delete/100626/bad+code
Del: spam -- https://master.php.net/note/delete/100626/spam
Del: non-english -- https://master.php.net/note/delete/100626/non-english
Del: in docs -- https://master.php.net/note/delete/100626/in+docs
Del: other reasons-- https://master.php.net/note/delete/100626
Reject -- https://master.php.net/note/reject/100626
Search -- https://master.php.net/manage/user-notes.php