Professional Web Applications Themes

MySQL quey working on SQL5 but noMySQL4 - MySQL

I'm a newbie to MySql/PHP so please be gentle!! I'm writing a web reservation system for a small hotel and the following sql query works perfectly locally on my machine using MySql5 & PHP5, but not on messrs Fasthosts server with PHP4.xx and MySql4.xx (The connection to the DB seems to be working correctly). On the server it throws up an error saying "Unknown column 'Av.AvailDate' in 'where clause'" $sql = "SELECT Distinct RoomTypeID, RoomName,RoomDesc,RoomTerms,RoomConfig,RoomPhoto FROM roomtypes RT Inner Join availability AV ON RT.RoomTypeID = Av.AvailRoomID Where (Av.AvailDate between'".$ArrDate."' and '" .$DelOcc."') AND NOT EXISTS (Select Distinct NoAv.AvailRoomID FROM availability ...

  1. #1

    Default MySQL quey working on SQL5 but noMySQL4

    I'm a newbie to MySql/PHP so please be gentle!!

    I'm writing a web reservation system for a small hotel and the
    following sql query works perfectly locally on my machine using MySql5
    & PHP5, but not on messrs Fasthosts server with PHP4.xx and MySql4.xx
    (The connection to the DB seems to be working correctly).

    On the server it throws up an error saying "Unknown column
    'Av.AvailDate' in 'where clause'"

    $sql = "SELECT Distinct RoomTypeID,
    RoomName,RoomDesc,RoomTerms,RoomConfig,RoomPhoto FROM roomtypes RT
    Inner Join availability AV ON RT.RoomTypeID = Av.AvailRoomID Where
    (Av.AvailDate between'".$ArrDate."' and '" .$DelOcc."') AND NOT EXISTS
    (Select Distinct NoAv.AvailRoomID FROM availability NoAv WHERE
    NoAv.AvailDate Between '" .$ArrDate."' AND '" .$DelOcc. "' AND
    NoAv.AvailRooms < 1 AND NoAv.AvailRoomID=Av.AvailRoomID)";

    I'd be very grateful for any pointers as I'm completely stuck on this
    one!



    --
    Buzby
    There's nothing more dangerous than a resourceful idiot
    Buzby Guest

  2. #2

    Default Re: MySQL quey working on SQL5 but noMySQL4

    On 11 May, 14:52, "Buzby" <net> wrote: 

    Well it's really difficult to read a query written as a block like
    that, but as far as I can see you don't have any table named (or
    aliased) "Av".
    The only tables/aliases I can see in the query are "RT", "AV" &
    "NoAv".

    Incidentally I would advise you to change the correalated subselect to
    a LEFT JOIN with a NULL test. Tends to be much more efficient.

    Captain Guest

  3. #3

    Default Re: MySQL quey working on SQL5 but noMySQL4

    Captain Paralytic wibbled:

     
    >
    > Well it's really difficult to read a query written as a block like
    > that, but as far as I can see you don't have any table named (or
    > aliased) "Av".
    > The only tables/aliases I can see in the query are "RT", "AV" &
    > "NoAv".
    >
    > Incidentally I would advise you to change the correalated subselect to
    > a LEFT JOIN with a NULL test. Tends to be much more efficient.[/ref]

    You star! I missed that completely - thanks very much!

    Last point taken on board - I'll have a look at that.

    --
    Buzby
    There's nothing more dangerous than a resourceful idiot
    Buzby Guest

Similar Threads

  1. MySQL Not Working WIth ColdFusion
    By Cyber_will in forum Coldfusion Database Access
    Replies: 10
    Last Post: April 6th, 08:42 AM
  2. Replies: 0
    Last Post: November 4th, 05:38 PM
  3. Query with a quey variable
    By ShapeShift in forum Coldfusion - Advanced Techniques
    Replies: 4
    Last Post: September 26th, 01:19 AM
  4. Multiple results in a quey
    By Edgard Berendsen in forum PHP Development
    Replies: 0
    Last Post: September 15th, 04:56 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139