Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

Need help writing a script that interacts with a MySQL DB!

by MDTech.us_MAN (Initiate)
on Sep 30, 2012 at 01:56 UTC ( #996451=perlquestion: print w/ replies, xml ) Need Help??
MDTech.us_MAN has asked for the wisdom of the Perl Monks concerning the following question:

Hello All! I am currently learning Perl CGI using a book I have. I now want to write a script that receives a person's name as an argument then, looks up the person in the database and returns the Name and Email. I need help with this part:
my @results; while (@results = $query1->fetchrow_array()) { foreach(@results) { if ($index = "1") { print "\nName: $_\n"; $index = "2" } else { if ($index = "2") { print "\nEmail: $_\n"; $index = "2" } } } }
Here is what I already have:
#!/usr/bin/perl use strict; use DBI; #my $username = 'root'; #my $dsn = 'DBI:mysql:mysql'; my $conn = DBI->connect('DBI:mysql:<DBName>;host=<ServerIP>', '<Userna +me>', '<Password>', { RaiseError => 1 }) || die "Could not connect to + the database"; my $console = $ARGV[0]; my $query = "SELECT * FROM People where Name = '$console'"; my $query1 = $conn->prepare($query) || die "Error preparing query" . $ +conn->errstr; $query1->execute || die "Error executing query" . $query1->errstr; print "$query"; my $index = "1"; my @results; while (@results = $query1->fetchrow_array()) { foreach(@results) { if ($index = "1") { print "\nName: $_\n"; $index = "2" } else { if ($index = "2") { print "\nEmail: $_\n"; $index = "2" } } } } if ($query1->rows == 0 ) { print "No Records", "\n"; }
Maxwell D.

Comment on Need help writing a script that interacts with a MySQL DB!
Select or Download Code
Re: Need help writing a script that interacts with a MySQL DB!
by chromatic (Archbishop) on Sep 30, 2012 at 03:33 UTC

    Which part is giving you trouble? We can only guess, but here are some suggestions:

    my $query = "SELECT * FROM People where Name = '$console'";

    Change the query to SELECT name, email FROM People... for two reasons. One, to get in the habit of selecting only the columns you need. Two, so that you know what you're getting back and in what order.

    (You should also look up prepared queries in the DBI documentation, because it's really easy to allow bad data into a query to get the wrong results or to make a security hole. For now, this isn't a problem if you're the only one who runs this program, but look up this information as soon as you get the program working and then change the program to use them.)

    if ($index = "1") {

    This is always going to be true because you're not comparing $index to "1". You're assigning "1" to $index. You should instead write if $index == 1 for two reasons. First, it's a numeric comparison, so you need to use the numeric equality operator. Second, it's a numeric comparison, so you can leave 1 as a numeric literal and not a string literal. With that all said, you don't need $index at all because...

    while (@results = $query1->fetchrow_array()) { foreach(@results) {

    ... you don't need the for loop. If you've only selected the name and email columns from the database, you can more easily and simply write:

    while (my ($name, $email) = $query1->fetchrow_array()) { print "\nName: $name\nEmail: $email\n"; }

    (You don't even have to select the name column because you're searching on it, but you get the point.)


    Improve your skills with Modern Perl: the free book.

      I'll one-up you with bound columns. (Perhaps not for the beginner, though.)

      $query1 = $conn->prepare(q{SELECT name, email FROM ...}); $query1->execute(...); $query1->bind_columns( \my ($name, $email) ); while ($query1->fetch) { print "\nName: $name\nEmail: $email\n"; }

      They're just magic. And among the cleanest and most efficient interfaces DBI provides.

Re: Need help writing a script that interacts with a MySQL DB!
by ckj (Chaplain) on Sep 30, 2012 at 05:43 UTC
    Instead of using this logic of $index, why can't you simply print the name and email column by using something like this:
    my @results; while (@results = $query1->fetchrow_array()) { print "Name : $results[1], Email: $results[2]\n"; }

    Generally, first row consist of ID. That's why, Here I'm assuming that after ID the 1st column is for name and 2nd column is for email address. Please, use the correct column number as in your table.

    Also, for printing all the rows, you can use like this:

    my @results; while (@results = $query1->fetchrow_array()) { foreach my $r (@row) { print "$r\n"; } }
    let me know if you still have any query.

    Regards, CKJ

      Here I'm assuming that 1st column is for name and 2nd column is for email address, you can use the correct column number here.
      Since Perl arrays are zero-based, your
      print "Name : $results[1], Email: $results[2]\n";
      should be
      print "Name : $results[0], Email: $results[1]\n";

      CountZero

      A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

      My blog: Imperial Deltronics
Re: Need help writing a script that interacts with a MySQL DB!
by Anonymous Monk on Sep 30, 2012 at 06:16 UTC
Re: Need help writing a script that interacts with a MySQL DB!
by tinita (Parson) on Sep 30, 2012 at 10:54 UTC
    my $query = "SELECT * FROM People where Name = '$console'";
    Please learn to use placeholders as early as possible and get used to it. they make your database statements more secure and the code is in most cases cleaner to read. You don't need to care about quoting the value.
    see also http://bobby-tables.com/
    my $query = "SELECT * FROM People where Name = ?"; # later: $query1->execute($console);

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://996451]
Approved by chacham
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (11)
As of 2014-10-22 08:57 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (114 votes), past polls