|
|
Posted by C. David Rossen on 09/28/05 11:19
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))?$_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/
>
>
Navigation:
[Reply to this message]
|