Link to home
Start Free TrialLog in
Avatar of thecarlot
thecarlot

asked on

Database searching and displaying

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
Avatar of maneshr
maneshr

what DB are you using??
are you using PERL? if so, do you have the DBI module installed??
Avatar of Paul Maker
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();

Avatar of thecarlot

ASKER

Adjusted points from 200 to 205
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.
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)
ASKER CERTIFIED SOLUTION
Avatar of Paul Maker
Paul Maker
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
sorry about ther indentation but when i cut and paste it seems to get trashed
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.