homepage Welcome to WebmasterWorld Guest from 54.166.228.100
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
Forum Library, Charter, Moderators: coopster & jatar k & phranque

Perl Server Side CGI Scripting Forum

    
Mysql/cgi scripting
need some advice about sql data arrays
XiloHenAdmin




msg:3803132
 3:15 am on Dec 9, 2008 (gmt 0)

Alright, long story short, I am new to cgi/perl scripting, but am faring reasonably well with the less complicated stuff, but what I am posting about, in my mind anyway, is complex, so.

I am trying to reproduce a php script I made to test out a method of inputting users into my Gallery2 g2_User table.

This is part of the code I was using. This is the part I need to replicate in cgi mysql:

$data = mysql_query("SELECT g_id FROM g2_User ORDER BY g_id DESC")
or die(mysql_error());
$info = mysql_fetch_array($data);
$ng_id = $info['g_id'];
echo ($ng_id);
$ng_id += 1;
echo ($ng_id);

the echo's were just a visual aid to make sure it was being incremented.

now, I tried tailoring this code to a cgi version which failed miserably. The main reason is, i get page errors on mysql_query and mysql_fetch_array.

So this is what I need to figure out, how can I do this code block in a cgi script.

Appreciate any and all help.

Thanks!

 

phranque




msg:3803139
 3:29 am on Dec 9, 2008 (gmt 0)

welcome to WebmasterWorld [webmasterworld.com], XiloHenAdmin!

those are php-specific functions.
if you want to do the equivalent in perl, you should use the DBI module [dbi.perl.org].
you can also download this module and find documentation on cpan:
[search.cpan.org...]

XiloHenAdmin




msg:3803195
 4:55 am on Dec 9, 2008 (gmt 0)

thanks Phranque, for the welcome and info. I perused the documentation searching for keywords like array elements and SELECT.

I found everything I needed but when I go to access the script I get an error 500. Now sure what the issue is though:


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

my $cgi = new CGI;

$dbh = DBI->connect("DBI:mysql:database:localhost","username","password") or die "Error: $DBI::errstr\n";

$sth = $dbh->prepare("SELECT g_id FROM g2_User ORDER BY g_id DESC");
$sth->execute();

while ( @row = $sth->fetchrow_array ) {

print header;
print "@row\n";
print end_html;

}

$dbh->disconnect;

also, is the syntax i am using now the correct way to setup an array using a SELECT FROM sql query?

phranque




msg:3803263
 10:44 am on Dec 9, 2008 (gmt 0)

i would try something like this:
print header;
foreach $row ( @row = $sth->fetchrow_array ) {
foreach $key (keys %$row){
print "$key:" . $row->{$key} . "\n";
}
print "\n";
}
print end_html;

this may be version dependent but the format of my connect data source looks more like:
"DBI:mysql:database=db-name;host=localhost"

the rest of it looks pretty good.

as for the 500 error, it's always a good idea to check the server error log for clues.

janharders




msg:3803367
 2:29 pm on Dec 9, 2008 (gmt 0)

phranque, I think you mixed fetchrow_array, fetchrow_hashref and fetchall_arrayref ;)


print header;
while(my $row = $sth->fetchrow_hashref())
{
foreach $key (keys %$row){
print "$key:" . $row->{$key} . "\n";
}
print "\n";
}
print end_html;

should fix that.

rocknbil




msg:3803470
 4:18 pm on Dec 9, 2008 (gmt 0)

