Multiple results

[lux]

Hello,

I have this query:

SELECT players.id AS id, players.name AS name, players.surname AS surname, players.email AS email, calendar.start AS beginning, calendar.end AS finnish, calendar.name AS title, calendar.event AS description, calendar.id AS eventid FROM `calendar` , `attendance` , `players` WHERE (attendance.present = "yes" AND calendar.start BETWEEN "1141060879" AND "1141665679" AND attendance.event = calendar.id AND attendance.player = players.id) OR (calendar.added BETWEEN "1140974479" AND "1141060879") ORDER BY players.id,calendar.start ASC LIMIT 0 , 30

The query returns the values I want, but many times the same rows (6 times each), does anyone know why and/or how to fix it?

[Troy]

It's called a "Cartesian product".
Mathematical explanation: http://en.wikipedia.org/wiki/Cartesian_product
SQL explanation: http://www.fluffycat.com/SQL/Cartesian-Joins/

Try this:
---------------------------------------------------
SELECT p.id
, p.name
, p.surname
, p.email
, c.start AS beginning
, c.end AS finnish
, c.name AS title
, c.event AS description
, c.id AS eventid
FROM calendar c
INNER JOIN attendance a ON a.event = c.id
INNER JOIN players p ON p.id = a.player
WHERE a.present = "yes"
AND ((c.start >= '1141060879'
AND c.start <= '1141665679')
OR (c.added >= '1140974479'
AND c.added <= '1141060879'))
ORDER BY p.id ASC
, c.start ASC
LIMIT 0, 30
---------------------------------------------------

Notice that I did not name all the colums using "AS". There is no need to specify a name for a column if the name is the same as the column name. Also notice I used table aliases to shorten the code. I used the JOIN methods instead of the WHERE clause join methods. It seems hard at first, but after you get used to it, you'll find the joins make a lot more sense to you and keeps the WHERE clauses straight forward. (My opinion of course.)

[lux]

SELECT p.id
  , p.name
  , p.surname
  , p.email
  , c.start AS beginning
  , c.end AS finnish
  , c.name AS title
  , c.event AS description
  , c.id AS eventid
FROM calendar c
INNER JOIN attendance a ON a.event = c.id
INNER JOIN players p ON p.id = a.player
WHERE  (
c.added >= '1141740333'
AND c.added <= '1141826733'
)
ORDER BY p.id ASC
  , c.start ASC
LIMIT 0, 30

Hey Troy,

First of all A BIG thanks for your comment. It really lightened up my mind a lot. Your one comment is better than the dozens of others I have recieved in other forums. But one thing hasn't really worked out, the query above won't results. I tried removing some parts of your query but this one in particular doesn't work. Below I included the database:

-- 
-- Table structure for table `attendance`
-- 

CREATE TABLE `attendance` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `event` int(10) unsigned NOT NULL default '0',
  `player` tinyint(3) unsigned NOT NULL default '0',
  `present` enum('yes','no') NOT NULL default 'no',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM PACK_KEYS=0 AUTO_INCREMENT=2 ;

-- 
-- Dumping data for table `attendance`
-- 

INSERT INTO `attendance` VALUES (1, 1, 2, 'yes');

-- --------------------------------------------------------

-- 
-- Table structure for table `calendar`
-- 

CREATE TABLE `calendar` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(128) NOT NULL default '',
  `author` tinyint(3) unsigned NOT NULL default '0',
  `event` text NOT NULL,
  `start` int(16) NOT NULL default '0',
  `end` int(16) NOT NULL default '0',
  `added` int(16) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM PACK_KEYS=1 AUTO_INCREMENT=2 ;

-- 
-- Dumping data for table `calendar`
-- 

INSERT INTO `calendar` VALUES (1, 'Birthday', 1, 'It''s my birthday!', 1141999200, 1142006400, 1141826592);

-- --------------------------------------------------------

-- 
-- Table structure for table `players`
-- 

