Custom function for inserting values into MySQL

--00504502cc01ad188704776d7609
Content-Type: text/plain; charset=ISO-8859-1

Okay friends, I have been wondering about writing a simple function that
will help me with my MySQL inserting. Not because I need to save time and
space, but because I wanted to.

I wrote a function for inserting 10 values (I have not been able to come up
with an idea how to make the number of values I'm inserting variable, so I'm
sticking with ten).

This function takes 22 parameters: #1 is the table name, #2-21 are the row
names and the values, and #22 is the "integar string".

The first 21 parameters are self-explanatory, the 22nd is a string of values
that need to be inserted as an integar, basically, not adding single quotes
around the value. Eg. $value2 = 5, not $value2 = '5'.

I am very hesitant to try this one out on my database, I've got tables of
important information and don't want to, I don't know, inadvertantly throw a
wrench into the works, AND I want to open up a dialoug about custom PHP
functions for working with MySQL, for the fun of it!

Here is my 10 value function for inserting data into a MySQL database table.

function insertinto10($table, $field1, $value1, $field2, $value2, $field3,
$value3, $field4, $value4, $field5, $value5, $field6, $value6, $field7,
$value7, $field8, $value8, $field9, $value9, $field10, $value10, $int =
NULL)
{
if (isset($int))
{
$sPattern = '/\s*/m';
$sReplace = '';
$int = preg_replace($sPattern, $sReplace, $int);
$pieces = explode(",", $int); // $pieces[0], $pieces[1] - each equal to
value numbers that are integars
$length = count($pieces);
// call custom function to create associative array eg. $newarray[2] = 1,
$newarray[4] = 1, $newarray[5] = 1 . . .
$integarArray = strtoarray($length, $int);
}

$valuesArray = array($value1, $value2, $value3, $value4, $value5, $value6,
$value7, $value8, $value9, $value10);

foreach ($valuesArray as $key => $value)
{
if (isset($integarArray[$key]) && $integarArray[$key] == 1)
{
// INTEGAR VALUE
$valuesArray[$key] = mysql_real_escape_string(stripslashes($value));
}
else
{
// STRING VALUE
$cleanValue = mysql_real_escape_string(stripslashes($value));
$valuesArray[$key] = "'{$cleanValue}'";
}
}

$result = mysql_query("INSERT INTO `{$table}` (`{$field1}`, `{$field2}`,
`{$field3}`, `{$field4}`) VALUES ({$valuesArray[1]}, {$valuesArray[2]},
{$valuesArray[3]}, {$valuesArray[4]}, {$valuesArray[5]}, {$valuesArray[6]},
{$valuesArray[7]}, {$valuesArray[8]}, {$valuesArray[9]},
{$valuesArray[10]})");
return $result;
}


You may find copying/pasting into your favorite code-editor helps make it
more readable.

Do you see any major hangups or screwups on first glance? And is my fear of
trying this out on my database unfounded? Does this even seem that useful?

--00504502cc01ad188704776d7609--

RE: Custom function for inserting values into MySQL

------=_NextPart_000_0125_01CA5BE5.B81AC140
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

> Do you see any major hangups or screwups on first glance?

Yes.

There is so much wrong with this I don't even know where to begin...

> This function takes 22 parameters: #1 is the table name,
> #2-21 are the row
> names and the values, and #22 is the "integar string".

Dude. Seriously? TWENTY TWO parameters.

Use this for variable number of parameters:
http://us2.php.net/manual/en/function.func-get-args.php

Or how about using an array/hash as your second parameter with the
field=>value pairs.

Which is astonishing since you have the concept of an array with this hack:

$valuesArray = array($value1, $value2, $value3, $value4, $value5,
$value6, $value7, $value8, $value9,
$value10);
foreach ($valuesArray as $key => $value)

The word you're looking for is "INTEGER" not "INTEGAR".

> And is my fear of trying this out on my database unfounded?

No. Don't use it.

> Does this even seem that useful?

No.

Your function is so very limited in scope and use. You're better off writing
a wrapper around the SQL functions and submit direct SQL as the string
parameter to the function. See attached db.inc.php.

You would also be better served using a method/function such as my
base.class.php::sync() which will insert or update a row.

The attached code is about a year old or so and has since been refined
further, but this should give you a good place to start.

http://daevid.com

------=_NextPart_000_0125_01CA5BE5.B81AC140
Content-Type: text/plain;
name="base.class.php"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
filename="base.class.php"

