RE: [PHP] Custom function for inserting values into MySQL
-Original Message- From: Shawn McKenzie [mailto:nos...@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:nos...@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:nos...@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. form name=form_add input type=text name=foo value=bar input type=submit name=action value=Add /form form name=form_update input type=text name=bee value=boo input type=submit name=action value=Update /form So if you click the 'Add' button, you get back: $_POST['foo'] = 'bar', $_POST['action'] = 'Add' if you click the 'Update' button, you get back: $_POST['bee'] = 'boo', $_POST['action'] = 'Update' where's the confusion? You can only submit one form on a page at a time. Why would you use the entire POST array? Presumably, anything in the form is of some value to your database and you'd want it. Otherwise why is it in the form? I guess I was going for multiple tables and not multiple forms. Consider a form that takes input for a Users table and a Groups table. As for the inputs not needed by the DB, there are too many examples I could give with lots of inputs, but here is the simplest example I can think of: username password captcha rememberme Presumably you don't need
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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] 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: [PHP] Custom function for inserting values into MySQL
-Original Message-
From: Shawn McKenzie [mailto:nos...@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: [PHP] 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: [PHP] Custom function for inserting values into MySQL
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
?php
#---
#
# Confidential - Property of Lockdown Networks, Inc.
# Do not copy or distribute.
# Copyright 2002-2008 Lockdown Networks, Inc. All rights reserved.
#
#---
require_once('global.inc.php');
require_once('error.class.php');
class baseClass
{
protected $db = 'V2_Data';
protected $table= NULL;
protected $id = NULL;
protected $created_on = NULL;
protected $_stamp = NULL;
protected $enabled = TRUE;
//we use generic __call __get and __set, but this is a special case.
function get_stamp(){ return $this-_stamp; }
function set_stamp($stamp) { $this-_stamp = $stamp; }
/**
* Constructor
*
* @access public
* @return object
* @parammixed $id the ID of the object to load from the
database (this could be a string or usually an integer)
* @author Daevid Vincent [dae...@]
* @version 1.2
* @date 09/20/07
*/
function __construct($id = NULL)
{
if ($_SESSION['companydb']) $this-db = $_SESSION['companydb'];
//this follows the Ruby way for ease of porting/sharring,
please stick with the convention.
if (is_null($this-table) preg_match( '/y$/',
$this-getClassname() ) 0)
$this-table = strtolower(preg_replace( '/y$/', 'ies',
$this-getClassName() ));
elseif( is_null( $this-table ) )
$this-table = strtolower($this-getClassName()).'s';
if (!is_null($id)) $this-load($id);
}
/**
* generate a key/value pair from the class' variables.
*
* @access public
* @return array
* @author Daevid Vincent [dae...@]
* @version 1.0
* @date 08/13/07
*/
public function get_array()
{
$row = array();
foreach($this as $key = $value)
$row[$key] = $value;
$row['enabled'] = ($this-enabled) ? 1 : 0;
return $row;
}
/**
* set the class' values based upon a SQL query.
*
* Note: Usually this is called by an extension class,
* which in turn calls the parent::load_from_sql()
* which generates an array and then calls
load_from_array()
*
* @access public
* @return array or false
* @paramint $id ID of the object to load
* @author Daevid Vincent [dae...@]
* @version 1.0
* @date 08/20/07
* @see load_from_array()
*/
function load($id = null)
{
if (intval($id) 1) return false;
$sql = SELECT *
FROM.$this-db...$this-table.
WHERE id = '.SQL_ESCAPE($id).';
$result = $this-load_from_sql($sql); //LIMIT 1 is appended by
base class
if ($result)
return $result;
else
throw
Re: [PHP] 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 allenmcc...@gmail.com 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
