Posted by Joseph Melnick on 09/28/30 11:19

Mr. Rossen Wrote,

"C. David Rossen" <cdrossen@cdrmarketing.com> wrote in message
news:46GdnZb7fO1suiffRVn-sw@comcast.com...
> Hello:
>
> I think I have what would be an easy solution for most people that know
> mysql. I just don't know it very well.
>
> I have a membership database of a professional organization of lawyers,
> cpa's, etc. There is a field called "designation" (laywers, cpa's, clu's,
> etc). I am going to build a search form with a drop down box so they can
> search by designation. How would I write the select statement? The table
> name is directory.
>
> select from directory where designation = ???????? order by LastName ASC
>
> Not sure how to reference the designation choice from the drop down list
> in
> the select statement.
>
> I also wanted to do a text search for LastName
>
> select from directory where LastName like ?????? order by LastName ASC
>
> If someone can lend a novice a hand, I'd greatly appreciate any help.
> Thanks
> in advance.
>
> David
>

Hello David,

You propably have a finite set of designations with a short form for each
CPA
An individual may have multiple of these designations so if you want to pick
up all that have a CPA designation
then use the INSTR() function to capture this set like this


select * from directory where INSTR('CPA',designation) > 0 order by LastName
ASC

The html form

<select name="designation">
<option name="CPA">CPA</option>
<option name="CLU">CLU</option>
....
</select>

And in your language of choice you will need to replace the 'CPA' in the
above query string before submitting it to your database.

With PHP / MySQL.

<?php
$dbhost = "localhost";
$dbuser = "myuser";
$dbpass = "mypassword";
$dbname = "mydatabase";

$dblink = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db($dbname, $dblink);
$designation =
(array_key_exists('designation',$_REQUEST))?$_REQUEST['designation']:"None";

$query = "select name,designation,phone from directory where
INSTR('".$designation."',designation) > 0 order by LastName ASC";
$result = mysql_query($query,$dblink) or die('select from directory failed:
' . mysql_error());
while($row = mysql_fetch_array($result)) {
// echo the list
echo $row['name']." ".$row['designation']." ".$row['phone']."<br>";
}
?>

The second $query would be similar to this assuming PHP and an search form
input field name of LastName:

$LastName =
(array_key_exists('LastName',$_REQUEST))?$_REQUEST['LastName']:"";
$query = "select name,designation,phone from directory where LastName like
'".$LastName."%' order by LastName ASC";

Hope this gets you off to a running start.

Joseph Melnick
JM Web Consultants
Toronto, ON, Canada
http://www.jphp.com/

[Back to original message]