How to Build Query Dynamically In PHP?

Problem:

You have a form(consider the following). You want to insert the form data into database but without manually creating the query.

Solution:

Use implode() function in php.

Example:

<?php
// connection with the server and the database
$connection = @mysql_connect("localhost", "root", "") or die("Couldn't connect to server");
$db = @mysql_select_db("php_implode", $connection) or die("Couldn't select database");

// form variables
$name = mysql_real_escape_string(strip_tags(trim($_POST['name'])), $connection);
$email = mysql_real_escape_string(strip_tags(trim($_POST['email'])), $connection);
$mobile = mysql_real_escape_string(strip_tags(trim($_POST['mobile'])), $connection);

// creating array with form data
$fields_arr = array("name" => $name,
"email" => $email,
"mobile" => $mobile
);

// start building the query
$stmt  = "INSERT INTO users";

// add the table fields
$stmt .= " (".implode(", ", array_keys($fields_arr)).")";

// add the field values
$stmt .= " VALUES ('".implode("', '", $fields_arr)."') ";

// execute query
$result = mysql_query($stmt) or die(mysql_error());
echo “User information Inserted.”;
?>

Table Structure:

CREATE TABLE IF NOT EXISTS `users` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`email` varchar(50) NOT NULL,
`mobile` varchar(50) NOT NULL,
PRIMARY KEY (`ID`)
)

How it Works:

Line 3 to 4	The code connects with the server and the database.
Line 7 to 9	Retrieving the three form fields – name, email, and mobile
Line 12 to 15	Here the fun begins. We’re creating an associative array. Its keys are the same as the ser table’s field name (see the ‘users’ table structure above) and values are the user supplied form data.
Line 18	It stores the initial part of the query in the $stmt variable
Line 21	The implode function creates the second part of the query, creating the table fields name. It joins each key of the array $fields_arr with another with a comma (which is specified as the first parameter). If we print this line, it would be like – (name, email, mobile)
Line 24	Here, the implode function creates the final part of the query, creating the values of the table fields. It joins each values of the array $fields_arr with another with a comma. Only the output of this line looks like this- VALUES (‘John Doe’, ‘john@yahoo.com’, ‘099788431435’)
Line 28	The query is executed.

Advantage of this system:

If we want to add two more fields age and address, its very simple. Add the following changes-

1. Add those 2 fields in the database.
2. Add those 2 fields in the form.
3. Add the following lines after line 9-

   <?php
   $age = mysql_real_escape_string(strip_tags(trim($_POST[age'])), $connection);
   $address = mysql_real_escape_string(strip_tags(trim($_POST['address'])), $connection);
   ?>

4. Add those 2 elements in the array $fields_arr


   <?php
   array("name" => $name,
       "email" => $email,
       "mobile" => $mobile,
       "address" => $address,
       "age" => $age
   );
   ?>