CREATE TABLE `players` (
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  `name` varchar(128) NOT NULL default '',
  `surname` varchar(128) NOT NULL default '',
  `email` varchar(128) NOT NULL default '',
  `password` varchar(128) NOT NULL default '',
  `homephone` varchar(128) NOT NULL default '',
  `mobile` varchar(128) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM PACK_KEYS=1 AUTO_INCREMENT=3 ;

-- 
-- Dumping data for table `players`
-- 

INSERT INTO `players` VALUES (1, 'John', 'Doe', '[e-mail]', 'md5password', '123456789', '8768686865');
INSERT INTO `players` VALUES (2, 'Bob', 'the Builder', '[e-mail]', 'thisismypassword', '987654321', '989797976');

Thanks,

Sam

[Troy]

Sam, good work sending me the full structure with sample data. I simply ran that against my MySql server and ended up with your 3 tables and the sample data. Then I ran the original query I sent you. I did not get any errors, but I did not get any data. This means the query is syntactically correct. Whew.

Then I simply examined the data to see why nothing matched the query. The reason is that the calendar start and add values are outside the range requested in the WHERE clause. I simply changed the last number in the where clause to 1151060879, and walla--it works.

Curious--I assume the 'start', 'end', and 'added' columns are datetime data? Why are you using int(16) instead of DATETIME?

[lux]

Then I simply examined the data to see why nothing matched the query. The reason is that the calendar start and add values are outside the range requested in the WHERE clause. I simply changed the last number in the where clause to 1151060879, and walla--it works.

Hey Troy,

But when you got results, did you get Bob and John or did you only get Bob (like I did?). I was hoping to get both...

[Troy]

I only got Bob, and you should only get Bob. Look at your sample data--the only player referenced in the attendance table is Bob. Since we are INNER JOINing with attendance, we'll only get players who are in the attendance table.

It is possible to do an "OUTER JOIN" where all rows of one of the tables show up regardless of whether they have any data in the joined tables, but it is relatively rare that your business logic would need that. If you do determine that you truly need an OUTER JOIN in this situation, let me know.

[lux]

Basically I'm trying to retrieve all the results of the events added during the last 24 hours and all those that will occur during the next week and people have said they were coming. Then all this is processed in php and e-mailed. What's the best way?

[Troy]

Well, without fully knowing your business, first, I'd make those start, end, and added columns type DATETIME. I'd adjust my logic to store actual timestamps in those columns. That way, the data is human-readable. You'd store values in this format '2006-03-06 14:21:08' You can query the data ranges just like you do now with the benefit that you can read the timestamps when viewing the data using tools like phpMyAdmin.

If changing those columns is not an option at this point, it's not a showstopper--it will work (I assume you are storing the unix integer time value instead of the timestamp format.)

I'm trying to retrieve all the results of the events added during the last 24 hours and all those that will occur during the next week and people have said they were coming.

Hmmmm, I'm guessing here, but what you describe sounds like 2 jobs to me.

1. You want to be able to notify all players of new events added.
2. If events are coming up in the next week, notify all players who have indicated they will attend.

I would do this using two seperate logic loops and two seperate queries because these are very different tasks.

In fact, I'd probably do this in three queries. For the first job, I'd probably query the players and put their names and email addresses into a PHP array. Then I'd query the calendar table for new events (added in last 24 hours). Then I'd send an email to all players about those new events. The third query would be the one I already gave you except you'd remove the added part of the where clause. This query finds upcoming events and those players who have said they'll attend. Problem is, if a player is signed up for more than one upcoming event, you'll have their email address in the results twice. This is not a problem really--just a fact of the data. So as you loop over the results in your PHP, you'll need to make sure you only send one email per email address.

[lux]

So is the outer join not a possibility to achieve this? As that would be an easier script. For the moment I was very close to achieving it with the existing query, as the only thing lacking was the recently added events. Otherwise I could maybe create a new query for it but then I would have to somehow send it n the same e-mail as many e-mails would be very inconvenient...

[Troy]

I'm fairly confident it is not possible to achieve what you want with a single query. Other SQL gurus are welcome to jump in to prove me wrong. ;)

If you are comfortable with PHP, what you describe is not that much PHP code work.

I'm shooting from the hip, but perhaps a strategy like this may work. I'm sure not all of this will sound as clear as it was in my head! Read and re-read slowly until it makes sense or you have to give up and ask me to explain further. Honestly, though, much more help beyond this, and I'll be writing your app for you--in which case, let me give you my PayPal address. ;)

1. Query all the players and store into an associative array where the player id is the key.

2. Add an 'emailbody' item to each player array. So you'd end up with an array that looks something like this:

[1] {
[name] => John
[surname] => Doe
[email] => [e-mail]
[emailbody] =>
}
[2] {
[name] => Bob
[surname] => Builder
[email] => [e-mail]
[emailbody] =>
}

3. Query all the newly added events. Loop over them and build a single email message appropriate to let players know about each new event.

4. Loop over all the players in the player array, and set [emailbody] equal to the new events message you created in step 3.

5. Query all upcoming events along with who is signed up for each ordered by player. As you loop over these events, you'll need to keep track of when the player id changes. As long as you are on the same player id, keep appending to an email message. When the player id changes, you can append the message you just built to the [emailbody] in the player array for that player.

6. In the end, you'll have a player array where some or all have a final [emailbody] ready to be sent. Simply loop over the player array and send each email.

[lux]

Ok, it does make sense and I had that already, but it repeated each event the same amount of times as the user id, hence number 7 brought the but to my attention :) I thought the problem was due to the fact that on mysql I recieved many results. But basically when retrieving the rows it generates an array kind of like the one you have:
Array
(
[1] => Array
(
[name] => John Doe
[email] => [e-mail]
[1] => Array
(
[start] => 1141929000
[end] => 1141938000
[name] => test
[desc] => testing this
)

)

But for some reasons the php code below is then repeated as many times as the user id.

foreach($id as $j => $k){
if(($j != "name")&&($j != "email")){ 
$text.=$k['name']." is on ".date("l dS of F \f\\r\o\m H:i",$k['start'])." till ".date("H:i",$k['end']).".
".$k['desc'].".

";
}

Anyway, if I don't find a solution I'll have to change my expectations.

[Troy]

...but it repeated each event the same amount of times as the user id...

To make sure we are on the same page, you do understand why--right? If I have one event and 10 players and 5 of the players are signed up for the event, if I execute a query asking for all events and who is signed up, I will get 5 rows. All 5 rows will be for the same event obviously.

Likewise, if I have 2 events and 10 player and 3 players are signed up for event 1 and 9 players are signed up for event 2, I'll get 12 rows in my dataset. This is expected and what you want. The part where you compile the data into one email per player can only be done in your post-query processing.

[lux]

Yes, that's true but isn't my problem. For some reason the php foreach condition will generate things like:
Hello is on Thursday 02nd of March from 12:00 till 14:00.
Hopefully it works.

Hello is on Thursday 02nd of March from 12:00 till 14:00.
Hopefully it works.

Hello is on Thursday 02nd of March from 12:00 till 14:00.
Hopefully it works.

Hello is on Thursday 02nd of March from 12:00 till 14:00.
Hopefully it works.

Hello is on Thursday 02nd of March from 12:00 till 14:00.
Hopefully it works.

This will be the e-mail the person will get, for some reason the code repeats itself as many times as the user's id. Foreach is only meant to do it once that I know. It's not even if I had used a loop like while or something like that.

[Troy]

If you are comfortable doing so, email me the query you are using along with a display of the dataset you are getting. You can create the dataset display using code like this:

echo "<pre>\n";
print_r($data);
echo "</pre>\n";

My address is troy at troywolf dot com.