SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    SitePoint Addict
    Join Date
    Feb 2001
    Location
    Shanghai, China
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, it's me again with another simple question.
    I think you discussed it often enough, but I couldn't find it on the board anywhere:

    I want to search in the db ( for example for date and place ).
    So I have my form with :

    form..
    text field..name: "date"
    text field..name: "place"

    And than I have my sql-query looking like this:

    select from..where date='$date' and place='$place'

    I think so far it is standard, is it?
    My problem is. If the user fills out just one of the form-fields, I get no results, but in this case I want it to search for the other one only. So do I have to make many select-querys and put them in if-loops?
    Thanx, Alex

  2. #2
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try this.

    where date like '$date%' and place like '$place%'

    That way if your date or place is missing it'll default to searching for % (which is a wildcard)


    This surely isn't the best way to do this - but it's probably the quickest fix.
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  3. #3
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    With only two fields you can do this

    $sqlstart .= "SELECT * from tablename ";

    if ($date || $place) {
    $sqlstart .= "WHERE ";
    }

    if ($date != "") {
    $sqlstart .= "date = '$date' "
    }

    if (($date != "") && ($place != "")) {
    $sqlstart .= "AND "
    }

    if ($place != "") {
    $sqlstart .= "place = '$place' "
    }
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  4. #4
    SitePoint Addict
    Join Date
    Feb 2001
    Location
    Shanghai, China
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But it will be more than two fields.
    Is there any other way for it?

  5. #5
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There are lots of ways to do it. Another way

    PHP Code:
    //Array of searchable fields make sure they are the same for the form elements as the names for the fields in the db
    $searchable = array("name""email""city""place""date");
    $sql .= "SELECT * FROM tablename WHERE (";
    foreach(
    $searchable as $val) {
        
    $tmp $val;
        
    $sql .= ($$tmp != "") ? "($val LIKE '%$$tmp%') AND " "($val LIKE '%') AND ";
        }
        
    $sql substr($sql0, -5).")"
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  6. #6
    SitePoint Addict
    Join Date
    Feb 2001
    Location
    Shanghai, China
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't understand it but it doesn't matter , I will have a try.

  7. #7
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well this uses variable variables so if you have the fields that you want searched in an array and the same form elements exist, then you can loop trhough the array use a variable ariable to see if the var is empty if it is you put a % sign in for the search if not then you put the real var try printing $sql to the screen before running the query it will probably clear a few things up for you.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  8. #8
    SitePoint Addict
    Join Date
    Feb 2001
    Location
    Shanghai, China
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey Freddy, that sound logical.
    But my little skript here doesn't function. I think it's not a problem of what you gave me.
    I get the error: "Supplied argument is not a valid MySQL result resource" in the line with the while ($row) ...
    I hate this error, I get it all the time and than I change something and suddenly it works.But not this time.
    That's the skript:


    <?php

    $dbcnx = @mysql_connect("localhost", "username", "password");
    mysql_select_db('dbname');

    //Array of searchable fields make sure they are the same for the form elements as the names for the fields in the db

    $searchable = array("vname", "nname", "loc");
    $sql .= "SELECT vname, nname, loc FROM tablename WHERE (";
    foreach($searchable as $val) {
    $tmp = $val;
    $sql .= ($$tmp != "") ? "($val LIKE '%$$tmp%') AND " : "($val LIKE '%') AND ";
    }

    $sql = substr($sql, 0, -5).")";


    while ($row = mysql_fetch_array($sql)) {
    $Vname = $row["vname"];
    echo("$Vname ");
    }

    ?>

    Thanx, Alex
    Last edited by smorb; Feb 22, 2001 at 16:47.

  9. #9
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You never actaully ran the query should be

    something like this right above the fetch_array() line
    PHP Code:
    $result mysql_query($sql);
    while (
    $row mysql_fetch_array($result)) {
    $Vname $row["vname"]; 
    echo(
    "$Vname "); 

    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  10. #10
    SitePoint Addict
    Join Date
    Feb 2001
    Location
    Shanghai, China
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ops, yes i forgott this. But now it is doing nothing. I can just see a white screen.

    <?php

    $dbcnx = @mysql_connect("localhost", "username", "password");
    mysql_select_db('dbname');

    //Array of searchable fields make sure they are the same for the form elements as the names for the fields in the db
    $searchable = array("vname", "nname", "ort");
    $sql .= "SELECT * FROM event WHERE (";
    foreach($searchable as $val) {
    $tmp = $val;
    $sql .= ($$tmp != "") ? "($val LIKE '%$$tmp%') AND " : "($val LIKE '%') AND ";
    }

    $sql = substr($sql, 0, -5).")";

    $result = mysql_query($sql);
    while ($row = mysql_fetch_array($result)) {
    echo("<p>" . $row["vname"] . "</p>");
    }


    ?>

    and that's my form:

    <FORM ACTION="search.php" METHOD=get>


    <p>
    <input type="text" name="vname">
    </p>
    <p>
    <input type="text" name="nname">
    </p>
    <p>
    <input type="text" name="ort">
    </p>
    <p>
    <input type=submit name="$submitvname" value="Submit">
    </p>
    </form>

    mhhh, I'm sure it's just another easy mistake.
    P.s Yes, i changed the 'username', 'password' and so...

  11. #11
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Next step in the full proof PHP Debug toolchest,

    Step 1: print $sql to the screen instead of running the query and then copy and paste it into PHPMyAdmin or the commandline to see what comes back.

    Step 2: use $result = mysql_query($sql) or DIE(mysql_error()); to see what error is prodcuing
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  12. #12
    SitePoint Addict
    Join Date
    Feb 2001
    Location
    Shanghai, China
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Step 2 has no result, I still get nothing back.
    But I don't understand step 1.
    Where do you want me to print $sql ?

  13. #13
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just print it to the screen it really doesn't matter where, so the code that you showed is that it, is that the whole shebang that you are trying to use?
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  14. #14
    SitePoint Addict
    Join Date
    Feb 2001
    Location
    Shanghai, China
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hää? Shebang is not in my dictionary ?
    When I put the code in the phpMyAdmin field, I get the reply:
    MySQL reports: You have an error in your SQL syntax near '$searchable = array("vname", "nname", "ort")' at line 1

    But I'm sure that "vname", "nname" and "ort" are the name of the fields in my table. So what is wrong?
    Thanks for your patience, Alex

  15. #15
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    All Iwanted you to do is to print $sql to the page load the page copy and paste the $sql as it looks after being printed to the screen and just paste that into PhPMyAdmin should look something like

    SELECT * FROM tablename WHERE ((vname LIKE '%') AND (nname LIKE '%') AND (ort LIKE '%'))
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  16. #16
    SitePoint Addict
    Join Date
    Feb 2001
    Location
    Shanghai, China
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you mean with "print $sql to the page" that I should write it anywhere in my php skript ( did you mean this ), than I get a error for the line where there is the $sql

  17. #17
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You put print $sql; on any line inside the php tags
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  18. #18
    SitePoint Addict
    Join Date
    Feb 2001
    Location
    Shanghai, China
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh, now I understand. Yes, I got this here:
    SQL-Befehl:
    SELECT * FROM event WHERE ((vname LIKE '%') AND (nname LIKE '%') AND (ort LIKE '%'))

    and in phpmysqladmin I got all inputs from "event".

  19. #19
    SitePoint Addict
    Join Date
    Feb 2001
    Location
    Shanghai, China
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey and if I fill out the form and send the inputs to the page, the domain looks like this:
    .../search.php?vname=nora&nname=spindler&ort=heidelberg&submit=Submit

    So everything should be OK, but where is the problem?

  20. #20
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So then there must be a problem in the php script, since we get results from PhpMyAdmin can you show me the code for the whole page?
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  21. #21
    SitePoint Addict
    Join Date
    Feb 2001
    Location
    Shanghai, China
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is the PHP-Skript:

    <HTML>
    <HEAD>

    </HEAD>
    <BODY>


    <UL>
    <?php

    $dbcnx = @mysql_connect("localhost", "xxx", "xxx");
    mysql_select_db('xxx');

    //Array of searchable fields make sure they are the same for the form elements as the names for the fields in the db
    $searchable = array("vname", "nname", "ort");
    $sql .= "SELECT * FROM event WHERE (";
    foreach($searchable as $val) {
    $tmp = $val;
    $sql .= ($$tmp != "") ? "($val LIKE '%$$tmp%') AND " : "($val LIKE '%') AND ";
    }

    $sql = substr($sql, 0, -5).")";

    $result = mysql_query($sql) or DIE(mysql_error());
    while ($row = mysql_fetch_array($result)) {
    echo("<p>" . $row["vname"] . "</p>");
    }

    print $sql
    ?>
    </UL>

    </BODY>
    </HTML>

    And this is the form:

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

    </head>

    <body bgcolor="#FFFFFF">
    <FORM ACTION="search.php" METHOD=get>
    <p>&nbsp; </p>
    <p>Suche nach/p>
    <p>Vorname
    <input type="text" name="vname">
    </p>
    <p> Nachname
    <input type="text" name="nname">
    </p>
    <p>Ort
    <input type="text" name="ort">
    </p>
    <p>
    <input type=submit name="submit" value="Submit">
    </p>
    </form>

    </body>
    </html>


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •