php insert sql function

Question

Hello everybody, I want to create one insert function in php, which I will use to insert data into database.

Of course all inserts are not the same, some use one table others use other, tables have different column numbers etc. What is the best way to do this ?

I'm currently on the web page for member registration, now I don't want to allow everyone to register, I want admin to approve which members will get approved and which ones will not, so I set one hidden field to 0 and after approval it becomes one.

CASE: Someone comes to the website and changes input field value to 1 with firebug and submits the registration, automatically gets approved, I didn't try this I'm just considering the possibility. Now if I put member_active inside insert function then it won't work for other insert queries with few/less database column rows.

What is the way to do this, is the global/general insert function good idea at all, I pretty much know how to program all of this, but lately I've become more careful about data/efficiency and such things that can make your website better/worse so I want a good start. Thank you

Answer 1

Just don't use form fields to insert them directly, if you're doing so (that's what I got when I read the question). In CI, pass the hidden field value as 0 to the insert function and that possibility is gone.

Answer 2

CodeIgniter also does this, but here it is:

function Insert($table, $data, $ignore = false)
{
    $sql = array();

    if (is_array($sql) === true)
    {
     $sql['query'] = 'INSERT ';

     if ($ignore === true)
     {
      $sql['query'] .= 'IGNORE ';
     }

     foreach ($data as $key => $value)
     {
      $data[$key] = Tick($key) . ' = ' . Quote($value);
     }

     $sql['query'] .= 'INTO ' . Tick($table) . ' SET ' . implode(', ', $data);
    }

    return implode('', $sql);
}

Obviously, you need to define your own Tick() and Quote() functions to back tick database entities and escape values respectively.

Answer 3

Well if youre going to go that far in abstraction then it would behoove you to also abstract the the entire process of writing a query to a class (see Zend_Db_Select, Doctrine_Query, Propel Criteria, etc..).

But more to you point, as Jimmie suggests if someone new is signing up, you know they always wll be pending approval... so dont even use a valu from the form, add it to the array of form values after submission but before passing to your insert functionality.

Answer 4

Instead of abstracting your SQL in the code, it's better to use a database's build in functionality: stores procedures (SP) or some databases only use DB functions.

It's not sound to abstract DB work into the code, because you're making the assumption that the table structure will never change (a major mistake on an application that gets constant changes and improvements).

By creating SPs you make a SQL call like:

$query = "EmployeeAdd('joe','smith','1970-12-22')";

Now your code has the benefit of being abstracted (and even somewhat non database specific) plus you can change your table structure on a whim and not have to change a single line of code. Of course keep in mind to follow this design mindset you need to create an EmployeeGet() and an EmployeeUpdate() functions and it's normal to have multiple versions of these, such as: EmployeeGetByID() EmployeesGetByJobID()

Pay special notice to the syntax, as it's important you go by Data + Action + How becuase after you write these functions for your entire database, if you were to call them GetEmployeeByID() then you'll have dozens of SPs listed, all the Gets together, which makes for poor organization.