Crusader

Ahhh, never mind. Solved thanks! Moved the user_id condition into the ON.

How can I do a LEFT JOIN with a where on the right and still include every value of the left table? SELECT i.`integer`, ui.`seen` FROM `integers` AS i LEFT JOIN `user_integers` AS ui ON (i.`integer` = ui.`integer`) WHERE i.`category` = 1 AND ui.`user_id` = 1 this results in something like this: integer seen 1 1 2 1 3 1 but I want it to display everything regardless of the seen. integer seen 1 1 2 1 3 1 4 null 5 null 6 null The tables are built like this: integers; integer category 1 1 2 1 3 1 4 1 5 1 6 1 7 2 8 2 user_integers; id user_id integer seen 1 1 1 1 1 1 2 1 1 1 3 1 1 2 2 1 1 2 1 1 Thanks

I've got about 5,000 integers that need to be checked if a user has seen before. These integers are grouped into different categories of varying size, so one category could contain 325 of the integers while another has 50 of them. They're all unique integers and won't appear in any category more than once. Meaning that if it's in one category, it's only in that category. Right now I have a table setup to contain the following: user_id | category_1 | category_2 | category_3 | category_n... etc In category_1 there's just a comma separated list of integers that the user has seen before. eg. 1,2,3,5,6,7,111,9,10 This seems a bit clunky so I'm thinking about storing each individual integer seen in its own row. Kind of like this. user_id | int_seen | category The only problem I can think of is that there could be any number of user_ids... It'd probably be capped at 10,000 but that'd still be 50,000,000 entries in the table... My solution to that would be clearing the table of a specific user_id's entries when they have seen all the integers and making another table that contains a list of users that have done this. That and maybe having a group_id column in the original table so people in a group can share a result set instead. What do you guys think? Any ideas or alternatives? edit: hope that made sense

Solved it myself. I used INNER JOINS instead.

Note: Query 2 should say UPDATE.

Hi again. I've got these two updates working properly but I'm not too sure if I've done them properly or elegantly. Running MySQL 4.1. Query 1: UPDATE LOW_PRIORITY `player_cargo` c, `player_ships` s SET c.amount = c.`amount` + ?, s.`open_holds` = s.`open_holds` - ? WHERE c.`id` = ? AND (s.`pilot` = ? AND s.`game` = ?) Query 2: PDATE LOW_PRIORITY `player_ships` s, `players` p SET s.`sector` = ?, s.`galaxy` = ?, p.`turns` = p.`turns` - ? WHERE (s.`game` = p.`game` AND s.`pilot` = p.`player_id`) AND p.`user_id` = ? Table structures: CREATE TABLE `players` ( `id` mediumint( unsigned NOT NULL auto_increment, `game` tinyint(3) unsigned NOT NULL default '0', `player_id` smallint(5) unsigned NOT NULL default '0', `user_id` smallint(5) unsigned NOT NULL default '0', `name` varchar(32) NOT NULL default '', `race` tinyint(2) unsigned NOT NULL default '0', `turns` float(4,1) unsigned NOT NULL default '100.0', `credits` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`), KEY `game` (`game`), KEY `player_id` (`player_id`), KEY `user_id` (`user_id`), KEY `name` (`name`), KEY `race` (`race`), KEY `turns` (`turns`), KEY `credits` (`credits`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `player_cargo` ( `id` int(10) unsigned NOT NULL auto_increment, `player_id` smallint(5) unsigned NOT NULL default '0', `game` tinyint(3) unsigned NOT NULL default '0', `good` tinyint(3) unsigned NOT NULL default '0', `amount` smallint(4) unsigned NOT NULL default '0', PRIMARY KEY (`id`), KEY `player_id` (`player_id`), KEY `game` (`game`), KEY `good` (`good`), KEY `cargo` (`player_id`,`game`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `player_ships` ( `id` mediumint( unsigned NOT NULL auto_increment, `game` tinyint(3) unsigned NOT NULL default '0', `pilot` smallint(5) unsigned NOT NULL default '0', `type` tinyint(3) unsigned NOT NULL default '1', `galaxy` tinyint(2) unsigned NOT NULL default '0', `sector` smallint(5) unsigned NOT NULL default '1', `armour` smallint(5) unsigned NOT NULL default '50', `hull` smallint(5) unsigned NOT NULL default '50', `holds` smallint(4) unsigned NOT NULL default '5', `open_holds` smallint(4) unsigned NOT NULL default '0', `weapons` varchar(32) NOT NULL default '0', `mines` smallint(4) unsigned NOT NULL default '0', `fighters` smallint(4) unsigned NOT NULL default '0', `scouts` smallint(4) unsigned NOT NULL default '0', `docked` tinyint(1) unsigned NOT NULL default '0', PRIMARY KEY (`id`), KEY `game` (`game`), KEY `pilot` (`pilot`), KEY `type` (`type`), KEY `galaxy` (`galaxy`), KEY `sector` (`sector`), KEY `docked` (`docked`), KEY `contact` (`pilot`,`sector`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Also... am I using too many indexes? Thanks!

