|
|
Posted by Joseph Melnick on 09/28/68 11:20
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))?$_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]
|