I don't believe it. It can't be true. I have an opportunity to correct phranque. He must not be awake (or, as usual, I'm missing something in his description . . . ) :-)


foreach $row ( @row = $sth->fetchrow_array ) {
foreach $key (keys %$row){ ....

fetchrow_array pulls an array list, not a hash.

foreach $key (@row){ print "$key \n"; ....

or

for $key (0..$#row){ print "$row[$key] \n"; ...

But in the context of the original problem, if you're trying to get the last inserted ID, what you want is to use limit 1 in your select. Otherwise, it will start with the last ID and iterate through the whole table, and the last value it will receive will be the first id.

Second, since you're only pulling one field, you don't need @row, and you don't need a while loop. A scalar will work, but remember fetchrow_array pulls an array, so sometimes you need to surround it by parentheses. ().

This should work, with some corrections to the error string ($dbh->errstr in a quoted string produces HASH{24234} or something):


#!/usr/bin/perl
use DBI;
$dbh = DBI->connect("DBI:mysql:database:localhost","username","password")
or die("Error: " . $dbh->errstr . "\n");


$sth = $dbh->prepare("select g_id from g2_User order by g_id desc limit 1;");
$sth->execute() or die("could not select g_id " . $dbh->errstr);
($last_id) = $sth->fetchrow_array;
#Don't forget to finish
$sth->finish;
$dbh->disconnect;


print "content-type:text/html\n\n";
print "last id is $last_id\n";

XiloHenAdmin




msg:3803501
 4:49 pm on Dec 9, 2008 (gmt 0)

oh, perfect, thanks guys! just tested it and it works.

I think i have it from here. All I need to do is increment $last_id by one then reinsert that value with the rest of the account data, perfect.

thanks again.

janharders




msg:3803572
 5:50 pm on Dec 9, 2008 (gmt 0)

wait, you're doing that to get the next free id?
in that case, you should set the id-field as the primary key and set auto_increment, so mysql will decide which is the next id (and tell you about the one it inserted in $sth->{'mysql_insertid'}).
e.g.
sql:

CREATE TABLE mytable (
id int primary key auto_increment,
username varchar(15)
);

perl-insert

my $insert_user = $dbh->prepare("INSERT INTO mytable (username) VALUES (?)");
$insert_user->execute($username);
print 'new user has id: ' . $insert_user->{'mysql_insertid'} . "\n";

of course, there should be error-checking, too, but I'm a very lazy person.

The way you thought of is fine in general, but there could be a race-condition where two new users should be added simultaneously, so they both go to get the last id, both get the same value, both insert a new record with the same "new" id, so either one will fail or your table will contain two rows with the same id.

vol7ron




msg:3803640
 7:09 pm on Dec 9, 2008 (gmt 0)

While that would be easy, it is my DBA experience that should say it is best to use a trigger and not an auto_increment. Why? ID should be text and not a number.

Numeric values should be reserved only for those fields that will be used in calculations (e.g. quantity, revenue, velocity). Numeric values should not be used for numbers that won't be used in calculations (database record IDs, line numbers in code, someone's "favorite number", or any model numbers/SKUs/VINs/etc). If one of those numbers eventually needs to be used in a calculation, then an ALTER TABLE statement can be issued, or if it is not going to be used routinely, the program can simply typecase the text to a numeric value.

In cases for ID, just build a trigger that creates the ID when a record is inserted.

rocknbil




msg:3803684
 8:22 pm on Dec 9, 2008 (gmt 0)

vol7ron, don't you agree that a numeric search is more efficient and faster than a text search?

select user_id from table

is a faster execution on a numeric field, is it not?

I agree with both yours and janharder's approach, with this exception, but I'm asking because I could be wrong.

For XiloHenAdmin's benefit, the autoincrement field should never be used as a relational id. Consider,

id¦first_name.....
1¦John
2¦Joe
3¦Bob
4¦Mary

id¦user_id¦images
1¦4¦mary.jpg
2¦3¦bob.jpg
3¦2¦joe.jpg
4¦1¦john.jpg

Delete from table where id=3;
Delete from images where user_id=3;

id¦first_name.....
1¦John
2¦Joe
4¦Mary

id¦user_id¦images
1¦4¦mary.jpg
3¦2¦joe.jpg
4¦1¦john.jpg

So far, no problems. Then you move your database, and the autoincrement fields do this:

id¦first_name.....
1¦John
2¦Joe
3¦Mary

id¦user_id¦images
1¦4¦mary.jpg
2¦2¦joe.jpg
3¦1¦john.jpg

select images from image-table where user_id=3;

I'm expecting to get Mary's pic but get nothing, because user_id 3 was deleted from that table.

So if you use a numeric id for a relational id, you should always make that field distinct from the autoincrement field (be it text or numeric.) You can just get the autoincrement field value and store it in a unique user_id field, and in most cases, it will be the same number. This has the added advantage of not having to check if it's unique, as an autoincrement will always be the next value plus one.

starting to stray off topic . . . sorry

phranque




msg:3803830
 11:33 pm on Dec 9, 2008 (gmt 0)

jh:
phranque, I think you mixed fetchrow_array, fetchrow_hashref and fetchall_arrayref

rb:
I don't believe it. It can't be true. I have an opportunity to correct phranque. He must not be awake

true on all counts!
but i was just testing you.
or maybe i didn't notice that missing ref thingy...
=8)

good catch guys - good to know i have someone watching my back here.

regarding the autoincrement column, my experience is primarily with mysql.
however i have never had a problem using an autoincrement column as a foreign key.
when you dump the database, the value of the autoincrement column is maintained unless it has been set to 0.

XiloHenAdmin




msg:3807941
 5:04 pm on Dec 15, 2008 (gmt 0)

@rocknbil vol7ron & janharders

the main reason I wanted to go the route I did is because this id spans several tables to allow the users to function correctly in Gallery 2.

I ended up with this code and it works pretty well. I had to change it a bit as of today because I forgot to make the id search in the correct table:


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

my $cgi = new CGI;

#CCBill POST parameter setup
my $username = $cgi->param("username");
my $password = $cgi->param("password");
my $email = $cgi->param("email");

#establish sql connection
$dbh = DBI->connect("DBI:mysql:x:localhost","x","x") or die("Error: " . $dbh->errstr . "\n");

#grab last id from g2_Entity
$sth = $dbh->prepare("select g_id from g2_Entity order by g_id desc limit 1;");
$sth->execute() or die("could not select g_id " . $dbh->errstr);
($g_id) = $sth->fetchrow_array;
#+1 to g_id for account creation
$g_id += 1;

#insert user info into g2_User
$sth = $dbh->prepare("INSERT INTO g2_User (g_id, g_userName, g_fullName, g_hashedPassword, g_email, g_language, g_locked) VALUES('$g_id', '$username', 'NULL', '$password', '$email', 'NULL', '0');");
$sth->execute() or die("could not insert " . $dbh->errstr);

$sth->finish;

#Insert both group vlaues for new user account
$sth = $dbh->prepare("INSERT INTO g2_UserGroupMap (g_userId, g_groupId ) VALUES('$g_id', '4');");
$sth->execute() or die("could not insert " . $dbh->errstr);

$sth->finish;

$sth = $dbh->prepare("INSERT INTO g2_UserGroupMap (g_userId, g_groupId ) VALUES('$g_id', '2');");
$sth->execute() or die("could not insert " . $dbh->errstr);

$sth->finish;

#reinsert new id + misc. info into g2_Entity
$sth = $dbh->prepare("INSERT INTO g2_Entity (`g_id`,`g_creationTimestamp`,`g_isLinkable`,`g_linkId`,`g_modificationTimestamp`,`g_serialNumber`,`g_entityType`,`g_onLoadHandlers`) VALUES ('$g_id', '1111111', '0', NULL , '1111111', '1', 'GalleryUser', NULL);");
$sth->execute() or die("could not insert " . $dbh->errstr);

$sth->finish;

#disconnect from sql
$dbh->disconnect;

#testing purposes only, will not be seen with CCBill transaction
print "content-type:text/html\n\n";
print "last id is $g_id\n";
print "$username\n$password\n$email\n\n";

janharders




msg:3808129
 8:43 pm on Dec 15, 2008 (gmt 0)

I guess, you could still go with auto_increments. Any sane (but hey, who says it is ;) database layout would have g_id in the table g2_User as auto_increment. In that case, you could just insert into g2_User without providing g_id and then do
my $g_id = $sth->{'mysql_insertid'};
and work with $g_id in the other queries.

two things though: you seem to be coding fine for "strict", yet you didn't "use" it. Best advise I can give when writing perl is to always "use strict;" at the very beginning of your script. It'll save you time when you're hunting typos and just enforces a certain level of clean code.
the other, and in your case more important thing: use placeholders for db-queries.
instead of writing
$sth = $dbh->prepare("INSERT INTO table (field) VALUES ('$value')");
$sth->execute();

you'll be better off using
$sth = $dbh->prepare("INSERT INTO table (field) VALUES (?)");
$sth->execute($value);

it's easier to read, the database-driver handles the escaping and protects you against SQL-injections and you can (if the database supports it) benefit from prepared statments. as you can see, you just put a placeholder (?) where your value should go and then supply the values to fill those placeholders to the execute-call. you can have multiple placeholders, of course
$sth = $dbh->prepare("INSERT INTO table (field, anotherfield) VALUES (?, ?)");
$sth->execute($value, $anothervalue);

don't put single quotes around placeholders or they will cease to work as placeholders and a questionmark will be passed to the database, which is usually not what you want. placeholders always bring their own singlequotes, they're like cool guests, bringing beer, helping you do the dishes and will actually prevent you from trashing your own place ;)

Also, as a third (I know I said two and I'm too lazy changing that, instead writing a few words to explain): I always prefer to put all my statementhandles at the top and give them meaningfull names, e.g.

my $insert_user = $dbh->prepare(....);
my $insert_user_into_group = $dbh->prepare(....);
....
$insert_user->($username);
$insert_user_into_group->($userid);

If you'll have to change things around in a few months from now, your comments will certainly help you. having meaningfull variable-names will help you even more.

[edited by: phranque at 1:14 am (utc) on Dec. 16, 2008]
[edit reason] disabled graphic smileys ;) [/edit]

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved