Jump to content

TheMagician

Members
  • Posts

    11
  • Joined

  • Last visited

    Never

Profile Information

  • Gender
    Not Telling

TheMagician's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. I have 2 classes; AnonymousUser (Null Object) and User (has some properties and methods). Both classes extend AbstractUser. Then I have a FactoryUser class that creates a proper object when called (AnonymousUser for guests and User for normal users). However, I was wondering how I would implement it so that AnonymousUser and User classes can't be called outside FactoryUser class (which would use Singleton pattern to create only one instance of either class). This would be to prevent so that when FactoryUser is called and an object is created from either class, you wouldn't be able to create, say, AnonymousUser AND User classes, which wouldn't make sense or multiple same objects for the user. So what I want is: $user = new AnonymousUser(); // FAIL $user = new User(); // FAIL $user = new FactoryUser::createUser( 0 ); // returns AnonymousUser object $user = new FactoryUser::createUser( 1 ); // returns previously created object or raises an Exception $user = new FactoryUser::createUser( 0 ); // same as above Thanks.
  2. But it returns the same rows, just in different order. I only want the highest score_score per score_song_id.
  3. Doesn't work. It only sorts the rows again to some arbitrary order (however still ordering by the score_song_id field, but the scores go in different order).
  4. CREATE TABLE `ks_scores` ( `score_id` int(11) NOT NULL auto_increment, `score_user_id` int(11) default NULL, `score_team_id` int(11) default NULL, `score_gtr_id` int(11) default NULL, `score_bass_id` int(11) default NULL, `score_drums_id` int(11) default NULL, `score_vox_id` int(11) default NULL, `score_game_id` tinyint(4) NOT NULL, `score_instrument_id` tinyint(4) NOT NULL, `score_diff_id` tinyint(4) default NULL, `score_song_id` int(11) NOT NULL, `score_score` int(11) NOT NULL, `score_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`score_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; CREATE TABLE `ks_scores_opt` ( `opt_id` int(11) NOT NULL, -- ks_scores.score_id `opt_stars` tinyint(4) default NULL, `opt_image` varchar(255) collate latin1_general_ci default NULL, `opt_video` varchar(255) collate latin1_general_ci default NULL, `opt_comment` varchar(255) collate latin1_general_ci default NULL, PRIMARY KEY (`opt_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; Query: SELECT s1.score_song_id, s1.score_score, s1.score_timestamp, opt.opt_stars, opt.opt_image, opt.opt_video, opt.opt_comment FROM ks_scores s1 LEFT JOIN ks_scores_opt AS opt ON ( s1.score_id = opt.opt_id ) WHERE s1.score_user_id =2 AND s1.score_game_id =3 AND s1.score_instrument_id =1 AND s1.score_diff_id =3 ORDER BY s1.score_song_id ASC Result: score_song_id score_score score_timestamp opt_stars opt_image opt_video opt_comment 1 266381 2008-03-03 16:57:02 5 NULL NULL NULL 1 999999 2008-11-11 04:00:02 0 NULL NULL NULL 1 283491 2008-07-26 19:24:16 5 NULL NULL NULL 2 154584 2008-03-03 16:57:30 5 NULL NULL NULL 2 999999 2008-11-11 04:01:06 0 NULL NULL NULL 3 999999 2008-11-11 04:01:06 0 NULL NULL NULL 3 228592 2008-03-03 16:57:49 5 NULL NULL NULL ... Wanted result: score_song_id score_score score_timestamp opt_stars opt_image opt_video opt_comment 1 999999 2008-11-11 04:00:02 0 NULL NULL NULL 2 999999 2008-11-11 04:01:06 0 NULL NULL NULL 3 999999 2008-11-11 04:01:06 0 NULL NULL NULL ... Question is, how do I achieve this? Group by returns arbitrary rows, I've tried that, even when using "ORDER BY s1.score_song_id ASC, s1.score_score DESC"... I believe the problem is called "group-wise aggregate" or "within-group aggregate", but I just can't seem to be able to get the solution working.
  5. Sorry for double post, but I found this from the FAQs: http://www.oreillynet.com/pub/a/mysql/2007/03/01/optimize-mysql-rank-data.html I'm gonna read it tomorrow, but it probably provides a precise answer to my problem.
  6. Ok, so do you suggest replacing the * with the column names? Query #1 might actually be an older query, my code seems to have another query instead of that (this is an old project). The one in the first post probably gave wrong results. It looks like this: QUERY #2 ALTERNATE VERSION SELECT l.id AS l_id, l.song AS l_song, l.notestreak AS l_ns, l.venue AS l_venue, p.* FROM list l LEFT JOIN ( SELECT DISTINCT song_id, score, id, game, diff, stars, percentage, notestreak, date FROM scores WHERE game=4 AND diff=4 AND user_id=2 ORDER BY song_id ASC, score DESC ) AS p ON (p.game=l.game) AND (p.diff=l.diff) AND (p.song_id=l.id) WHERE l.game=4 AND l.diff=4 GROUP BY l_id ORDER BY l_id What I'm trying to achieve here is: - Select highest score for each song_id from 'scores' table for user_id=2 (users can submit more than 1 score for each song_id) - Select proper song name etc. from the table 'list'
  7. TABLE #1 STRUCTURE - SCORES CREATE TABLE `scores` ( `id` int(11) NOT NULL auto_increment, `user_id` int(11) NOT NULL, `song_id` int(11) NOT NULL, `game` int(11) NOT NULL, `diff` int(11) NOT NULL, `score` int(11) NOT NULL, `stars` int(11), `percentage` int(11), `notestreak` int(11), `date` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ); #1 EXAMPLE DATA INSERT INTO scores (user_id, song_id, game, diff, score, stars, percentage, notestreak) VALUES (2, 1, 4, 4, 255255, 5, 100, 551), (2, 1, 4, 4, 255655, 5, 100, 551), (3, 1, 4, 4, 256255, 5, 100, 551), (2, 2, 4, 4, 255255, 5, 100, 772), (3, 2, 4, 4, 256255, 5, 100, 772); TABLE #2 STRUCTURE - SONGS CREATE TABLE `list` ( `id` int(11) NOT NULL, `song` varchar(64) NOT NULL, `game` int(11) NOT NULL, `diff` int(11) NOT NULL, `notestreak` int(11) NOT NULL, `venue` int(11) NOT NULL ); #2 EXAMPLE DATA INSERT INTO `list` VALUES (1, 'Slow Ride', 4, 4, 551, -1); INSERT INTO `list` VALUES (2, 'Talk Dirty To Me', 4, 4, 772, -1); QUERY #1 - RETRIEVE SCORES AND SONG NAMES SELECT s.*, l.song FROM scores s, list l WHERE s.user_id = 2 AND s.game = 4 AND s.diff = 4 AND l.game = 4 AND l.id = s.song_id AND l.is_dlc = 0 GROUP BY s.song_id ORDER BY s.song_id ASC QUERY #2 - RETRIEVE RANK FOR EACH SCORE SELECT COUNT( score ) + 1 AS rank FROM ( SELECT MAX( score ) AS score FROM scores WHERE game = 4 AND diff = 4 AND song_id = _ # this id is retrieved from the above query AND user_id > 1 AND score > ______ # this score is retrieved from the above query GROUP BY user_id ORDER BY score DESC ) AS rank_table Question #1: How could I combine these queries? Temporary solution: PHP array that has other arrays inside, where the rank is also added so that I can also sort the output by "rank" field. I have a feeling that this is very slow though. And if I could combine those queries I could get rid of a lot of code. Question #2: Could I make the queries run faster ie. by changing them or adding indexes? I have tried to set some indexes, but they're probably not good, so I'm asking experts' advice here. Thanks! edit: MySQL version: 5.0.41-community-nt
  8. Apparently I had to change this line: <option value="{loop_teams.TEAM_ID}">{loop_teams.TEAM_NAME} ({loop_teams.TEAM_PARTNER})</option> To: <option value="{switch_show_team_selector.loop_teams.TEAM_ID}">{switch_show_team_selector.loop_teams.TEAM_NAME} ({switch_show_team_selector.loop_teams.TEAM_PARTNER})</option>
  9. I know this should go to phpBB MOD forums, but the phpBB2 section is completely locked down so I can't ask it there. So basically, I have a problem with having a loop inside a switch <!-- template.tpl --> <!-- BEGIN switch_show_team_selector --> <select name="team"> <!-- BEGIN loop_teams --> <option value="{loop_teams.TEAM_ID}">{loop_teams.TEAM_NAME} ({loop_teams.TEAM_PARTNER})</option> <!-- END loop_teams --> <!-- END switch_show_team_selector --> </select> <?php // set template file $template->set_filenames(array( 'team_selector' => 'template.tpl') ); i // IF found one or more teams: show the select element $template->assign_block_vars('switch_select_team', array()); // WHILE: output all found teams within option elements inside the select element $template->assign_block_vars('switch_select_team.loop_teams', array( 'TEAM_ID' => $row['team_id'], 'TEAM_NAME' => $row['team_name']), 'TEAM_PARTNER' => $row['team_partner']) ); // render team selector $template->pparse('team_selector'); // clean data $template->destroy(); ?> This code almost works, except that the values aren't outputted, but it still outputs as many option elements as there are found teams. If I change 'switch_select_team.teams' to just 'teams' then no option elements are outputted at all. I've tried everything I can think of, can anyone who's familiar with the engine help?
  10. I think I fixed it, problem solved unless someone can find a problem in it. <?php function returnLink($url, $title, $alt=null) { // Return either link or normal text // If $alt is not null, append text after the link $real_url = '?' . $_SERVER['QUERY_STRING']; if(!is_null($alt)) { if(($real_url == $url) || strpos($real_url, $url . '&') !== false) { echo sprintf('<p class="selected_link">%s</p> %s', $title, $alt); } else { echo sprintf('<a href="%s">%s</a> %s', $url, $title, $alt); } } else { if(($real_url == $url) || strpos($real_url, $url . '&') !== false) { echo sprintf('<p class="selected_link">%s</p>', $title); } else { echo sprintf('<a href="%s">%s</a>', $url, $title); } } } ?>
  11. <?php function returnLink($url, $title, $alt=null) { // Return either link or normal text // If $alt is not null, append text after the link if(!is_null($alt)) { if(!strpos($_SERVER['REQUEST_URI'], $url)) { echo sprintf('<a href="%s">%s</a> %s', $url, $title, $alt); } else { echo sprintf('<p class="selected_link">%s</p> %s', $title, $alt); } } else { if(!strpos($_SERVER['REQUEST_URI'], $url)) { echo sprintf('<a href="%s">%s</a>', $url, $title); } else { echo sprintf('<p class="selected_link">%s</p>', $title); } } } ?> This function works pretty well for situations like the following: <?php returnLink("?foo=1"); returnLink("?foo=5&bar=6"); ?> But if you have something like this: <?php returnLink("?foo=1"); returnLink("?foo=10"); ?> Then both of the links are outputted as normal text, which is what I don't want. How should I modify my function to also take this into consideration? Thanks. edit: Added PHP tags.
×
×
  • 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.