Programmatic approach to string construction
Scott Johnson
I am looking for some opinions or practical approach to constructing a
string programmatically.
What I am doing is taking a boat load of post variables and assembling
an sql statement for insert(replace).
The needed post variables have the following data.
$_POST['id-(integer of item matching DB id)'] = (value to insert)
e.g. id-52487 = 4521254
Not all the POST vars are going to be used in the sql.
Here is my basic structure minus error & injection checking for clarity
$columns = '';
$value = '';
//step thru the post vars
foreach($_POST as $key=>$value) {
// Check if needed post var starts with 'id-'
if(strcmp(substr($key,0,3),'id-') == 0) {
// Now get rid of the 'id-'
$id = explode('id-',$key);
$columns .= $id[1].",";
$values .= "'".$value."',";
}
}
$replace = "REPLACE INTO DB (".$columns.") VALUES (".$values.")";
Now the problem I am running into is the extra ',' comma at the end of
each $columns and $values string which mysql is gonna choke on.
I am thinking I can do this one of 2 ways. Not put it in there in the
first place or take it out once it's in.
The taking out part is easy enough with some string manipulation but it
seems like a bastardized way of doing it.
I would like to not even put it in there, but I cannot figure out how.
I can't get an array size to eliminate it on the last one since the last
one might not be an 'id-' one, unless I create a separate array of
needed var and then process them.
I have to assume a few of you have had this issue in the past and would
be interested on how you handled it.
Thanks
Scotty
"Álvaro G. Vicario"
Or you could just use arrays and then implode().
> The taking out part is easy enough with some string manipulation but it
> seems like a bastardized way of doing it.
>
> I would like to not even put it in there, but I cannot figure out how.
> I can't get an array size to eliminate it on the last one since the last
> one might not be an 'id-' one, unless I create a separate array of
> needed var and then process them.
>
> I have to assume a few of you have had this issue in the past and would
> be interested on how you handled it.
--
-- http://alvaro.es - �lvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programaci�n web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--
Roger
put the commas first, if required, ie.
$columns = '';
$value = '';
//step thru the post vars
foreach($_POST as $key=>$value) {
// Check if needed post var starts with 'id-'
if(strcmp(substr($key,0,3),'id-') == 0) {
// Now get rid of the 'id-'
$id = explode('id-',$key);
if ($columns <> '') {
$columns .= ",";
$values .= ",";
}
$columns .= $id[1];;
$values .= "'".$value."'";
C. (http://symcbean.blogspot.com/)
<alvaro.NOSPAMTH...@demogracia.com.invalid> wrote:
> Scott Johnson escribió:
>
>
>
>
> > $columns = '';
> > $value = '';
> > //step thru the post vars
> > foreach($_POST as $key=>$value) {
> > // Check if needed post var starts with 'id-'
> > if(strcmp(substr($key,0,3),'id-') == 0) {
> > // Now get rid of the 'id-'
> > $id = explode('id-',$key);
> > $columns .= $id[1].",";
> > $values .= "'".$value."',";
> > }
> > }
> > $replace = "REPLACE INTO DB (".$columns.") VALUES (".$values.")";
>
> > Now the problem I am running into is the extra ',' comma at the end of
> > each $columns and $values string which mysql is gonna choke on.
>
> > I am thinking I can do this one of 2 ways. Not put it in there in the
> > first place or take it out once it's in.
>
> Or you could just use arrays and then implode().
>
> > The taking out part is easy enough with some string manipulation but it
> > seems like a bastardized way of doing it.
>
Better yet, normalize your database schema - then its a dawdle.
(Another good reason for using relational databases properly)
C.
Jerry Stuckle
Don't put it in there. �lvaro's idea of using an array is good. If you
don't want to do that, use a flag and add the comma at the beginning if
not the first time through, i.e.
$first = true;
$columns = '';
$value = '';
//step thru the post vars
foreach($_POST as $key=>$value) {
// Check if needed post var starts with 'id-'
if(strcmp(substr($key,0,3),'id-') == 0) {
// Now get rid of the 'id-'
$id = explode('id-',$key);
if ($first)
$first = false;
else
$columns .= ', ';
$columns .= $id[1];
$values .= "'".$value."',";
}
}
$replace = "REPLACE INTO DB (".$columns.") VALUES (".$values.")";
Bear in mind that your current code is very unsafe. Keys coming from
the user can be changed by the user, allowing a hacker to change any id
in your database.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================
Captain Paralytic
> ==================
Whilst I agree that Alvaro's option is the best, when I do have this
sort of situation, I avoid having to test a flag at each stage by
always pre-pending the comma and then using a simple substr($columns,
1).
Jerry Stuckle
That works well, also, Paul.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================
Scott Johnson
> On Nov 6, 8:53 am, "�lvaro G. Vicario"
> <alvaro.NOSPAMTH...@demogracia.com.invalid> wrote:
>> Scott Johnson escribi�:
C.
I am not sure what you mean, can you explain? The table I am using has
only 2 fields, not too sure how much normalized that can be?
Thanks
Scotty
Scott Johnson
<snipped>
>
> put the commas first, if required, ie.
>
> $columns = '';
> $value = '';
> //step thru the post vars
> foreach($_POST as $key=>$value) {
> // Check if needed post var starts with 'id-'
> if(strcmp(substr($key,0,3),'id-') == 0) {
> // Now get rid of the 'id-'
> $id = explode('id-',$key);
> if ($columns <> '') {
> $columns .= ",";
> $values .= ",";
> }
>
> $columns .= $id[1];;
> $values .= "'".$value."'";
> }
>
>
> }
>
>
> $replace = "REPLACE INTO DB (".$columns.") VALUES (".$values.")";
This is one of those 'DUH' moments for me..:)
That makes perfect sense, thanks for the solution.
Scotty
Scott Johnson
I love it when 3 people who's views I respect agree. Makes my decision
so much easier.
Thanks all very much for taking the time for the replies.
Scotty
Scott Johnson
Thanks for the input.
Yeah I always do injection cleaning before usage. I just left it out for
easier to read code.
But I do appreciate the sanity check just in case. :)
Scotty
Scott Johnson
OK Never mind, I completely blew this procedure.
I was treating this as if each id had a column and it did not.
I should of been inserting the items individually.
You are correct in your analysis of what I brought to the table, however
what I was trying to accomplish was wrong.
Thanks for the insight.
But all the replies I did receive are in my little notebook for future use.
Scotty
C. (http://symcbean.blogspot.com/)
> C. (http://symcbean.blogspot.com/) wrote:
>
> > (Another good reason for using relational databases properly)
>
> > C.
>
> C.
>
> I am not sure what you mean, can you explain? The table I am using has
> only 2 fields, not too sure how much normalized that can be?
>
Then why loop through EVERY POST var and not explicitly dereference
the ones which map to the table - there are a lot of unanswered
questions here - but the more information you provide, the more your
approach looks to be the wrong one.
C.
crim...@googlemail.com
> $columns = '';
> $value = '';
> //step thru the post vars
> foreach($_POST as $key=>$value) {
> // Check if needed post var starts with 'id-'
> if(strcmp(substr($key,0,3),'id-') == 0) {
> // Now get rid of the 'id-'
> $id = explode('id-',$key);
> $columns .= $id[1].",";
> $values .= "'".$value."',";
> }}
>
> $replace = "REPLACE INTO DB (".$columns.") VALUES (".$values.")";
Just for fun - functions-only solution:
$a=array_intersect_ukey($_POST,$_POST,create_function
('$key1,$key2','return !preg_match("/^id-.+$/",$key1);'));
$columns=join(',',array_map(create_function('$v','return substr($v,
3);'),array_keys($a)));
$values =join(',',array_map(create_function('$v','return
"\'".addslashes($v)."\'";'),$a));
Scott Johnson
Yeah it was the wrong approach. I got ahead of myself in writing the
code before I thought it out.
Scotty