#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    2
    Rep Power
    0
    Can anyone help? I like to approach things simply at first but I'm a bit stumped here. I have a questionaire type form which is executing a query to a Mysql database table which contains information about places to visit:

    eg

    CREATE TABLE decision (
    id int(11) DEFAULT '0' NOT NULL auto_increment,
    title varchar(50),
    Budget int(50),
    Accommodation varchar(50),
    Type varchar(50),
    Location varchar(50),
    Services varchar(50),
    Season varchar(50),
    Health varchar(50),
    Distance int(50),
    Childcare varchar(50),
    PRIMARY KEY (id)
    );

    The questionaire works OK when executed

    $searchStmt = "SELECT * from decision where " ;


    if ($Budget)
    $searchStmt .= "Budget <= '$Budget' and " ;

    if ($Accommodation)
    $searchStmt .= "Accommodation like '$Accommodation' and " ;

    if ($Type)
    $searchStmt .= "Type like '%$Type%' and " ;

    if ($Location)
    $searchStmt .= "Location like '%$Location%' and " ;

    if ($Services1)
    $searchStmt .= "Services like '%$Services1%' and " ;

    if ($Services2)
    $searchStmt .= "Services like '%$Services2%' and " ;

    if ($Services3)
    $searchStmt .= "Services like '%$Services3%' and " ;

    if ($Childcare1)
    $searchStmt .= "Childcare like '%$Childcare1%' and " ;

    if ($Childcare2)
    $searchStmt .= "Childcare like '%$Childcare2%' and " ;

    if ($Childcare3)
    $searchStmt .= "Childcare like '%$Childcare3%' and " ;

    if ($Childcare4)
    $searchStmt .= "Childcare like '%$Childcare4%' and " ;

    if ($Childcare5)
    $searchStmt .= "Childcare like '%$Childcare5%' and " ;

    if ($Season)
    $searchStmt .= "Season like '%$Season%' and " ;

    if ($Health)
    $searchStmt .= "Health like '%$Health%' and " ;

    if ($Distance)
    $searchStmt .= "Distance like '$Distance' and " ;




    $stmt= substr($searchStmt, 0, strlen($searchStmt)-4) ;

    // Connect to the Database
    if (!($link=mysql_pconnect($hostName, $userName, $password))) {

    exit() ;
    }

    The query is derived from a form which contains tick boxes. The Childcare column for instance takes the tick from the relevant box which has a value which corresponds to that contained in the database eg childcare1 Value=Babysitting. So if this box is ticked the result is true and you get a result.

    My problem is which the accomodation section. I have 4 different criteria:

    Hotel, Self_catering, Camping and resort.

    The query works but it's not satisfactory as one might want to either stay in a hotel or Self Catering but not a resort or Camping. I simply don't know how to construct this sort of query. I thought about making seperate columns in the table for each type of Accommodation and then saying if Hotel is True and Self Catering is True then result so something like:

    if ($Hotel)
    $searchStmt .= "Hotel like '$Hotel' and " ;

    if ($Self_catering)
    $searchStmt .= "Self_catering like '$Self_catering%' and " ;

    if ($Camping)
    $searchStmt .= "Camping like '$Camping' and " ;

    if ($Resort)
    $searchStmt .= "Resort like '$Resort' and " ;

    etc etc. I've tried using OR and various others eg &#0124; &#0124; But I'm not getting anywhere.

    I hope I've made myself clear and that someone can at least give me a pointer as to how this type of query should be constructed.

    Thanks in advance

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

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,648
    Rep Power
    4493
    Maybe you need to program some () in around the accomodations area?

    Assuming at least one of the accomodations is going to be chosen:

    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    $searchStmt .= " ( ";
    if (isset($Hotel))
    {
    $searchStmt .= "Hotel like '$Hotel' OR " ;
    }
    if (isset($Self_catering))
    {
    $searchStmt .= "Self_catering like '$Self_catering%' OR " ;
    }
    if (isset($Camping))
    {
    $searchStmt .= "Camping like '$Camping' OR " ;
    }
    if (isset($Resort))
    {
    $searchStmt .= "Resort like '$Resort' OR " ;
    }
    //strip off last OR
    $searchStmt = substr($searchStmt,0,-3)
    $searchStmt .= " ) ";
    [/code]
    Also, the code you gave was just an example right? The syntax wasn't correct for your if blocks and I didn't see where you executed the query.

    When designing queries like this, always print them out to the screen before you execute them, to see if there are any formatting errors.

    ---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
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    2
    Rep Power
    0
    Thanks for your help but I haven't made myself clear enough. Also I did only post the relevant part of the code but here's the whole query as it stands:

    <HTML>
    <HEAD>
    <TITLE>Family Travel</TITLE><LINK REL="stylesheet" HREF="stylesheet.css">
    </HEAD>
    <BODY>
    <P ALIGN="CENTER"><IMG SRC="images/logo3.gif" WIDTH="325" HEIGHT="104"
    BORDER="0"></P>
    <TABLE WIDTH="95%" BORDER="0" CELLPADDING="0" CELLSPACING="0"
    ALIGN="CENTER">
    <TR VALIGN="TOP">
    <TD WIDTH="100%" VALIGN="TOP" ALIGN="LEFT" COLSPAN="3">


    <?PHP

    // search.php

    require("globals.php3") ;


    // Generate the SQL command for doing a select from the Database
    $searchStmt = "SELECT * from katie where " ;


    if ($Budget)
    {$searchStmt .= ("Budget <= '$Budget' and ") ;}


    if ($Hotel)
    {$searchStmt .= ("Hotel like '$Hotel' or ") ;}

    if ($Self_catering)
    {$searchStmt .= ("Self_catering like '$Self_catering' or ") ;}

    if ($Camping)
    {$searchStmt .= ("Camping like '$Camping' or ") ;}

    if ($Family_resorts)
    {$searchStmt .= ("Family_resorts like '$Family_resorts' and ") ;}


    if ($City)
    {$searchStmt .= ("City like '$City' or ") ;}

    if ($Coastal)
    {$searchStmt .= ("Coastal like '$Coastal' or ") ;}

    if ($Activity)
    {$searchStmt .= ("Activity like '$Activity' or ") ;}

    if ($Cultural)
    {$searchStmt .= ("Cultural like '$Cultural' or ") ;}

    if ($Resort)
    {$searchStmt .= ("Resort like '$Resort' or ") ;}

    if ($Rural)
    {$searchStmt .= ("Rural like '$Rural' and ") ;}

    if ($Restaurant)
    {$searchStmt .= ("Restaurant like '$Restaurant' and ") ;}

    if ($Catering)
    {$searchStmt .= ("Catering like '$Catering' and ") ;}

    if ($Maid_service)
    {$searchStmt .= ("Maid_service like '$Maid_service' and ") ;}






    $stmt= substr($searchStmt, 0, strlen($searchStmt)-4) ;

    // Connect to the Database
    if (!($link=mysql_pconnect($hostName, $userName, $password))) {

    exit() ;
    }

    // Select the Database
    if (!mysql_select_db($databaseName, $link)) {

    exit() ;
    }

    // Execute the Statement
    if (!($result =mysql_query($stmt, $link))) {

    exit() ;
    }

    // Display the results of the search

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

    echo "The Decision Tree has found the following locations that fit your criteria:<center><br>
    <table border=0 width=100%>n"; do { echo "
    <tr>
    n"; printf("
    <td><A HREF='../reports/reports.php3?myrow=%s'>n", $myrow["title"]); echo "
    n";
    printf("

    %s</A></td>
    </tr>n", $myrow["title"]); } while ($myrow = mysql_fetch_array($result)); echo "
    </table></center>n"; } else { echo " <br><br>Sorry, no entries match your search"; } ?>
    <P><BR></P>


    <P><BR></P> </TD>
    </TR>
    </TABLE> </BODY>
    </HTML>

    It sort of works when I do a search with a query that contains multiple selections on a section ie Hotel is a match and so is self_catering the records returned include those which are matched. OK. But if I execute a query which contains the same query but also has City checked in the next section then records returned ignore this fact and still match records containing Hotel and Self_catering. What I need to construct is a query that says if Hotel and self_catering are true and also this is a city then bring back records which match. I've tried to sub divide the query but I can't get beyond a basic match.

    Bye the way why did you use isset. I couldn't see what this was doing?

    Thanks
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    2
    Rep Power
    0
    http://www.php.net/manual/function.isset.php

    It is a standard PHP function. It returns true if the variable exists.

    Note: it might exist and contain nothing. PHP is just checking if it exists.

Similar Threads

  1. MySQL Select query
    By MrTee1 in forum MySQL Help
    Replies: 0
    Last Post: January 30th, 2004, 12:16 PM
  2. MySQL query using DB driven drop down menu
    By msg270 in forum PHP Development
    Replies: 3
    Last Post: January 22nd, 2004, 05:41 PM
  3. Replies: 1
    Last Post: January 16th, 2004, 08:39 PM
  4. Overwriting mysql query with the query results
    By onerandomday in forum PHP Development
    Replies: 1
    Last Post: January 14th, 2004, 05:45 AM
  5. mysql query problem???
    By mijan10 in forum PHP Development
    Replies: 2
    Last Post: January 4th, 2004, 04:39 PM

IMN logo majestic logo threadwatch logo seochat tools logo