Makes connecting/querying a MySQL database from PHP a snap.
Download
Latest release, v1.1
Latest release, v1.1
Forums
Support & Development
Support & Development
The Code
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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; }
}
?> |