#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    56
    Rep Power
    14
    Please excuse the messy code, I'm still in early stages.

    I have the following code i'm using using with a form to search. Currently I have it set up to search if a last name or first name is entered.

    Question: How can I set this up So that if you search on last name only it returns results which match and also if you search on last name and first name it returns the results that match exactly; Not ones that match both last name and first name?

    ex. search on john doe; will return

    john doe

    not---

    john smith
    anna doe
    john doe


    <html>
    <head>
    <title>Untitled</title>
    </head>

    <body>

    <?php

    $db = mysql_connect("pittweb");

    mysql_select_db("mysql",$db);

    if ($submit) {

    $result = mysql_query("SELECT * FROM members WHERE last_name LIKE '$last_name' OR first_name LIKE '$first_name'",$db);

    while ($myrow = mysql_fetch_array($result)) {

    printf("<Table border=1 cellpadding=2><TR><TD width=75 class="sans"><b>%s</b>n</td><TD width=105 class="sans"><b>%s</b>n</td><td width=275 class="sans">%s</td><td width=75 class="sans"><a href="http://pitt-mylan-ir/edit_member.php3?member_id=%s">EDIT</a>n</td><td width=75 class="sans"><a href="http://pitt-mylan-ir/confirm_delete.php3?member_id=%s">DELETE</a>n</td></tr></table>", $myrow["first_name"], $myrow["last_name"], $myrow["company"], $myrow["member_id"],$myrow["member_id"]);


    }

    }

    ?>

    <form method="post" action="<?php echo $PHP_SELF?>">

    <?php

    if ($member_id) {

    // editing so select a record

    $sql = "SELECT * FROM members WHERE member_id=$member_id";

    $result = mysql_query($sql);

    $myrow = mysql_fetch_array($result);

    $member_id = $myrow["member_id"];

    $first_name = $myrow["first_name"];

    $last_name = $myrow["last_name"];

    $address = $myrow["address"];

    $city = $myrow["city"];

    $company = $myrow["company"];

    $faxlist = $myrow["faxlist"];

    $state = $myrow["state"];

    $zip = $myrow["zip"];

    $email = $myrow["email"];

    $q = $myrow["q"];

    $k = $myrow["k"];

    $conference = $myrow["conference"];

    $calendar = $myrow["calendar"];

    $christmas = $myrow["christmas"];

    $pocketcal = $myrow["pocketcal"];

    // print the member_id for editing

    ?>

    <input type=hidden name="member_id" value="<?php echo $member_id ?>">

    <?php

    }

    ?>


    <Table><tr>
    <td width=50 class="sans">First:</td><td><input type="Text" name="first_name" value="<?php echo $first_name ?>" size="30"><br></td><td width=50>&nbsp;</td><td width=100 class="sans">10K:</td><td><input type="checkbox" name="k" value=<?php echo $k ?> <?php if ($k="CHECKED") { echo "CHECKED"; } ?>></td></tr>

    <tr><td width=50 class="sans">Last:</td><td><input type="Text" name="last_name" value="<?php echo $last_name ?>" size="30"><br></td><td width=50>&nbsp;</td><td width=100 class="sans">10Q:</td><td><input type="checkbox" name="q" value=<?php echo $q ?> <?php if ($q="CHECKED") { echo "CHECKED"; } ?>></td></tr>

    <tr><td width=50 class="sans">company:</td><td><input type="Text" name="company" value="<?php echo $company ?>" size="30"><br></td><td width=50>&nbsp;</td><td width=100 class="sans">Calendar:</td><td><input type="checkbox" name="calendar" value=<?php echo $calendar ?> <?php if ($calendar="CHECKED") { echo "CHECKED"; } ?>></td></tr>

    <tr><td width=50 class="sans">Address:</td><td><input type="Text" name="address" value="<?php echo $address ?>" size="30"><br></td><td width=50>&nbsp;</td><td width=100 class="sans">Conference Call:</td><td><input type="checkbox" name="conference" value=<?php echo $conference ?> <?php if ($conference="CHECKED") { echo "CHECKED"; } ?>></td></tr>

    <tr><td width=50 class="sans">City:</td><td><input type="Text" name="city" value="<?php echo $city ?>" size="30"><br></td><td width=50>&nbsp;</td><td width=100 class="sans">Faxlist:</td><td><input type="checkbox" name="faxlist" value=<?php echo $faxlist ?> <?php if ($faxlist="CHECKED") { echo "CHECKED"; } ?>></td></tr>

    <tr><td width=50 class="sans">State:</td><td><input type="Text" name="state" value="<?php echo $state ?>" size="30"><br></td><td width=50>&nbsp;</td><td width=100 class="sans">Investor Xmas:</td><td><input type="checkbox" name="christmas" value=<?php echo $christmas ?> <?php if ($christmas="CHECKED") { echo "CHECKED"; } ?>></td></tr>

    <tr><td width=50 class="sans">Zip:</td><td><input type="Text" name="zip" value="<?php echo $zip ?>" size="30"><br></td><td width=50>&nbsp;</td><td width=100 class="sans">Pocket Calendar:</td><td><input type="checkbox" name="pocketcal" value=<?php echo $pocketcal ?> <?php if ($pocketcal="CHECKED") { echo "CHECKED"; } ?>></td></tr>

    <tr><td width=50 class="sans">Email:</td><td><input type="Text" name="email" value="<?php echo $email ?>" size="30"><br></td><td width=50>&nbsp;</td><td width=100 class="sans">&nbsp;</td><td>&nbsp;</td></tr>


    <tr><td width=50 class="sans">&nbsp;</td><td class="sans"><input type="Submit" name="submit" value="Enter information"></td></tr>

    </form>

    </table>

    </body>

    </html>




  2. #2
  3. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,642
    Rep Power
    4492
    Construct your query:
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    if(isset($last_name))
    {
    $sql = "SELECT * FROM members WHERE ";
    if(isset($first_name))
    {
    $sql .= "first_name = '$first_name' AND last_name = '$last_name'";
    }
    else
    {
    $sql .= "last_name = '$last_name'";
    }
    $result = mysql_query($sql);
    }
    else
    {
    echo "Bad search criteria.";
    }
    [/code]

    Using LIKE without any wildcards '%' isn't very efficient.

    ---John Holmes...

    ------------------
    *************************************************************
    * The manual can probably answer 90% of your questions...
    *
    * PHP Manual. www.php.net/manual
    * MySQL Manual: www.mysql.com/documentation/mysql/bychapter
    *************************************************************
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    56
    Rep Power
    14
    Alright John, I stumbled onto very similar example as your, but yours is much more efficient.

    The only other question I have is, with the current setup both first name and last name must be filled in to get results. I guess I'm trying to get it to meet exact criteria all the time. If first and last name are put into search on the results exactly matching are returned. My goal is to have it so if just first name is entered then everyone that matches the first name are returned. I also must keep in mind that first and last are just 2 possible fields for searching.

    Is this realistic what I'm trying to get at here?

    example: search on john doe will return;

    john doe

    NOT

    john smith or jane doe

    example: search on john will return;

    john doe
    john smith
    etc.

    example: search on "john" for first name and "McDonalds" for company will return;

    all john that work at mcdonald's

    Hope this clears it up. Maybe this isn't that possible.

    Thanks again for your help.

    I now have the following code:

    <html>
    <head>
    <title>Untitled</title>
    <SCRIPT LANGUAGE="JavaScript"><!-- //
    if (navigator.appVersion.indexOf("Win") == -1) {
    document.write('<LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheets/interactive_mac.css" TITLE="Imac">');
    }else{
    document.write('<LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheets/interactive_win.css" TITLE="Iwin">');
    }
    //-->
    </SCRIPT><LINK rel="stylesheet" href="stylesheets/cross_sheet.css">
    </head>

    <body>

    <?php

    $db = mysql_connect("pittweb");

    mysql_select_db("mylan",$db);

    if ($submit) {

    if(isset($last_name)){
    $sql = "SELECT * FROM members WHERE ";
    if(isset($first_name))
    {
    $sql .= "first_name = '$first_name' AND last_name = '$last_name'";
    }
    else
    {
    $sql .= "last_name = '$last_name'";
    }
    $result = mysql_query($sql);
    }
    else
    {
    echo "Bad search criteria.";
    }

    $results = mysql_query($query, $db);



    while ($myrow = mysql_fetch_array($result)) {

    printf("<Table border=1 cellpadding=2><TR><TD width=75 class="sans"><b>%s</b>n</td><TD width=105 class="sans"><b>%s</b>n</td><td width=275 class="sans">%s</td><td width=75 class="sans"><a href="http://pitt-mylan-ir/edit_member.php3?member_id=%s">EDIT</a>n</td><td width=75 class="sans"><a href="http://pitt-mylan-ir/confirm_delete.php3?member_id=%s">DELETE</a>n</td></tr></table>", $myrow["first_name"], $myrow["last_name"], $myrow["company"], $myrow["member_id"],$myrow["member_id"]);


    }

    }

    ?>
    <br>
    <a href="query.php3">Return to Search</a>

    </body>

    </html>



    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by SepodatiCreations:
    Construct your query:
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    if(isset($last_name))
    {
    $sql = "SELECT * FROM members WHERE ";
    if(isset($first_name))
    {
    $sql .= "first_name = '$first_name' AND last_name = '$last_name'";
    }
    else
    {
    $sql .= "last_name = '$last_name'";
    }
    $result = mysql_query($sql);
    }
    else
    {
    echo "Bad search criteria.";
    }
    [/code]

    Using LIKE without any wildcards '%' isn't very efficient.

    ---John Holmes...

    [/quote]

  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    56
    Rep Power
    14
    Thanks to everyone who helped. Here's the final working result:

    <?php

    $db = mysql_connect("pittweb");

    mysql_select_db("mysql",$db);

    if ($submit) {

    $and = "";
    $sql = "select * from members where";

    if ($first_name){
    $sql .= " first_name='$first_name' ";
    $and = "AND";
    }
    if ($last_name){
    $sql .= $and;
    $sql .= " last_name='$last_name' ";
    $and = "AND";
    }
    if ($company){
    $sql .= $and;
    $sql .= " company='$company' ";
    $and = "AND";
    }
    if ($address){
    $sql .= $and;
    $sql .= " address='$address' ";
    $and = "AND";
    }
    if ($city){
    $sql .= $and;
    $sql .= " city='$city' ";
    $and = "AND";
    }
    if ($zip){
    $sql .= $and;
    $sql .= " zip='$zip' ";
    $and = "AND";
    }
    if ($state){
    $sql .= $and;
    $sql .= " state='$state' ";
    $and = "AND";
    }
    if ($email){
    $sql .= $and;
    $sql .= " email='$email' ";
    $and = "AND";
    }
    if ($q){
    $sql .= $and;
    $sql .= " q='$q' ";
    $and = "AND";
    }
    if ($k){
    $sql .= $and;
    $sql .= " k='$k' ";
    $and = "AND";
    }
    if ($calendar){
    $sql .= $and;
    $sql .= " calendar='$calendar' ";
    $and = "AND";
    }
    if ($conference){
    $sql .= $and;
    $sql .= " conference='$conference' ";
    $and = "AND";
    }
    if ($christmas){
    $sql .= $and;
    $sql .= " christmas='$christmas' ";
    $and = "AND";
    }
    if ($pocketcal){
    $sql .= $and;
    $sql .= " pocketcal='$pocketcal' ";
    $and = "AND";
    }
    if ($faxlist){
    $sql .= $and;
    $sql .= " faxlist='$faxlist' ";
    $and = "AND";
    }
    $result = mysql_query($sql);
    }
    else
    {
    echo "Bad search criteria.";
    }


    $results = mysql_query($query, $db);



    while ($myrow = mysql_fetch_array($result)) {

    printf("<Table border=1 cellpadding=2><TR><TD width=75 class="sans"><b>%s</b>n</td><TD width=105 class="sans"><b>%s</b>n</td><td width=275 class="sans">%s</td><td width=75 class="sans"><a href="http://pitt-mylan-ir/edit_member.php3?member_id=%s">EDIT</a>n</td><td width=75 class="sans"><a href="http://pitt-mylan-ir/confirm_delete.php3?member_id=%s">DELETE</a>n</td></tr></table>", $myrow["first_name"], $myrow["last_name"], $myrow["company"], $myrow["member_id"],$myrow["member_id"]);


    }



    ?>


    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by cstephenson:
    Alright John, I stumbled onto very similar example as your, but yours is much more efficient.

    The only other question I have is, with the current setup both first name and last name must be filled in to get results. I guess I'm trying to get it to meet exact criteria all the time. If first and last name are put into search on the results exactly matching are returned. My goal is to have it so if just first name is entered then everyone that matches the first name are returned. I also must keep in mind that first and last are just 2 possible fields for searching.

    Is this realistic what I'm trying to get at here?

    example: search on john doe will return;

    john doe

    NOT

    john smith or jane doe

    example: search on john will return;

    john doe
    john smith
    etc.

    example: search on "john" for first name and "McDonalds" for company will return;

    all john that work at mcdonald's

    Hope this clears it up. Maybe this isn't that possible.

    Thanks again for your help.

    I now have the following code:

    <html>
    <head>
    <title>Untitled</title>
    <SCRIPT LANGUAGE="JavaScript"><!-- //
    if (navigator.appVersion.indexOf("Win") == -1) {
    document.write('<LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheets/interactive_mac.css" TITLE="Imac">');
    }else{
    document.write('<LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheets/interactive_win.css" TITLE="Iwin">');
    }
    //-->
    </SCRIPT><LINK rel="stylesheet" href="stylesheets/cross_sheet.css">
    </head>

    <body>

    <?php

    $db = mysql_connect("pittweb");

    mysql_select_db("mylan",$db);

    if ($submit) {

    if(isset($last_name)){
    $sql = "SELECT * FROM members WHERE ";
    if(isset($first_name))
    {
    $sql .= "first_name = '$first_name' AND last_name = '$last_name'";
    }
    else
    {
    $sql .= "last_name = '$last_name'";
    }
    $result = mysql_query($sql);
    }
    else
    {
    echo "Bad search criteria.";
    }

    $results = mysql_query($query, $db);



    while ($myrow = mysql_fetch_array($result)) {

    printf("<Table border=1 cellpadding=2><TR><TD width=75 class="sans"><b>%s</b>n</td><TD width=105 class="sans"><b>%s</b>n</td><td width=275 class="sans">%s</td><td width=75 class="sans"><a href="http://pitt-mylan-ir/edit_member.php3?member_id=%s">EDIT</a>n</td><td width=75 class="sans"><a href="http://pitt-mylan-ir/confirm_delete.php3?member_id=%s">DELETE</a>n</td></tr></table>", $myrow["first_name"], $myrow["last_name"], $myrow["company"], $myrow["member_id"],$myrow["member_id"]);


    }

    }

    ?>
    <br>
    <a href="query.php3">Return to Search</a>

    </body>

    </html>



    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    if(isset($last_name))
    {
    $sql = "SELECT * FROM members WHERE ";
    if(isset($first_name))
    {
    $sql .= "first_name = '$first_name' AND last_name = '$last_name'";
    }
    else
    {
    $sql .= "last_name = '$last_name'";
    }
    $result = mysql_query($sql);
    }
    else
    {
    echo "Bad search criteria.";
    }
    [/code]

    Using LIKE without any wildcards '%' isn't very efficient.

    ---John Holmes...

    [/quote]

    [/B][/quote]

Similar Threads

  1. searching function in VB
    By bostonmonkey in forum Visual Basic Programming
    Replies: 2
    Last Post: December 5th, 2003, 09:12 AM
  2. I HATE winxp searching
    By don_sparko in forum Dev Shed Lounge
    Replies: 4
    Last Post: August 7th, 2003, 03:52 PM
  3. file searching
    By cyberjaya in forum PHP Development
    Replies: 2
    Last Post: June 16th, 2003, 01:03 AM
  4. what is searching engine?
    By pentium5 in forum Linux Help
    Replies: 3
    Last Post: August 7th, 2002, 11:16 PM
  5. Replies: 1
    Last Post: December 8th, 2000, 04:58 AM

IMN logo majestic logo threadwatch logo seochat tools logo