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
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_D RIVER.':'. $DATA_SOUR CE,$DATA_S OURCE_USER NAME,$DATA _SOURCE_PA SSWORD)
|| 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,max len,row_de limiter,fi eld_delimi ter,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","satu rn") || 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();
# 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_D
|| 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
{
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,max
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","satu
# 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();
ASKER
Adjusted points from 200 to 205
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
are you using PERL? if so, do you have the DBI module installed??