Solved

Database searching and displaying

Posted on 2000-03-21
8
159 Views
Last Modified: 2013-12-25
I have a basic database and I want to have a CGI script that searches through it and then returns information to display. I want numerous searchable fields etc. Go to www.cars.co.nz to see an example of what sort of searching and displaying I want. Thanks
0
Comment
Question by:thecarlot
  • 5
  • 2
8 Comments
 
LVL 16

Expert Comment

by:maneshr
ID: 2642275
what DB are you using??
are you using PERL? if so, do you have the DBI module installed??
0
 
LVL 10

Expert Comment

by:makerp
ID: 2643893
heres an expmale of dbi .. hope this helps. you will need to create a single table database (table1) with two feilds name and address, put some data in them and then create an odbc datasource to your database, call the datasource 'test_db'. this is not a cgi run it at the cmd line. run as cgi just put a content type in and a few <BR>'s to make the out out nice

# make use of the dbi module. for activeperl go to www.activestate.com/packages/zips and
# download the DBI package first and then download what ever DBD drivers you what to use
# this script makes use of the odbc driver module
use DBI;

# the dbd driver we actually want to use
$DBI_DRIVER = "ODBC";

# the dsn info
$DATA_SOURCE = "test_db";
$DATA_SOURCE_USERNAME = "";
$DATA_SOURCE_PASSWORD = "";

# global database handle for this script
my $dbh;

# connect to database, the first parameter will take the form of 'dbi:ODBC:test_db'
$dbh = DBI->connect('dbi:'.$DBI_DRIVER.':'.$DATA_SOURCE,$DATA_SOURCE_USERNAME,$DATA_SOURCE_PASSWORD)
      || die("Connection error : ".$DBI::errstr);

# set the LongReadLen really high incase we have meno fields
$dbh->{LongReadLen} = 30000;

# prepare a stmt, this returns a statment handle
$sth = $dbh->prepare("SELECT * FROM table1") || die("Stmt error : ".$dbh->errstr);


print("Expected fields to be returned from first query.......\n");
@fi = $sth->{'NAME'};
for($i=0;$i<$sth->{'NUM_OF_FIELDS'};$i++)
{
      print("field : @{$fi[0]}[$i]\n");
}

# execute our stmt
$sth->execute() || die("Stmt error : ".$dbh->errstr);

# if we are debugging then we may wish to dump our result in one hit
# the dump_results function uses default values for row delimiter's etc
# these can be overridden
# dump_results(st_handle,maxlen,row_delimiter,field_delimiter,fileh_for_dump)
print("Dump of the results returned by stmt, for debugging purposes\n");
DBI::dump_results($sth);

print("\nNormal results retrival using a loop to go through each row returned\n");
# execute our stmt
$sth->execute() || die("Stmt error : ".$dbh->errstr);

# fetch and print the rows, returns undef on failure, hash reference on success
while($dat = $sth->fetchrow_hashref())
{
      # get the name field from our hashref
      print("Name : ".$dat->{"name"}."\t Address : ".$dat->{"address"}."\n");
}

# finish the statement handle, if we dont do this the disconnect function will spew some errors
$sth->finish();

print("\nDoing some inserts into the database\n");
# set auto commit to false so we have to explicetly call commit on the database handle
$dbh->{"AutoCommit"} = 0;
# we can prepare a stmt and execute it several times with different values in the place holders like this
$sth = $dbh->prepare("INSERT INTO table1 (name,address) VALUES (?,?)") || die("Stmt error : ".$dbh->errstr);
# execute it but this time pass the values we want to replace the place holders
$sth->execute("bart","saturn") || die("Stmt error : ".$dbh->errstr);
# commit it
$dbh->commit() || die("Stmt error : ".$dbh->errstr);
# turn auto commit back on
$dbh->{"AutoCommit"} = 1;
# finish with the stmt handle
$sth->finish();

print("\nDoing an update on all records (changing address to mars)\n");
# we can use the do function to execute a stmt and get the number of rows it affected
$rows = $dbh->do("UPDATE table1 SET address = 'mars'") || die("Stmt error : ".$dbh->errstr);
print("That affected ".$rows." rows\n");

# now print the updated table out
print("\nUpdated table contents are : \n");
$sth = $dbh->prepare("SELECT * FROM table1") || die("Stmt error : ".$dbh->errstr);
$sth->execute() || die("Stmt error : ".$dbh->errstr);

while($dat = $sth->fetchrow_hashref())
{
      # get the name field from our hashref
      print("Name : ".$dat->{"name"}."\t Address : ".$dat->{"address"}."\n");
}
$sth->finish();

print("\n\tBye bye.... Shutting up shop !!!!");
# closes the database
$dbh->disconnect();

0
 

Author Comment

by:thecarlot
ID: 2646976
Adjusted points from 200 to 205
0
 

Author Comment

