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 3 of 4 Next Page

SQL Insert/Update Function

Now we create functions to insert and update data.  Insert and update share a lot of code, so we will combine them into a third function which will be transparent to outside users.


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

/********************************************************/

/*
 * InsertQuery
 * Desc: Insert data into the database.
 * Parms:
 *   $tableName - database table name.
 *   $values - associative array of field names and corresponding values.
 *   $debug - If true then return SQL query without executing.
 * Returns:
 *   Nothing on success.
 *   Error String on failure.
 */
Function InsertQuery ($tableName, $values, $debug=false)
{
/* Insert the $values into the database.
 * e.g.
 * $values = array ("name"=>"kris","email"=>"[email protected]");
 * InsertQuery ("employee", $values);
*/
return InsertUpdateQuery ("", $tableName, $values, $debug);
}

/********************************************************/

/*
 * UpdateQuery
 * Desc: Update data in the database.
 * Parms:
 *   $tableName - database table name.
 *   $values - associative array of field names and corresponding values.
 *   $where - SQL Where clause to specify which row(s) to update.
 *   $debug - If true then return SQL query without executing.
 * Returns:
 *   Nothing on success.
 *   Error String on failure.
 */
Function UpdateQuery ($tableName, $values, $where="", $debug=false)
{
/* Update the $values in the database.
 * e.g.
 * $values = array ("name"=>"kris","email"=>"[email protected]");
 * $where = "WHERE id='1'";
 * UpdateQuery ("employee", $values, $where);
*/
if (empty($where)) $where = " ";
return InsertUpdateQuery ($where, $tableName, $values, $debug);
}

/********************************************************/

Function InsertUpdateQuery ($type, $tableName, $fieldValues, $debug=false)
{
$db = mysql_connect(DB_HOST, DB_USER, DB_PASS);
if ($db == false){
return mysql_errno().": ".mysql_error()."<br>";
}

$i = 0;
$fields = "";
$values = "";
$updateList = "";

while (list ($key, $val) = each ($fieldValues)){
if ($i > 0){
$fields .= ", ";
$values .= ", ";
$updateList .= ", ";
}

$fields .= $key;

// If you do not want to add quotes
// around the field then specify
// /*NO_QUOTES*/ when passing in the value.
// For update statements like
// "update poll set total_votes=total_votes+1",
// you do not want
// the value field to have quotes around it.
if (strstr($val,"/*NO_QUOTES*/")){
$val = str_replace ("/*NO_QUOTES*/", "", $val);
$updateList .= "$key=$val";
$values .= $val;
}
else{
$updateList .= "$key='$val'";
$values .= "'$val'";
}

$i++;
}

if (empty($type)){
$query = "insert into $tableName ($fields) values ($values)";
}
else{
$query = "update $tableName set $updateList $type";
}

if ($debug){
@mysql_close($db);
return $query;
}

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

if (!$stmt){
$error = mysql_error ($db);

if (strstr($error, "Duplicate entry")){
$error = "Field value already in use, choose another.<br>\n";
}
}

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

return $error;
}

?>

PHP Code:

<?php

// Sample calls to new insert and update functions.
$values = array ("name"=>"Kris", "email"=>"[email protected]");
$error = InsertQuery ("employee", $values);

if (!empty($error)){
echo "Error: $error<br>\n";
}

$values = array ("email"=>"[email protected]");
$error = UpdateQuery ("employee", $values, "where name='Kris'");
if (!empty($error)){
echo "Error: $error<br>\n";
}

?>

Previous Page Page 3 of 4 Next Page