MySQL selecting

[Ultimater]

A user fills out a form with loads of information and submits it. First off I need to generate an ID for the user even before writting all that information, that they took the time to fill-out, into my database. The method I am using, which simply reserves a place in the database for the user and to give him that id, is to write their IP address, the time of their post, the program process id, and a bunch of random numbers to make the post totally unique. After all this information is entered, I will need to select all posts in the database that contain all of this information combined (should be 1 every time) then I can have the user's Id handy in Perl THEN I can enter their actual information into the proper field of the post id given to them. Again I am doing all of this just to simply have a full-proof method to prompt the user their user id in the database after all their information is entered into the database.

Since the database will always reserve a post then afterwards enter their actual infromation into the servered post, I needed a way to distinguish real posts from reservation posts. The method I used to do this is a boolean field in the database "reserved". If the boolean value is true then it is a reserved post to simply hold all kinds of crazyly unique information about the user to make the post totally unique from all others and if the boolean value if false then it is the actual information that the user entered.

Here's the scenario:

The array @whatever contains the key information for the reservation post that will be entered into the table "business" and I will later use this key information to pull it back up in order to obtain the id given by MySQL from the field "id int NOT NULL auto_increment primary key".

my @whatever=($$,time(),$ENV{'REMOTE_ADDR'},$ENV{'REMOTE_HOST'},rand(10000),rand(10000),
rand(10000),rand(10000),rand(10000),rand(10000),rand(10000),rand(10000),rand(10000),rand(10000),
rand(10000),rand(10000),rand(10000),rand(10000),rand(10000));
my $sql = "INSERT INTO business (busname,businfo,contact,website,street,city,zip,state,phone,fax,email,link1,link2,upload1,upload2,i p,datereg,dateedit,pass,reserved) values('".join("','",@whatever)."',1)";
my $sth = $dbh->prepare($sql);
$sth->execute();

The above makes a successful entry into the database.

Now here is the part I'm messing up on that isn't working -- pulling up the post with the information contained within @whatever.
All I want the following to do is print to the page the id of the post and nothing more.

$sql="SELECT id FROM business WHERE busname = '$whatever[0]' AND businfo = '$whatever[1]' AND contact = '$whatever[2]' AND website = '$whatever[3]' AND street = '$whatever[4]' AND city = '$whatever[5]' AND zip = '$whatever[6]' AND zip = '$whatever[7]' AND state = '$whatever[8]' AND phone = '$whatever[9]' AND fax = '$whatever[10]' AND email = '$whatever[11]' AND link1 = '$whatever[12]' AND link2 = '$whatever[13]' AND upload1 = '$whatever[14]' AND upload2 = '$whatever[15]' AND ip = '$whatever[16]' AND datereg = '$whatever[17]' AND dateedit = '$whatever[18]' AND pass = '$whatever[19]' AND reserved = '1'";
$sth = $dbh->prepare($sql);
$sth->execute();
$sth->bind_columns(\my ($uid));
while ($sth->fetch()) {

print $uid;

}
$sth->finish(); ## Optional most of the time


Note: that there is no need to declare $sql and $sth with "my" a second time because all of this is being done back-to-back.

[Scriptage]

I can't understand a word you are saying but have you looked into CGI::Session instead of doing all that crazy random number stuff?

use CGI::Session qw(-ip-match);

my $sid = param("cgisessid") || undef;

my $session = new CGI::Session(undef, $sid, {Directory=>'tmp'});

my $sessionid = $session->id();

# To store info in the session

$session->param("somefield","somevalue");

# To get data

my $data = $session->param("somefield");

# Then

my $sth = $dbh->prepare(qq{

SELECT id FROM business WHERE sessionid = ? --

});

$sth->execute($sessionid);

The session id must be passed back for this to work...

print "<input type=hidden name=cgisessid value=$sessionid>";

All you need is a field in the database for sessionid.

Forgive me if I'm barking up the wrong tree but the only reason I can see you going to all this trouble is to be able track the post. This code will maintain the state of the program so you can track the post etc. It's probably best to use cookies but that isn't too hard to change.

Regards

Carl

[Ultimater]

Thanks for the reply Scriptage, you sure-as-well aren't "barking-up the wrong tree".
Sounds like a good idea however I don't want to resort to cookies if I don't need-to -- so there is room to compromise.

I already have enough information between

$$,time(),$ENV{'REMOTE_ADDR'},$ENV{'REMOTE_HOST'}

the process number of the process running the Perl interpreter, the time, the user's remote address ,and remote host. So I can just join that information into a string and use it in-place-of session ids and work from there.
The random numbers were over-doing it, I admit. :D

[Ultimater]

Finally got it to work! I had to use $sth->fetchrow_array() in place of $sth->fetch()

[Jeff Mott]

CGI::Session does not rely on cookies. CGI::Session will store the session data in your database and leave you with a unique ID, which you can then pass from page to page through the query string or form submissions.