Select records from result of drop down selection

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

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))?$_REQU EST['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/

Joseph:

Thank you very much for all the help.

Actually, this morning I figured out something that seems to work but quite
different from what you have given me. I'll run it by you and maybe you can
see if I may run into trouble even though it works.

First, I created an HTML page for both forms (1 to search by designation,
the other to do the name search). For the designation search, the name of
the drop down is "designation" and the value of each selection is whatever
it is (attorney, cpa, etc). The action of that form is as follows:

<form action="http://www.ekcepc.org/designation.php" method="post">

Then here is how I did the section of the designation.php file:

//select the database
mysql_select_db("ekcep001");

$Designation=$_POST['Designation'];

//select table and criteria
$result = mysql_query("select * from directory where Designation =
'$Designation' ORDER BY LastName ASC");

//get the content
while($r=mysql_fetch_array($result))
{

$LastName=$r["LastName"];
$FirstName=$r["FirstName"];
$Company=$r["Company"];
$StreetAddress=$r["StreetAddress"];
$City=$r["City"];
$State=$r["State"];
$Zip=$r["Zip"];
$Phone=$r["Phone"];
$Fax=$r["Fax"];
$Email=$r["Email"];
$Designation=$r["Designation"];

//display the results
echo
"<center>
<table border='0' width='50%' cellspacing='0' cellpadding='6'>
<tr>
<td bgcolor='#F7EFCE'>
<font face='Arial'><span style='font-size: 9pt'>
<b>$LastName, $FirstName</b><br>
$Company<br>
$StreetAddress<br>
$City, $State $Zip<br>
<b>Tel:</b> $Phone<br>
<b>Fax:</b> $Fax<br>
<a href='mailto:$Email'>$Email</a><br>
<b>Designation:</b> $Designation<br>
</tr>
<tr>
<td><hr color='green'></td>
</tr>
</table>
</center>";
}
?>

For the name search, I simply have a text box on HTML page and action is:

<form action="http://www.ekcepc.org/lastname.php" method="post">

Then here is how I did the section of the lastname.php file:

//select the database
mysql_select_db("ekcep001");

$LastName=$_POST['LastName'];

//select table and criteria
$result = mysql_query("select * from directory where LastName = '$LastName'
ORDER BY LastName ASC");

//get the content
while($r=mysql_fetch_array($result))
{

$LastName=$r["LastName"];
$FirstName=$r["FirstName"];
$Company=$r["Company"];
$StreetAddress=$r["StreetAddress"];
$City=$r["City"];
$State=$r["State"];
$Zip=$r["Zip"];
$Phone=$r["Phone"];
$Fax=$r["Fax"];
$Email=$r["Email"];
$Designation=$r["Designation"];

//display the results
echo
"<center>
<table border='0' width='50%' cellspacing='0' cellpadding='6'>
<tr>
<td bgcolor='#F7EFCE'>
<font face='Arial'><span style='font-size: 9pt'>
<b>$LastName, $FirstName</b><br>
$Company<br>
$StreetAddress<br>
$City, $State $Zip<br>
<b>Tel:</b> $Phone<br>
<b>Fax:</b> $Fax<br>
<a href='mailto:$Email'>$Email</a><br>
<b>Designation:</b> $Designation<br>
</tr>
<tr>
<td><hr color='green'></td>
</tr>
</table>
</center>";
}
?>

Both of them seem to work. Are there any advantages of doing it the way you
layed out? Perhaps there are some hidden dangers with the way I did it. Let
me know. Thanks again.

David

"Joseph Melnick" <jmelnick@jphp.com> wrote in message
news:CJqdnaw_C4Zp3ybfRVn-3w@rogers.com...
> 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))?$_REQU EST['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/
>
>

Mr. Rossen Wrote:

"C. David Rossen" <cdrossen@cdrmarketing.com> wrote in message
news:ibednRncQ8Lz0CbfRVn-oQ@comcast.com...
> Joseph:
>
> Thank you very much for all the help.
>
> Actually, this morning I figured out something that seems to work but
> quite
> different from what you have given me. I'll run it by you and maybe you
> can
> see if I may run into trouble even though it works.
>
> First, I created an HTML page for both forms (1 to search by designation,
> the other to do the name search). For the designation search, the name of
> the drop down is "designation" and the value of each selection is whatever
> it is (attorney, cpa, etc). The action of that form is as follows:
>
> <form action="http://www.ekcepc.org/designation.php" method="post">
>
> Then here is how I did the section of the designation.php file:
>
> //select the database
> mysql_select_db("ekcep001");
>
> $Designation=$_POST['Designation'];

// $Designation could be an empty or may not exist.
// the following line will allow you to set $Designation yp a value of your
choosing
// with the added benefit of sending a known value to your database via
your query.
// You might also validate this value to protect your database for type,
length, format and content.
// string, three to 6 characters in length?, of your know set Lawyer, CPA,
CLU...
$Designation =
(array_key_exists('Designation',$_POST))?$_POST['Designation']:"";


>
> //select table and criteria
> $result = mysql_query("select * from directory where Designation =
> '$Designation' ORDER BY LastName ASC");
>
> //get the content
> while($r=mysql_fetch_array($result))
> {
>
> $LastName=$r["LastName"];
> $FirstName=$r["FirstName"];
> $Company=$r["Company"];
> $StreetAddress=$r["StreetAddress"];
> $City=$r["City"];
> $State=$r["State"];
> $Zip=$r["Zip"];
> $Phone=$r["Phone"];
> $Fax=$r["Fax"];
> $Email=$r["Email"];
> $Designation=$r["Designation"];
>
> //display the results
> echo
> "<center>
> <table border='0' width='50%' cellspacing='0' cellpadding='6'>
> <tr>
> <td bgcolor='#F7EFCE'>
> <font face='Arial'><span style='font-size: 9pt'>
// just a sylistic note: if you know about css then you should know that the
<font> tag is redundant here.
<span style='font-family: Arial, sans-serif; font-size: 9px'>

> <b>$LastName, $FirstName</b><br>
> $Company<br>
> $StreetAddress<br>
> $City, $State $Zip<br>
> <b>Tel:</b> $Phone<br>
> <b>Fax:</b> $Fax<br>
> <a href='mailto:$Email'>$Email</a><br>
> <b>Designation:</b> $Designation<br>
> </tr>
> <tr>
> <td><hr color='green'></td>
> </tr>
> </table>
> </center>";
> }
> ?>
>
> For the name search, I simply have a text box on HTML page and action is:
>
> <form action="http://www.ekcepc.org/lastname.php" method="post">
>
> Then here is how I did the section of the lastname.php file:
>
> //select the database
> mysql_select_db("ekcep001");
>
> $LastName=$_POST['LastName'];
>
> //select table and criteria
> $result = mysql_query("select * from directory where LastName =
> '$LastName'
> ORDER BY LastName ASC");
>
> //get the content
> while($r=mysql_fetch_array($result))
> {
>
> $LastName=$r["LastName"];
> $FirstName=$r["FirstName"];
> $Company=$r["Company"];
> $StreetAddress=$r["StreetAddress"];
> $City=$r["City"];
> $State=$r["State"];
> $Zip=$r["Zip"];
> $Phone=$r["Phone"];
> $Fax=$r["Fax"];
> $Email=$r["Email"];
> $Designation=$r["Designation"];
>
> //display the results
> echo
> "<center>
> <table border='0' width='50%' cellspacing='0' cellpadding='6'>
> <tr>
> <td bgcolor='#F7EFCE'>
> <font face='Arial'><span style='font-size: 9pt'>
> <b>$LastName, $FirstName</b><br>
> $Company<br>
> $StreetAddress<br>
> $City, $State $Zip<br>
> <b>Tel:</b> $Phone<br>
> <b>Fax:</b> $Fax<br>
> <a href='mailto:$Email'>$Email</a><br>
> <b>Designation:</b> $Designation<br>
> </tr>
> <tr>
> <td><hr color='green'></td>
> </tr>
> </table>
> </center>";
> }
> ?>
>
> Both of them seem to work. Are there any advantages of doing it the way
> you
> layed out? Perhaps there are some hidden dangers with the way I did it.
> Let
> me know. Thanks again.
>
> David
>

The use of INSTR() allows for more flexibility in finding a specific
designation in a string where more than one may occur.

The use of LIKE allows for more flexibility in finding like lastnames.

Also you could investigate the SOUNDEX() function where you can compare
whether a string sounds like a lastname in a column.

The PHP function ARRAY_KEY_EXISTS() in the construct presented allows you
to deal with a scemario where your user does not give you an expected
request and set your variable to a default value and enhances flexibilty.

Nothing wrong with your approach here but there are a number of ways to
accomplish these tasks.

Keep in mind that you should:

Never trust client input!
Always test your work.
Always expect that your clients will use your application in ways you do not
expect.
When things go wrong that it will be you who has to figure out how they
broke your program.

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



> "Joseph Melnick" <jmelnick@jphp.com> wrote in message
> news:CJqdnaw_C4Zp3ybfRVn-3w@rogers.com...
>> 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))?$_REQU EST['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/
>>
>>
>
>