ok i have tried rebuilding the query every way i can, i have even created a
new script with the output query of the original:
<?php
include ('dbconnect.php');
$query = "SELECT A.*, B.*
FROM WMS_Allocations A, WMS_Bookings B
WHERE A.User_ID = '1'
AND A.User_ID = B.User_ID
AND B.Booking_Start_Date >= '2003-04-28'
AND B.Booking_End_Date <= '2003-04-29' ";
$result = mysql_query($query) or die ('Query failed, MySQL says:
'.mysql_error().' in '.__FILE__.' line '.__LINE__);
$num = mysql_num_rows($result);
echo "query = $query";
echo "num = $num";
?>
and this gives the expected result, but the original script keeps telling me
there are no rows in the result. i am absolutely baffled here...
"Jim Hunter" <jim@epiuniverse.com> wrote in message
3EA5893F.000015.01228@JHUNTER.ibaset.com">news:3EA5893F.000015.01228@JHUNTER.ibaset.com...
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
.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
