hair pulling time

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Deddog
Forum Commoner
Posts: 55
Joined: Thu Sep 26, 2002 6:05 am
Location: Brighton

hair pulling time

Post 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?
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

Post by []InTeR[] »

Can you parse the result and the date/layout in the database?
I think it's about the join.
User avatar
Heavy
Forum Contributor
Posts: 478
Joined: Sun Sep 22, 2002 7:36 am
Location: Viksjöfors, Hälsingland, Sweden
Contact:

Post 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:
Deddog
Forum Commoner
Posts: 55
Joined: Thu Sep 26, 2002 6:05 am
Location: Brighton

Post 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
Deddog
Forum Commoner
Posts: 55
Joined: Thu Sep 26, 2002 6:05 am
Location: Brighton

Post 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;
User avatar
Heavy
Forum Contributor
Posts: 478
Joined: Sun Sep 22, 2002 7:36 am
Location: Viksjöfors, Hälsingland, Sweden
Contact:

Post 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.
Deddog
Forum Commoner
Posts: 55
Joined: Thu Sep 26, 2002 6:05 am
Location: Brighton

Post 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
User avatar
Heavy
Forum Contributor
Posts: 478
Joined: Sun Sep 22, 2002 7:36 am
Location: Viksjöfors, Hälsingland, Sweden
Contact:

Post 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?
Deddog
Forum Commoner
Posts: 55
Joined: Thu Sep 26, 2002 6:05 am
Location: Brighton

Post 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>');
//
Post Reply