SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 48 of 48
  1. #26
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am not sure if i understand you correctly. do you mean something like this?

    if($giveortake=='3') { $sql .=' AND departureDate between "'$depx'" and "'$depx'" .' + INTERVAL + 3 = 3;}

  2. #27
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,077
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    No, I don't.
    Please read my previous post again.
    Also please read post #23 again, since I've got this feeling you still don't know what I said there.

    I think you really should get a decent book on how to write PHP and MySQL, because it looks like your just hacking random characters together and hope it works. Programming doesn't work that way. No offence.

  3. #28
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have a PHP book and I looked at onlines tutorials, but cant find anything that explains like this scenario. We managed to sort out the duration bit which I understand, but the bit with INTERVAL is not straightforward.

    You mention to use the Between bit but the examples I found in the book are simple and just compare between two dates... In my case, I have to use the Interval as well so Perhaps some clarification with details on your previous comments would help!

  4. #29
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,077
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Okay, let me try to break this down for you

    PHP Code:
    if($giveortake=='3') { $sql .=' AND departureDate between "'$depx'" and "'$depx'" + 3;} 
    The problem here is that ' AND departureDate between "'$depx'" and "'$depx'" is a string, and 3 is an integer and you're using + to try to concatenate them. However, + is not the operator to concatenate string, but . (dot) is.
    Effectively what you're doing here is add ' AND departureDate between "'$depx'" and "'$depx'" and 3 together, and since ' AND departureDate between "'$depx'" and "'$depx'" is not a number it will evaluate to 0, thus you have 0 + 3 = 3.

    Also, you're ending the string and opening it again wrongly.
    Key to understand here is that there are strings with single quotes and string with double quotes. These have different meaning. Strings in double quotes are parsed by PHP, while string in single quotes are not.

    To give you an example
    PHP Code:
    $name 'ScallioXTX';
    echo 
    'Hi, my name is $name'// Hi, my name is $name
    echo "Hi, my name is $name"// Hi, my name is ScallioXTX 
    Note the difference between the single quotes and the double quotes.

    So to combine this, you should have done this:
    PHP Code:
    if($giveortake=='3') {
      
    $sql .= " AND departureDate BETWEEN '$depx' AND '$depx' + INTERVAL 3 DAY";

    That is, the value of the column departureDate should lie between $depx and $depx + 3 days

    Now, for the if-then-else part (as per post #23).

    If you select all rows that have a specific departureDate and also have a departureDate between that same date and another date, you end up with all rows that have the specific departureDate, and the interval is ignored.
    This is easier to understand if we swap the different clauses in the WHERE query. Suppose we have this query

    [SQL]
    SELECT
    field1,field2,field3
    FROM
    some_table
    WHERE
    departureDate BETWEEN '2010-05-10' AND '2010-05-13'
    AND
    departureDate = '2010-05-10'
    [/SQL]

    Now, MySQL evaluates this query from left to right and will first find all rows that are between '2010-05-10' and '2010-05-13', so you might have an intermidiate result that contains the dates '2010-05-10', '2010-05-11' and '2010-05-13'. Now, we go over to the next part, WHERE deparureDate = '2010-05-10'. Clearly, '2010-05-11' and '2010-05-13' are not equal to '2010-05-10' so they get dropped from the result, leaving you with only '2010-05-11'.

    So, what you need to do is change the check for the departureDate in the WHERE clause.

    So, your complete code thusfar would be

    PHP Code:
    $dep mysql_real_escape_string($dep);
    $dest mysql_real_escape_string($dest);

    // rewrite $dep to YYYY-mm-dd format, and call that $depx
    list($day$month$year) = split'[/]'$dep);
    $depx "$year-$month-$day";

    // Change MySQL keywords to uppercase, so you can discern them better from the rest of the code

    $sql 'SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, DATE_FORMAT(departureDate, "%d/%m/%Y") AS formattedDate FROM holidays WHERE ';

    if (
    $giveortake == '3' || $giveortake == '5') {
      
    // We now need to search in a range of dates, using BETWEEN and INTERVAL
      
    $sql .= 'departureDate BETWEEN "'.$depx.'" AND "'.$depx.'" + INTERVAL '.$giveortake.' DAY';
    } else {
      
    // We need to check for exactly one date
      
    $sql .= 'departureDate = "'.$depx.'"';
    }

    if(
    $duration=='7' || $duration=='3') {
      
    $sql .= ' AND duration='.$duration;
    }

    $result mysql_query($sql);
    while (
    $row mysql_fetch_array($result))
    {
      
    outputProduct($row);


  5. #30
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,077
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Hm, thats not entirely correct I see.
    It should be:

    PHP Code:
    $dep mysql_real_escape_string($dep);
    $dest mysql_real_escape_string($dest);

    // rewrite $dep to YYYY-mm-dd format, and call that $depx
    list($day$month$year) = split'[/]'$dep);
    $depx "$year-$month-$day";

    // Change MySQL keywords to uppercase, so you can discern them better from the rest of the code
    $sql 'SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, DATE_FORMAT(departureDate, "%d/%m/%Y") AS formattedDate FROM holidays WHERE ';

    if (
    $giveortake == '3' || $giveortake == '5') {
      
    $useGiveortake true;
      
    // We now need to search in a range of dates, using BETWEEN and INTERVAL
      
    $sql .= 'departureDate BETWEEN "'.$depx.'" AND "'.$depx.'" + INTERVAL '.$giveortake.' DAY';
    } else {
      
    // We need to check for exactly one date
      
    $sql .= 'departureDate = "'.$depx.'"';
    }

    if(
    $duration=='7' || $duration=='3') {
      
    $sql .= ($useGiveortake 'AND ' '') .'duration='.$duration;
    }

    $result mysql_query($sql);
    while (
    $row mysql_fetch_array($result))
    {
      
    outputProduct($row);

    Otherwise it won't work if you supply a $duration but no $giveortake.
    the ? in $useGiveortake ? 'AND ' : '' is known as the ternary operator, read about it here.

  6. #31
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks ScallioXTX for the clarification and explanation on this. I havent been on my pc yet. i will go through it again and pass you any feedback


    Thanks again

  7. #32
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thankx Scallio XTX. It works now. but the destination part (
    destination like '%$dest%') is missing from the main query . The destination is not a condition but should be excecuted as part of the main query.


    I tried adding it to the Where part in the main query and then added AND to each condition but it generated this error like (Cnnot divide by Zero)

    any ideas?
    thanks

  8. #33
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,077
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Sounds like you messed up the string concatenation (again).
    Can you post the complete code?

  9. #34
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The only 2 things I added were:

    1) destination like '%$dest%' at the end of the main query
    2) The AND part in the condition bit


    $sql="SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, price,buyurl, departureDate from holidays WHERE departureDate = '$depx' and destination like '%$dest%' ";


    $sql .= ' AND departureDate BETWEEN "'.$depx.'" AND "'.$depx.'" + INTERVAL '.$giveortake.' DAY';} else { // We need to check for exactly one date $sql .= ' AND departureDate = "'.$depx.'"';}

  10. #35
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,077
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    1)
    Why is "departureDate = '$depx'" back in the main query!? The whole point of my last 10 posts was to get it out of there!
    It seems like you're copy/pasting random snippets from my posts into your own script. Please use my complete script to make sure you incorporate all changes and don't miss any.

    2)
    Did you also change
    PHP Code:
    if($duration=='7' || $duration=='3') {
      
    $sql .= ($useGiveortake 'AND ' '') .'duration='.$duration;

    to

    PHP Code:
    if($duration=='7' || $duration=='3') {
      
    $sql .= ' AND duration='.$duration;

    (i.e., remove the ($useGiveortake ? 'AND ' : '') part)

    and have you also removed the line:
    PHP Code:
    $useGiveortake true
    since you don't need it anymore?

  11. #36
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, I already made thoses changes. i forgot and posted the wrong one on here. I didnt include the departureDate in the query and I did remove the $useGiveortake ? 'AND ' : '') part as well

  12. #37
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok this is the code now:

    $sql = 'SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, DATE_FORMAT(departureDate, "%d/%m/%Y") AS formattedDate FROM holidays WHERE destination like '%$dest%' ';


    if ($giveortake == '3' || $giveortake == '5') {

    $sql .= ' AND departureDate BETWEEN "'.$depx.'" AND "'.$depx.'" + INTERVAL '.$giveortake.' DAY'; }
    else {

    $sql .= 'departureDate = "'.$depx.'"'; }


    if($duration=='7' || $duration=='3') { $sql .= ' AND duration='.$duration;}

    $result = mysql_query($sql) or die("Query error: ".mysql_errno().": ".mysql_error());


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

    outputProduct($row)
    ;
    }


    -----------------------------end-----------------------------------------

    and this is the error message:



    Warning: Division by zero in D:\inetpub\vhosts\holidays.php on line 71
    Query error: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND departureDate BETWEEN "2010-05-28" AND "2010-05-28" + INTERVAL 3 DAY' at line 1

    Note: I dont get this error message when I remove the destination part in the main query

    Thanks

  13. #38
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,077
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    I will show you the first line of your script in color hightlighting, you should be able to figure out from that what's wrong:

    PHP Code:
    $sql 'SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, DATE_FORMAT(departureDate, "%d/%m/%Y") AS formattedDate FROM holidays WHERE destination like '%$dest%' '
    See how the % characters turn green and $dest turns blue? They're not supposed to do that, they're supposed to be red, as they need to be a part of the string.

    It has to with double quoted strings, single quoted strings, and string concatenation, which I explained to you in post #29

  14. #39
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I know your point: I did the double quotes but still getting the same error message:

    $sql = 'SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, DATE_FORMAT(departureDate, "%d/%m/%Y") AS formattedDate FROM holidays WHERE destination like "'%$dest%'" ';

  15. #40
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Angry

    I can see anything wrong with the code now. I did exactly as you mentioned. I am still getting that error message. Any ideas?

    ---------------code---------------------------------------------------
    $sql = 'SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, DATE_FORMAT(departureDate, "%d/%m/%Y") AS formattedDate FROM holidays WHERE destination like "'%$dest%'" ';


    if ($giveortake == '3' || $giveortake == '5' || $giveortake == '7') {

    $sql .= 'departureDate BETWEEN "'.$depx.'" AND "'.$depx.'" + INTERVAL '.$giveortake.' DAY'; }
    else {

    $sql .= 'departureDate = "'.$depx.'"'; }




    if($duration=='7' || $duration=='3') { $sql .= ' AND duration='.$duration;}

    $result = mysql_query($sql) or die("Query error: ".mysql_errno().": ".mysql_error());

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

    outputProduct($row)
    ;
    }

  16. #41
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,077
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by hm9 View Post
    I did exactly as you mentioned.
    No, you did not. If you did, it would work

    The problem is in the first line. Let's break it down:

    $sql =

    Ok, that's a variable assinment, nothing wrong there. Moving along

    'SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, DATE_FORMAT(departureDate, "%d/%m/%Y") AS formattedDate FROM holidays WHERE destination like "'

    Thats a string, it is delimited by the single quotes I colored red. Nothing wroing with that string, let's move on

    %$dest%

    Ehm, okay, what? Since this is not part of the string PHP will interpret the % sign as the modulo operator. That can't be right, can it? That's not what you meant! You meant to put it in the string, so why didn't you do so?

    So, okay, we will move the % in the string, and we get

    'SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, DATE_FORMAT(departureDate, "%d/%m/%Y") AS formattedDate FROM holidays WHERE destination like "%'

    See, now the % is in the string, all pretty in blue.

    So, we still have

    $dest%'" ';

    Okay, that's wrong again. You cannot follow a string directly by a variable, since that's an allowed construct in PHP. What you need to do is concatenate it, like so:

    'SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, DATE_FORMAT(departureDate, "%d/%m/%Y") AS formattedDate FROM holidays WHERE destination like "%' . $dest

    See the green part at the end? We concatenated the variable to the string.
    Now, to move on

    %'" ';

    Well, what do you know, there is the modulo operator again! This one also has to go in the string, and then you need to concatenate the last string with the variable proceeding it.

    $dest . '%" ';

    So, all together now:

    'SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, DATE_FORMAT(departureDate, "%d/%m/%Y") AS formattedDate FROM holidays WHERE destination like "%' . $dest . '%" ';

    Now, make sure you read this post thorougly, and once you did, read it again! And then get a PHP editor with syntax highlighting like NetBeans, Eclipse, Aptana, Coda, Notepad++, or any other of the free editors out there that make it easy to spot these kind of errors as you type.

  17. #42
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok. I see your point now. The divide by zero problem is now solved and I dont get that error message anymore, but I still get the the following syntax error in the INTERVAL part:

    1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND departureDate BETWEEN "2010-05-27" AND "2010-05-27" + INTERVAL 3 DAY' at line 1.


    $sql .= 'AND departureDate BETWEEN "'.$depx.'" AND "'.$depx.'" + INTERVAL '.$giveortake.' DAY'; }
    else {

    $sql .= 'departureDate = "'.$depx.'"'; }

    -----------------------------------------------------

  18. #43
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,077
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Can you post the complete code in [php], [/php] tags please?

  19. #44
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post

    Ok here is the code:

    <php>

    $dep = mysql_real_escape_string($dep);
    $dest = mysql_real_escape_string($dest);

    list($day, $month, $year) = split( '[/]', $dep);
    $depx = "$year-$month-$day";
    echo $depx ;// to test it works


    'SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, DATE_FORMAT(departureDate, "%d/%m/%Y") AS formattedDate FROM holidays WHERE destination like "%' . $dest . '%" ';

    if ($giveortake == '3' || $giveortake == '5' || $giveortake == '7') {

    $sql .= ' departureDate BETWEEN "'.$depx.'" AND "'.$depx.'" + INTERVAL '.$giveortake.' DAY'; }
    else {

    $sql .= ' departureDate = "'.$depx.'"'; }



    if($duration=='7' || $duration=='3') { $sql .= ' AND duration='.$duration;}

    if($type=='All inclusive' || $type=='Bed and Breakfast') { $sql .= ' AND type='.$type; }

    $result = mysql_query($sql) or die("Query error: ".mysql_errno().": ".mysql_error());


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

    outputProduct($row)
    ;
    }

    </php>

    Thanks

  20. #45
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,077
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Code:
    $dep = mysql_real_escape_string($dep);
    $dest = mysql_real_escape_string($dest);
    
    list($day, $month, $year) = split( '[/]', $dep);
    $depx = "$year-$month-$day";
    echo $depx ;// to test it works
    
    
    'SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, DATE_FORMAT(departureDate, "%d/%m/%Y") AS formattedDate FROM holidays WHERE destination like "%' . $dest . '%" ';
    
    if ($giveortake == '3' || $giveortake == '5' || $giveortake == '7') {
    
    $sql .= ' AND departureDate BETWEEN "'.$depx.'" AND "'.$depx.'" + INTERVAL '.$giveortake.' DAY'; }
    else {
    
    $sql .= ' AND departureDate = "'.$depx.'"'; }
    
    
    
    if($duration=='7' || $duration=='3') { $sql .= ' AND duration='.$duration;}
    
    if($type=='All inclusive' || $type=='Bed and Breakfast') {
      $sql .= ' AND type="'.$type.'"';
    }
    
    $result = mysql_query($sql) or die("Query error: ".mysql_errno().": ".mysql_error());
    
    
    while ($row = mysql_fetch_array($result))
    {
    
    outputProduct($row)
    ;
    }
    Changes/additions in red.
    And you need to use square brackets when posting code:


    [php]
    $somevar = 'Some string';
    [/php]

  21. #46
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks but sorry I am still getting the syntax error message. Its complaining about the Line with the INTERVAL part

    PHP Code:

    if ($giveortake == '3' || $giveortake == '5' || $giveortake == '7') {

    $sql .= ' AND departureDate BETWEEN "'.$depx.'" AND "'.$depx.'" + INTERVAL '.$giveortake.' DAY'; }
    else {

    $sql .= ' AND departureDate = "'.$depx.'"'; } 
    Error message:
    Query error: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND departureDate = "2010-05-28"' at line 1



    I am not sure why as when I removed the dest from the main query, there was no syntax error

    Any ideas?
    Any idea?

  22. #47
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,027
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Just before the query is run, add this line:

    PHP Code:
    echo $sql
    Paste the query here
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  23. #48
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Problem solved. The $sql .= bit was missing from the main query:

    So it should be:

    PHP Code:

    $sql 
    .= 'SELECT distinct destination, accomodation, resort, departurepoint, returnDate, duration, type, price, buyurl,  DATE_FORMAT(departureDate, "%d/%m/%Y") AS formattedDate FROM holidays WHERE destination like "%' $dest '%" '
    Thanks again for your help and patience


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
  •