Friday, November 23, 2012

Collection : PHP : Insert Data Function

In my previous post we added select_tag function for views as a helper function. Now we will add some function for inserting record. The names of functions might be similar to rails methods. This becomes easy for me to keep it simple to remember.

Lets consider we have some products table with id, name and description columns. As I have kept id auto generated we will not add if field in form.

In new.php
  1. <form id="product" method="POST" action="create.php" >
  2.  <input name="product[name]" type="text" value="" />
  3.  <textarea rows="6" name="product[description]"> </textarea>
  4.  <input id="product_submit" type="button" value="Save" onclick="submit();" />
  5. </form>
Check View.

Now when you click on save button it should redirect you to create.php. For now just add
  1. <?php print_r($_POST); ?>

and you can see (in view) Check View.

If you need to validate the records you can just add some code before the function we are going to add.
But I will just keep it simple, so directly going to add it to the database.

In create.php
  1. require_once 'includes/php/active_queries.php';
  2. insert_record($_POST['product'], "products");
  3. header('Location: index.php');

I have added active_queries.php, with this we will insert the data in table.
  1. /**
  2.  * For Inserting a single record
  3.  * @param array $record <p>Array of single record</p>
  4.  * @param string $model <p>Table name in which you want to make changes</p>
  5.  */
  6. function insert_record($record, $model) {
  7.   $fields = count($record);
  8.   if ($fields > 0) {
  9.     $i = 0;
  10.     $k = 0;
  11.     $insert = "INSERT INTO " . $model . " (";
  12.     foreach ($record as $key => $value) {
  13.       $k++;
  14.       $insert .= $key;
  15.       if ($fields > $k) {
  16.         $insert .= ", ";
  17.       }
  18.     }
  19.     $insert .= ") VALUES (";
  20.     foreach ($record as $key => $value) {
  21.       $i++;
  22.       if (gettype($value) == 'string') {
  23.         $insert .= " '" . pg_escape_string($value) . "'";
  24.       } else {
  25.         $insert .= $value;
  26.       }
  27.       if ($fields > $i) {
  28.         $insert .= ", ";
  29.       }
  30.     }
  31.     $insert .= ")";
  32.     $result = pg_query($insert);
  33.     return pg_affected_rows($result);
  34.   }
  35. }

This function will run loop of array twice. First to get the keys and then the values. And then it will add the data to the database. Once the query runs successfully it will return to the index page as we have mentioned header location as index.php. And you can see the inserted records.
Check Form View and Check Index View.

Now what if we want to add multiple records in one form/ click. This is not required for now but this can be used in nested_attributes.

So I added multiple_products.php in which we will add multiple fields.
  1. <form id="products" method="POST" action="create.php" >
  2.   <input name="multiple" type="hidden" value="true" /><br />
  3.   <?php for ($i=1; $i<=3; $i++) { ?>
  4.     <i><?php echo $i ?>. Name :</i><br />
  5.     <input name="products[<?php echo $i ?>][name]" type="text" value="" />    
  6.     <i>Description :</i><br />
  7.     <textarea rows="6" name="products[<?php echo $i ?>][description]"> </textarea><br />
  8.   <?php } ?>
  9.   <input id="product_submit" type="button" value="Save" onclick="submit();" />
  10. </form>
Check View

We are not creating another php file to submit these products to database. We will use the same create.php for which I added hidden field as multiple=true

In active_queries.php
  1. /**
  2.  * For Inserting multiple records
  3.  * @param array $records <p>Array of records</p>
  4.  * @param string $model <p>Table name in which you want to make changes</p>
  5.  */
  6. function insert_multiple_records($records, $model) {
  7.   foreach ($records as $record) {
  8.     insert_record($record, $model);
  9.   }
  10. }

And in create.php need to change some code. Instead of
  1. insert_record($_POST['product'], "products");

change code to
  1. if(isset($_POST['multiple'])){
  2.   insert_multiple_records($_POST['products'], "products");
  3. }else{
  4.   insert_record($_POST['product'], "products");
  5. }
Check View.

This will just get all data in and will run in loop which will call previous function(insert_record()) for each record array.

So after redirecting to index page we can view the inserted to reqords . Check View.

You can download whole source from github

No comments:

Post a Comment