by:thecarlot
ID: 2646977
Thanks for the Answer Makerp, but before I give you the points I need to know a little more. I know very very little about Perl/CGI scripts and how they work and how they are programmed. I have a basic knowledge about visual basic and its language (although that is irrelevent). I know about html so thats not a problem and my friend is an Access guru (the database will be made in Access) so there is no problem there. But I need what you have already written in more understandable terms considering my Perl/CGI knowledge (in other words tell me exactly what each line means in pure english, sorry I have to be such a pain). Again go to www.cars.co.nz to see what sort of a search I want to run because my database will be for cars and will require the same searchable fields etc. Thanks alot, hope to hear from you soon.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 10

Expert Comment

by:makerp
ID: 2648358
i will do you a nice simple serach cgi script the out puts the results in html. it will use a single table database (ACCESS)
0
 
LVL 10

Accepted Solution

by:
makerp earned 205 total points
ID: 2648433
# this script uses a single table access db. the table is called mates
# and it contains 3 rows.. id (autonumber), name (text), address (text)
# you will need to create this db and then create an odbc dsn up. go to
# control panel/odbc and click the system tab. go to add and go through the steps.
# give the dsn a name and thgen select the db you have just created. the dsn must be a system
# dsn so the web user account can see it.....

# this script is very simple and is intended to show you the basics. i have commented it
# if you done know perl then i suggest a few quick perl tutorials just to learn the
# basics.. you dont need to be to hot to do perl cgi. once you are cool with
# this then go iver the dbi_demo again as this shows of some of the slightly more
# advanced features of dbi/dbd's


# dbi library
use DBI;
# cgi library
use CGI;

# the dbd driver we actually want to use/ you will need to download (see other example)
# dbd driver is : DBD-ODBC
$DBI_DRIVER = "ODBC";
# the dsn info
$DATA_SOURCE = "test_db";

# create a cgi object
$query = new CGI;

# if we have some parameters then the script must have been called from our
# form so lets do a query a splash out the results
if($query->param())
{
      # print out the http header, but tell the browser to
      # expire it straight away as its dynamic
      print $query->header(-expires=>'now');

      # connect to our db
      $dbh = DBI->connect('dbi:'.$DBI_DRIVER.':'.$DATA_SOURCE,undef,undef) || die("Connection error : ".$DBI::errstr);

      # set the LongReadLen really high incase we have meno fields
      $dbh->{LongReadLen} = 30000;

      # prepare a stmt, this returns a statment handle. note the use of the $query->param() to fetch an input
        # field from the form that called this script. this is the search field on the form
      $sth = $dbh->prepare("SELECT * FROM mates WHERE name = '".$query->param('name')."'") || die("Stmt error : ".$dbh->errstr);

      # execute it
      $sth->execute() || die("Stmt error : ".$dbh->errstr);

      # now loop through the results printing out a lovely
      # table for the user
      print("Your <B>query</B> yileded the following results <BR><BR>");
      print("<TABLE BORDER=1><TR><TH>Name</TH><TH>Address</TH><TR>");
      # fetchrow_hashref returns a hash, you can use the colum name in the following way to get results
      while($dat = $sth->fetchrow_hashref())
      {
            # get the name and address  field from our hashref
            print("<TR><TD>".$dat->{"name"}."</TD><TD>".$dat->{"address"}."</TD></TR>");
      }
      print("</TABLE>");
      $sth->finish();
      print("<HR>Return to search page <A HREF=cgi_test.pl>re-search</A>");
}
else # print out the form
{
      print $query->header();
      print("
            <HR>
            Search the database
            <FORM METHOD=POST ACTION=cgi_test.pl>
                  <INPUT TYPE=TEXT NAME=name>
                  <BR>
                  <INPUT TYPE=SUBMIT VALUE='Submit Query'>
            </FORM>
      ");
}
0
 
LVL 10

Expert Comment

by:makerp
ID: 2648438
sorry about ther indentation but when i cut and paste it seems to get trashed
0
 
LVL 10

Expert Comment

by:makerp
ID: 2648448
i hope this starts you off. any questions post a comment and ill awnswer.

the best thing for you to do now is probably paste the expamle into a page and then call it from a browser. see it working then change it. when you are developing your search engine make sure that you get the sql to do the work ,, NOT your code.

you will need perl installed.. if you a win32 then i stronly recomed activeperl. once you have this install the dbi and odbc dbd driver.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

In this tutorial I will show you how to provide a dynamic RTF document on your website generated with data from your database. For this tutorial you will need Microsoft Word or WordPad, WhizBase and Microsoft Access. In this tutorial I will show …
Batch, VBS, and scripts in general are incredibly useful for repetitive tasks.  Some tasks can take a while to complete and it can be annoying to check back only to discover that your script finished 5 minutes ago.  Some scripts may complete nearly …
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now