Search Help Board

PHP FAQ
PHP Articles
PHP Help
Bulletin Board

PHP Manual (NEW!)
First Time PHP'ers
Help with programming
Sql assignment help
PHP Homework Help


 

Previous Page Page 2 of 4 Next Page

SQL Select Function

Fetching data is the most common task, so let's make it easier.  We will put all the logic for retrieving information from the database into one generic function.

PHP Code:

<?php

define (DB_HOST, "CHANGE_THIS");
define (DB_NAME, "CHANGE_THIS");
define (DB_USER, "CHANGE_THIS");
define (DB_PASS, "CHANGE_THIS");

if (!strcmp(DB_HOST,"CHANGE_THIS")){
echo "You must setup your database settings. Change the defines DB_HOST, DB_NAME, DB_USER and DB_PASS\n";
exit;
}

/*
 * SelectQuery
 * Desc: Retrieve data from the database.
 * Parms:
 *   $tables - comma separated list of table names.
 *   $fields - comma separated list of field names or "*".
 *   $where - SQL Where clause (e.g. "where id=2").
 *   $groupBy - SQL Group clause (e.g. "group by name").
 *   $orderBy - SQL Order clause (e.g. "order by name").
 *   $show_debug - If true then print SQL query.
 * Returns:
 *   2d array of rows and columns on success.
 *   Error String on failure.
 */
Function SelectQuery ($tables, $fields, $where="",
     $groupBy="", $orderBy="", $show_debug=false)
{
$db = mysql_connect(DB_HOST, DB_USER, DB_PASS);
if ($db == false){
return mysql_errno().": ".mysql_error()."<br>";
}

// Return the data requested by the fields, tables and where.
// Return the data in a 2 dimensional array.
$values = array();
$field_array = split (", ?", $fields);

$max_fields = ($fields == "*")? 20 : count($field_array);

if (!empty($where)){
if (!strstr(strtolower($where),"where ")) $where = "where $where";
}

$query = "select $fields from $tables $where $groupBy $orderBy";
if ($show_debug == true) echo "query=$query<br>\n";

$stmt = mysql_db_query (DB_NAME, $query, $db);

if ($stmt == false){
return mysql_errno().": ".mysql_error()."<br>";
}

while ($fields = mysql_fetch_row ($stmt)){
$values[] = $fields;
}

@mysql_free_result ($stmt);
@mysql_close($db);

return $values;
}

?>

PHP Code:

<?php

// Sample call to new select function.
$values = SelectQuery ("employee", "name, email");

if (!is_array($values)){
echo "Error: $values<br>\n";
exit;
}

while (list(,$v) = each ($values)){
echo "$v[0], $v[1]<br>\n";
}

?>

Web Browser Result:

Kris, [email protected]
John, [email protected]

PHP Code:

<?php

// Full sample call to new select function.
$values = SelectQuery ("employee", "name, email", "where name like 'k%'", "group by name,email", "order by name");

if (!is_array($values)){
echo "Error: $values<br>\n";
exit;
}

while (list(,$v) = each ($values)){
echo "$v[0], $v[1]<br>\n";
}

?>

Web Browser Result:

Kris, [email protected]

Previous Page Page 2 of 4 Next Page