SamQuery | sam j levy

Makes connecting/querying a MySQL database from PHP a snap.

Download
Latest release, v1.1

Forums
Support & Development

The Code

<?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; }
}
?>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

<?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; }

}