Jump to content

running_out_of_imagination

Members
  • Posts

    14
  • Joined

  • Last visited

    Never

Contact Methods

  • Website URL
    http://www.srmurias.net

Profile Information

  • Gender
    Not Telling
  • Location
    Southampton, UK

running_out_of_imagination's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. Hey, I made some progress, I found out this is called cartesian product (expalined in [a href=\"http://en.wikipedia.org/wiki/Cartesian_product\" target=\"_blank\"]wikipedia[/a] and [a href=\"http://www.fluffycat.com/SQL/Cartesian-Joins/\" target=\"_blank\"]sql[/a]) So the code looks like this now: [code] 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 >= '1141826733' AND c.start <= '1142431533') OR (c.added >= '1141740333' AND c.added <= '1141826733')) ORDER BY p.id ASC , c.start ASC[/code] But the second part of the OR statement doesn't work. That is if I remove the c.start part, it will return an empty set. I was expecting to get all results added in the previous 24 hours and all the events that people said they were coming that will occur in the next week. If anyone could help that would be great, there's the db below: [code] -- -- 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', 'john@doe.com', 'md5password', '123456789', '8768686865'); INSERT INTO `players` VALUES (2, 'Bob', 'the Builder', 'bob@builder.com', 'thisismypassword', '987654321', '989797976'); [/code]
  2. [!--quoteo(post=350744:date=Mar 1 2006, 07:22 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 1 2006, 07:22 PM) [snapback]350744[/snapback][/div][div class=\'quotemain\'][!--quotec--] Everything I see tells me the query is good. However, the test data you just gave me is not good data. The row that you insert into attendance has two bad references. If Bob is attending "Event" then it should be INSERT INTO `attendance` VALUES (10, 9, 11, 'yes'); Because Bob's id column is set to 9, and "Event" has its id column set to 11. The attendance table needs to link 9 in the player table to 11 in the calendar table. A table is not an array. You cannot use 1 and 2. [/quote] Sorry, auto increment mistake. The ids are supposed to be 1 and 2 for John and Bob respectively.
  3. yes, maybe have the database details on a dbconfig.php and just include it on the main script. Also don't try to have highlight_file($_GET[$bar]) as people could just change the variable on the URL.
  4. [!--quoteo(post=350515:date=Mar 1 2006, 05:25 AM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 1 2006, 05:25 AM) [snapback]350515[/snapback][/div][div class=\'quotemain\'][!--quotec--] Maybe you could post some relevant information about your table structures. [/quote] Here is the structure with some data: [code] 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=11; -- -- Dumping data for table `attendance` -- INSERT INTO `attendance` VALUES (10, 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=12; -- -- Dumping data for table `calendar` -- INSERT INTO `calendar` VALUES (11, 'Event', 1, 'This is an event', 1141207200, 1141221600, 1141061678); -- -------------------------------------------------------- -- -- 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=10; -- -- Dumping data for table `players` -- INSERT INTO `players` VALUES (8, 'John', 'Doe', 'john@doe.com', 'md5password', '123456789', '8768686865'); INSERT INTO `players` VALUES (9, 'Bob', 'the Builder', 'bob@builder.com', 'thisismypassword', '987654321', '989797976');[/code] I hope it helps. It has 2 people and one event to which Bob is attending.
  5. Do you mean a function like highlight_file()?
  6. If you want the products that cost £10,00 you have to use: [code]costPound = '$costPound' AND costPence = '$costPence'";[/code] Maybe merge both fileds as no one will look up for something that costs £x,65 beeing x any value
  7. [!--quoteo(post=350439:date=Mar 1 2006, 12:02 AM:name=Barand)--][div class=\'quotetop\']QUOTE(Barand @ Mar 1 2006, 12:02 AM) [snapback]350439[/snapback][/div][div class=\'quotemain\'][!--quotec--] Is the code you posted inside another loop?it' [/quote] No, it's inside the two foreach loops: [code]foreach($events as $m => $id){ //per player          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'].".       ";    } } }  [/code]
  8. [!--quoteo(post=350433:date=Feb 28 2006, 11:31 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Feb 28 2006, 11:31 PM) [snapback]350433[/snapback][/div][div class=\'quotemain\'][!--quotec--] [code]a.event = c.id [/code] [/quote] Those are not the (major) issues. With the query you gave me I get an empty set, something due with the part above. Because if I run the query without that bit in particular I get results.
  9. Hey, the outcome is right and sorry for the tab. But php repeats this as many times as the user's id. So if my user id is 3 I'll get: [!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Training is on Tuesday 28th of February from 11:00 till 13:00 We'll have training today Party is on Wednesday 1st of March from 10:00 till 14:00 Party on high street Training is on Tuesday 28th of February from 11:00 till 13:00 We'll have training today Party is on Wednesday 1st of March from 10:00 till 14:00 Party on high street Training is on Tuesday 28th of February from 11:00 till 13:00 We'll have training today Party is on Wednesday 1st of March from 10:00 till 14:00 Party on high street[/quote] Is it because I have a foreach in another foreach or is some variable in the wrong place?
  10. No, I want the events that were added in the last 24 hours and the events that will occur during the week. Not the events added during the last 24 hours that will occur during the next week. Hence I have to use OR, right?
  11. Ok, this query is based on 3 databases. One that has the players and their details (e-mail), one called calendar which has events (trainings, games) and one called attendance (which stores information weather people attend or not the events). The query is supposed to retrieve rows with the players details and events details that: were added in the last 24 hours will start during the next week This data is then processed in php and a script e-mails the people. My initial script did that correctly but retrieved the same rows many times.
  12. Hey wickning1, Your solution had 2 consequences. First of all it did stop the repeated entries from showing up, but it now doesn't really run the second part of the query: [code] c.added BETWEEN "1140974479" AND "1141060879"/code] Could it be something related with the OR?
  13. I have this foreach statement that is supposed to take data from an array and process it to put it an e-mail, but I've noticed that the script does it as many times as your user id. Can anyone figure out why? Array Structure: Array ( [1] => Array ( [name] => John Doe [email] => john@doe.com [6] => Array ( [start] => 1141124400 [end] => 1141131600 [name] => Training [desc] => We'll have training today ) [7] => Array ( [start] => 1141207200 [end] => 1141221600 [name] => Party [desc] => Party on high street ) ) ) PHP code: [code] foreach($events as $m => $id){ //per player          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'].". "; } } }    [/code] Does anyone know how to fix this?
  14. Hello, I have this query: [code] 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[/code] 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?
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.