Tag Archive for query

snippets | March 7, 2012
No comments

Rebuild query string

<?php
function rebuild_qs($curr_vars) {
if (!empty($_SERVER['QUERY_STRING'])) {
$parts = explode("&", $_SERVER['QUERY_STRING']);
$curr_vars = str_replace(" ", "", $curr_vars); // remove whitespace
$c_vars = explode(",", $curr_vars);
$newParts = array();
foreach ($parts as $val) {
$val_parts = explode("=", $val);
if (!in_array($val_parts[0], $c_vars)) {
array_push($newParts, $val);
}
}
if (count($newParts) != 0) {
$qs = "&".implode("&", $newParts);
} else {
return false;
}
return $qs; // this is your new created query string
} else {
return false;
}
}
/* Example:
script.php?ident=1<?php echo rebuild_qs("ident, submit, var_one"); ?> */
?>

source

Category: Uncategorized | Tags: , , ,
snippets | February 26, 2012
No comments

Rendering Query Results in Vertical Format

SELECT * from users WHERE id=1G

source

Category: Uncategorized | Tags: , ,
snippets | February 26, 2012
No comments

Create a CSV File From Query Results

SELECT * FROM users INTO OUTFILE '/home/user/users.csv' FIELDS TERMINATED BY 	' ->LINES TERMINATED BY '
';

source

Category: Uncategorized | Tags: , , ,
snippets | February 12, 2012
No comments

Simple Data Access Class

<?php
// Author: Emilio Cavazos
// Date:   4/21/2007
// Notes:  Encapsulate mysql functionality (simple code for simple tasks)
//
// ==============
// example usage
// ==============
//
// // contact db parameters
// $contact['first_name'] = $_POST['first_name'];
// $contact['last_name'] = $_POST['last_name'];
// $contact['office_phone'] = $_POST['office_phone'];
// $contact['mobile_phone'] = $_POST['mobile_phone'];
// $contact['email'] = $_POST['email'];

// // insert contact
// $dal->updateById('contacts', $contact, 'id', $_GET['con_id']);

