Solved

Database searching and displaying

Posted on 2000-03-21
8
163 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

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
 
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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This tutorial will discuss the log-in process using WhizBase. In this article I assume you already know HTML. I will write the code using WhizBase Server Pages, so you need to know some basics in WBSP (you might look at some of my other articles abo…
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 …
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…
The viewer will learn how to count occurrences of each item in an array.

829 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