mysql+cgi questions

[Geowil]

Alright, I finally got this ccbill bs to work correctly after a week, so I am thinking of throwing a bloody party (j/k)

but, first I need to fix one problem. I still need to set up the script so that it writes to my database so my user accounts can get made.

Now I already have a script that does do this for a test table, but for the real thing there is a complication. There is a user Id field. each id has to be different or else the insert fails.

so. when I had this thing phpified I had a for loop, that was supposed to work but didnt, that would keep incrementing the user id by 1 until data insert did not fail.

but the coding for cgi/pearl is way different so I am not sure how to do something like that.

right now this is what I have:


#!/usr/bin/perl
use DBI;
use CGI;
use CGI::Carp qw(warningsToBrowser fatalsToBrowser);

my $cgi = new CGI;

my $user = $cgi->param("username");
my $pass = $cgi->param("password");
my $email = $cgi->param("email");
my $g_id = 0;
$dbh = DBI->connect("DBI:mysql:xilohen_XiloGallery:localhost","username","password") or die "Error: $DBI::errstr\n";
my $sql = "INSERT INTO g2_user (g_id, g_userName, g_fullName, g_password, g_email, g_language, g_locked) VALUES ('$g_id','$user','NULL','$pass','$email','NULL','0')";
$sth = $dbh->prepare($sql);
$sth->execute;
$dbh->do($sql);
$dbh->disconnect;


So, what I was thinking was, how can I code a query to see if the g_id, in this case 0, is present, then add one to it and check again, and repeate the process until the id is not found. Then go on to do the INSERT INTO code.

thanks.

[PolyGreat]

All of that coding work is completely unnecessary. Certainly, it can be done that way, but the simplest way is to make that column in the database auto-increment, such that each value must be unique, and then insert null into the column with the database statement. MySQL will automatically increment it for you. When you create the table, just create the column something like this:

id MEDIUMINT NOT NULL AUTO_INCREMENT

Blessings,

PolyGreat

[Nedals]

#!/usr/bin/perl

#add these
use strict;
use warnings;

use DBI;
use CGI;
use CGI::Carp qw(warningsToBrowser fatalsToBrowser);

my $cgi = new CGI;

my $user = $cgi->param("username");
my $pass = $cgi->param("password");
my $email = $cgi->param("email");

#my $g_id = 0; # not needed
my $dbh = DBI->connect("DBI:mysql:xilohen_XiloGallery:localhost","username","password") or die "Error: $DBI::errstr\n";

#NO
#my $sql = "INSERT INTO g2_user (g_id, g_userName, g_fullName, g_password, g_email, g_language, g_locked) VALUES ('$g_id','$user','NULL','$pass','$email','NULL','0')";
#$sth = $dbh->prepare($sql);
#$sth->execute;
#$dbh->do($sql);


# Use placeholders. They will prevent sql injection, a security issue
my $sql = "INSERT INTO g2_user (g_userName, g_password, g_email) VALUES (?,?,?);

#You do not need to insert g_fullName, g_language, g_locked. They can be the default settings of the DB columns
#Use auto_increment for the g_id column as 'PolyGreat' suggested

$dbh->do($sql, {}, $user, $pass, $email); #combines prepare() and execute()

$dbh->disconnect;