#----------------------------------------------------------- --------=0A=
#=0A=
# Confidential - Property of Lockdown Networks, Inc.=0A=
# Do not copy or distribute.=0A=
# Copyright 2002-2008 Lockdown Networks, Inc. All rights reserved.=0A=
#=0A=
#----------------------------------------------------------- --------=0A=
=0A=
require_once('global.inc.php');=0A=
require_once('error.class.php');=0A=
=0A=
class baseClass=0A=
{=0A=
protected $db =3D 'V2_Data';=0A=
protected $table =3D NULL;=0A=
=0A=
protected $id =3D NULL;=0A=
protected $created_on =3D NULL;=0A=
protected $_stamp =3D NULL;=0A=
protected $enabled =3D TRUE;=0A=
=0A=
//we use generic __call __get and __set, but this is a special case.=0A=
function get_stamp() { return $this->_stamp; }=0A=
function set_stamp($stamp) { $this->_stamp =3D $stamp; }=0A=
=0A=
/**=0A=
* Constructor=0A=
* =0A=
* @access public=0A=
* @return object=0A=
* @param mixed $id the ID of the object to load from the database (this =
could be a string or usually an integer)=0A=
* @author Daevid Vincent [daevid@]=0A=
* @version 1.2=0A=
* @date 09/20/07=0A=
*/=0A=
function __construct($id =3D NULL)=0A=
{=0A=
if ($_SESSION['companydb']) $this->db =3D $_SESSION['companydb'];=0A=
=0A=
//this follows the Ruby way for ease of porting/sharring, please stick =
with the convention.=0A=
if (is_null($this->table) && preg_match( '/y$/', $this->getClassname() =
) > 0)=0A=
$this->table =3D strtolower(preg_replace( '/y$/', 'ies', =
$this->getClassName() ));=0A=
elseif( is_null( $this->table ) )=0A=
$this->table =3D strtolower($this->getClassName()).'s';=0A=
=0A=
if (!is_null($id)) $this->load($id);=0A=
}=0A=
=0A=
/**=0A=
* generate a key/value pair from the class' variables.=0A=
*=0A=
* @access public=0A=
* @return array=0A=
* @author Daevid Vincent [daevid@]=0A=
* @version 1.0=0A=
* @date 08/13/07=0A=
*/=0A=
public function get_array()=0A=
{=0A=
$row =3D array();=0A=
foreach($this as $key =3D> $value) =0A=
$row[$key] =3D $value;=0A=
=0A=
$row['enabled'] =3D ($this->enabled) ? 1 : 0;=0A=
=0A=
return $row;=0A=
}=0A=
=0A=
/**=0A=
* set the class' values based upon a SQL query.=0A=
*=0A=
* Note: Usually this is called by an extension class, =0A=
* which in turn calls the parent::load_from_sql() =0A=
* which generates an array and then calls load_from_array()=0A=
*=0A=
* @access public=0A=
* @return array or false=0A=
* @param int $id ID of the object to load=0A=
* @author Daevid Vincent [daevid@]=0A=
* @version 1.0=0A=
* @date 08/20/07=0A=
* @see load_from_array()=0A=
*/=0A=
function load($id =3D null)=0A=
{=0A=
if (intval($id) < 1) return false;=0A=
=0A=
$sql =3D "SELECT *=0A=
FROM ".$this->db.".".$this->table." =0A=
WHERE id =3D '".SQL_ESCAPE($id)."'";=0A=
=0A=
$result =3D $this->load_from_sql($sql); //LIMIT 1 is appended by base =
class=0A=
if ($result)=0A=
return $result;=0A=
else=0A=
throw new Exception(translate('%1$s threw an exception trying to load =
object #%2$s', __CLASS__, $id));=0A=
}=0A=
=0A=
/**=0A=
* set the class' values based upon a SQL table which is converted to an =
array of column(key) value pairs and passed to load_from_array().=0A=
*=0A=
* @access public=0A=
* @return array or false=0A=
* @param string $sql SQL schema columns to use as array keys=0A=
* @author Daevid Vincent [daevid@]=0A=
* @version 1.0=0A=
* @date 08/13/07=0A=
* @see load_from_array()=0A=
*/=0A=
public function load_from_sql($sql =3D null)=0A=
{=0A=
if (is_null($sql)) return false;=0A=
=0A=
$result =3D SQL_QUERY($sql." LIMIT 1");=0A=
if($result && $row =3D SQL_ASSOC_ARRAY($result))=0A=
{=0A=
return $this->load_from_array($row);=0A=
}=0A=
else return false;=0A=
}=0A=
=0A=
/**=0A=
* set the class' values based upon an array.=0A=
*=0A=
* @access public=0A=
* @return boolean=0A=
* @param array $row class or SQL schema column/value pairs=0A=
* @param array $force force loading of value pairs=0A=
* @author Daevid Vincent [daevid@]=0A=
* @version 1.1=0A=
* @date 12/17/07=0A=
* @see load_from_sql()=0A=
*/=0A=
public function load_from_array($row, $force =3D false)=0A=
{=0A=
if (!$force && intval($row['id']) < 1) return false;=0A=
=0A=
foreach($row as $key =3D> $value)=0A=
$this->$key =3D $value;=0A=
=0A=
$this->enabled =3D ($row['enabled'] == '1') ? true : false;=0A=
//$this->iterateVisible();=0A=
return true;=0A=
}=0A=
=0A=
/**=0A=
* INSERT or UPDATE an object's Database row.=0A=
*=0A=
* Pass in an array of column name/value pairs to INSERT/UPDATE those =
specifically, using schema defaults for the rest.=0A=
* =0A=
* @access public=0A=
* @return boolean false on error, true on UPDATE, record ID on INSERT=0A=
* @param array $row SQL schema column/value pairs=0A=
* @param boolean $auto_escape (true) will wrap all values in =
SQL_ESCAPE()=0A=
* @param boolean $show_errors toggle SQL errors, use SQL_ERROR_NUM() or =
SQL_ERROR() to handle yourself.=0A=
* @author Daevid Vincent [daevid@]=0A=
* @version 1.2=0A=
* @date 10/11/07=0A=
*/=0A=
public function sync($row =3D null, $auto_escape =3D true, $show_errors =
=3D true)=0A=
{=0A=
if (is_null($this->table)) return false;=0A=
=0A=
if (is_null($row)) $row =3D $this->get_array();=0A=
=0A=
if (count($row) < 1) return;=0A=
=0A=
//[dv] this is a handy way to shortcut and update a record with the =
passed in array key/vals.=0A=
if ($row['id'] < 1) unset($row['id']);=0A=
if (!$this->id && $row['id'] > 0) $this->id =3D $row['id'];=0A=
=0A=
//[dv] I thought about scrubbing the $row array of empty values, =0A=
// but that causes a problem if you actually DO want to wipe out =
some values.=0A=
=0A=
$row['enabled'] =3D ($row['enabled']) ? 1 : 0;=0A=
=0A=
if (intval($this->id) < 1) =0A=
{=0A=
$cols =3D "`".implode("`, `", array_keys($row))."`";=0A=
$temp =3D array();=0A=
foreach ( $row as $val )=0A=
{=0A=
if (!is_null($val)) =0A=
{=0A=
$value =3D trim($val);=0A=
$temp[] =3D ($auto_escape ===3D true) ? "'".SQL_ESCAPE( $val =
)."'" : $val;=0A=
}=0A=
else=0A=
$temp[] =3D "NULL";=0A=
}=0A=
$values =3D implode(', ',$temp);=0A=
$sql =3D "INSERT INTO ".$this->table." (created_on, ".$cols.") VALUES =
(NOW(), ".$values.")"; =0A=
}=0A=
else=0A=
{=0A=
$sql =3D "UPDATE ".$this->table." SET ";=0A=
unset($row['id']);=0A=
$temp =3D array();=0A=
foreach ($row as $col =3D> $value)=0A=
{=0A=
if (!is_null($value)) =0A=
{=0A=
$value =3D trim($value);=0A=
$temp[] =3D $col." =3D ".(($auto_escape ===3D true) ? =
"'".SQL_ESCAPE( $value )."'" : $value);=0A=
}=0A=
else=0A=
$temp[] =3D $col." =3D NULL";=0A=
}=0A=
$sql .=3D implode(', ', $temp);=0A=
$sql .=3D " WHERE id =3D '".$this->id."' LIMIT 1";=0A=
}=0A=
=0A=
if ($sth =3D SQL_QUERY($sql, false, $show_errors)) =0A=
{=0A=
if (intval($this->id) < 1) =0A=
{=0A=
$this->id =3D SQL_INSERT_ID();=0A=
$row['id'] =3D $this->id; //or load_from_array will fail=0A=
$this->load_from_array($row); //[dv] TODO: not sure if this is needed=0A=
AddUserLog('Action', MakeUserLog('Added %1$s =
[%2$s]',str_replace('_', ' ', $this->getClassName()), $this->id));=0A=
return $this->id;=0A=
}=0A=
else=0A=
{=0A=
$row['id'] =3D $this->id; //or load_from_array will fail=0A=
$this->load_from_array($row); //[dv] TODO: not sure if this is needed=0A=
AddUserLog('Action', MakeUserLog('Saved %1$s =
[%2$s]',str_replace('_', ' ', $this->getClassName()), $this->id));=0A=
return true;=0A=
}=0A=
}=0A=
else =0A=
return false;=0A=
}=0A=
=0A=
/**=0A=
* Delete the corresponding class object ID from the database.=0A=
*=0A=
* Note: 'delete' is a reserved word in PHP=0A=
*=0A=
* @access public=0A=
* @return boolean=0A=
* @author Daevid Vincent [daevid@]=0A=
* @version 1.1=0A=
* @date 10/10/07=0A=
*/=0A=
public function delete()=0A=
{=0A=
if( intval( $this->id ) < 1 )=0A=
return( false );=0A=
=0A=
if (SQL_QUERY("DELETE FROM ".$this->db.".".$this->table." WHERE id =3D =
'".$this->id."' LIMIT 1")) =0A=
{=0A=
AddUserLog('Action', MakeUserLog('Deleted %1$s =
[%2$s]',str_replace('_', ' ', $this->getClassName()), $this->id));=0A=
=0A=
foreach($this as $key =3D> $value) =0A=
$this->$key =3D null;=0A=
=0A=
return true;=0A=
}=0A=
else =0A=
return false;=0A=
}=0A=
=0A=
/**=0A=
* Shows all exposed variables in this class=0A=
*=0A=
* @access public=0A=
* @return array=0A=
* @param boolean $print to print out each value=0A=
* @author Daevid Vincent [daevid@]=0A=
* @version 1.0=0A=
* @date 08/13/07=0A=
*/=0A=
public function iterateVisible($print =3D false) =0A=
{=0A=
if ($print) echo =
"\n
".$this->getClassName()."::iterateVisible:
\n";=0A=
=0A=
$tmp =3D array();=0A=
foreach($this as $key =3D> $value) =0A=
{=0A=
$tmp[$key] =3D $value;=0A=
if ($print) print $key." =3D> ".$value."
\n";=0A=
}=0A=
=0A=
return $tmp;=0A=
}=0A=
=0A=
/**=0A=
* returns the name of this class as a string=0A=
* =0A=
* @access public=0A=
* @return string=0A=
* @author Daevid Vincent [daevid@]=0A=
* @version 1.0=0A=
*/=0A=
public function getClassName()=0A=
{=0A=
//return __CLASS__;=0A=
return get_class($this);=0A=
}=0A=
=0A=
/**=0A=
* Provides generic getters and setters=0A=
*=0A=
* @access public=0A=
* @param string $method The method name.=0A=
* @param array $arguments The arguments passed to the method.=0A=
* @return mixed=0A=
* @author Daevid Vincent [daevid@]=0A=
* @date 08/21/2007=0A=
* @version 1.1=0A=
* @see __get(), __set()=0A=
*/=0A=
public function __call( $method, $arguments )=0A=
{=0A=
$prefix =3D strtolower( substr( $method, 0, 3 ) );=0A=
$property =3D strtolower( substr( $method, 4 ) );=0A=
=0A=
if ( empty($prefix) || empty($property) ) return;=0A=
=0A=
if ( 'get' == $prefix )=0A=
{ =0A=
if ( property_exists($this, $property) )=0A=
return $this->$property;=0A=
else=0A=
return $this->__get($property);=0A=
}=0A=
elseif ( 'set' == $prefix )=0A=
{=0A=
if ( property_exists($this, $property) )=0A=
return $this->$property =3D $arguments[0];=0A=
else=0A=
return $this->__set($property, $arguments[0]);=0A=
}=0A=
=0A=
echo "

Attempted to '".$method."' =
variable in class '".$this->getClassName()."'.

\n";=0A=
backtrace();=0A=
}=0A=
=0A=
/**=0A=
* magic function to handle any accessing of undefined variables.=0A=
* Since PHP is "lax" this will help prevent stupid mistakes.=0A=
* =0A=
* @access public=0A=
* @return void=0A=
* @param mixed $var name of the variable=0A=
* @author Daevid Vincent [daevid@]=0A=
* @version 1.0=0A=
* @date 08/13/07=0A=
* @see __set(), __call()=0A=
*/=0A=
public function __get($var) =0A=
{=0A=
echo "

Attempted to __get() variable =
'".$var."' in class '".$this->getClassName()."'.

\n";=0A=
backtrace();=0A=
}=0A=
=0A=
/**=0A=
* magic function to handle any setting of undefined variables.=0A=
* Since PHP is "lax" this will help prevent stupid mistakes.=0A=
* =0A=
* @access public=0A=
* @return void=0A=
* @param mixed $var name of the variable=0A=
* @param mixed $val value of the variable=0A=
* @author Daevid Vincent [daevid@]=0A=
* @version 1.0=0A=
* @date 08/13/07=0A=
* @see __get(), __call()=0A=
*/=0A=
public function __set($var, $val) =0A=
{=0A=
echo "

Attempted to __set() variable =
'".$var."' to '".$val."' in class =
'".$this->getClassName()."'.

\n";=0A=
backtrace();=0A=
}=0A=
=0A=
/**=0A=
* The destructor method will be called as soon as all references to a =
particular object are removed =0A=
* or when the object is explicitly destroyed.=0A=
*=0A=
* This End User method will save the $_SESSION first=0A=
* http://www.php.net/session-set-save-handler=0A=
*=0A=
* @access public=0A=
* @author Daevid Vincent [daevid@]=0A=
* @since 1.0=0A=
* @version 1.0=0A=
* @date 08/13/07=0A=
*/=0A=
/*=0A=
function __destruct() =0A=
{=0A=
session_write_close();=0A=
=0A=
parent::__destruct();=0A=
}=0A=
*/=0A=
}=0A=
?>=0A=

------=_NextPart_000_0125_01CA5BE5.B81AC140
Content-Type: text/plain;
name="db.inc.php"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
filename="db.inc.php"

#----------------------------------------------------------- --------
#
# Confidential - Property of Lockdown Networks, Inc.
# Do not copy or distribute.
# Copyright 2002-2008 Lockdown Networks, Inc. All rights reserved.
#
#----------------------------------------------------------- --------

// I use this 'db.inc.php' for other modules, so that's why the =
host/user/pw are not in the globals.php file
// also, it's silly to use define() here since this is the only spot =
they're ever used.

$GLOBALS['__DB_HANDLE'] =3D false;

require_once('ironbars.php'); //[dv] why is this here? what uses it?

//TODO: [dv] we REALLY should make these all $SQLOPTION[] and update =
them in ALL files to avoid confusion and collisions...
$OPTION['host'] =3D '';
$OPTION['username'] =3D 'root';
$OPTION['password'] =3D '';
$OPTION['noHTML'] =3D false;
$OPTION['fullQuery'] =3D false;
$OPTION['useLogger'] =3D true;
$OPTION['profile'] =3D 0;
//$OPTION['outfile'] =3D false; //set this to a filename, and use =
$showSQL in your queries and they'll go to this file.

define ('MAX_SQL_ERRORS', 10);

if (!array_key_exists('autoConnect',$OPTION)) $OPTION['autoConnect'] =3D =
true;
if ($OPTION['autoConnect']) SQL_CONNECT("localhost");

/*
* We are (currently) trying to prevent just one trivial type of sql =
injection.
* Namely, the one that attempts to end query with a ; and then add an =
extra query=20
* to the end. This is a common technique, and the one that is easiest =
to detect.
*
* First, we watch for unbalanced quotes. If any are found, the query is =
invalid anyway
* and thus will not be allowed to run.
*
* Second, I can't think of a single valid use of a semicolon outside =
the literals=20
* enclosed into ''. Semicolons will be alloedd in those literals, but =
not outside.
*
* Single quotes that are in the literals and have been SQL_ESCAPE()'d =
are treated properly,
* that is as a single character within the literal. So are the =
backslashed-escaped chars.
*
* Any other additions are welcome, but this is at least a good start.
*
* @author Vlad Krupin [vlad@]
*/
function IS_SAFE_SQL_QUERY($q){
$len =3D strlen($q);
$inside =3D false; // inside a literal (enclosed by '')
$ret =3D true; // query assumed good unless we can prove otherwise.
for($i =3D 0; $i < $len; $i++)
{
$more =3D ($i < ($len - 1)); // we have at least one more character
=09
// CR3940 - we can't use the $q[$i] here because the bracket operator =
doesn't
// currently work with multibyte strings. Yuck.
switch( substr( $q, $i, 1 ) )=20
{
case "\\":
//[krogebry] Why would there be a test for '$inside' here?
// anything after a \ should be an escaped char, that's what \ =
does.
#if($inside && $more)
#{
$i++; // whatever follows MUST be an escaped character.
#continue;
#}
break;
=09
case "'":
// we are inside the string and came up with a properly-escaped quote
#if($inside && $more && ($q[$i+1] == "'")){
if($inside && $more && substr( $q, $i, $i - 1 ) == "\\" ){
$i++;
continue;
}
$inside =3D !$inside;
break;
=09
case ";":
// semicolons outside literals are not permitted.
if(!$inside) return "Possible chain query via semi-colon injection";
=09
//case "-":
// //testing for -- comments
// if (substr( $q, $i, 2 ) == '--') return "Possible '-- comment' =
injection.";
// break;
=09
}// switch()
}
if($inside) $ret =3D "Unbalanced single quotes";
=09
#print "Ret: [$ret]
\n";
return $ret;
}

/**
* Make a connection to a mysql db.
* @access public
* @author Daevid Vincent [daevid@]
*/
function SQL_CONNECT($server =3D 'localhost')
{
global $OPTION;
=09
$GLOBALS['__CONNECTED_SERVER'] =3D $server;
if (!$OPTION['username']) $OPTION['username'] =3D 'root';
if (!$OPTION['password']) $OPTION['password'] =3D '';
=09
$tries =3D 5;
for($i =3D 1; $i <=3D $tries; $i++)=20
{
switch ( strtolower($server) )
{
case "1":
case "localhost":
default:
$GLOBALS['__DB_HANDLE'] =3D @mysql_pconnect("localhost", =
$OPTION['username'], $OPTION['password']);
if (is_resource($GLOBALS['__DB_HANDLE'])) break 2;
}
=09
echo translate("Unable to connect to database. Retrying [%1\$s/%2\$s] =
in 5 seconds.\n", $i, $tries);
sleep(5);
}
=09
if (!is_resource($GLOBALS['__DB_HANDLE']))=20
{
echo translate("Could not connect to %1\$s server. Aborting.\n", =
$GLOBALS['__CONNECTED_SERVER']);
return false;
}
=09
// Set our connection, results, and client charsets to UTF-8
SQL_QUERY('SET NAMES utf8');
=09
//echo translate("Got __DB_HANDLE %1\$s", $GLOBALS['__DB_HANDLE']);
return $GLOBALS['__DB_HANDLE'];
}

/**
* Save the SQL connection object to a global area
* @access public
* @author Evan Webb [evan@]
*/
function SQL_SAVE_CONN() {
if(!isset($GLOBALS['__DB_HANDLES'])) {
$GLOBALS['__DB_HANDLES'] =3D array();
}

array_push($GLOBALS['__DB_HANDLES'],$GLOBALS['__DB_HANDLE']) ;
}

/**
* Reuse a stored connection
* @access public
* @author Evan Webb [evan@]
*/
function SQL_RESTORE_CONN($con=3Dnull) {
if(is_null($con)) {
$GLOBALS['__DB_HANDLE'] =3D array_pop($GLOBALS['__DB_HANDLES']);
} else {
$GLOBALS['__DB_HANDLE'] =3D $con;
}
}

/**
* Select a db
* @access public
* @author Daevid Vincent [daevid@]
*/
function SQL_DB($dbname, $exit =3D true)
{
if ( @mysql_select_db($dbname,$GLOBALS['__DB_HANDLE']) )
{
$GLOBALS['__CURRENT_DB'] =3D $dbname;
return true;
}
else=20
{
if ($exit == true)
exit("Could not connect to the '".$dbname."' Database.");
else
return false; //this is in case you want to do your own error =
handling.
}
}

/**
* Outputs the SQL to /tmp/SQL_profile.txt in detail.=20
*=20
* profile SQL statements in varying detail levels.
* Detail Levels:
* 1 =3D Y-m-d/ h:i:s
* 2 =3D SQL timing
* 3 =3D filename
*
* @access public
* @return boolean on success or failure.
* @param string $sql The SQL query to be executed, this can be SELECT, =
INSERT, UPDATE or DELETE amongst others.
* @param int $detail the detail level as an integer 1-3.
* @author Daevid Vincent [daevid@]
* @since 3.11
* @version 1.1
* @date 05/11/05
*/
function SQL_PROFILE($sql, $detail =3D 3 )
{
if ($detail == 0) return false;
if (!isset($sql)) return false;
=09
if (!$handle =3D fopen("/tmp/SQL_profile.txt", 'a'))=20
{
echo "unable to open file /tmp/SQL_profile.txt\n";
return false;
}

//not really required, as they're handled inherently
//if ($detail > 4) $detail =3D 4;
//if ($detail < 1) $detail =3D 1;

$text =3D date("[Y-m-d h:i:s ");

if ($detail >=3D 2) //start timer
{
list($usec, $sec) =3D explode(" ",microtime());=20
$sql_start =3D ((float)$usec + (float)$sec);
}

$result =3D @mysql_query($sql, $GLOBALS['__DB_HANDLE']);

if ($detail >=3D 2) //end timer
{
list($usec, $sec) =3D explode(" ",microtime());=20
$text .=3D number_format( (((float)$usec + (float)$sec) - $sql_start), =
4 ).'s';
}

//we do this here so as not to upset the timer too much
if ($detail >=3D 3)
{
$text .=3D ' '.$_SERVER['SCRIPT_FILENAME'];
$traceArray =3D debug_backtrace();
$text .=3D ' '.$traceArray[1]['file'].' ('.$traceArray[1]['line'].')';
$text =3D str_replace('/lockdown/', '', $text);
}

$sql =3D str_replace("\n", ' ', $sql);
$sql =3D preg_replace('/\s+/',' ', $sql);
if (!fwrite($handle, $text.'] '.$sql."\n"))=20
{
echo "unable to write to file /tmp/SQL_profile.txt\n";
return false;
}

@fclose($handle);

return $result;
} //SQL_PROFILE


/**
* Output the HTML debugging string in color coded glory for a sql query
* This is very nice for being able to see many SQL queries
* @access public
* @return void. prints HTML color coded string of the input $query.
* @param string $query The SQL query to be executed.
* @author Daevid Vincent [daevid@]
* @since 4.0
* @version 1.0
* @date 04/05/05
* @todo highlight SQL functions.
*/
function SQL_DEBUG( $query )
{
if( $query == '' ) return 0;

global $SQL_INT;
if( !isset($SQL_INT) ) $SQL_INT =3D 0;

//[dv] I like my version better...
//require_once('classes/geshi/geshi.php');
//$geshi =3D new GeSHi($query, 'sql');
//echo $geshi->parse_code();
//return;

//TODO: [dv] I wonder if a better way to do this is to split the string =
into array chunks and examine them each individually?
=09
//TODO: [dv] I think we'd get better results if we normalize the $query =
string by stripping out any \n\r characters:
$query =3D str_replace( array("\n", "\r", ' '), ' ', $query);
=09
//[dv] this has to come first or you will have goofy results later.
//[dv] UGH this number one is causing me lots of grief... why can't i =
figure out the regex to use?
//highlight numbers=20
//$query =3D preg_replace("/[\s=3D](\d+)\s/", " COLOR=3D'#FF6600'>$1", $query, -1);
//highlight strings between quote marks
$query =3D preg_replace("/(['\"])([^'\"]*)(['\"])/i", "$1 COLOR=3D'#FF6600'>$2$3", $query, -1);
//highlight functions
$query =3D preg_replace("/(\w+)\s?\(/", " COLOR=3D'#CC00FF'>".strtoupper('\\1')."(", $query, -1);
//underline tables/databases
$query =3D preg_replace("/(\w+)\./", "$1.", $query, -1);

$query =3D str_ireplace(
array (
'*',
'SELECT ',
'UPDATE ',
'DELETE ',
'INSERT ',
'INTO ',
'VALUES ',
'FROM ',
'LEFT ',
'JOIN ',
'WHERE ',
'LIMIT ',
'ORDER BY ',
'AND ',
'OR ', //[dv] note the space. otherwise you match to 'colOR' =
;-)
' DESC',
' ASC',
' ON ',
' AS '
),
array (
"*",
"SELECT ",
"UPDATE ",
"DELETE ",
"INSERT ",
"INTO ",
"VALUES ",
"FROM ",
"LEFT ",
"JOIN ",
"WHERE ",
"LIMIT ",
"ORDER BY ",
"AND ",
"OR ",
" DESC",
" ASC",
" ON ",
" AS "
),
$query
);

echo "DEBUG SQL[".$SQL_INT."]: =
".$query.";
\n";

$SQL_INT++;

} //SQL_DEBUG


/**
* A wrapper around the mysql_query function.=20
*=20
* Handles the $db handle, errors and echoing of the SQL query itself
* and stores any errors in the global variable errorString;
*
* @access public
* @return result set handle pointer suitable for.
* @param string $sql The SQL query to be executed, this can be SELECT, =
INSERT, UPDATE or DELETE amongst others.
* @param boolean $showSQL output the $sql to the display (for debugging =
purposes usually). false by default.
* @param boolean $showErrors output any errors encountered to the =
display (for debugging purposes usually). true by default.
* @param boolean $execute useful for debuging when you don't want the =
SQL command to actually execute, but you may want to see the query =
passed i.e. SQL_QUERY($sql, true, true, false); true by default.
* @param boolean $noHTML when using the function in console scripts to =
strip off HTML tags.
* @param int $profile detail level (1-3) to output the SQL to =
/tmp/SQL_profile.txt.
* @param int $count The counter used for recursion
* @param string $errorOutput What format the error message, if any, =
should be returned as. Can be txt, xml or html (default).
* @author Daevid Vincent [daevid@]
* @since 3.0
* @version 1.4
* @date 06/04/07
*/
function SQL_QUERY($sql, $showSQL =3D false, $showErrors =3D true, =
$execute =3D true, $noHTML =3D false, $profile =3D 0, $count =3D 0, =
$errorOutput =3D 'html')
{
global $OPTION;
=09
if ($showSQL)=20
{
//[dv] the preg_replace will magically strip out the spaces, newlines, =
tabs and other funky chars to make one nice string.
$sql =3D preg_replace("/\s+/",' ', (preg_replace("/\s/",' ',$sql)) );
=09
if ($OPTION['outfile'])
file_put_contents($OPTION['outfile'], 'SQL: '.$sql."\n", (FILE_APPEND =
| LOCK_EX) );
elseif ($noHTML || $OPTION['noHTML'])
echo "SQL: ".$sql."\n";
else
SQL_DEBUG( $sql );
}
=09
if ($execute)
{
//[dv] added to remove all comments (which may help with SQL =
injections as well.
//$sql =3D preg_replace("/#.*?[\r\n]/s", '', $sql);
//$sql =3D preg_replace("/--.*?[\r\n]/s", '', $sql);
//$sql =3D preg_replace("@/\*(.*?)\*/@s", '', $sql);
=09
// execute query only if it appears to be safe.
if ( ($error_str =3D IS_SAFE_SQL_QUERY($sql)) ===3D TRUE )
{
if ($OPTION['profile'] > 0) $profile =3D $OPTION['profile'];
=09
if ($profile > 0)
$result =3D SQL_PROFILE($sql, $profile);
else
$result =3D @mysql_query($sql,$GLOBALS['__DB_HANDLE']);
} else {
$error =3D "Malformed query (".$error_str."). Execution blocked.";
$result =3D FALSE; // indicate that we failed
}
=09
if (!$result)=20
{
if(!isset($GLOBALS['SQL_ErrorString'])) $GLOBALS['SQL_ErrorString'] =
=3D "";
=09
// if error has not been set, then we have a 'regular' mysql error. =
Otherwise it is a potentially malicious query.
if(!isset($error)){
$error =3D mysql_error($GLOBALS['__DB_HANDLE']);
$errno =3D mysql_errno($GLOBALS['__DB_HANDLE']);
=09
if(($errno == 2013)||($errno == 2006)) {
if($count > 20) {
logger("Maximum number of reconnect attempts =
exceeded, giving up.");
} else {
sleep(2);
if ($errno == 2013)
{
SQL_CONNECT($GLOBALS["__CONNECTED_SERVER"]);
}
elseif ($errno == 2006)
{
SQL_CLOSE();
SQL_CONNECT($GLOBALS["__CONNECTED_SERVER"]);
SQL_DB($GLOBALS['__CURRENT_DB'], false);
}
return SQL_QUERY($sql, $showSQL, $showErrors, =
$execute, $noHTML, $profile, $count + 1, $errorOutput);
}
}
}
else $errno =3D 0; // not 'regular' mysql error? well, we need some =
error code anyway.
=09
// get rid of needlessly verbose MySQL error string
$error =3D preg_replace( '/^You have an error in your SQL =
syntax;.*?near\s*/i', 'Syntax error near ', $error );

// trim to size if necessary
if(!$OPTION['fullQuery'] && strlen( $error ) > 100 )
$error =3D substr( $error, 0, 100 ) . "...";
=09
if ($showErrors && $errorOutput == 'xml' )
{
$GLOBALS['SQL_ErrorString'] .=3D '' . $errno . =
'' . "\r\n";
$GLOBALS['SQL_ErrorString'] .=3D '' . =
mb_htmlentities($error) . '' . "\r\n";
$GLOBALS['SQL_ErrorString'] .=3D '' . =
mb_htmlentities($sql) . '' . "\r\n";
$GLOBALS['SQL_ErrorString'] .=3D '' . =
mb_htmlentities(backtrace(false)) . '' . "\r\n";
echo $GLOBALS['SQL_ErrorString'];
}
elseif ($showErrors && ( $errorOutput == 'text' || $errorOutput =
== 'txt' ) )
{
$GLOBALS['SQL_ErrorString'] .=3D 'Error Code: ' . $errno . "\n";
$GLOBALS['SQL_ErrorString'] .=3D 'Error Message: ' . $error . =
"\n\n";
$GLOBALS['SQL_ErrorString'] .=3D 'Error SQL: ' . $sql . "\n\n";
$GLOBALS['SQL_ErrorString'] .=3D 'Backtrace: ' . backtrace( =
false, $errorOutput ) . "\n";
echo $GLOBALS['SQL_ErrorString'];
}
elseif ($showErrors)=20
{
$GLOBALS['SQL_ErrorString'] .=3D "SQL ERROR :: =
".$errno." :: ".$error."
SIZE=3D'-3'>".$sql."\n".backtrace(false);
=09
//TODO: [dv] is there a way to determine if we're in a CGI vs. Web =
page?
if ($noHTML or $OPTION['noHTML'])
echo strip_tags($GLOBALS['SQL_ErrorString'])."\n";
else
echo "

padding: 5px;'>
CLASS=3D'error'>".$GLOBALS['SQL_ErrorString']."

CLASS=3D'error'>" : '' ) . 'BACKTRACE' . ( $ouptut == 'html' ? =

"" : '' ) . " ::\n";

	$traceArr =3D debug_backtrace();

	array_shift($traceArr);

	$tabs =3D sizeof($traceArr)-1;

	foreach ($traceArr as $arr)=20

	{

		for ($i=3D0; $i < $tabs; $i++) $s .=3D ( $output == 'html' ? ' =

  ' : ' ' );

		$tabs -=3D 1;

		//$s .=3D "";

		if (isset($arr['class'])) $s .=3D $arr['class'].'.';

		if (isset($arr['args']) && is_array($arr['args']))

			foreach($arr['args'] as $v)=20

			{

				if (is_null($v)) $args[] =3D 'null';

				else if (is_array($v)) $args[] =3D 'Array['.sizeof($v).']';

				else if (is_object($v)) $args[] =3D 'Object:'.get_class($v);

				else if (is_bool($v)) $args[] =3D $v ? 'true' : 'false';

				else {=20

					$v =3D (string) @$v;

					$str =3D ( $output == 'html' ? =

htmlspecialchars(substr($v,0,$MAXSTRLEN)) : substr($v,0,$MAXSTRLEN) );

					if (strlen($v) > $MAXSTRLEN) $str .=3D '...';

					$args[] =3D $str;

				}

			}

	=09

		if (isset($arr['args']) && is_array($args))

			$s .=3D ( $output == 'html' ? '' : '' ) . $arr['function'] . =

'(' . ( $output == 'html' ? '' : '' ) . implode( ', ', $args ) . =

( $output == 'html' ? '' : '' ) . ')' . ( $output == 'html' ? =

'' : '' );

		//$s .=3D ( $output == 'html' ? '' : '' );

		if( $output == 'html' )

			$s .=3D sprintf(" :: line #%d,"." =

file: 
href=3D\"file:/%s\">%s",$arr['line'],$arr['file'],$arr['file']=

);

		else

			$s .=3D sprintf(" :: line #%d, file: %s", $arr['line'], $arr['file'] =

);

		$s .=3D "\n";

	}=20

	$s .=3D ( $output == 'html' ? "

Re: Custom function for inserting values into MySQL

I would take a look at some of the frameworks like codeignter to see
how they do things.

But like Davied mentioned a simpler way to handle the passing into the
function would be

Function save($table, $data)


Where data is an array of key value pairs which takes your 22
parameters down to 2.

The array could look like

$data = array('id' => 1, 'name' => 'bob' ...)

Bastien

Sent from my iPod

On Nov 2, 2009, at 8:32 PM, Allen McCabe wrote:

> Okay friends, I have been wondering about writing a simple function
> that
> will help me with my MySQL inserting. Not because I need to save
> time and
> space, but because I wanted to.
>
> I wrote a function for inserting 10 values (I have not been able to
> come up
> with an idea how to make the number of values I'm inserting
> variable, so I'm
> sticking with ten).
>
> This function takes 22 parameters: #1 is the table name, #2-21 are
> the row
> names and the values, and #22 is the "integar string".
>
> The first 21 parameters are self-explanatory, the 22nd is a string
> of values
> that need to be inserted as an integar, basically, not adding single
> quotes
> around the value. Eg. $value2 = 5, not $value2 = '5'.
>
> I am very hesitant to try this one out on my database, I've got
> tables of
> important information and don't want to, I don't know, inadvertantly
> throw a
> wrench into the works, AND I want to open up a dialoug about custom
> PHP
> functions for working with MySQL, for the fun of it!
>
> Here is my 10 value function for inserting data into a MySQL
> database table.
>
> function insertinto10($table, $field1, $value1, $field2, $value2,
> $field3,
> $value3, $field4, $value4, $field5, $value5, $field6, $value6,
> $field7,
> $value7, $field8, $value8, $field9, $value9, $field10, $value10,
> $int =
> NULL)
> {
> if (isset($int))
> {
> $sPattern = '/\s*/m';
> $sReplace = '';
> $int = preg_replace($sPattern, $sReplace, $int);
> $pieces = explode(",", $int); // $pieces[0], $pieces[1] - each
> equal to
> value numbers that are integars
> $length = count($pieces);
> // call custom function to create associative array eg. $newarray
> [2] = 1,
> $newarray[4] = 1, $newarray[5] = 1 . . .
> $integarArray = strtoarray($length, $int);
> }
>
> $valuesArray = array($value1, $value2, $value3, $value4, $value5,
> $value6,
> $value7, $value8, $value9, $value10);
>
> foreach ($valuesArray as $key => $value)
> {
> if (isset($integarArray[$key]) && $integarArray[$key] == 1)
> {
> // INTEGAR VALUE
> $valuesArray[$key] = mysql_real_escape_string(stripslashes($value));
> }
> else
> {
> // STRING VALUE
> $cleanValue = mysql_real_escape_string(stripslashes($value));
> $valuesArray[$key] = "'{$cleanValue}'";
> }
> }
>
> $result = mysql_query("INSERT INTO `{$table}` (`{$field1}`, `
> {$field2}`,
> `{$field3}`, `{$field4}`) VALUES ({$valuesArray[1]}, {$valuesArray
> [2]},
> {$valuesArray[3]}, {$valuesArray[4]}, {$valuesArray[5]},
> {$valuesArray[6]},
> {$valuesArray[7]}, {$valuesArray[8]}, {$valuesArray[9]},
> {$valuesArray[10]})");
> return $result;
> }
>
>
> You may find copying/pasting into your favorite code-editor helps
> make it
> more readable.
>
> Do you see any major hangups or screwups on first glance? And is my
> fear of
> trying this out on my database unfounded? Does this even seem that
> useful?

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Custom function for inserting values into MySQL

Allen McCabe wrote:
> Okay friends, I have been wondering about writing a simple function that
> will help me with my MySQL inserting. Not because I need to save time and
> space, but because I wanted to.
>
> I wrote a function for inserting 10 values (I have not been able to come up
> with an idea how to make the number of values I'm inserting variable, so I'm
> sticking with ten).
>
> This function takes 22 parameters: #1 is the table name, #2-21 are the row
> names and the values, and #22 is the "integar string".
>
> The first 21 parameters are self-explanatory, the 22nd is a string of values
> that need to be inserted as an integar, basically, not adding single quotes
> around the value. Eg. $value2 = 5, not $value2 = '5'.
>
> I am very hesitant to try this one out on my database, I've got tables of
> important information and don't want to, I don't know, inadvertantly throw a
> wrench into the works, AND I want to open up a dialoug about custom PHP
> functions for working with MySQL, for the fun of it!
>
> Here is my 10 value function for inserting data into a MySQL database table.
>
> function insertinto10($table, $field1, $value1, $field2, $value2, $field3,
> $value3, $field4, $value4, $field5, $value5, $field6, $value6, $field7,
> $value7, $field8, $value8, $field9, $value9, $field10, $value10, $int =
> NULL)
> {
> if (isset($int))
> {
> $sPattern = '/\s*/m';
> $sReplace = '';
> $int = preg_replace($sPattern, $sReplace, $int);
> $pieces = explode(",", $int); // $pieces[0], $pieces[1] - each equal to
> value numbers that are integars
> $length = count($pieces);
> // call custom function to create associative array eg. $newarray[2] = 1,
> $newarray[4] = 1, $newarray[5] = 1 . . .
> $integarArray = strtoarray($length, $int);
> }
>
> $valuesArray = array($value1, $value2, $value3, $value4, $value5, $value6,
> $value7, $value8, $value9, $value10);
>
> foreach ($valuesArray as $key => $value)
> {
> if (isset($integarArray[$key]) && $integarArray[$key] == 1)
> {
> // INTEGAR VALUE
> $valuesArray[$key] = mysql_real_escape_string(stripslashes($value));
> }
> else
> {
> // STRING VALUE
> $cleanValue = mysql_real_escape_string(stripslashes($value));
> $valuesArray[$key] = "'{$cleanValue}'";
> }
> }
>
> $result = mysql_query("INSERT INTO `{$table}` (`{$field1}`, `{$field2}`,
> `{$field3}`, `{$field4}`) VALUES ({$valuesArray[1]}, {$valuesArray[2]},
> {$valuesArray[3]}, {$valuesArray[4]}, {$valuesArray[5]}, {$valuesArray[6]},
> {$valuesArray[7]}, {$valuesArray[8]}, {$valuesArray[9]},
> {$valuesArray[10]})");
> return $result;
> }
>
>
> You may find copying/pasting into your favorite code-editor helps make it
> more readable.
>
> Do you see any major hangups or screwups on first glance? And is my fear of
> trying this out on my database unfounded? Does this even seem that useful?
>

I'll echo what the others have said about the parameters. For me
personally, if I am passing more than three parameters (sometimes even
three) I rethink my function. I'm not sure what you envision using this
function for, but the approach I use for forms and databases is always
arrays. I get an array from my forms, I insert that array into the
database, and of course I fetch arrays out of the database. These are
all indexed the same with the index as the field name of the table so
it's easy.


--
Thanks!
-Shawn
http://www.spidean.com

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Custom function for inserting values into MySQL

In your example, I would name my form inputs similar to name
="data[user_id]".

Then you just pass the $_POST['data'] array to your function.

-Shawn

Allen McCabe wrote:
> You raise some good points. I always name my input fields after the
> entity names ( eg. input type="hidden" name ="user_id" value=" ?php
> echo $resultRow['user_id'] ? " ).
>
> I suppose I am still in the phase of learning efficiency, and perhaps
> trying to 'get out it' by writing functions that I can just call and
> pass parameters instead of fully learning the core concepts.
>
> I just think functions are so damn cool :)
>
>
> I'll echo what the others have said about the parameters. For me
> personally, if I am passing more than three parameters (sometimes even
> three) I rethink my function. I'm not sure what you envision
> using this
> function for, but the approach I use for forms and databases is always
> arrays. I get an array from my forms, I insert that array into the
> database, and of course I fetch arrays out of the database. These are
> all indexed the same with the index as the field name of the table so
> it's easy.
>
>
> --
> Thanks!
> -Shawn
> http://www.spidean.com
>
>
>

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Custom function for inserting values into MySQL

Shawn McKenzie wrote:
> Allen McCabe wrote:
>>
>> Do you see any major hangups or screwups on first glance? And is my fear of
>> trying this out on my database unfounded? Does this even seem that useful?
>>

in all honesty.. loads of screwups - don't try it out on your database &
ultimately if it isn't re-usable then it isn't useful (and it's isn't
re-usable unless every single table you have is the same.. which they
aren't)

to be a bit more constructive though.. this is a road most developers
have been down, and well known solutions already exist.

You've got two choices..

1] continue down this route and learn as you go (but for god sake get a
test database) - recommended if you really want to learn not just PHP
but programming in general; once you understand it all you can go
looking at design patterns, common solutions and how other people do it
and have enough knowledge to make informed decisions.

2] just use what's made and don't think too much about it, you'll be
productive and can throw in support/help requests whenever it goes
wrong, works for some people.. to do this get a decent framework and
read it's manual (or use pdo, or an ORM for PHP or something)

all depends on what you want, how much time you have, and where you want
to end up.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Custom function for inserting values into MySQL

> -----Original Message-----
> From: Shawn McKenzie [mailto:nospam@mckenzies.net]
> Sent: Wednesday, November 04, 2009 6:20 AM
> To: Allen McCabe; PHP General
> Subject: Re: [PHP] Custom function for inserting values into MySQL
>
> In your example, I would name my form inputs similar to name
> ="data[user_id]".
>
> Then you just pass the $_POST['data'] array to your function.
>
> -Shawn
>
> Allen McCabe wrote:
> > You raise some good points. I always name my input fields after the
> > entity names ( eg. input type="hidden" name ="user_id" value=" ?php
> > echo $resultRow['user_id'] ? " ).
> >
> > I suppose I am still in the phase of learning efficiency,
> and perhaps
> > trying to 'get out it' by writing functions that I can just call and
> > pass parameters instead of fully learning the core concepts.
> >
> > I just think functions are so damn cool :)
> >
> >
> > I'll echo what the others have said about the
> parameters. For me
> > personally, if I am passing more than three parameters
> (sometimes even
> > three) I rethink my function. I'm not sure what you envision
> > using this
> > function for, but the approach I use for forms and
> databases is always
> > arrays. I get an array from my forms, I insert that
> array into the
> > database, and of course I fetch arrays out of the
> database. These are
> > all indexed the same with the index as the field name
> of the table so
> > it's easy.
> >
> >
> > --
> > Thanks!
> > -Shawn
> > http://www.spidean.com
> >
> >
> >

There are pro's and cons to this type of thing. In general that is how I do
it too, but you have to be aware of security and organization. It's not
always smart to expose your DB field names directly so you might want to
obscure them for some critical values. If your passing from one controlled
function/method to another then this isnt an issue so much.

I also follow the ruby/rails ideal where tables are plural names ("users")
and classes are singular names ("user.class.php"). Tables always have fields
for 'id','created_on','timestamp','enabled'. Except in 'glue table' cases
(1:n or n:m). Classes extend a base class which handles a lot of the minutea
including the magic __get() and __set() routines as well as knowing what
table they should be through introspection (ie. Their own file name).

No need to name your fields as arrays. $_POST is already an array. You've
just added more complexity/dimensions. When you submit your form just pass
$_POST to your function instead. In the function, is where you should do any
normalizing, scrubbing and unsetting (as per good MVC ideology)...

In your page form:

if ($_POST['submit'] == 'Update')
{
$result = process_data($_POST);
}

Then in some include file somewhere (here is a simplified example of
course):

function process_data($data)
{
//perhaps you don't care about the submit button
unset($data['submit']);

//maybe you don't want everyone to know your DB schema
//so you re-map from form element names to DB fields...
$data['user_id'] = $data['uid'];
unset($data['uid']);

//strip white space off
foreach ($data as $k => $v) $data[$k] = trim($v);

//do validity checking of each important data item
if (intval($data['user_id']) < 1) return false;

//any other pre-processing

//do interesting stuff here with scrubbed $data array now
sql_query('UPDATE mytable SET ...... WHERE user_id = '.$data['user_id'].'
LIMIT 1');

//of course, I would use a routine that builds the update / insert
statements from
//the array key/value pairs -- see previous attached example
base.class.php in this thread.
}

http://daevid.com


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Custom function for inserting values into MySQL

Daevid Vincent wrote:
>> -----Original Message-----
>> From: Shawn McKenzie [mailto:nospam@mckenzies.net]
>> Sent: Wednesday, November 04, 2009 6:20 AM
>> To: Allen McCabe; PHP General
>> Subject: Re: [PHP] Custom function for inserting values into MySQL
>>
>> In your example, I would name my form inputs similar to name
>> ="data[user_id]".
>>
>> Then you just pass the $_POST['data'] array to your function.
>>
>> -Shawn
>>
>> Allen McCabe wrote:
>>> You raise some good points. I always name my input fields after the
>>> entity names ( eg. input type="hidden" name ="user_id" value=" ?php
>>> echo $resultRow['user_id'] ? " ).
>>>
>>> I suppose I am still in the phase of learning efficiency,
>> and perhaps
>>> trying to 'get out it' by writing functions that I can just call and
>>> pass parameters instead of fully learning the core concepts.
>>>
>>> I just think functions are so damn cool :)
>>>
>>>
>>> I'll echo what the others have said about the
>> parameters. For me
>>> personally, if I am passing more than three parameters
>> (sometimes even
>>> three) I rethink my function. I'm not sure what you envision
>>> using this
>>> function for, but the approach I use for forms and
>> databases is always
>>> arrays. I get an array from my forms, I insert that
>> array into the
>>> database, and of course I fetch arrays out of the
>> database. These are
>>> all indexed the same with the index as the field name
>> of the table so
>>> it's easy.
>>>
>>>
>>> --
>>> Thanks!
>>> -Shawn
>>> http://www.spidean.com
>>>
>>>
>>>
>
> There are pro's and cons to this type of thing. In general that is how I do
> it too, but you have to be aware of security and organization. It's not
> always smart to expose your DB field names directly so you might want to
> obscure them for some critical values. If your passing from one controlled
> function/method to another then this isnt an issue so much.
>
> I also follow the ruby/rails ideal where tables are plural names ("users")
> and classes are singular names ("user.class.php"). Tables always have fields
> for 'id','created_on','timestamp','enabled'. Except in 'glue table' cases
> (1:n or n:m). Classes extend a base class which handles a lot of the minutea
> including the magic __get() and __set() routines as well as knowing what
> table they should be through introspection (ie. Their own file name).
>
> No need to name your fields as arrays. $_POST is already an array. You've
> just added more complexity/dimensions. When you submit your form just pass
> $_POST to your function instead. In the function, is where you should do any
> normalizing, scrubbing and unsetting (as per good MVC ideology)...
>

The way I normally do it I learned from the CakePHP framework which is
very similar to (I think an attempt at a clone of) Rails. I'm not sure
if they do it the same way in Rails, but as you were mentioning, in a
Cake view of a form they use the table name as the array name
(name="Users[username]"). Internally to the framework this may make
things easier, but imagine you have a page with 2 or more forms that
update different tables, or if your form had some fields that you wanted
to check after submission but are not DB fields. Why would you use the
entire POST array?

--
Thanks!
-Shawn
http://www.spidean.com

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Custom function for inserting values into MySQL

> -----Original Message-----
> From: Shawn McKenzie [mailto:nospam@mckenzies.net]
> Sent: Wednesday, November 04, 2009 4:59 PM
> To: Daevid Vincent
> Cc: 'Allen McCabe'; 'PHP General'
> Subject: Re: [PHP] Custom function for inserting values into MySQL
>
> Daevid Vincent wrote:
> >> -----Original Message-----
> >> From: Shawn McKenzie [mailto:nospam@mckenzies.net]
> >> Sent: Wednesday, November 04, 2009 6:20 AM
> >> To: Allen McCabe; PHP General
> >> Subject: Re: [PHP] Custom function for inserting values into MySQL
> >>
> >> In your example, I would name my form inputs similar to name
> >> ="data[user_id]".
> >>
> >> Then you just pass the $_POST['data'] array to your function.
> >>
> >> -Shawn
> >>
> >> Allen McCabe wrote:
> >>> You raise some good points. I always name my input fields
> after the
> >>> entity names ( eg. input type="hidden" name ="user_id"
> value=" ?php
> >>> echo $resultRow['user_id'] ? " ).
> >>>
> >>> I suppose I am still in the phase of learning efficiency,
> >> and perhaps
> >>> trying to 'get out it' by writing functions that I can
> just call and
> >>> pass parameters instead of fully learning the core concepts.
> >>>
> >>> I just think functions are so damn cool :)
> >>>
> >>>
> >>> I'll echo what the others have said about the
> >> parameters. For me
> >>> personally, if I am passing more than three parameters
> >> (sometimes even
> >>> three) I rethink my function. I'm not sure what you envision
> >>> using this
> >>> function for, but the approach I use for forms and
> >> databases is always
> >>> arrays. I get an array from my forms, I insert that
> >> array into the
> >>> database, and of course I fetch arrays out of the
> >> database. These are
> >>> all indexed the same with the index as the field name
> >> of the table so
> >>> it's easy.
> >>>
> >>>
> >>> --
> >>> Thanks!
> >>> -Shawn
> >>> http://www.spidean.com
> >>>
> >>>
> >>>
> >
> > There are pro's and cons to this type of thing. In general
> that is how I do
> > it too, but you have to be aware of security and
> organization. It's not
> > always smart to expose your DB field names directly so you
> might want to
> > obscure them for some critical values. If your passing from
> one controlled
> > function/method to another then this isnt an issue so much.
> >
> > I also follow the ruby/rails ideal where tables are plural
> names ("users")
> > and classes are singular names ("user.class.php"). Tables
> always have fields
> > for 'id','created_on','timestamp','enabled'. Except in
> 'glue table' cases
> > (1:n or n:m). Classes extend a base class which handles a
> lot of the minutea
> > including the magic __get() and __set() routines as well as
> knowing what
> > table they should be through introspection (ie. Their own
> file name).
> >
> > No need to name your fields as arrays. $_POST is already an
> array. You've
> > just added more complexity/dimensions. When you submit your
> form just pass
> > $_POST to your function instead. In the function, is where
> you should do any
> > normalizing, scrubbing and unsetting (as per good MVC ideology)...
> >
>
> The way I normally do it I learned from the CakePHP framework which is
> very similar to (I think an attempt at a clone of) Rails.
> I'm not sure
> if they do it the same way in Rails, but as you were mentioning, in a
> Cake view of a form they use the table name as the array name
> (name="Users[username]"). Internally to the framework this may make
> things easier, but imagine you have a page with 2 or more forms that
> update different tables, or if your form had some fields that
> you wanted to check after submission but are not DB fields.

The $_POST array will ONLY contain the key/values for the FORM that
contained the submit button.

Re: Custom function for inserting values into MySQL

Daevid Vincent wrote:
>
>
>> -----Original Message-----
>> From: Shawn McKenzie [mailto:nospam@mckenzies.net]
>> Sent: Wednesday, November 04, 2009 4:59 PM
>> To: Daevid Vincent
>> Cc: 'Allen McCabe'; 'PHP General'
>> Subject: Re: [PHP] Custom function for inserting values into MySQL
>>
>> Daevid Vincent wrote:
>>>> -----Original Message-----
>>>> From: Shawn McKenzie [mailto:nospam@mckenzies.net]
>>>> Sent: Wednesday, November 04, 2009 6:20 AM
>>>> To: Allen McCabe; PHP General
>>>> Subject: Re: [PHP] Custom function for inserting values into MySQL
>>>>
>>>> In your example, I would name my form inputs similar to name
>>>> ="data[user_id]".
>>>>
>>>> Then you just pass the $_POST['data'] array to your function.
>>>>
>>>> -Shawn
>>>>
>>>> Allen McCabe wrote:
>>>>> You raise some good points. I always name my input fields
>> after the
>>>>> entity names ( eg. input type="hidden" name ="user_id"
>> value=" ?php
>>>>> echo $resultRow['user_id'] ? " ).
>>>>>
>>>>> I suppose I am still in the phase of learning efficiency,
>>>> and perhaps
>>>>> trying to 'get out it' by writing functions that I can
>> just call and
>>>>> pass parameters instead of fully learning the core concepts.
>>>>>
>>>>> I just think functions are so damn cool :)
>>>>>
>>>>>
>>>>> I'll echo what the others have said about the
>>>> parameters. For me
>>>>> personally, if I am passing more than three parameters
>>>> (sometimes even
>>>>> three) I rethink my function. I'm not sure what you envision
>>>>> using this
>>>>> function for, but the approach I use for forms and
>>>> databases is always
>>>>> arrays. I get an array from my forms, I insert that
>>>> array into the
>>>>> database, and of course I fetch arrays out of the
>>>> database. These are
>>>>> all indexed the same with the index as the field name
>>>> of the table so
>>>>> it's easy.
>>>>>
>>>>>
>>>>> --
>>>>> Thanks!
>>>>> -Shawn
>>>>> http://www.spidean.com
>>>>>
>>>>>
>>>>>
>>> There are pro's and cons to this type of thing. In general
>> that is how I do
>>> it too, but you have to be aware of security and
>> organization. It's not
>>> always smart to expose your DB field names directly so you
>> might want to
>>> obscure them for some critical values. If your passing from
>> one controlled
>>> function/method to another then this isnt an issue so much.
>>>
>>> I also follow the ruby/rails ideal where tables are plural
>> names ("users")
>>> and classes are singular names ("user.class.php"). Tables
>> always have fields
>>> for 'id','created_on','timestamp','enabled'. Except in
>> 'glue table' cases
>>> (1:n or n:m). Classes extend a base class which handles a
>> lot of the minutea
>>> including the magic __get() and __set() routines as well as
>> knowing what
>>> table they should be through introspection (ie. Their own
>> file name).
>>> No need to name your fields as arrays. $_POST is already an
>> array. You've
>>> just added more complexity/dimensions. When you submit your
>> form just pass
>>> $_POST to your function instead. In the function, is where
>> you should do any
>>> normalizing, scrubbing and unsetting (as per good MVC ideology)...
>>>
>> The way I normally do it I learned from the CakePHP framework which is
>> very similar to (I think an attempt at a clone of) Rails.
>> I'm not sure
>> if they do it the same way in Rails, but as you were mentioning, in a
>> Cake view of a form they use the table name as the array name
>> (name="Users[username]"). Internally to the framework this may make
>> things easier, but imagine you have a page with 2 or more forms that
>> update different tables, or if your form had some fields that
>> you wanted to check after submission but are not DB fields.
>
> The $_POST array will ONLY contain the key/values for the FORM that
> contained the submit button.
>
>

RE: Custom function for inserting values into MySQL

> -----Original Message-----
> From: Shawn McKenzie [mailto:nospam@mckenzies.net]
> Sent: Thursday, November 05, 2009 6:14 AM
> To: Daevid Vincent
> Cc: 'Allen McCabe'; 'PHP General'
> Subject: Re: [PHP] Custom function for inserting values into MySQL
>
> Daevid Vincent wrote:
> >
> >
> >> -----Original Message-----
> >> From: Shawn McKenzie [mailto:nospam@mckenzies.net]
> >> Sent: Wednesday, November 04, 2009 4:59 PM
> >> To: Daevid Vincent
> >> Cc: 'Allen McCabe'; 'PHP General'
> >> Subject: Re: [PHP] Custom function for inserting values into MySQL
> >>
> >> Daevid Vincent wrote:
> >>>> -----Original Message-----
> >>>> From: Shawn McKenzie [mailto:nospam@mckenzies.net]
> >>>> Sent: Wednesday, November 04, 2009 6:20 AM
> >>>> To: Allen McCabe; PHP General
> >>>> Subject: Re: [PHP] Custom function for inserting values
> into MySQL
> >>>>
> >>>> In your example, I would name my form inputs similar to name
> >>>> ="data[user_id]".
> >>>>
> >>>> Then you just pass the $_POST['data'] array to your function.
> >>>>
> >>>> -Shawn
> >>>>
> >>>> Allen McCabe wrote:
> >>>>> You raise some good points. I always name my input fields
> >> after the
> >>>>> entity names ( eg. input type="hidden" name ="user_id"
> >> value=" ?php
> >>>>> echo $resultRow['user_id'] ? " ).
> >>>>>
> >>>>> I suppose I am still in the phase of learning efficiency,
> >>>> and perhaps
> >>>>> trying to 'get out it' by writing functions that I can
> >> just call and
> >>>>> pass parameters instead of fully learning the core concepts.
> >>>>>
> >>>>> I just think functions are so damn cool :)
> >>>>>
> >>>>>
> >>>>> I'll echo what the others have said about the
> >>>> parameters. For me
> >>>>> personally, if I am passing more than three parameters
> >>>> (sometimes even
> >>>>> three) I rethink my function. I'm not sure what
> you envision
> >>>>> using this
> >>>>> function for, but the approach I use for forms and
> >>>> databases is always
> >>>>> arrays. I get an array from my forms, I insert that
> >>>> array into the
> >>>>> database, and of course I fetch arrays out of the
> >>>> database. These are
> >>>>> all indexed the same with the index as the field name
> >>>> of the table so
> >>>>> it's easy.
> >>>>>
> >>>>>
> >>>>> --
> >>>>> Thanks!
> >>>>> -Shawn
> >>>>> http://www.spidean.com
> >>>>>
> >>>>>
> >>>>>
> >>> There are pro's and cons to this type of thing. In general
> >> that is how I do
> >>> it too, but you have to be aware of security and
> >> organization. It's not
> >>> always smart to expose your DB field names directly so you
> >> might want to
> >>> obscure them for some critical values. If your passing from
> >> one controlled
> >>> function/method to another then this isnt an issue so much.
> >>>
> >>> I also follow the ruby/rails ideal where tables are plural
> >> names ("users")
> >>> and classes are singular names ("user.class.php"). Tables
> >> always have fields
> >>> for 'id','created_on','timestamp','enabled'. Except in
> >> 'glue table' cases
> >>> (1:n or n:m). Classes extend a base class which handles a
> >> lot of the minutea
> >>> including the magic __get() and __set() routines as well as
> >> knowing what
> >>> table they should be through introspection (ie. Their own
> >> file name).
> >>> No need to name your fields as arrays. $_POST is already an
> >> array. You've
> >>> just added more complexity/dimensions. When you submit your
> >> form just pass
> >>> $_POST to your function instead. In the function, is where
> >> you should do any
> >>> normalizing, scrubbing and unsetting (as per good MVC ideology)...
> >>>
> >> The way I normally do it I learned from the CakePHP
> framework which is
> >> very similar to (I think an attempt at a clone of) Rails.
> >> I'm not sure
> >> if they do it the same way in Rails, but as you were
> mentioning, in a
> >> Cake view of a form they use the table name as the array name
> >> (name="Users[username]"). Internally to the framework
> this may make
> >> things easier, but imagine you have a page with 2 or more
> forms that
> >> update different tables, or if your form had some fields that
> >> you wanted to check after submission but are not DB fields.
> >
> > The $_POST array will ONLY contain the key/values for the FORM that
> > contained the submit button.
> >
> >