|
|
|
|
Bookmark this on Delicious
Share on Facebook
 Slashdot It!
Digg  pg_insert
Insert array into table
(PHP 4 >= 4.3.0, PHP 5)
Example 1939. pg_insert() example<?php Code Examples / Notes » pg_insertexcalibur
Today at work I isolated a problem I was having with this function to how I was formatting the date. I was assigning the date in my code as follows: $today = date( "Ymd" ); // ISO 8601 This format is acceptable to PostgreSQL, as verified by their documentation and buy tests using psql. However, to make it work in my code, I had to make the following change: $today = date( "Y-m-d" ); // also ISO 8601 format rorezende
Time is money, then I write a function similar to pg_insert in PHP (only output sql statement) : function db_mount_insert($table,$array) { $str = "insert into $table ("; while(list($name,$value) = each($array)) { $str .= "$name,"; } $str[strlen($str)-1] = ')'; $str .= " values ("; reset($array); while(list($name,$value) = each($array)) { if(is_string($value)) $str .= "'$value',"; else $str .= "$value,"; } $str[strlen($str)-1] = ')'; $str .= ";" ; return $str; } shane
Returns SQL statement, slight improvement on the code from 'rorezende at hotmail dot com'. This version adds bool values correctly.It also checks to make sure there is actually a value in the array before including it in the sql statement. (ie: null values or empty strings won't be added to the sql statement) <?PHP function db_build_insert($table,$array) { $str = "insert into $table "; $strn = "("; $strv = " VALUES ("; while(list($name,$value) = each($array)) { if(is_bool($value)) { $strn .= "$name,"; $strv .= ($value ? "true":"false") . ","; continue; }; if(is_string($value)) { $strn .= "$name,"; $strv .= "'$value',"; continue; } if (!is_null($value) and ($value != "")) { $strn .= "$name,"; $strv .= "$value,"; continue; } } $strn[strlen($strn)-1] = ')'; $strv[strlen($strv)-1] = ')'; $str .= $strn . $strv; return $str; } ?> mina86
Next version :) My version checks whether value is bool, null, string or numeric and if one of the values is not function returns false if not. null values are inserted as NULL, bool as true or false and strings are add-shlashed before adding to query string. Note, that this function is not safe. SQL injection is possible with column names if you use $_POST or something similar as a $array. <?php function db_build_insert($table, $array) { if (count($array)===0) return false; $columns = array_keys($array); $values = array_values($array); unset($array); for ($i = 0, $c = count($values); $i$c; ++$i) { if (is_bool($values[$i])) { $values[$i] = $values[$i]?'true':'false'; } elseif (is_null($values[$i])) { $values[$i] = 'NULL'; } elseif (is_string($values[$i])) { $values[$i] = "'" . addslashes($values[$i]) . "'"; } elseif (!is_numeric($values[$i])) { return false; } } return "INSERT INTO $table ($column_quote" . implode(', ', $columns) . ") VALUES (" . implode(', ', $values) . ")"; } ?> jsnell
If you need schema support, this function will do something similar to pg_insert: function pg_insert_with_schema($connection, $table, $updates) { $schema = 'public'; if (strpos($table, '.') !== false) list($schema, $table) = explode('.', $table); if (count($updates) == 0) { $sql = "INSERT INTO $schema.\"$table\" DEFAULT VALUES"; return pg_query($sql); } else { $sql = "INSERT INTO $schema.\"$table\" "; $sql .= '("'; $sql .= join('", "', array_keys($updates)); $sql .= '")'; $sql .= ' values ('; for($i = 0; $i < count($updates); $i++) $sql .= ($i != 0? ', ':'').'$'.($i+1); $sql .= ')'; return pg_query_params($connection, $sql, array_values($updates)); } } andychr17
Had a few issues while trying to run this in PHP 4.4.0: - I could not get it to work with column names that are SQL reserved words (example: desc, order). I was forced to change the column names in order to use the function. I could not put the column names in quotes, because that caused pg_convert() to fail. - Function was returning false until I passed the PGSQL_DML_EXEC option. skippy
Beware of the following: pg_insert() and pg_update() are adding slashes to all character-like fields they work with. This makes them SQL injection super-safe, but there are unwanted consequences, as follows: If you have a regular setup with magic_quotes_gcp=On, and you use pg_insert() or pg_update(), you will end up with fields that look as if you used addslashes() twice. To solve this, you can use stripslashes() on the data just before using it with pg_insert() or pg_update(). There's another alternative, which seems better to me. Why make yourself crazy all over the code, adding slashes, stripping slashes, worrying whether magic_quotes_gpc is on or off and so on and so forth? Why do this, when the only place you actually need those slashes is right when you push the data into the database? So why not get rid of your addslashes() and stripslashes() from all over your code, and turn magic_quotes_gcp off. As long as you always use pg_insert() and pg_update() to do your DB work, you're SQL-injection safe AND slash-headache free. |
Change Language
pg_affected_rows pg_cancel_query pg_client_encoding pg_close pg_connect pg_connection_busy pg_connection_reset pg_connection_status pg_convert pg_copy_from pg_copy_to pg_dbname pg_delete pg_end_copy pg_escape_bytea pg_escape_string pg_execute pg_fetch_all_columns pg_fetch_all pg_fetch_array pg_fetch_assoc pg_fetch_object pg_fetch_result pg_fetch_row pg_field_is_null pg_field_name pg_field_num pg_field_prtlen pg_field_size pg_field_table pg_field_type_oid pg_field_type pg_free_result pg_get_notify pg_get_pid pg_get_result pg_host pg_insert pg_last_error pg_last_notice pg_last_oid pg_lo_close pg_lo_create pg_lo_export pg_lo_import pg_lo_open pg_lo_read_all pg_lo_read pg_lo_seek pg_lo_tell pg_lo_unlink pg_lo_write pg_meta_data pg_num_fields pg_num_rows pg_options pg_parameter_status pg_pconnect pg_ping pg_port pg_prepare pg_put_line pg_query_params pg_query pg_result_error_field pg_result_error pg_result_seek pg_result_status pg_select pg_send_execute pg_send_prepare pg_send_query_params pg_send_query pg_set_client_encoding pg_set_error_verbosity pg_trace pg_transaction_status pg_tty pg_unescape_bytea pg_untrace pg_update pg_version |
