Wednesday 7 February 2007

PHP: MySQL interface


/*
Mysql functions. Usage:

$isloc=1; $db=array('loc'=>array('localhost', 'root', '', 'db1'), 'srv'=>array());
mconn();
msel("INSER2T INTO stats (^i) VALUES (^v)", array('dt'=>'2006-12-25', 'hh'=>'5'));
msel("UPDATE stats SET ^u WHERE ".mesc('id=%s', "log'in"), array('dt'=>'2006-12-25', 'hh'=>'5'));
msel("SELECT FROM stats WHERE login=%s", array("log'in"), 'hash');
mdis();

mesc('id=%s', "log'in");
mesc('id=%s OR id=%s', array("log'in", "p'wd"));
mesc('id IN (%a)', array("log'in", "p'wd"));
*/

function mconn($link='') {
global $isloc, $db;
$t=$link.($isloc ? 'loc' : 'srv');
$db['_l'][$link]=mysql_connect($db[$t][0], $db[$t][1], $db[$t][2]);
mysql_select_db($db[$t][3], $db['_l'][$link]);
}
function mdis($link='') {
global $db;
mysql_close($db['_l'][$link]);
}
function msel($qu, $fv=array(), $st='', $link='', $sq='') {
global $db;
if (strpos($qu, 'SELECT')!==false) {
$qu=mesc($qu, $fv);
if ($sq) echo $qu."<br>\n";
$res=mysql_query($qu, $db['_l'][$link]);
if ($res===false) {echo '<b>'.$qu.mysql_error()."</b><br>\n"; return;}
$mfa=($st=='scal' ? MYSQL_NUM : MYSQL_ASSOC);
$lines=array(); while ($row=mysql_fetch_array($res, $mfa)) $lines[]=$row;
if ($st=='scal') return ($lines ? $lines[0][0] : '');
else if ($st=='hash') return ($lines ? $lines[0] : array());
else return $lines;
return $lines;
}
if (strpos($qu, 'INSERT')!==false) {
$fds=array(); $vas=array();
foreach ($fv as $f=>$v) {$fds[]=$f; $vas[]="'".mysql_escape_string($v)."'";}
$qu=str_replace('^i', implode(', ', $fds), $qu);
$qu=str_replace('^v', implode(', ', $vas), $qu);
} else if (strpos($qu, 'UPDATE')!==false) {
$vas=array();
foreach ($fv as $f=>$v) {$vas[]=$f.'='."'".mysql_escape_string($v)."'";}
$qu=str_replace('^u', implode(', ', $vas), $qu);
}
if ($sq) echo $qu."<br>\n";
$res=mysql_query($qu, $db['_l'][$link]);
if ($res===false) {echo '<b>'.mysql_error()."</b><br>\n"; return;}
}
function mesc($qu, $fv) {
if (strpos($qu, '%s')!==false) {
if (!is_array($fv)) return str_replace('%s', "'".mysql_escape_string($fv)."'", $qu);
else {foreach ($fv as $v) $qu=preg_replace('/%s/', "'".mysql_escape_string($v)."'", $qu, 1);}
} else if (strpos($qu, '%a')!==false) {
foreach ($fv as $i=>$v) $fv[$i]="'".mysql_escape_string($v)."'";
$str=implode(', ', $fv); if (!$str) $str=0;
return str_replace('%a', $str, $qu);
} else return $qu;
}

No comments: