perl accessing MySQL database

Comments

• mdw1982

  September 2003

  : Hi
  :
  : I am new to perl. I would apprecaite if somebody can tell me how a perl script can aceess a MYSQL database file and show a result of a that search in a HTML format.
  :
  : Maeaning i have a list of the my products in MYSQL. I want user to
  : put in a name of the product and then click on search button on my website
  :
  : THat search should go check the MYSQL database and show the result in HTML.
  :
  : Is there any sample code that i can use. links or steps or sample code will help me do this
  :
  : Thanks
  : SN
  :
  
  
  No problem....
  
  Here's the code example of how to make this happen.
  
  [code]
  #!/usr/bin/perl
  
  # this use statement calls the DBI module to interface with MySQL
  # ###############################################################
  use DBI;
  
  # global DB connection variable
  # ##################################
  $hostname = "localhost";
  $username = "";
  $password = "";
  $database = "";
  $tableName = "";
  
  # The initial line for making the connection MySQL
  $dbh = DBI->connect("DBI:mysql:$database:$hostname","$username","$password");
  
  # Sending the query to the db for processing
  $sth = $dbh->prepare("select IPAddress from $tableName where IPAddress = '$entry'");
  
  $sth->execute;
  while ($row_hash = $sth->fetchrow_hashref)
  {
  $IPAddress = $row_hash->{'IPAddress'};
  #
  } # end of while loop
  [/code]
  
  Thats basically all there is to it. Mind you this is a basic example of how to the connection is made and how you send the query and get back the information from the database. If you need to know more detail on dealing with that data as its coming in you'll have to supply a little more details as to the application of these methods.
  
  Mark
  
