Entry

Internet: Provider:Powweb: Database:MySql: How to connect to your MySql database using Perl? Static

Dec 28th, 2004 14:45
Knud van Eeden,

----------------------------------------------------------------------
--- Knud van Eeden --- 28 December 2004 - 05:31 am -------------------

Internet: Provider:Powweb: Database:MySql: How to connect to your 
MySql database using Perl? Static

---

Steps: Overview:

 1. -To change your MySql settings goto

      https://ops.powweb.com/?module=packages

     1. login with your powweb userid and password

     2. -Select tab 'Hosting packages'

     3. -Possibly add a new user to your MySql

         1. -Click on link 'MySql'

            1. -In the text box 'Addditional users'
                add

                1. 'Username' = <your new user name>

                      e.g. tester

                2. 'Password' = <your new password>

                      e.g. test

     4. -Possibly create a new table to your
         MySql

         1. -Click on link

             http://phpmyadmin.powweb.com

             1. Select your MySql server

                 e.g.
                  mysql20.powweb.com

             2. -Click button 'Go'

             3. -Login with your MySql username
                 and MySql password
                 (e.g. of this new user which
                  you just added, thus 'tester'
                  and 'test')

             4. -Click button 'Login'

         2. Click in the left pane on your 'database'
            link

         3. Then create your table in the right pane
            'Create new table on database <your database>'

            1. Choose a name for your new table

                e.g. 'mytable1'

            2. Choose the amount of columns (=fields)

                e.g. 5

            3. Choose the datatypes

                e.g. INT if numbers like 1, 2, 3

                e.g. VARCHAR if string like "John", "Vanessa", ...

                e.g. choose a length like 50 for strings

            4. Give the fields or columns a name

                 e.g. NR for first integer column

                 e.g. FirstName for second column

                 e.g. LastName for third column

            4. -When ready select your table, and
                e.g. add some data (by clicking on the
                'Insert' tab, and filling in)

 2. -Upload e.g. the following Perl program

     e.g.

      dddtest1.pl

     -use Total Commander
     -store in your cgi-bin,
     -upload in ASCII or text mode
     -and set attribute 755

--- cut here: begin --------------------------------------------------

#!/usr/bin/perl

use CGI qw(:standard);

use Mysql;

print header();

#Import the CGI parameters into the $Q structure
import_names();

#Connect to your database

my $db = "your databasename";

my $host = "mysql20.powweb.com";

my $user = "tester";

my $password = "test";

$dbh = DBI->connect( "DBI:mysql:database=$db;host=$host", $user, 
$password, {RaiseError => 1} ) or die "Couldn't connect to 
database:$serverTabl " . DBI->errstr;

# ---

# To run your SQL query: in general:
#
# my $sth = $dbh->prepare( "<your SQL query>" );

my $sth = $dbh->prepare( "INSERT INTO `mytable1` ( `nr` , 
`firstname` , `lastname` ) VALUES ( '5', 'Knud', 'van Eeden' );" );

$sth->execute;

my $sth = $dbh->prepare( "INSERT INTO `mytable1` ( `nr` , 
`firstname` , `lastname` ) VALUES ( '6', 'Knud', 'van Eeden' );" );

$sth->execute;

my $sth = $dbh->prepare( "INSERT INTO `mytable1` ( `nr` , 
`firstname` , `lastname` ) VALUES ( '7', 'Knud', 'van Eeden' );" );

$sth->execute;

my $sth = $dbh->prepare( 'SELECT * FROM mytable1 WHERE firstname 
LIKE "%Knud%"' );

$sth->execute;

# ---

# to show the results of the SQL query:

my @array;

while ( @array = $sth->fetchrow_array() ) {
   print( "<BR>" );
   write();
}

# Check to see if fetch terminated early
warn( $DBI::errstr ) if $DBI::err;

$sth->finish();

$dbh->disconnect();

format STDOUT =
@<<<<<<@<<<<<<<<<
$array[ 0 ], $array[ 1 ]
.

# ---

$dbh->disconnect;

--- cut here: end ----------------------------------------------------

 3. Test your program

    1. Type the URL of your program in your browser

        e.g.

         http://www.yourwebsite/cgi-bin/dddtest1.pl

    2. You should see in your browser output like
       the following:

--- cut here: begin --------------------------------------------------

5 Knud
6 Knud
7 Knud

--- cut here: end ----------------------------------------------------

---
---

Internet: see also:

---



----------------------------------------------------------------------