OK, now you have to back off even further and make sure that you have complete connectivity to the tables. Change the SQL to be a simple query and see if you get the right result. First use: Select * from WMS_Allocations; and make sure you get all the rows from that table. Then make the SQL: Select * from WMS_Bookings and make sure you get all the rows there as well. Then add in the Where clause to this SQL to see if it returns fewer rows. Then add in the User_Id='1' to see if you then just get 3 rows. If you are still getting the correct rows returned, then fold in the WMS_Allocations table again, add the correct joins in the Where clause and see what happens. When you have a problem like this, start from the ground and work up to see where the problem creeps in. You might be surprised where the error is... -------Original Message------- From: shaun Date: Tuesday, April 22, 2003 11:18:35 AM To: php-db@lists.php.net Subject: Re: Bizzare Error ok i have tidied up the SQL a little: SELECT A.*, B.* FROM WMS_Allocations A, WMS_Bookings B WHERE A.User_ID = B.User_ID AND A.User_ID = '1' AND B.Booking_Start_Date >= '2003-04-28' AND B.Booking_End_Date <= '2003-04-29' but i still get zero rows from my php script, but when i enter the sql directly to Mysql i get 3 rows? "Jim Hunter" <jim@epiuniverse.com> wrote in message 3EA5814A.000010.01228@JHUNTER.ibaset.com">news:3EA5814A.000010.01228@JHUNTER.ibaset.com... To start with, the result set you get in MySQL is probably not the result set you are looking for because the SQL you have here is not logical. You are asking for U.User_ID to be returned from WMS_User but you are not including that table in the where clause so you are going to get every row in that table returned. You are also including a table WMS_Projects that is not anywhere in the Select or Where clause. I would suggest to trim down your SQL to only contain tables A and B then add back in the other tables once you add the proper code in the where clause to link them into the first query. Execute this SQL on your database and see if you get fewer rows returned: SELECT A.*, B.* FROM WMS_Allocations A, WMS_Bookings B WHERE A.User_ID = B User_ID AND A.User_ID = '1' AND B.Booking_Start_Date >= '2003-04-28' AND B Booking_End_Date <= '2003-04-29'; Jim -------Original Message------- From: shaun Date: Tuesday, April 22, 2003 10:35:36 AM To: php-db@lists.php.net Subject: Re: Bizzare Error sorry, here it is: num = 0 query = SELECT A.*, B.*, U.User_ID FROM WMS_Allocations A, WMS_Bookings B, WMS_User U, WMS_Projects P WHERE A.User_ID = B.User_ID AND A.User_ID = '1' AND B.Booking_Start_Date >= '2003-04-28' AND B.Booking_End_Date <= '2003-04-29' when this query is entered directly to mysql there are lots of rows, i dont understand this at all! "Jim Hunter" <jim@epiuniverse.com> wrote in message 3EA57293.000001.01228@JHUNTER.ibaset.com">news:3EA57293.000001.01228@JHUNTER.ibaset.com... What Doug was trying to say was, "without seeing the sample output, how can we help you determin the problem with your SQL. Will you please post the sample 'query=' output so we can better assist you". -------Original Message------- From: shaun Date: Tuesday, April 22, 2003 09:50:46 AM To: php-db@lists.php.net Subject: Re: Bizzare Error $booking_end_date is correct, when i say i cut and paste the query i maen i cut and paste the output of $query so the PHP variables have been parsed... "Doug Thompson" <dthompson@brickbarn.com> wrote in message 200304221638.h3MGc7G9026669@brickbarn.com">news:200304221638.h3MGc7G9026669@brickbarn.com... > Is > AND B.Booking_End_Date <= '".$booking_end_date."'"; > > maybe supposed to be > AND B.Booking_End_Date <= '".$_GET[booking_end_date]."'"; > > Also, because mysql cannot convert the PHP variables, you have to be doing more than cutting and pasting the query into the CLI. My crystal ball just went cloudy. > > Doug > > On Tue, 22 Apr 2003 17:18:45 +0100, shaun wrote: > > >Hi, > > > >I am completely foxed by this: > > > > $query = "SELECT A.*, B.*, U.User_ID > > FROM WMS_Allocations A, WMS_Bookings B, WMS_User U, > >WMS_Projects P > > WHERE A.User_ID = B.User_ID > > AND A.User_ID = '".$ses_user_id."' > > AND B.Booking_Start_Date >= '".$_GET[booking_date]."' > > AND B.Booking_End_Date <= '".$booking_end_date."'"; > > echo "query = $query<br>"; > > $result = mysql_query($query) or die ('Query failed, MySQL says: > > '.mysql_error().' in '.__FILE__.' line '.__LINE__) > > $num = mysql_num_rows($result); > > echo "num = $num<br>"; > > if($num != 0){ > > //..... > > > >$num is zero, but it shouldn't be and when i cut and paste the query > >directly into mysql i get loads of rows. I also get no error messages. > >I would be very grateful if someone could point out what i have > >done wrong here. > > > >P.S. This message has been posted in PHP general but i was asked > >to post it here so please don't attack me for cross posting! > > > > > > > >-- > >PHP Database Mailing List (http://www.php.net/) > >To unsubscribe, visit: http://www.php.net/unsub.php > > > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php ... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php .. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php .
