Solved

Database searching and displaying

Posted on 2000-03-21
8
164 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

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

Title # Comments Views Activity
issue with beginner powershell script. 5 75
convert Systemjs to Webpack 3 82
This script has to run not aganist multiple servers using csv 16 40
Powershell Code 3 42
Introduction:   Welcome to my first article ever. To begin with, the reason I write this article.  I participated in a question on Experts Exchange about the start command in Windows and there were some discussion about the usage. The discussio…
A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
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 …

733 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