insert/update functions for mysql, what do you think?

[Goat]

I am working on two really simple functions that automatically generate (and execute) insert and update mysql queries and I have some questions:

 

Can there be any problems later on if functions treat numeric and string values the same? The function reports error if some value is neither numeric nor string (checked by using is_numeric and is_string functions), otherwise it just uses mysql_real_escape_string on all values in array and adds '' everywhere. Unlike microsoft access, mysql doesn't seem to care if you put '' on numeric value.

 

The simplest usage of insert function (for form data entry) would be:

 

 db_insert('users', 'name,e_mail,bio', $_REQUEST); 

 

First argument is table, another is the list of fields (separated by comma) , and third is the associative array of data that you want to insert.

 

Can you notice any problems?

 

regards, Goat

 

[GKWelding]

the only major problem I can see is that you have no support for boolean datatypes. Is there any particular reason for this?

[roopurt18]

You might run into some issues if you want to insert a NULL or the current MySQL timestamp using NOW().

 

I'd make a suggestion though on improved type checking.  Use a static array to keep track of the table schemas so you can cast to the proper types.

 

$table = 'users';
$values = array( 'username' => 'larry', 'password' => 'asdlfjawfiwaoeijfalfjw2oifjo', 'active' => true );

function db_insert( $table, $values ) {
  if( ! is_string( $table ) ) throw new Exception( '$table is not a string: ' . var_export( $table, true ) );
  if( ! strlen( $table ) ) throw new Exception( '$table can not be empty: ' . var_export( $table, true ) );
  if( ! preg_match( '@[a-z][a-z0-9_]+@i', $table ) ) throw new Exception( '$table is improper format: ' . var_export( $table, true ) );
  if( ! is_array( $values ) ) throw new Exception( '$values is not an array: ' . var_export( $values, true ) );
  if( ! count( $values ) ) throw new Exception( '$values is empty: ' . var_export( $values, true ) );
  $regexp_column = '[a-z][a-z0-9_]+';
  $col_string = implode( ',', array_keys( $values ) );
  if( ! preg_match( "@{$regexp_column}(,{$regexp_column})*@i", $col_string ) ) throw new Exception( 'column name in $values violates format: ' . var_export( array_keys( $values, true ) ) );

  static $table_info = array();

  if( ! array_key_exists( $table, $table_info ) ) {
    // we have not queried information_schema for the column info on this table
    // run a query such as: select * from information_schema.columns where table_name = $table
    // to get each columns type and length
    // if you fail, throw an exception
  }

  $cols = array();
  $vals = array();
  $info = $table_info[$table];
  foreach( $values as $col => $val ) {
    // determine the columns type
    if( $info[$col]['type'] === 'tinyint' ) {
      if( $val != 0 && $val != 1 ) throw new Exception( "Attempting to set column {$col} to improper value: " . var_export( $val, true ) );
    }
    // you could perform all sorts of various checks here for all sorts of data types / combinations
    $cols[] = "`{$col}`";
    $vals[] = $val; // strings will already be enclosed in single quotes
  }
  $sql = "insert into `{$table}` ( " . implode( ', ', $cols ) . " ) values ( " . implode( ', ', $vals ) . " )";
  // run sql
}

 

Just some thoughts. 

[Goat]

Thank you very much roopurt18 for very detailed answer. I am also using keys for cols and values for data, but in my case there is also extra check because my code checks if all keys in associative array are the same as values in string in 2nd function argument (so user can't omit something or add more), but there are better and less confusing ways to check that.

 

Anyway, can you tell me what is "static" for in you code? I use "global" to access variables outside function if that's what you meant.

 

regards,

Goat

[roopurt18]

When a variable in a function is declared static it will keep it's value between function calls.

 

So on the first function call the variable is an empty array and it might retrieve info for table users.

 

The second function call might be for table news so it retrieves info for news.

 

The third function call might be for users again and since the variable is static it already has the table info.

 

Essentially it ensures that your query to information_schema for the column info only occurs once per table per script execution.

[Goat]

Cool!

 

Now you gave me an idea: Is there a way to store small amounts of data between script calls without db? I know that $_SESSION holds most of the data on the server (only session id is in cookie if I am not mistaken), but can we use the same principle here, only it's for all users? I know that I don't need to hold a lot of data (it's just one array per table), and I am sure it would be hella lot faster than accessing information_schema from mysql every time script runs. But then again I would need one more script to reset data every time I alter tables (although that could be solved by adding timestamp to values, so they reset every 5 mins or so)  . Nah this is getting too complex already.

 

regards,

Goat

[roopurt18]

You would want to see if there's a way to register a shared memory resource on your platform.  I've never done such a thing with PHP, although I've certainly thought about it.

 

Another thing you could do is create a PHP object to represent the table schemas and store it in the session.  Then you will only need to query the schema once per user per session.

 

You have to keep in mind though that MySQL is also optimized and will cache query results and serve result sets from its own internal cache.  I would go with the static method since it's easiest to implement and just keep it as-is until you have a real need to optimize further with your own code and logic.