MySQL ODBC Connector - Creating a Web Database Search Application

Author

Creating a Web Database Search Application

Douglas S. Davis

2006-02-25, 9:43 am

Hello,

I commonly create webpages that need to search through a MySQL
database and then display the results to the user. An example would
be a database that contains the following:

first name
last name
age
gender
location
job
interests

I usually create a webpage with a combination of drop down menus,
checkboxes, and radio buttons that allow the user to select what
things they want to search for. An example might be that a user
wants to find all results with:

last name like "smi"
age between 25 and 35
gender = female
interests like "golf"

But I've found it tricky to craft MySQL select statements because due
to the way the searches can vary, the queries get complicated pretty quickly.

Is there any simple way to do this type of thing? I write my scripts
in Perl on Unix (Solaris). Are there webpages that will explain good
ways to do this?

Any tips are welcomed.

Thanks,

Douglas

Douglas S. Davis
Programmer/Analyst
Haverford College
Administrative Computing
370 Lancaster Ave.
Haverford, PA 19041
610-896-4206

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Jonathan Mangin

2006-02-25, 9:43 am

----- Original Message -----
From: "Douglas S. Davis" <dsdavis@haverford.edu>
To: <mysql@lists.mysql.com>
Sent: Tuesday, February 21, 2006 3:58 PM
Subject: Creating a Web Database Search Application

> Hello,
>
> I commonly create webpages that need to search through a MySQL
> database and then display the results to the user. An example would
> be a database that contains the following:
>
> first name
> last name
> age
> gender
> location
> job
> interests
>
>
> I usually create a webpage with a combination of drop down menus,
> checkboxes, and radio buttons that allow the user to select what
> things they want to search for. An example might be that a user
> wants to find all results with:
>
> last name like "smi"
> age between 25 and 35
> gender = female
> interests like "golf"
>
> But I've found it tricky to craft MySQL select statements because due
> to the way the searches can vary, the queries get complicated pretty
quickly.
>
> Is there any simple way to do this type of thing? I write my scripts
> in Perl on Unix (Solaris). Are there webpages that will explain good
> ways to do this?
>
> Any tips are welcomed.
>
>
>
> Thanks,
>
>
> Douglas
>
You could have a menu listing the reports available and craft
the sql specially for each. The hard part is naming them so
it's readily apparent what each report reports.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Jonathan Mangin

2006-02-25, 9:43 am

----- Original Message -----
From: "Douglas S. Davis" <dsdavis@haverford.edu>
To: <mysql@lists.mysql.com>
Sent: Tuesday, February 21, 2006 3:58 PM
Subject: Creating a Web Database Search Application

> Hello,
>
> I commonly create webpages that need to search through a MySQL
> database and then display the results to the user. An example would
> be a database that contains the following:
>
> first name
> last name
> age
> gender
> location
> job
> interests
>
>
> I usually create a webpage with a combination of drop down menus,
> checkboxes, and radio buttons that allow the user to select what
> things they want to search for. An example might be that a user
> wants to find all results with:
>
> last name like "smi"
> age between 25 and 35
> gender = female
> interests like "golf"
>
> But I've found it tricky to craft MySQL select statements because due
> to the way the searches can vary, the queries get complicated pretty
quickly.
>
> Is there any simple way to do this type of thing? I write my scripts
> in Perl on Unix (Solaris). Are there webpages that will explain good
> ways to do this?
>
> Any tips are welcomed.
>
>
>
> Thanks,
>
>
> Douglas

[Hit send too soon]

With so few fields, sticking with one report isn't
out of the question. Don't know if you're using
this technique:

my $lname = $q->param('lname') || '%';

So all fields not filled/selected by the user are
wildcards by default. A completely empty form
pulls all data in the relevant tables.

--Jon

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

BÁRTHÁZI András

2006-02-25, 9:43 am

Hi,

> With so few fields, sticking with one report isn't
> out of the question. Don't know if you're using
> this technique:
>
> my $lname = $q->param('lname') || '%';
>
> So all fields not filled/selected by the user are
> wildcards by default. A completely empty form
> pulls all data in the relevant tables.

Hmm, doing a

... column-name LIKE '%' ...

isn't slower than including nothing?

I mean (warning!, no escaping):

push @where, "lname LIKE '$lname'"
if $lname;

And:

$where = '';
$where = "WHERE ".join(' AND ', @where)
if @where;

Bye,
Andras

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links