class DataAccessLayer
{
// connection variables
private $_server = 'localhost';
private $_username = 'root';
private $_password = '';
private $_database = 'db';
public  $debug = false;

// connection
private $_conn;

function __construct() {
$this->_conn = new mysqli($this->_server, $this->_username, $this->_password, $this->_database);

// check connection
if (mysqli_connect_errno()) {
printf('Connect failed: %s
', mysqli_connect_error());
exit();
}

// change character set to utf8
if (!$this->_conn->set_charset('utf8')) {
printf('Error loading character set utf8: %s
', $this->_conn->error);
}
}

function __destruct() {
$this->_conn->close();
}

// print error
public function error() {
return $this->_conn->errno . ': ' . $this->_conn->error;
}

// count all rows - return int
public function totalRows($field, $table) {
$sql = 'select ' . $field . ' from '
. $table;

$result = $this->_conn->query($sql);

// execute query
return $result->num_rows;
}

public function query($sql) {
// output sql sting for debugging
// crude debugging
if($this->debug) {
echo '<h3>Query</h3>';
echo '<div>';
echo $sql;
echo '</div>';
}

// execute query
return $this->_conn->query($sql);
}

public function queryLimit($sql, $page, $pageCount) {
$sql .= ' limit ' . $page . ', ' . $pageCount;

// execute query
return $this->query($sql);
}

public function nonQuery($sql) {
// execute query
$this->query($sql);

return $this->_conn->affected_rows;
}

public function select($table) {
$sql = 'select * from '
. $table;

// execute query
return $this->query($sql);
}

public function selectFields($table, $parameters) {
$sql = 'select ';

// build column names
foreach ($parameters as $key => $value) {
$sql .= $value;

if($key != end(array_keys($parameters))){
$sql .= ', ';
}
}

$sql .= ' from ' . $table;

// execute query
return $this->query($sql);
}

public function selectById($table, $idName, $idValue) {
$sql = 'select * from '
. $table
. ' where '
. $idName
. ' = '
. $idValue;

// execute query
return $this->query($sql);
}

public function selectByIdOrder($table, $idName, $idValue, $order) {
$sql = 'select * from '
. $table
. ' where '
. $idName
. ' = '
. $idValue
. ' order by ' . $order;

// execute query
return $this->query($sql);
}

public function selectWhere($table, $parameters, $where) {
$sql = 'select ';

// build column names
foreach ($parameters as $key => $value) {
$sql .= $value;

if($key != end(array_keys($parameters))){
$sql .= ', ';
}
}

$sql .= ' from ' . $table
.= ' where ' . $where;

// execute query
return $this->query($sql);
}

public function selectWhereOrder($table, $where, $order) {
$sql = 'select *'
. ' from ' . $table
. ' where ' . $where
. ' order by ' . $order;

// execute query
return $this->query($sql);
}

public function selectFieldsWhereOrder($table, $parameters, $where, $order) {
$sql = 'select ';

// build column names
foreach ($parameters as $key => $value) {
$sql .= $value;

if($key != end(array_keys($parameters))){
$sql .= ', ';
}
}

$sql .= ' from ' . $table
. ' where ' . $where
. ' order by ' . $order;

// execute query
return $this->query($sql);
}

public function selectOrder($table, $order) {
$sql = 'select * from '
. $table
. ' order by ' . $order;

// execute query
return $this->query($sql);
}

public function selectFieldsOrder($table, $parameters, $order) {
$sql = 'select ';

// build column names
foreach ($parameters as $key => $value) {
$sql .= $value;

if($key != end(array_keys($parameters))){
$sql .= ', ';
}
}

$sql .= ' from ' . $table
.= ' order by ' . $order;

// execute query
return $this->query($sql);
}

//public function selectWhereOrder($table, $parameters, $where, $order)

// search query
public function search($table, $fieldsToSearch, $search) {
$searchWords = explode(' ', $search);

$sql = 'select *';

$sql .= ' from ' . $table . ' where ';

// search columns for a match
foreach($searchWords as $wKey => $wValue) {
$sql .= '(';

foreach ($fieldsToSearch as $key => $value) {
$sql .= $value . ' like '%' . $wValue . '%'';

if($key != end(array_keys($fieldsToSearch))){
$sql .= ' or ';
}
}

if($wKey != end(array_keys($searchWords))){
$sql .= ') and ';
} else {
$sql .= ')';
}
}

// execute query
return $this->query($sql);
}

// search by fields query
public function searchFields($table, $fields, $fieldsToSearch, $search) {
$searchWords = explode(' ', $search);

$sql = 'select ';

// build column names
foreach ($fields as $key => $value) {
$sql .= $value;

if($key != end(array_keys($fields))){
$sql .= ', ';
}
}

$sql .= ' from ' . $table . ' where ';

// search columns for a match
foreach($searchWords as $wKey => $wValue) {
$sql .= '(';

foreach ($fieldsToSearch as $key => $value) {
$sql .= $value . ' like '%' . $wValue . '%'';

if($key != end(array_keys($fieldsToSearch))){
$sql .= ' or ';
}
}

if($wKey != end(array_keys($searchWords))){
$sql .= ') and ';
} else {
$sql .= ')';
}
}

// execute query
return $this->query($sql);
}

// search query
public function searchKeyConstrain($table, $fieldsToSearch, $search, $keyId, $keyValue) {
$searchWords = explode(' ', $search);

$sql = 'select *';

$sql .= ' from ' . $table . ' where ';

// search columns for a match
foreach($searchWords as $wKey => $wValue) {
$sql .= '(';

foreach ($fieldsToSearch as $key => $value) {
$sql .= $value . ' like '%' . $wValue . '%'';

if($key != end(array_keys($fieldsToSearch))){
$sql .= ' or ';
}
}

if($wKey != end(array_keys($searchWords))){
$sql .= ') and ';
} else {
$sql .= ')';
}
}

$sql .= ' and ' . $keyId . ' = ' . $keyValue;

// execute query
return $this->query($sql);
}

// search custom query
public function searchQuery($sql, $fieldsToSearch, $search) {
$searchWords = explode(' ', $search);

$sql .= ' where ';

// search columns for a match
foreach($searchWords as $wKey => $wValue) {
$sql .= '(';

foreach ($fieldsToSearch as $key => $value) {
$sql .= $value . ' like '%' . $wValue . '%'';

if($key != end(array_keys($fieldsToSearch))){
$sql .= ' or ';
}
}

if($wKey != end(array_keys($searchWords))){
$sql .= ') and ';
} else {
$sql .= ')';
}
}

// execute query
return $this->query($sql);
}

// search custom query
public function searchQueryOrder($sql, $fieldsToSearch, $search, $order) {
$searchWords = explode(' ', $search);

$sql .= ' where ';

// search columns for a match
foreach($searchWords as $wKey => $wValue) {
$sql .= '(';

foreach ($fieldsToSearch as $key => $value) {
$sql .= $value . ' like '%' . $wValue . '%'';

if($key != end(array_keys($fieldsToSearch))){
$sql .= ' or ';
}
}

if($wKey != end(array_keys($searchWords))){
$sql .= ') and ';
} else {
$sql .= ')';
}
}

$sql .= $order;

// execute query
return $this->query($sql);
}

// todo: add trim function to values
public function insert($table, $parameters) {

$sql = 'insert into '
. $table
. ' (';

// build column names
foreach ($parameters as $key => $value) {
$sql .= $key;

if($key != end(array_keys($parameters))){
$sql .= ', ';
}
}

$sql .= ') values (';

// build values for columns
foreach ($parameters as $key => $value) {
$sql .= ''' . $value . ''';

if($key != end(array_keys($parameters))){
$sql .= ', ';
}
}

$sql .= ') ';

// execute query
$this->query($sql);

return $this->_conn->insert_id;
}

public function insertQuery($sql) {
// execute query
$this->query($sql);

return $this->_conn->insert_id;
}

//public function insertSafe($table, $parameters, $types)

// todo: add trim function to values
public function updateById($table, $parameters, $idName, $idValue) {
$sql = 'update '
. $table
. ' set ';

// build column value pairs
foreach ($parameters as $key => $value) {
$sql .= $key . ' = '' . $value . ''';

if($key != end(array_keys($parameters))){
$sql .= ', ';
}
}

$sql .= ' where '
. $idName . ' = ' . $idValue;

// execute query
$this->query($sql);

return $this->_conn->affected_rows;
}

public function updateWhere($table, $parameters, $what, $wValue) {
$sql = 'update '
. $table
. ' set ';

// build column value pairs
foreach ($parameters as $key => $value) {
$sql .= $key . ' = '' . $value . ''';

if($key != end(array_keys($parameters))){
$sql .= ', ';
}
}

$sql .= ' where '
. $what . ' = '' . $wValue . ''';

// execute query
$this->query($sql);

return $this->_conn->affected_rows;
}

public function deleteById($table, $idName, $idValue) {
$sql = 'delete from '
. $table
. ' where '
. $idName . ' = ' . $idValue;

// execute query
$this->query($sql);

return $this->_conn->affected_rows;
}
}
?>

source

Category: Uncategorized | Tags: , , , , , , ,
snippets | February 2, 2012
No comments

mysql insert query

INSERT into table_name (column1, column2) values (value1, value2)

source

Category: Uncategorized | Tags: , , ,
snippets | February 2, 2012
No comments

mysql select query

SELECT column_names FROM table_name WHERE conditions

source

Category: Uncategorized | Tags: , , ,
snippets | February 2, 2012
No comments

mysql update query

UPDATE table_name SET column_name1 = value1, column_name2 = value2 WHERE conditions

source

Category: Uncategorized | Tags: , , ,
snippets | January 18, 2012
No comments

Execute MySQL query from command line

mysql.exe -udb_user -pdb_password db_name < source.sql > output.tab

source

Category: Uncategorized | Tags: , , , ,
snippets | December 31, 2011
No comments

Inserting from a form into a database

<cfquery datasource="turpinsshoutbox" username="root">
INSERT INTO comment(poster, email, msg)
VALUES ('#Form.poster#', '#Form.email#', '#Form.msg#')
</cfquery>
<cflocation url="shoutbox.cfm" addToken="No">

source

Category: Uncategorized | Tags: , , , ,
snippets | December 17, 2011
No comments

Javascript – Get Part Url

function getPartUrl(part) {
var query = self.location.href;
if( query.indexOf(part) == -1 ){
return false;
}else{
return true;
}

}
//Example
<script>document.write getPartUrl('news.php') ? 1 : 0</script>

source

Category: Uncategorized | Tags: , , , , , , ,
« Older Entries   Recent Entries »