Please help me figure out what index to put in for this. I see this in all my slow logs since it doesn't use indexes. It's called on pretty much every page on my forum. SELECT moderator.mid as is_mod, moderator.allow_warn, m.*, g.* FROM ibf_members m LEFT JOIN ibf_groups g ON (g.g_id=m.mgroup) LEFT JOIN ibf_moderators moderator ON (moderator.member_id=m.id OR moderator.group_id=m.mgroup ) WHERE m.id=592; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE m const PRIMARY,id PRIMARY 3 const 1 1 SIMPLE g const PRIMARY PRIMARY 4 const 1 1 SIMPLE moderator ALL group_id,member_id NULL NULL NULL 30 I can't, for the life of me, figure this out.

Thanks!

What's the error message?

Well that's where I learned about LOW_PRIORITY but it wasn't quite clear to me whether or not it would leave users in limbo until everyone else had finished moving/updating.

Would using an "UPDATE LOW_PRIORITY" have any negative effects on a game? I'm storing user positions in the database and updating their row whenever they move to a different area (which would be a lot). UPDATE LOW_PRIORITY `players` SET `sector` = ?, `galaxy` = ?, `x` = ?, `y` = ? WHERE `game` = ? AND `player_id` = ? LIMIT 1 Before calling that statement this is initially run at the start of every page. SELECT * FROM `players` WHERE `user_id` = ? AND `game` = ? LIMIT 1 Would this result in nobody being able to move until someone else has completed theirs? I don't want that to happen and am not sure how to test this. ??? Note: I believe table locking is set to off on mysqld.

It should be. I figured it out though. This: if(($this->map[$v]['x'] > 0) && ($this->x_walls[$pos - 1] >= 0)) { if($exits == '') $exits .= $this->sectors[$x_pos - 1][$y_pos]; else $exits .= ', ' . $this->sectors[$x_pos - 1][$y_pos]; } Should be this: if(($this->map[$v]['x'] > 0) && ($this->x_walls[$pos - 1] == 0)) { if($exits == '') $exits .= $this->sectors[$x_pos - 1][$y_pos]; else $exits .= ', ' . $this->sectors[$x_pos - 1][$y_pos]; } Any more improvements, bugs you notice would be appreciated. Thanks for your time!

I just tested it and it does come up as true :S With some help, I found the problem. It was in the function spawn. Here's the fix: if(rand(0, 99) < $mobility) { if($ny != $y) $this->y_walls[($cy * $width) + $cx] = 0; else $this->x_walls[($cy * $width) + $cx] = 0; $max_limit_2++; //return 1; } return 1; // MOVE IT HERE Now the only issue I have is that some sectors don't show their connections to others. [4] => Array ( [x] => 0 [y] => 3 [exits] => 3, 5 ) [14] => Array ( [x] => 1 [y] => 3 [exits] => 4, 15 ) Note how 4 doesn't know 14 connects to it? Thanks again for help.

I recently ported a Python map making algorithm to PHP and decided to improve upon it. My script works fine but unfortunately it's pretty slow. It can generate perfect mazes but it takes at least 2 minutes to generate a 5x5 map. You can view the source here: http://pastebin.com/m56bdc991 Is there anything I can do to speed it up or generally more efficient? Thanks. PS: If you want to use/modify the code I wrote feel free to but PLEASE DO NOT SELL IT!

table: lyrics id, title, artist, album, lyrics table: albums id, title, year query: SELECT l.album, l.title, a.year FROM `lyrics` AS l, albums AS a WHERE l.album = a.id ORDER BY a.year DESC My query is setup like above and I'd like to paginate the results. The problem is that I want to paginate the results AND keep results fully grouped by album. For example, I only want to get results from only the first 5 albums and paginate the rest. The way I have it now would cut results off onto the next page. Any ideas on how to accomplish this? Thanks