Usually I build my queries dynamically.
I store all the values into a hash ( this is in perl mind you.. )
Then do something like this..
my (@fields, @vals);
my ($field_str, $val_str);
foreach $field_name (keys %hash)
{
if ($hash{$field_name})
{
push @fields, $field_name;
push @vals, $hash{$field_name};
}
}
$field_str = join(",",@fields);
$val_str = join("','",@vals);
$val_str = "'" . $val_str . "'";
$dbh->do("INSERT into TABLE ( $field_str ) VALUES ( $val_str )");
That's a rather simple ver of it.. you'd probably want to insert some
type of data integrity checking in there ( or before you store the
values in the hash. ) and it'd be wise to have some other error checking
too, but it avoids having to deal with NULL vals.
Hope it helps.
--
sh
On Fri, 2002-04-12 at 19:52, Carl Schmidt wrote:
> Thank you for the information. Let me please clarify that i am using a
> web form. I tried the one solution of converting those values that are
> not set to null, but mysql complained:
> SQL: INSERT INTO
>
>Development_Event_Show(showID,venueID,description,ageCutoff,ageDelimiter,price1,price2)
> VALUES('201423cb79b72b5e11','41663c7c59b77da26','',21,,21,)
> SQL Error: You have an error in your SQL syntax near '21,)' at line 1
>
> Notice the pair of double commas and the one comma at the end? Thats the
> result of setting those values to null. I thought maybe dynmically
> building the query would be best because that way:
> 1.)those values that are to be set as null, simply could be left out of
> the insert statement.
> 2.)If each timeI returned a row where values were = -1, then i would have
> to add extra logic in ther presentation layer to have those values not be
> displayed.
>
> Any thoughts?
>
> Carl
>
>
> On Fri, 12 Apr 2002, Christopher Thompson wrote:
>
> > On Friday 12 April 2002 8:10 pm, Carl Schmidt wrote:
> > > I have a form where a user enters some numbers into text boxes. Some of
> > > the text boxes can be left blank. The business logic receives all
> > > variables to all text boxes. Should I :
> >
> > I'm assuming that you are using an executable file and NOT a web form. Any
> > data you get from a web form, you must treat as suspect and perform business
> > logic checks in another tier.
> >
> > > 1.) Validate on the page for the presence of a value, and if not, set the
> > > corresponding value to -1 (it will never be this value). Therefore the
> > > business logic blindly inserts everything.
> >
> > This is reasonable. Alternatively, set the corresponding value to NULL.
> >
> > > 2.) Do not have the variables that correspond to blank text boxes be
> > > set to -1. Have the business logic build a query based on which variables
> > > are or
> > > are not containing a value. In this case some fields for that row may be
> > > null.
> >
> > I'd avoid this if you can. Too much dynamicness and it will be hard to test.
> >
> > sql,query.
> >
> > ---------------------------------------------------------------------
> > Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail <[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