• Apotheosis

  September 2003

  : Hi
  :
  : I am new to perl. I would apprecaite if somebody can tell me how a perl script can aceess a MYSQL database file and show a result of a that search in a HTML format.
  :
  : Maeaning i have a list of the my products in MYSQL. I want user to
  : put in a name of the product and then click on search button on my website
  :
  : THat search should go check the MYSQL database and show the result in HTML.
  :
  : Is there any sample code that i can use. links or steps or sample code will help me do this
  :
  : Thanks
  : SN
  :
  
  
  First, you need to know how to create an SQL statement. There's many things you can do within the sql statement. I'll tell you about the most basic for selecting data: SELECT, FROM, WHERE.
  
  SELECT: You will put the field name/s of the data you wish to extract from.
  
  FROM: You will put the table name you wish to extract the information from.
  
  WHERE: This limits the data extracted. Example:
  
  WHERE field_name = 'whatever'
  
  This would only extract data where the field_name contains the word 'whatever'.
  
  Here's a small example to help you get started:
  
  [code]
  #!/usr/bin/perl -w
  use strict;
  use DBI;
  #calls DBI module
  
  my $search = param('search');
  #This will grab the name of the product they want to search for.
  
  
  my $dbh = DBI->connect(source:host:port, username, password, %attributes) or die "$DBH::errstr
  ";
  #connects to database
  
  
  my $sth=$dbh->prepare("SELECT description FROM product_table
  WHERE name = '$search'");
  #stores and prepares an sql statement within $sth.
  
  
  $sth->execute;
  #Performs the sql statement.
  
  
  my ($description);
  $sth->bind_columns(undef, ($description));
  #Puts the data from the database to the variables.
  
  
  while($data = $sth->fetch) {
  #begins a while loop that reads in a row of data that matches the sql statement.
  
  
  print header;
  print qq(Name: $search
  
  Description: $description
  
  );
  #prints the name they searched for and the description.
  }
  
  $dbh->disconnect;
  #disconnects from the database.[/code]
  
• Jonathan

  September 2003

  : Hi
  :
  : I am new to perl. I would apprecaite if somebody can tell me how a
  : perl script can aceess a MYSQL database file and show a result of a
  : that search in a HTML format.
  :
  : Maeaning i have a list of the my products in MYSQL. I want user to
  : put in a name of the product and then click on search button on my
  : website
  :
  : THat search should go check the MYSQL database and show the result
  : in HTML.
  :
  : Is there any sample code that i can use. links or steps or sample
  : code will help me do this
  Suggest you check out the guide in the CodePedia too:-
  http://www.codepedia.com/1/PerlDBITutorial
  
  The examples you've been given also look good to me.
  
  Have fun,
  
  Jonathan
  
  ###
  for(74,117,115,116){$::a.=chr};(($_.='qwertyui')&&
  (tr/yuiqwert/her anot/))for($::b);for($::c){$_.=$^X;
  /(p.{2}l)/;$_=$1}$::b=~/(..)$/;print("$::a$::b $::c hack$1.");
  
  
• sncgi

  September 2003

  Hey Thanks guys ,
  
  quick question , now this may sound stupid here but appreciate your response where do i place this SQL / perl script. ?
  
  I have search button on my webpage and asearch string.
  
  SO do i place this perl code within the HTML code ?
  
  
  
  
  ==============
  : : Hi
  : :
  : : I am new to perl. I would apprecaite if somebody can tell me how a perl script can aceess a MYSQL database file and show a result of a that search in a HTML format.
  : :
  : : Maeaning i have a list of the my products in MYSQL. I want user to
  : : put in a name of the product and then click on search button on my website
  : :
  : : THat search should go check the MYSQL database and show the result in HTML.
  : :
  : : Is there any sample code that i can use. links or steps or sample code will help me do this
  : :
  : : Thanks
  : : SN
  : :
  :
  :
  : First, you need to know how to create an SQL statement. There's many things you can do within the sql statement. I'll tell you about the most basic for selecting data: SELECT, FROM, WHERE.
  :
  : SELECT: You will put the field name/s of the data you wish to extract from.
  :
  : FROM: You will put the table name you wish to extract the information from.
  :
  : WHERE: This limits the data extracted. Example:
  :
  : WHERE field_name = 'whatever'
  :
  : This would only extract data where the field_name contains the word 'whatever'.
  :
  : Here's a small example to help you get started:
  :
  : [code]
  : #!/usr/bin/perl -w
  : use strict;
  : use DBI;
  : #calls DBI module
  :
  : my $search = param('search');
  : #This will grab the name of the product they want to search for.
  :
  :
  : my $dbh = DBI->connect(source:host:port, username, password, %attributes) or die "$DBH::errstr
  ";
  : #connects to database
  :
  :
  : my $sth=$dbh->prepare("SELECT description FROM product_table
  : WHERE name = '$search'");
  : #stores and prepares an sql statement within $sth.
  :
  :
  : $sth->execute;
  : #Performs the sql statement.
  :
  :
  : my ($description);
  : $sth->bind_columns(undef, ($description));
  : #Puts the data from the database to the variables.
  :
  :
  : while($data = $sth->fetch) {
  : #begins a while loop that reads in a row of data that matches the sql statement.
  :
  :
  : print header;
  : print qq(Name: $search
  : Description: $description);
  : #prints the name they searched for and the description.
  : }
  :
  : $dbh->disconnect;
  : #disconnects from the database.[/code]
  :
  
  
• Jonathan

  September 2003

  : quick question , now this may sound stupid here but appreciate your
  : response where do i place this SQL / perl script. ?
  :
  : I have search button on my webpage and asearch string.
  :
  : SO do i place this perl code within the HTML code ?
  Nope, you put it in a file with a .pl extension, and place it in the cgi-bin directory (or any other place where scripts are executed) on your server and if it's a UNIX/Linux one chmod it to 755. Then you need to set the action attribute of the form data surrounding the text field and the button to point at the script.
  
  Jonathan
  
  ###
  for(74,117,115,116){$::a.=chr};(($_.='qwertyui')&&
  (tr/yuiqwert/her anot/))for($::b);for($::c){$_.=$^X;
  /(p.{2}l)/;$_=$1}$::b=~/(..)$/;print("$::a$::b $::c hack$1.");