<?php
/**
* @package  SamQuery
* @version  1.1
* @link     http://samjlevy.com/samquery
* @author   Samuel Levy <http://samjlevy.com/>
 
-------------
example usage
-------------
 
select:
    samq(TABLE,WHAT,JOIN,WHERE,ORDER)
    $array = samq("test"); // Select everything from table 'test'
    $array = samq("history","ColumnA",NULL,"ColumnC IS NULL"); // Select 'ColumnA' from table 'history' where 'ColumnC' is NULL
    $array = samq("users","*",NULL,NULL,"DateAdded DESC"); // Select everything from table 'users' and order descending by 'DateAdded'
    $array = samq("users","*","INNER JOIN addresses ON userID = addressUser"); // Select everything from table 'users' and JOIN the table to the 'addresses' table
 
insert:
    samq_i(TABLE,Array of COLUMNS,Array of VALUES)
    samq_i("test",array("testID","testName"),array(4,"Hello World")); // Insert into table 'test' a new record, making 'testID' equal 4 and 'testName' equal 'Hello World'
    samq_i("history",array("ColumnA","ColumnB","ColumnC"),array(1942,"Pearl Harbor",NULL)); // Insert into table 'history' the value 1942 for 'ColumnA', 'Pearl Harbor' for 'ColumnB', and NULL for 'ColumnC'
 
update:
    samq_u(TABLE,Array of Columns,Array of Values,WHERE)
    samq_u("test",array("testName",testCount"),array("Cool Hand Luke",40),"testID = 1"); // Update table 'test' changing 'testName' to 'Cool Hand Luke' and 'testCount' to 40 where 'testID' is 1
    samq_u("users",array("userDescription"),array(NULL)); // Update table 'test' so that the 'userDescription' is NULL for all records
 
delete:
    samq_d(TABLE,WHERE)
    samq_d("test"); // Delete everything from the 'test' table
    samq_d("users","userID = 1"); // Delete from the 'user' table the entry where 'userID' equals 1
 
custom:
    samq_c(QUERY,1 for Result)
    $array = samq_c("SELECT * FROM test",1); // Select all from table 'test' and request a resulting array (include second param of 1)
    samq_c("DROP TABLE test"); // Drop the 'test' table, this requires no resulting array (do not include second param of 1)
 
notes:
    Only column names and values for INSERT and UPDATE are automatically escaped, use esc() for other situations, ex: WHERE x = " . esc($variable)
    If you desire to insert/update a NULL value, use NULL as an element in the array, ex: array("test",NULL)
    If you want an array returned (instead of true/false) for samq_c function, remember to add the last parameter of 1
**/
 
// change to your db information
$db_host = "localhost";
$db_user = "root";
$db_password = "password";
$db_name = "test";
 
// -------------------------------- do not need to edit below this line --------------------------------
 
// establish connection
mysql_connect($db_host, $db_user, $db_password) or die ("Error connecting to database");
mysql_select_db($db_name) or die ("Error selecting database");
 
// escape characters
function esc($value) {
    return mysql_real_escape_string(trim($value));
}
 
// select
function samq($table, $what="*", $join=NULL, $where=NULL, $order=NULL) {
    // build query
    $q = "SELECT " . trim($what) . " FROM " . trim($table);
    if (!is_null($join))  $q.= " " . trim($join);
    if (!is_null($where)) $q.= " WHERE " . trim($where);
    if (!is_null($order)) $q.= " ORDER BY " . trim($order);
 
    // capture result and return as array
    $result = mysql_query($q) or die("MySQL error: " . mysql_error());
    for($i = 0; $result_array[$i] = mysql_fetch_assoc($result); $i++) ;
    array_pop($result_array);
    return $result_array;
}
 
// insert
function samq_i($table, $columns, $values) {
    // build query
    $q = "INSERT INTO " . trim($table);
 
    // columns
    if (!is_null($columns)) {
        $q.= " (";
        $count = count($columns);
        $i = 1;
        foreach($columns as $x) {
            $q.= esc($x);
            if ($i != $count) $q.= ",";
            $i++;
        }
        $q.= ")";
    }
 
    // values
    $q.= " VALUES (";
    $count = count($values);
    $i = 1;
    foreach($values as $x) {
        if (is_null($x)) { $q.= "NULL"; } else {
            if ( !ctype_digit(trim($x)) || (strlen(trim($x)) > 1 && substr(trim($x),0,1) == 0) ) $q.= "'";
            $q.= esc($x);
            if ( !ctype_digit(trim($x)) || (strlen(trim($x)) > 1 && substr(trim($x),0,1) == 0) ) $q.= "'";
        }
        if ($i != $count) $q.= ",";
        $i++;
    }
    $q.= ")";
 
    // execute query
    mysql_query($q) or die("MySQL error: " . mysql_error());
    return true;
}
 
// update
function samq_u($table, $columns, $values, $where=NULL) {
    // convert lists to arrays, and combine
    $combined = array_combine($columns,$values);
    $count = count($combined);
 
    // build query
    $q = "UPDATE " . trim($table) . " SET";
    $i = 1;
    foreach($combined as $column => $value) {
        $q.= " " . esc($column) . " = ";
 
        if (is_null($value)) { $q.= "NULL"; } else {
            if ( !ctype_digit(trim($value)) || (strlen(trim($value)) > 1 && substr(trim($value),0,1) == 0) ) $q.= "'";
            $q.= esc($value);
            if ( !ctype_digit(trim($value)) || (strlen(trim($value)) > 1 && substr(trim($value),0,1) == 0) ) $q.= "'";
        }
        if ($i != $count) $q.= ",";
        $i++;
    }
    $q.= " ";
    if (!is_null($where)) $q.= "WHERE " . trim($where);
 
    // execute query
    mysql_query($q) or die("MySQL error: " . mysql_error());
    return true;
}
 
// delete
function samq_d($table, $where=NULL) {
    // build query
    $q = "DELETE FROM " . trim($table);
    if (!is_null($where)) $q.= " WHERE " . trim($where);
 
    // execute query
    mysql_query($q) or die("MySQL error: " . mysql_error());
    return true;
}
 
// custom query
function samq_c($q,$r=NULL) {
    // execute and capture result
    $result = mysql_query($q) or die("MySQL error: " . mysql_error());
 
    // if result is requested, convert to array
    if ($r==1) {
        for($i = 0; $result_array[$i] = mysql_fetch_assoc($result); $i++) ;
        array_pop($result_array);
    }
 
    // if result is requested, return array
    if ($r==1) { return $result_array; } else { return true; }
}
?>