can't get the INSERT sql to add new database entries, stops at 1...

[spewn]

here's my code:

#!/usr/bin/perl
use CGI qw(:standard);
use CGI::Carp 'fatalsToBrowser';
use DBI;
$cgi = new CGI;
$dbh = DBI->connect("DBI:mysql:myDB:localhost","uname","pass");
$xname = $cgi->param('oName');
$xclink = $cgi->param('cLink');
$xglink = $cgi->param('gLink');
$xdesc = $cgi->param('oDesc');
$xinfo = $cgi->param('oInfo');
$xpaid = $cgi->param('aPaid');
$xprog = $cgi->param('aProg');
$sth2 = $dbh->prepare("INSERT INTO offers VALUES(0,'$xname','','$xclink','$xglink','$xdesc','$xinfo','','','$xpaid','$xprog','','')");
$sth2->execute;
print << "EOF";
Location: db-add.html
EOF

if the database is empty, it will add the first one...but will not add anything other than that.

any ideas?

the first field is an INT, and is the primary key, 7 digits long.

thanks!

- g

 

[spewn]

nevermind. i didn't have the primary key on auto increment.

rookie mistake.

busch league.

- g

 

[MillerH]

=)

Glad you figured i out. However, there are a few other pieces of advice that I would give you.

[ol]
[li]Always use "use strict;" and declare your variables. Always.[/li]
[li]I strongly advise you to use placeholders when working with a database. It's just a lot easier and safer if you let DBI handle all of the escaping and security for you.[/li]
[li]Include die statements for your DBI operations. It already handles the generation of error messages, so you might as well let it help you with a die on error.[/li]
[li]Finally, your INSERT statement currently assumes the order of a columns. This is bad. Always include the actual columns that you are inserting into. There are multiple reasons for this. First off, this lets you avoid the possibility that you are inserting them in the wrong order. Secondly, this lets you not have to specify columns that are intentionally blank. And finally, if you were to change your schema by adding a column, this sql statement would then fail because the number of columns would not match the table. If you specify all of your column names you can modify the table without having to risk failure for your old sql queries.[/li]
[/ol]
Anyway, the final change I can't make for you as I don't know your table, but here is your code so modified.

[gray]#!/usr/bin/perl[/gray]
[url=http://perldoc.perl.org/functions/use.html][black][b]use[/b][/black][/url] [green]CGI[/green] [red]qw([/red][purple]:standard[/purple][red])[/red][red];[/red]
[black][b]use[/b][/black] [green]CGI::Carp[/green] [red]'[/red][purple]fatalsToBrowser[/purple][red]'[/red][red];[/red]
[black][b]use[/b][/black] [green]DBI[/green][red];[/red]

[black][b]use[/b][/black] [green]strict[/green][red];[/red]

[url=http://perldoc.perl.org/functions/my.html][black][b]my[/b][/black][/url] [blue]$cgi[/blue] = new CGI[red];[/red]
[black][b]my[/b][/black] [blue]$dbh[/blue] = DBI->[maroon]connect[/maroon][red]([/red][red]"[/red][purple]DBI:mysql:myDB:localhost[/purple][red]"[/red],[red]"[/red][purple]uname[/purple][red]"[/red],[red]"[/red][purple]pass[/purple][red]"[/red][red])[/red] or [url=http://perldoc.perl.org/functions/die.html][black][b]die[/b][/black][/url] [red]"[/red][purple]Can't connect to Database[/purple][red]"[/red][red];[/red]

[black][b]my[/b][/black] [blue]$xname[/blue] = [blue]$cgi[/blue]->[maroon]param[/maroon][red]([/red][red]'[/red][purple]oName[/purple][red]'[/red][red])[/red][red];[/red]
[black][b]my[/b][/black] [blue]$xclink[/blue] = [blue]$cgi[/blue]->[maroon]param[/maroon][red]([/red][red]'[/red][purple]cLink[/purple][red]'[/red][red])[/red][red];[/red]
[black][b]my[/b][/black] [blue]$xglink[/blue] = [blue]$cgi[/blue]->[maroon]param[/maroon][red]([/red][red]'[/red][purple]gLink[/purple][red]'[/red][red])[/red][red];[/red]
[black][b]my[/b][/black] [blue]$xdesc[/blue] = [blue]$cgi[/blue]->[maroon]param[/maroon][red]([/red][red]'[/red][purple]oDesc[/purple][red]'[/red][red])[/red][red];[/red]
[black][b]my[/b][/black] [blue]$xinfo[/blue] = [blue]$cgi[/blue]->[maroon]param[/maroon][red]([/red][red]'[/red][purple]oInfo[/purple][red]'[/red][red])[/red][red];[/red]
[black][b]my[/b][/black] [blue]$xpaid[/blue] = [blue]$cgi[/blue]->[maroon]param[/maroon][red]([/red][red]'[/red][purple]aPaid[/purple][red]'[/red][red])[/red][red];[/red]
[black][b]my[/b][/black] [blue]$xprog[/blue] = [blue]$cgi[/blue]->[maroon]param[/maroon][red]([/red][red]'[/red][purple]aProg[/purple][red]'[/red][red])[/red][red];[/red]

[black][b]my[/b][/black] [blue]$sth[/blue] = [blue]$dbh[/blue]->[maroon]prepare[/maroon][red]([/red][red]q{[/red][purple]INSERT INTO offers VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)[/purple][red]}[/red][red])[/red][red];[/red]
[blue]$sth[/blue]->[maroon]execute[/maroon][red]([/red][fuchsia]0[/fuchsia], [blue]$xname[/blue], [red]'[/red][purple][/purple][red]'[/red], [blue]$xclink[/blue], [blue]$xglink[/blue], [blue]$xdesc[/blue], [blue]$xinfo[/blue], [red]'[/red][purple][/purple][red]'[/red], [red]'[/red][purple][/purple][red]'[/red], [blue]$xpaid[/blue], [blue]$xprog[/blue], [red]'[/red][purple][/purple][red]'[/red], [red]'[/red][purple][/purple][red]'[/red][red])[/red] or [black][b]die[/b][/black] [blue]$dbh[/blue]->[maroon]errstr[/maroon][red];[/red]

[url=http://perldoc.perl.org/functions/print.html][black][b]print[/b][/black][/url] [red]<< "EOF"[/red][red];[/red]
[purple]Location: db-add.html[/purple]
[red]EOF[/red]

[tt]------------------------------------------------------------
Pragmas (perl 5.8.8) used :
[ul]
[li]strict - Perl pragma to restrict unsafe constructs[/li]
[/ul]
Core (perl 5.8.8) Modules used :
[ul]
[li]CGI - Simple Common Gateway Interface Class[/li]
[li]CGI::Carp - CGI routines for writing to the HTTPD (or other) error log[/li]
[/ul]
Other Modules used :
[ul]
[li]DBI[/li]
[/ul]
[/tt]

- Miller