#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2000
    Location
    USA
    Posts
    67
    Rep Power
    15
    Hello, I am trying to connect to a mySQL server with perl so that i can display contents of the database on a web page.

    $SQL="SELECT * FROM list";
    ##########################
    #the followig line seems to be giving me the problem
    $sth=$dbh->prepare($SQL);
    ##########################
    $sth=$sth->execute;
    while($pointer=$sth->fetchrow_hashref)
    {
    $name=$pointer->{'name'};
    print "$name<br>n";
    }

    what am i doing wrong?
    i would appreciate any help possible
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2000
    Location
    Blackstone, VA, USA
    Posts
    5
    Rep Power
    0
    How about giving us some error messages.
    Thanks.
  4. #3
  5. .Net Developer
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2000
    Location
    London
    Posts
    987
    Rep Power
    15
    Hi,

    following may work for you:

    $dbh=DBI->connect('dbi:mysql:databasename','username','pwd');

    $sql="SELECT * FROM tblname";
    $sth=$dbh->prepare($sql);

    #####i think here you made the mistake ####
    ##$sth=$sth->execute;###
    ###Return value should not be a statement handle########


    $rv=$sth->execute;

    while(@row = $sth->fetchrow_array) {
    print $row[0];
    }

    or


    while($pointer = $sth->fetchrow_hashref) {
    print $pointer->{'name'};
    }






    [This message has been edited by Shiju Rajan (edited March 15, 2000).]
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2000
    Location
    USA
    Posts
    67
    Rep Power
    15
    I tried out the different code you used, but I still seem to have the same problem.
    The problem : no resutls show up on the page.
    The code seems to stop responding right after the line
    <b>$sth=$dbh->prepare($sql);</b>

    I tried putting a print statment right after that line, and that print statment would not print.


  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2000
    Location
    USA
    Posts
    67
    Rep Power
    15
    codemaster:

    I do not seem to be getting any errors. THe page just does not load any data. The code seems to stop running after the prepare statement.
  10. #6
  11. .Net Developer
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2000
    Location
    London
    Posts
    987
    Rep Power
    15
    Are you sure you have some records in the database??????.

    try this

    $dbh=DBI->connect('dbi:mysql:databasename','username','pwd');

    $sql="SELECT * FROM tblname";

    print "Database Connectedn";

    $sth = $dbh->prepare($sql)
    or die "Can't prepare $sql: $dbh->errstrn";


    $rv = $sth->execute
    or die "can't execute the query: $sth->errstrn";


    if ($rv==0){

    print "No Recordsn";

    }else{
    while(@row = $sth->fetchrow_array) {
    print $row[0];
    }
    }

  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2000
    Location
    USA
    Posts
    67
    Rep Power
    15
    I tried the out the code, and only the "Database Connected" comes out.

    I know that I have data in the database because i can log in with a seperate client and extract data with the same SQL statement.
  14. #8
  15. .Net Developer
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2000
    Location
    London
    Posts
    987
    Rep Power
    15
    hi,

    i tested this script in my server And it is working fine.

    You may see the test at:

    http://www.samakcreations.com/test.cgi

    here is the full source code which i am using to fetch a record from a login table(fields are username and password):


    #!/usr/local/bin/perl

    use CGI;
    use DBI;

    $q=new CGI;

    print $q->header;

    print "<html>n";
    print "<head><title>database test</title></head>n";
    print "<body>n";
    print "<h1>Database Test</h1>n";


    $dbh=DBI->connect('dbi:mysql:database','usr','pwd');

    $sql="SELECT * FROM login";

    print "Database Connected<br>n";

    $sth = $dbh->prepare($sql);


    $rv = $sth->execute;


    if ($rv==0){

    print "No Recordsn";

    }else{
    while(@row = $sth->fetchrow_array) {
    print "UserName :".$row[0]."<br>n";
    print "Password :".$row[1]."<br>n";
    }
    }
    print "</body>n";
    print "</html>n";


    If it is not fetching the record ,then i think the problem with your data.

    try another way also.


    after prepare statement just try to execute the statement:

    $sth->execute;

    while(@row = $sth->fetchrow_array) {
    print "FirstField :".$row[0]."<br>n";
    }




  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2000
    Location
    USA
    Posts
    67
    Rep Power
    15
    First of all, i want to say thanks for all help that i have been recieving from you.

    i tried out the website link, and what you have there is exactly what i want on my site. i then copied the stuff that you put on the message, but i dont get those results. It says "databaseconnected" but i dont see any data. I checked my database on mysql server, and its all there.
  18. #10
  19. .Net Developer
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2000
    Location
    London
    Posts
    987
    Rep Power
    15
    Hi David,

    why don't you try the same in PHP?.

    I don't know ,what is the problem with your script!!.

    Are you getting some error when you run the script???.

    Which version of mysql you are using??.






  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2000
    Location
    USA
    Posts
    67
    Rep Power
    15
    Shiju Rajan,

    I would turn to PHP, but my web host does not support it. I have done sites similar to this one, but using ASP and so PHP is a goodc hoice, but i dont have hte resource.

    Good news, i got it to work , but i used the Mysql module instead of the DBI module, and i got it working the first time.

    I dont understand why the example you gave me doesnt work. I have one question about tit though. Where do you specify the server? I did not notice where i would tell the script where to find the sql server. In the other versin that i wrote, i had to specify the adress of the mysql server. HEre is the code i used

    #!/usr/local/bin/perl

    use Mysql;
    use CGI;

    print "Content--type:text/htmlnn";

    print "<html>n";
    print "<head><title>Dynamic Generated List</title></head>n";
    print "<font face='arial'>n";

    $host="my.sqlserver.com";
    $database="dbname";
    $user="root";
    $password="12345";

    $db = Mysql->connect($host, $database, $user, $password);

    $db->selectdb($database);

    $sql_query="select * from table";

    $query = $db->query($sql_query);

    @information=$query->fetchrow;

    print "<table border='1'>n";
    print "<font face='arial'>n";
    print "<tr>n";
    print "<td>firstname</td>n";
    print "<td>lastname</td>n";
    print "<td>comments</td>n";
    print "</tr>n";

    $num=$query->numrows;

    $i=0;

    while($i<$num)
    {
    @information=$query->fetchrow;
    print "<tr>n";
    foreach $i(@information)
    {
    print "<td>$i</td>n";
    }
    print "</tr>n";
    $i=$i+1;
    }

    print "<tr><td><input type='submit' value='Submit Purchase'><input type='reset' value='Reset'></td></tr>n";

    And thanks for all teh help you have given me, i truly appreciate it.

IMN logo majestic logo threadwatch logo seochat tools logo