Page 1 of 1

hair pulling time

Posted: Thu Jun 26, 2003 4:27 am
by Deddog
below PHP select segment returns two results what ever the date! At best it should only return one.

SELECT cc.Office FROM contacts.contactdetails cc
LEFT JOIN contacts.absent ca ON ( cc.Contact_Id = ca.User_id)
AND ( ca.Start_Date >= '2003-06-26'
AND ca.End_Date <= '2003-06-26'
AND ca.AM = '0' AND ca.PM = '0' ) AND ca.User_id IS NOT NULL
WHERE cc.Office IS NOT NULL;

any ideas?

Posted: Thu Jun 26, 2003 4:32 am
by []InTeR[]
Can you parse the result and the date/layout in the database?
I think it's about the join.

Posted: Thu Jun 26, 2003 4:48 am
by Heavy
Try inserting the keyword DISTINCT.
Also read the following from the mysql manual:
mysql manual wrote:You should generally not have any conditions in the ON part that are used to restrict which rows you have in the result set (there are exceptions to this rule). If you want to restrict which rows should be in the result, you have to do this in the WHERE clause.
I found that at this address: http://www.mysql.com/doc/en/JOIN.html

So my suggestion is:

Code: Select all

SELECT DISTINCT cc.Office FROM contacts.contactdetails cc
LEFT JOIN contacts.absent ca ON ( cc.Contact_Id = ca.User_id)
WHERE 
ca.Start_Date >= '2003-06-26' 
AND ca.End_Date <= '2003-06-26' 
AND ca.AM = '0' 
AND ca.PM = '0' 
AND ca.User_id IS NOT NULL
AND cc.Office IS NOT NULL;
Of course, I could not test it though. :wink:

Posted: Thu Jun 26, 2003 4:52 am
by Deddog
Hello again InTer,
bit thick,don't understand your recommendation - sorry.

below is an earlier version of the whole select beast. Becuase of the "OR" a result is now returned. BUT there should be two results returned this time.

$Break = '-';
$TheDate = 26/06/2003;
$DateArray = array_reverse(explode('/',$TheDate));
$TempDate = implode($Break, $DateArray);

SELECT cc.Office FROM contacts.contactdetails cc
LEFT JOIN contacts.absent ca ON cc.Contact_Id = ca.User_id
and ((ca.Start_Date >='$TempDate'
and ca.End_Date <='$TempDate'
and ca.AM ='0' AND ca.PM='0')
||
(ca.Start_Date ='$TempDate'
and ca.End_Date ='$TempDate'
and ca.PM ='1'))
WHERE ca.User_id IS NOT NULL AND cc.Office IS NOT NULL

Posted: Thu Jun 26, 2003 5:06 am
by Deddog
Thanks Heavy.
the below code still only returns one result but i am happeir with its layout. Although i did come accross a statement which said that any conditions relating to the right hand side of the join should go in the ON section and not the WHERE section.

SELECT DISTINCT cc.Office FROM contacts.contactdetails cc
LEFT JOIN contacts.absent ca ON cc.Contact_Id = ca.User_id
WHERE ((ca.Start_Date >='2003-06-26'
and ca.End_Date <='2003-06-26'
and ca.AM ='0' AND ca.PM='0')
||
(ca.Start_Date ='2003-06-26'
and ca.End_Date ='2003-06-26'
and ca.PM ='1'))
AND ca.User_id IS NOT NULL AND cc.Office IS NOT NULL;

Posted: Thu Jun 26, 2003 5:51 am
by Heavy
Ok. I didn't know that and the manual quote above says that there are "exceptions to this rule". Maybe youv'e pointed it out.

What do you mean "one result". Do you mean you only get One row from the select statement?
What exactly is the question? My english isn't very good and I could use some more information on how you'd like it to work and what the problem is.

Posted: Thu Jun 26, 2003 6:04 am
by Deddog
Sorry.

Ok here’s the scenario. Want to offer offices available for general use if the owner is absent.

I offer the offices as available in the AM and the PM

There are two office owners off.

Owner 1 is off on the 26th in the afternoon (PM) only

Owner 2 is off from the 20th till 30th.

I’m expecting the first part of the select to locate Owner2 and the “OR” part to locate Owner 1.

At the moment it would seem that Owner 1 is being correctly identified.

Cheers,

Deddog

Posted: Thu Jun 26, 2003 10:50 am
by Heavy
You haven't told us if you are using mysql. But if you are, you could try this tool and check the results of a SELECT there:

http://www.mysql.com/downloads/mysqlcc.html

Since you don't provide any PHP code I still wonder if it is the query that is not doing what you want or if it is the php code.

for example:
Do you try to loop through the result rows until there are no more rows left?

Code: Select all

<?php
$result = mysql_query('Select username from Users order by username');
if(mysql_num_rows($result)){
	while ($row = mysql_fetch_assoc($result)){
		echo 'Username: ' . $row['username'] . '<br>';
	}
}
?>
Do you loop the until rows are out like above?

Posted: Fri Jun 27, 2003 2:18 am
by Deddog
Doh!

Yes i'm using mysql and php. I listed the sql because its actually part of beast. I'm certain the rest of the beast works, its just the sql i've stated that is giving me grief.

I tested it to boot and i can't for the life of me see the problem. Think its has something to do with start_date and end_date being compared to tempdate. Below is the whole beast and yes i do loop through the results.

Thanks for sticking with me :D

$connection = mysql_connect("1**.29.***.1*4", "i*****", "*****");
//
$sqlAMOff = "SELECT rooms.RoomsId, rooms.RoomName FROM MeetingRoom.rooms
LEFT JOIN MeetingRoom.booked ON rooms.RoomsId=booked.RoomsId and booked.BookedDate ='$TempDate' and booked.BookedTime='AM'
WHERE booked.RoomsId IS NULL and rooms.Available = 'Yes' and rooms.RoomsId IN
(SELECT DISTINCT cc.Office FROM contacts.contactdetails cc
LEFT JOIN contacts.absent ca ON cc.Contact_Id = ca.User_id WHERE
((ca.Start_Date >='$TempDate' and ca.End_Date <='$TempDate' and ca.AM ='0' and ca.PM ='0') ||
(ca.Start_Date ='$TempDate' and ca.End_Date ='$TempDate' and ca.AM ='1'))
AND ca.User_id IS NOT NULL AND cc.Office IS NOT NULL AND ca.AM IS NOT NULL AND ca.PM IS NOT NULL )";
$resultAMOff = mysql_query($sqlAMOff,$connection) or die(mysql_error().'<p>'.$sqlAMOff.'</p>');
//