SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›11 »

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Is there a more elegant way of doing what I'm trying to do here?

SELECT (some stuff) FROM Game WHERE (TimeLimitBunit = 'MINUTE' AND TIMESTAMPADD(MINUTE,TimeLimitB,LastMove) < NOW()) OR (TimeLimitBunit = 'HOUR' AND TIMESTAMPADD(HOUR,TimeLimitB,LastMove) < NOW()) OR (TimeLimitBunit = 'DAY' AND TIMESTAMPADD(DAY,TimeLimitB,LastMove) < NOW())

What I really wanted to do was just this:

SELECT (some stuff) FROM Game WHERE TIMESTAMPADD(TimeLimitBunit,TimeLimitB,LastMove) < NOW()

but it doesn't work, presumably because TIMESTAMPADD receives 'MINUTE' rather than MINUTE as its first argument and doesn't know what to do.

# ¿ Jan 6, 2009 20:29

Adbot: ADBOT LOVES YOU

# ¿ May 17, 2024 18:11

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Tell me whether the query I have constructed is a reasonably sensible one for the task I want MySQL to do. Is there a far simpler way of doing this?

code:

SELECT User,
       Name,
       NumWins,
       NumPlays,
       ROUND(100*NumWins/NumPlays, 2) AS PercentageWon
       FROM (
           SELECT User,
                  Name,
                  SUM(Co) AS NumPlays,
                  SUM(Co*CASE GameResult WHEN 'Finished 1st' THEN 1
                                         ELSE 0 END)
                      AS NumWins
                  FROM (
                      SELECT COUNT(PlayerGameRcd.User) AS Co,
                             PlayerGameRcd.User,
                             PlayerGameRcd.GameResult,
                             User.Name
                             FROM PlayerGameRcd
                                 LEFT JOIN User
                                 ON PlayerGameRcd.User = User.UserID
                                 LEFT JOIN Game
                                 ON PlayerGameRcd.Game = Game.GameID
                             WHERE PlayerGameRcd.GameResult <> 'Game Aborted'
                                   AND PlayerGameRcd.GameCounts = 1
                                   AND Game.Friendly = 0
                             GROUP BY PlayerGameRcd.User,
                                      PlayerGameRcd.GameResult
                       ) AS QRYA
                   GROUP BY User
            ) AS QRYB
       ORDER BY PercentageWon DESC
       LIMIT 50

The purpose of the query is to populate a table of up to 50; the table's purpose is to show the details of the people who have won the greatest proportion of games (out of all those who have played at least one game to completion). I want to tabulate their names, the number of games they have played to completion, the number of games they have won, and the percentage of the games they played to completion that they won. I also want to get back their user ID numbers so that I can make each name in the table a hyperlink to the appropriate user details page. PlayerGameRcd is a table containing records of players' performance in games. GameResult is the player's result from a game.

# ¿ Jan 10, 2009 04:28

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

My web host provides PHPMyAdmin as one of the tools that can be used to manage MySQL databases. I was idly looking through just now and noticed that a warning message of the following form appears on the page for each of the tables in my database (except for one of them which has two columns for its primary key):

quote:

The following indexes appear to be equal and one of them should be removed: PRIMARY, UserID

Is this something I should be concerned about?

The tables were created using commands of the following form:

quote:

CREATE TABLE User(UserID SERIAL, some other columns, PRIMARY KEY(UserID))

# ¿ Feb 5, 2009 21:41

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

surrealcatalyst posted:

Why create tables with commands rather than using PHPMyAdmin, if you've got access to it?

I didn't realise at the time that there was a tool like PHPMyAdmin available. I was learning PHP and MySQL out of a book and pretty much did what the book said to do. It gave the PHP code for a page that could be used for sending queries to the database. I still use that page when I want to query the database, except for security it now needs the MySQL username, password and database name to be entered by the user.

# ¿ Feb 5, 2009 23:18

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Suppose that I have a table called User and a table called UserLogins. The table UserLogins has the columns UserID, LoginTime (together making the table's Primary Key), and EnteredNumber. EnteredNumber is an integer between 0 and 9 that the user is required to enter when he logs in.

Suppose I want to use a query to create a table of users that is sorted according to the total of all the numbers that a user has ever entered when he logged in, in descending order... but if a user has logged in more than 100 times, I only want to take into account the last 100 times he logged in. How do I go about doing this?

(The task I am trying to figure out how to do actually has nothing to do with users logging in; I just decided to express it in a generic setting. So if your response is "That sounds ridiculous, why are you trying to do that?", yes it is ridiculous and no I'm not really trying to do that, but please tell me how I would do it anyway.)

# ¿ Feb 7, 2009 16:06

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Xae posted:

Try this:

code:


SELECT USER_ID, sum(num_entered)
  FROM  (SELECT USER_ID, num_entered
	   FROM base_table
	  WHERE user_id = lc_counts.user_id
	    AND ROWNUM < 101
	  ORDER BY time_stamp desc) temp_work_table
;

Thanks for the suggestion. I ought to have mentioned that I am constrained to using MySQL. Looking on the internet, I see that ROWNUM is a feature that other RDBMSs have but that MySQL does not have. Is there a way of doing it that will work in MySQL?

Edit: Also, I want to make sure it's clear that I want to apply the "most recent 100 logins" requirement to the users individually, not to the output as a whole. So if I have 3 users Adam, Beth and Chloe who have logged in 120, 50 and 90 times respectively, I want to get back three rows: the sum of Adam's last 100 login numbers, the sum of all of Beth's login numbers and the sum of all of Chloe's login numbers. And the only ordering in time of the logins that matters is the ordering of the individual user's logins relative to one another; it shouldn't matter when Chloe's logins occurred relative to Adam's, for example.

Hammerite fucked around with this message at 02:18 on Feb 8, 2009

# ¿ Feb 8, 2009 02:12

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Xae posted:

You will need to use a work table to store the results then. MySQL supports the LIMIT function to limit the number of rows.
Pseudo Code

OK, thanks for telling me how to do it.

It looks like that way of doing things would be quite computationally intensive. Since the actual task I have in mind is related to displaying information on a web page that anyone can access, I might consider automating the query to run once per day and each time store the results in the User table, or something like that.

# ¿ Feb 8, 2009 18:21

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

I have a table of Users and it has a lot of columns that represent 1/0, true/false settings; like whether the user is banned, whether he is validated, and a whole slew of preference settings that can be either on or off. I've set the columns as TINYINT(1); so basically there are a bunch of columns the size of a single byte that are being used to hold boolean values. Is this particularly inefficient compared to storing these settings, or groups of them, as a single number which can be decomposed into a sum of powers of two ("bitwise" interpretation of numbers I think this is referred to)? Or is the difference negligible?

Using MySQL if it matters.

# ¿ Feb 21, 2009 00:56

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Thanks for the answers. I have another, unrelated question.

I recently was obliged to restore my database from a backup copy and have just found out that throughout the replacement version, wherever the pound sterling character � should appear it has been replaced with a question mark. Thankfully, the question mark is a character that ought never to appear anywhere so I was able to correct this using a simple query (UPDATE TableName SET ColumnName = REPLACE(ColumnName, '?', '�')). But why did this happen in the first place, and how can I prevent it happening the next time I either back up the database or have to restore it from backup?

Looking at the SQL export file that forms my backup copy, I see that the fault exists in the backup copy of the database. The backup was produced by phpMyAdmin's export facility. All of the tables in this export file are specified in the following way:

CREATE TABLE IF NOT EXISTS TableName ( {some stuff} ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT={some number} ;

I'm assuming that the problem arises out of some character encoding issue. So is there some way I can set the character encoding of my tables so that this won't happem in future, or is it a limitation of the method I am using to back up the database? I was previously unaware of any need to supervise a character encoding, as no problems have been evident up to this point.

# ¿ Feb 22, 2009 02:02

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Reading on Wiki I note that the � symbol can be reliably obtained in HTML, even in a textarea environment, using the code £. Perhaps I ought to consider moving to this way of storing the symbol in the database.

EDIT: This is what I've chosen to do.

Hammerite fucked around with this message at 17:51 on Feb 22, 2009

# ¿ Feb 22, 2009 03:21

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Regarding my character encoding problem, I noticed that all accented characters that my users have typed (I have users speaking a variety of languages) were also changed to question marks in the backup. I could use character entities for these too (I found out that PHP has a function that does this), but that seems just OTT. So looking around on the Internet, I saw commentary on changing the character encoding of MySQL tables.

I've converted all of my tables from latin1 character encoding, latin1_swedish_ci collation to UTF-8 character encoding, utf8_general_ci collation. I made a backup copy both before and after doing this; I was pleased to see that in the "after" backup, the accented characters have come out correctly. But I was also confused when I noted that in the "before" backup, at which time the tables were still in latin1 encoding, the accented characters also survived - which is in contrast with what I expected would be the case given their state in the export file a couple of days ago when I first found the problems. I guess I don't know whether I've made the slightest bit of difference by changing the character encodings of my tables.

# ¿ Feb 23, 2009 20:06

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Victor posted:

Read what I said before: some code in the "pipeline" or "call chain" was probably loving things up. Accented characters have codes > 127.

I read it, and I appreciate it; I'm just remarking that I found it surprising that I got a different result re:accented characters the two times I made a backup from the latin1_swedish_ci database despite thinking I was doing absolutely everything just the same. Of course, I probably did something subtly different the 2nd time, or looked for the wrong things in the export file.

Edit: I did some experimenting with a test database and couldn't reproduce the "replace with question marks" behaviour at all, although the export function does behave better in general when the table is defined to be UTF-8, so it seems like the conversion was a worthwhile thing to do...

Hammerite fucked around with this message at 23:00 on Feb 23, 2009

# ¿ Feb 23, 2009 22:24

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

My web application allows people to play a board game against each other over the internet in a play-by-email type of format. There are two tables we are concerned with, Game and User. There are five different colours or "seats" for players in the game: Red, Yellow, Green, Purple, and White. A row in the Game table stores information on who is playing in each game as follows: there are five columns PlayerR, PlayerY, PlayerG, PlayerP and PlayerW which store the user ID of the player in that seat, or are null if that seat is unfilled. So for example we might have

code:

PlayerR   PlayerY   PlayerG   PlayerP   PlayerW
34        NULL      2         789       NULL

When somebody accesses the page for a game (to view the game's current status), or one of the players submits a move in the game, it is necessary to fetch certain information about the game's players along with information about the game (the players' usernames, whether they are male or female, the values they have set for certain preferences, etc). So the query I use at present looks something like the following:

code:

SELECT Game.*,
       PlayerR.Name AS NameR, PlayerR.IsAdmin AS IsAdminR, (other stuff),
       PlayerY.Name AS NameY, PlayerY.IsAdmin AS IsAdminY, (other stuff),
       PlayerG.Name AS NameG, PlayerG.IsAdmin AS IsAdminG, (other stuff),
       PlayerP.Name AS NameP, PlayerP.IsAdmin AS IsAdminP, (other stuff),
       PlayerW.Name AS NameW, PlayerW.IsAdmin AS IsAdminW, (other stuff)
       FROM Game LEFT JOIN User AS UserR ON Game.PlayerR = UserR.UserID
                 LEFT JOIN User AS UserY ON Game.PlayerY = UserY.UserID
                 LEFT JOIN User AS UserG ON Game.PlayerG = UserG.UserID
                 LEFT JOIN User AS UserP ON Game.PlayerP = UserP.UserID
                 LEFT JOIN User AS UserW ON Game.PlayerW = UserW.UserID
       WHERE Game.GameID = (some number)

PHP then goes through a process of putting the returned information into arrays, e.g. $Name will be an array of 5 whose elements are the NameR, NameY, NameG, NameP and NameW returned in the query results.

My question is, is this method of using five LEFT JOINs makedly inefficient and is there a much better way of doing it?

Using MySQL.

# ¿ Mar 28, 2009 12:54

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

When Daylight Saving Time began here recently, I ran into a problem with my PHP/MySQL website. Certain things (posted messages, moves taken in games, etc.) have a DATETIME variable associated with them to say when they happened. When someone, for example, views a posted message, the time it was posted is displayed at the top. The intention was always to have this time displayed as UTC (the application isn't very advanced and currently doesn't serve users times converted appropriately for their time zones). When DST started here, the times started to be shifted ahead by one hour, which isn't what was wanted. I initially assumed the problem was originating with PHP, but following some posts in the PHP general help thread and some experimentation, I realised the problem is with the way I'm recording the times when communicating with MySQL.

At present, whenever I insert or update a record with a datetime value, I just use the NOW() command to record the time. As in,

code:

'UPDATE Thread SET NumberOfPosts = NumberOfPosts + 1,
                   LastPoster = '.$_SESSION[MyUserID].',
                   LastPost = NOW()
               WHERE ThreadID = '.$EscapedThreadID

Reading the documentation for NOW() it becomes clear that the time recorded is appropriate to the time zone, including Daylight Saving Time if it applies. This isn't what I want. Now if I send the following query to MySQL:

code:

SELECT CONVERT_TZ(NOW(), @@session.time_zone, '+00:00')

I get the correct current UTC time. So the obvious (but maybe clunky) solution that occurs is to go through my code replacing NOW() everywhere with CONVERT_TZ(NOW(), @@session.time_zone, '+00:00'). So the above query would become

code:

'UPDATE Thread SET NumberOfPosts = NumberOfPosts + 1,
                   LastPoster = '.$_SESSION[MyUserID].',
                   LastPost = CONVERT_TZ(NOW(),
                                         @@session.time_zone,
                                         '+00:00')
               WHERE ThreadID = '.$EscapedThreadID

Is this needlessly complicated? Is there a better way of doing it?

# ¿ Apr 7, 2009 13:10

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Jethro posted:

UTC_TIMESTAMP()?

Ah, thanks, that's just what I needed.

# ¿ Apr 7, 2009 23:20

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

I asked a question about this problem a few pages ago and got a response from Triple Tech, but didn't ask any of the followup questions that have occurred to me since (mostly since I wrote it off as something I would deal with later on). I will restate the problem for convenience.

My database (MySQL) stores information about play-by-email board games (one particular board game) that people are playing against each other. Each game allows for up to 4 players, and there are 5 colours that the users can play as (allowing for future functionality that is anticipated to be necessary). When a user starts or joins a game, he chooses what colour he will be. I have a table Game and a table User. The table Game, in a poor piece of design, carries all of the information about who is playing in the game, in 5 columns: UserR, UserY, UserG, UserP, UserH (for Red, Yellow, Green, Purple and Grey). (These columns are NULL if there is nobody playing as the corresponding colour.) When running database queries, it is presently necessary to perform 5 LEFT JOINs, as in

code:

SELECT Game.(some columns),
       UserR.(some columns),
       UserY.(some columns),
       UserG.(some columns),
       UserP.(some columns),
       UserH.(some columns)
FROM Game LEFT JOIN User AS UserR ON Game.UserR = User.UserID
          LEFT JOIN User AS UserY ON Game.UserY = User.UserID
          LEFT JOIN User AS UserG ON Game.UserG = User.UserID
          LEFT JOIN User AS UserP ON Game.UserP = User.UserID
          LEFT JOIN User AS UserH ON Game.UserH = User.UserID
WHERE Game.GameID = x

NB. Here I write "UserR.(some columns)" as shorthand for "UserR.column1, userR.column2, ..."; not because I intend to write "UserR.(column1, column2, ...)" or suchlike.

Sometimes even 6 LEFT JOINs are needed, as there is a separate column for "game creator" and it is sometimes desirable to have this user's information joined as UserC. (The game's creator is usually one of the players, but need not always be.)

This is evidently not good design and should be restructured by DROPping the columns UserR, UserY, UserG, UserP and UserH and implementing a new table, GamePlayer, that has three columns: Game, User and Colour. Then, the above query becomes

code:

SELECT GamePlayer.Colour,
       Game.(some columns),
       User.(some columns)
FROM GamePlayer LEFT JOIN Game ON GamePlayer.Game = Game.GameID
                LEFT JOIN User ON GamePlayer.User = User.UserID
WHERE GamePlayer.Game = x

When finding information on (say) the games being played by a particular user, the following query may be used:

code:

SELECT GamePlayer.Colour, Game.(some columns)
FROM GamePlayer LEFT JOIN Game ON GamePlayer.Game = Game.GameID
WHERE GamePlayer.User = x

One question I have is this: Sometimes I want to get information about all the games a particular user is playing, and also information about the people with whom he is playing those games. For example, I might want to generate an HTML table showing details of all his games, to include the name of the player whose turn it is right now as well as a link to that player's list of games. For this I need the user ID and user name of that user, which necessitates fetching his name (either that or adding a redundant "user name" column to the GamePlayer table).

Suppose I want to fetch information about the games that user x is playing, the creators of those games, and the players who are playing in the games. Is the following query the best way of doing this?

code:

SELECT GamePlayer.Colour,
       Game.(some columns),
       GamePlayerX.Colour,
       User.(some columns),
       UserC.(some columns)
FROM GamePlayer LEFT JOIN Game ON GamePlayer.Game = Game.GameID
                INNER JOIN GamePlayer AS GamePlayerX ON Game.GameID = GamePlayerX.Game
                LEFT JOIN User ON GamePlayerX.User = User.UserID
                LEFT JOIN User AS UserC ON Game.Creator = UserC.UserID
WHERE GamePlayer.User = x
ORDER BY Game.GameID

This query would, if I understand correctly, return more than one row per game; it would, for each game, return n rows where n is the number of players in the game. These rows would be identical everywhere except in the column from GamePlayerX and the columns from User. The PHP program that receives the data will then have to go through the rows checking at each new row whether the game being reported on is a new one, or the same as the previous row and detailing a new player. Is there a better way to do this?

# ¿ Jun 1, 2009 13:48

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Triple Tech posted:

Common problem, at least for me. You could also seperate out the two queries (game per user, users per game) and loop through the list of games individually. The overhead for the entire process goes up, but it's more functional and logical from a coder's point of view.

So as an alternative to doing that big complicated query I posted, which of the following do you think is better...?

(Ignore please my failure to use a database framework, this is an entirely separate issue that I may address at another time)

1) Query to get all the games user x is playing in ...

code:

SELECT GamePlayer.Game,
       GamePlayer.Colour,
       Game.(some columns),
       User.(some columns)
FROM GamePlayer LEFT JOIN Game ON GamePlayer.Game = Game.GameID
                LEFT JOIN User ON Game.Creator = User.UserID
WHERE GamePlayer.User = x
ORDER BY GamePlayer.Game

... followed by PHP code similar to this ...

php:

<?
$GamesArray = array();
while ( $row = mysqli_fetch_assoc($QueryResult) ) {
    $GamesArray[] = $row;
}
$NumGames = count($GamesArray);
$GameIDList = array();
for ($i=0;$i<$NumGames;$i++) {
    $GameIDList[] = $GamesArray[$i][Game];
}
$GameIDListString = implode(', ',$GameIDList);
?>

... followed by the following query to get information about the players of these games ...

code:

SELECT GamePlayer.Game, GamePlayer.Colour, User.(come columns)
FROM GamePlayer LEFT JOIN User ON GamePlayer.User = User.UserID
WHERE GamePlayer.Game IN ($GameIDListString)
ORDER BY GamePlayer.Game

... and then the following PHP code ...

php:

<?
$Counter = 0;
$ReadPlayerYet = 0;
while ( $row = mysqli_fetch_assoc($QueryResult) ) {
    if ( $row[Game] != $GamesArray[$Counter][Game] ) {
        if ( $ReadPlayerYet ) {
            $Counter++;
            if ( $row[Game] != $GamesArray[$Counter][Game] ) {
                $error = 1;
            } else {
                $error = 0;
            }
        } else {
            $error = 1;
        }
        if ( $error ) {
            // handle broken games that have no players
        }
    } else {
        $ReadPlayerYet = 1;
    }
    foreach ( $row as $key => $value ) {
        $GamesArray[$Counter][$key][$row[Colour]] = $value;
    }
}
?>

... after which I pass to whatever function outputs the HTML table.

2) Query to get all the games user x is playing in, as in 1) ...

code:

SELECT GamePlayer.Game,
       GamePlayer.Colour,
       Game.(some columns),
       User.(some columns)
FROM GamePlayer LEFT JOIN Game ON GamePlayer.Game = Game.GameID
                LEFT JOIN User ON Game.Creator = User.UserID
WHERE GamePlayer.User = x
ORDER BY GamePlayer.Game

... then use PHP code similar to this to loop through all of the games and query to find out who is playing in them?

php:

<?
$GamesArray = array();
while ( $row = mysqli_fetch_assoc($QueryResult) ) {
    $QueryResultX = mysqli_query($link,
                    'SELECT GamePlayer.Colour,
                            User.(some columns)
                     FROM GamePlayer LEFT JOIN User
                                     ON GamePlayer.User = User.UserID
                     WHERE GamePlayer.Game = '.$row[Game]);
    while ( $rowX = mysqli_fetch_assoc($QueryResultX) ) {
        foreach ( $rowX as $key => $value ) {
            $row[$key][$rowX[Colour]] = $value;
        }
    }
    $GamesArray[] = $row;
}
?>

And then pass to the function that generates my HTML table.

What's the difference in overhead likely to be between doing these things? 1) looks the tidiest, in that it involves doing two queries of limited complexity instead of n+1 - what's the difference in overhead likely to be between doing that and just using a single query to get all the data, as at the end of my previous post?

I guess I'm asking opinions, which way of doing it is "least bad"?

What happens if I'm looking to get information about a single game (to include information about its creator and all of its players)? I imagine I'd use the following:

code:

SELECT GamePlayer.Colour,
       Game.(some columns),
       User.(some columns),
       UserC.(some columns)
FROM GamePlayer LEFT JOIN Game ON GamePlayer.Game = Game.GameID
                LEFT JOIN User ON GamePlayer.User = User.UserID
                LEFT JOIN User AS UserC ON Game.Creator = UserC.UserID
WHERE GamePlayer.Game = x

I guess there's no reason that should be overly taxing for the database. But perhaps I should instead use two queries:

code:

SELECT Game.(some columns), User.(some columns)
FROM Game LEFT JOIN User ON Game.Creator = User.UserID
WHERE Game.GameID = x

(expect to receive 0 or 1 rows back from this)

code:

SELECT GamePlayer.Colour, User.(some columns)
FROM GamePlayer LEFT JOIN User ON GamePlayer.User = User.UserID
WHERE GamePlayer.Game = x

(expect multiple rows back from this)

# ¿ Jun 1, 2009 18:04

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

OK, well, thank you both for your suggestions! I will give it some more thought which approach to take.

# ¿ Jun 1, 2009 22:28

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

would this work?

code:

SELECT
 MON1, MON3, YTD, YR1,
 (CASE YR3CHECK WHEN 0 THEN YR3 ELSE NULL) AS YR3,
 (CASE YR5CHECK WHEN 0 THEN YR5 ELSE NULL) AS YR5,
 (CASE YR10CHECK WHEN 0 THEN YR10 ELSE NULL) AS YR10
 FROM (

SELECT
 (SUM(MON1)/100) AS MON1, (SUM(MON3)/100) AS MON3,
 (SUM(YTD)/100) AS YTD, (SUM(YR1)/100) AS YR1,
 (((POWER(((SUM(YR3)/10000)+1),(1.0/3.0)))-1)*100) AS YR3,
 (((POWER(((SUM(YR5)/10000)+1),(1.0/5.0)))-1)*100) AS YR5,
 (((POWER(((SUM(YR10)/10000)+1),(1.0/10.0)))-1)*100) AS YR10,
 (SUM(YR3CHECK)) AS YR3CHECK,
 (SUM(YR5CHECK)) AS YR5CHECK,
 (SUM(YR10CHECK)) AS YR10CHECK
 FROM ( 

SELECT
 MON1, MON3, YTD, YR1,
 YR3, (CASE WHEN YR3 IS NULL THEN 1 ELSE 0) AS YR3CHECK,
 YR5, (CASE WHEN YR5 IS NULL THEN 1 ELSE 0) AS YR5CHECK,
 YR10, (CASE WHEN YR10 IS NULL THEN 1 ELSE 0) AS YR10CHECK
 FROM(
            
SELECT
 (PERCENT * MON1) AS MON1, (PERCENT * MON3) AS MON3,
 (PERCENT * YTD) AS YTD, (PERCENT * YR1) AS YR1,
 (PERCENT * YR3) AS YR3, (PERCENT * YR5) AS YR5,
 (PERCENT * YR10) AS YR10
 FROM ( 
    
SELECT MJC_SEC_ID AS SEC_ID, MJC_ALCTN_PCT AS PERCENT,
 MSP_CMLV_1MTHR_PCT AS MON1, MSP_CMLV_3MTHR_PCT AS MON3,
 MSP_CMLV_YTDR_PCT AS YTD, MSP_CMLV_1YRR_PCT AS YR1,
 MSP_CMLV_3YRR_PCT AS YR3, MSP_CMLV_5YRR_PCT AS YR5,
 MSP_CMLV_10YRR_PCT AS YR10
 FROM MSPERFRM, ( 

    
SELECT DISTINCT
 MJC_SEC_ID, MJC_ALCTN_PCT
 FROM MJCOMP
 
 WHERE MJC_DCP_NBR = 'PLAN'
  AND MJC_RCP_PTF_CD = 'RCP_ID'
  AND ( MJC_COMP_XPDT IS NULL
   OR MJC_COMP_XPDT > 'RunDate' )
    
 
 ) AS SUBTABLE1
 WHERE ( MSPERFRM.MSP_SEC_ID = SUBTABLE1.MJC_SEC_ID )
 AND MSPERFRM.MSP_MTH_STR_DT = 'RunDate'
    

) AS SUBTABLE2 ) AS SUBTABLE3 ) AS SUBTABLE4 ) AS SUBTABLE5

edit: remove superfluous comma

Hammerite fucked around with this message at 13:11 on Jun 9, 2009

# ¿ Jun 8, 2009 22:16

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

actually this approach doesn't need that many "levels" of subquery, here is a better version with one fewer level

code:

SELECT
 MON1, MON3, YTD, YR1,
 (CASE YR3CHECK WHEN 0 THEN YR3 ELSE NULL) AS YR3,
 (CASE YR5CHECK WHEN 0 THEN YR5 ELSE NULL) AS YR5,
 (CASE YR10CHECK WHEN 0 THEN YR10 ELSE NULL) AS YR10
 FROM (

SELECT
 (SUM(MON1)/100) AS MON1, (SUM(MON3)/100) AS MON3,
 (SUM(YTD)/100) AS YTD, (SUM(YR1)/100) AS YR1,
 (((POWER(((SUM(YR3)/10000)+1),(1.0/3.0)))-1)*100) AS YR3,
 (((POWER(((SUM(YR5)/10000)+1),(1.0/5.0)))-1)*100) AS YR5,
 (((POWER(((SUM(YR10)/10000)+1),(1.0/10.0)))-1)*100) AS YR10,
 (SUM(YR3CHECK)) AS YR3CHECK,
 (SUM(YR5CHECK)) AS YR5CHECK,
 (SUM(YR10CHECK)) AS YR10CHECK
 FROM (
            
SELECT
 (PERCENT * MON1) AS MON1,
 (PERCENT * MON3) AS MON3,
 (PERCENT * YTD) AS YTD,
 (PERCENT * YR1) AS YR1,
 (PERCENT * YR3) AS YR3,
 (PERCENT * YR5) AS YR5,
 (PERCENT * YR10) AS YR10,
 (CASE WHEN YR3 IS NULL THEN 1 ELSE 0) AS YR3CHECK,
 (CASE WHEN YR5 IS NULL THEN 1 ELSE 0) AS YR5CHECK,
 (CASE WHEN YR10 IS NULL THEN 1 ELSE 0) AS YR10CHECK
 FROM ( 
    
SELECT MJC_SEC_ID AS SEC_ID, MJC_ALCTN_PCT AS PERCENT,
 MSP_CMLV_1MTHR_PCT AS MON1, MSP_CMLV_3MTHR_PCT AS MON3,
 MSP_CMLV_YTDR_PCT AS YTD, MSP_CMLV_1YRR_PCT AS YR1,
 MSP_CMLV_3YRR_PCT AS YR3, MSP_CMLV_5YRR_PCT AS YR5,
 MSP_CMLV_10YRR_PCT AS YR10
 FROM MSPERFRM, ( 

    
SELECT DISTINCT
 MJC_SEC_ID, MJC_ALCTN_PCT
 FROM MJCOMP
 
 WHERE MJC_DCP_NBR = 'PLAN'
  AND MJC_RCP_PTF_CD = 'RCP_ID'
  AND ( MJC_COMP_XPDT IS NULL
   OR MJC_COMP_XPDT > 'RunDate' )
    
 
 ) AS SUBTABLE1
 WHERE ( MSPERFRM.MSP_SEC_ID = SUBTABLE1.MJC_SEC_ID )
 AND MSPERFRM.MSP_MTH_STR_DT = 'RunDate'
    

) AS SUBTABLE2 ) AS SUBTABLE3 ) AS SUBTABLE4

# ¿ Jun 9, 2009 13:18

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

I don't know anything about "decode", but this is how I think I would do it

code:

SELECT Type,
       SUM(CASE Province WHEN 'ONT' THEN Amount ELSE 0) AS ONT,
       SUM(CASE Province WHEN 'NFL' THEN Amount ELSE 0) AS NFL,
       ...
       SUM(CASE Province WHEN 'MAN' THEN Amount ELSE 0) AS MAN
FROM Table
GROUP BY Type

This has maintainability issues re: what happens if new provinces are created, existing provinces merge etc. You'd have to edit all your queries.

# ¿ Jun 18, 2009 01:32

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

MoNsTeR posted:

Trunc() is an oracle function, I'm not sure what the MySQL equivalent is. All it does is take a date field with a value like '22-JUN-2009 08:48' and cut it off to '22-JUN-2009'.

DATE()

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date

# ¿ Jun 22, 2009 17:36

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

I'm sure I'm being retarded, but can someone point out to me what's wrong with this query?

code:

SELECT CASE NumPlays
            WHEN 0 THEN 0
            ELSE ROUND(10000*POW((1-1/NumPlays), 2)*NumWins/NumPlays)
            END
            AS Rating
       FROM (
           SUM(
               CASE Game.NumberOfPlayers
                    WHEN 2 THEN 0.8
                    ELSE 1
                    END
           ) AS NumPlays,
           SUM(
               CASE WHEN PlayerGameRcd.GameResult = 'Finished 1st'
                        AND Game.NumberOfPlayers = 2
                        THEN 0.8
                    WHEN PlayerGameRcd.GameResult = 'Finished 1st'
                        AND Game.NumberOfPlayers > 2
                        THEN 1
                    WHEN PlayerGameRcd.GameResult = 'Finished 2nd'
                        AND Game.NumberOfPlayers = 3
                        THEN 0.44
                    WHEN PlayerGameRcd.GameResult = 'Finished 2nd'
                        AND Game.NumberOfPlayers = 4
                        THEN 0.63
                    WHEN PlayerGameRcd.GameResult = 'Finished 3rd'
                        AND Game.NumberOfPlayers = 4
                        THEN 0.26
                    ELSE 0
                    END
           ) AS NumWins
           FROM PlayerGameRcd
               LEFT JOIN Game
               ON PlayerGameRcd.Game = Game.GameID
           WHERE PlayerGameRcd.User = 1           /*(any number)*/
               AND PlayerGameRcd.GameResult <> 'Game Aborted'
               AND PlayerGameRcd.GameCounts = 1
               AND Game.Friendly = 0
       ) AS QRYA

I get the following:

MySQL posted:

MySQL error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SUM( CASE Game.NumberOfPlayers WHEN 2 THEN ' at line 7

# ¿ Jul 2, 2009 23:27

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Ah poo poo, I've forgotten to put the word SELECT at the start of my subquery. I knew it would be something retarded. Thanks.

# ¿ Jul 2, 2009 23:32

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

I was going to post again puzzling over the fact that my query didn't seem to be working in cases where there are no rows over which to sum. It didn't seem to be giving me back an answer. I worked out after a bit of puzzling that it was giving back an answer, but the method I was using to view the output was fooling me into thinking that it wasn't. It was just giving back NULL. It comes down to the fact that SUMming over zero rows doesn't give you zero in SQL (well, MySQL anyway); it gives you NULL. I find this rather annoying; it seems ridiculous to me (as a mathematician) that an empty sum should be asserted to be something other than zero. This rant isn't addressed to anyone in particular; carry on.

# ¿ Jul 3, 2009 00:14

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

When I go to phpMyAdmin and view the details of most of the tables in my database, I see something that looks like the attatched image.

My database has 7 tables. Two of them have two columns as a Primary Key. Those tables do not exhibit this behaviour in phpMyAdmin. The other 5 each have a single column as Primary Key. The naming convention for each of these tables' Primary Key columns is (tablename)ID. So the User table has a Primary Key called UserID. Those tables all exhibit this behaviour. I don't know what PRIMARY is in the image; there is no column called that in the table, so I don't understand what phpMyAdmin is trying to tell me.

Only registered members can see post attachments!

# ¿ Jul 3, 2009 19:46

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Hammerite posted:

When I go to phpMyAdmin and view the details of most of the tables in my database, I see something that looks like the attatched image.

My database has 7 tables. Two of them have two columns as a Primary Key. Those tables do not exhibit this behaviour in phpMyAdmin. The other 5 each have a single column as Primary Key. The naming convention for each of these tables' Primary Key columns is (tablename)ID. So the User table has a Primary Key called UserID. Those tables all exhibit this behaviour. I don't know what PRIMARY is in the image; there is no column called that in the table, so I don't understand what phpMyAdmin is trying to tell me.

Some googling suggested that the problem is with something called an "index", which is related in a fashion I don't understand to the primary key for a table. The 5 tables with the warning message each have two indexes, one of which is named after the primary key column that I specified when I created the table, the other called "PRIMARY".

The attatched image shows the results of asking MySQL to show me the indexes on two of my tables, one of which (Message) is one with duplicate indexes, the other of which (WatchedGame) has a single index involving two columns.

It becomes clear that there was some fault with the queries I used to create these tables. Something in said queries caused MySQL to get the wrong idea and add an additional index to each one. But I'm confused as to how a CREATE TABLE query should be written, because I didn't use different syntax for the different types of tables. I created WatchedGame using

code:

CREATE TABLE WatchedGame(
    User BIGINT(20) UNSIGNED NOT NULL,
    Game BIGINT(20) UNSIGNED NOT NULL,
    PRIMARY KEY(User, Game)
) CHARACTER SET utf8 COLLATE utf8_general_ci

I created Message using

code:

CREATE TABLE Message(
    MessageID SERIAL,
    User BIGINT(20) UNSIGNED NOT NULL,
    Thread BIGINT(20) UNSIGNED NOT NULL,
    PostDate DATETIME NOT NULL,
    MessageText TEXT NOT NULL,
    DeletedByUser TINYINT(1) NOT NULL DEFAULT 0,
    DeletedByAdmin TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY(MessageID)
) CHARACTER SET utf8 COLLATE utf8_general_ci

Evidently there's something wrong with the second of these queries, but what is it? The syntax is identical so I don't understand why MySQL's behaviour was different.

Also, I guess I should delete one of the duplicate indexes, but does it matter which one? If I delete the wrong one, will MessageID cease to be my primary key and cause me problems, or any other wacky poo poo like that?

code:

DROP INDEX PRIMARY ON Message

Only registered members can see post attachments!

# ¿ Jul 3, 2009 20:25

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

I just tested this out again by creating a throwaway table

code:

CREATE TABLE Bumblebee (
    BumblebeeID SERIAL,
    BeeName VARCHAR(20) NOT NULL,
    PRIMARY KEY (BumblebeeID)
)

It does indeed create the table with a primary key and a duplicate unique key. I've tried googling but I haven't found any pages explaining this behaviour. This seems bizarre.

Edit: The problem is with using SERIAL. I ought to use BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT in its place.

Hammerite fucked around with this message at 21:55 on Jul 3, 2009

# ¿ Jul 3, 2009 21:48

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

camels posted:

Where's the real key of the table Message? If you define MessageID as an autoincrementing number then it can be used as a surrogate key, and to simplify joins, but without a real relational key then the table isn't normalized to first normal form and would allow duplicate rows to be inserted.

I don't understand! MessageID is the primary key of Message. What then constitutes a real key?

camels posted:

Table WatchedGame does not have any foreign keys defined that reference the tables User or Game. Similarly the table Message does not have any foreign keys, say to the User and/or Thread tables. Foreign keys are a form of declarative referential integrity (DRI), and without DRI it is possible for invalid data values to be written to the tables.

How do you mean - such as a record with a User value that doesn't match the UserID value of any row in table User? I suppose I was aware of the possibility but unconcerned, as I was not aware of an easy way to prevent it at the database level. I will look into the idea of defining some foreign keys for my tables.

camels posted:

Some of the columns may be sized too wide: The "ID" columns are declared as BIGINT(20), and column MessageText is declared as TEXT. Sizing columns to fit the data more closely is a good practice for data integrity and performance.

The reason I originally set some of my longer string type columns to TEXT was because I had the impression (I do not know where from) that the maximum size you could give to a VARCHAR column was 255, as in MessageText VARCHAR(255). Reading http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html I see that this impression was erroneous. Do you suggest (say) MessageText VARCHAR(10000) or similar, with the limit being some large number I don't want messages to exceed?

# ¿ Jul 4, 2009 13:27

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

OK, thank you for your advice.

edit:

quote:

I think I would recommend reading a book or article on database basics, especially an introduction to data modeling, keys, and normalization.

Have you a particular text in mind?

Hammerite fucked around with this message at 01:47 on Jul 5, 2009

# ¿ Jul 5, 2009 01:30

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

As an additional thought, googling tells me that in order to use foreign keys in MySQL I need to switch the tables that will use them from the MyISAM engine to InnoDB. Idle reading suggests that MyISAM is a generally somewhat higher-performance engine than InnoDB but that since in particular some of my tables are UPDATE'd quite often in comparison with how often they are read, InnoDB with foreign keys may or may not be superior. It is tough for me to know what is optimal.

# ¿ Jul 5, 2009 02:37

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Was disappointed just now to find that you can't do

code:

SELECT Name,
       Data_length + Index_length AS Size,
       Rows,
       Row_format
FROM (SHOW TABLE STATUS) AS XXX

in MySQL.

# ¿ Jul 10, 2009 23:43

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

I have a change I'm planning to make to my database design but I'm not certain whether it meets any standard of normalisation or other general design principles.

Basically I have a table Game which contains the games my users are playing on my site. A game can be in one of three states: Recruiting, In Progress, or Finished. If it's recruiting then there are various options that can be set: what is the minimum number of players, what is the maximum number of players, whether the game is private and what its password is, etc. These details, obviously, are stored as columns in the database. But those columns are useless once the game has started. It is typical for there to be anything from 0 to 10 games recruiting at a time; and at the moment there are something like 3000 games overall in the other two states.

So I plan on adding a new table which describes settings for a game in the "recruiting" stage. Something like

code:

CREATE TABLE LobbySettings (
    GameID BIGINT(20) UNSIGNED NOT NULL,
    MinimumPlayers TINYINT(1) UNSIGNED NOT NULL,
    MaximumPlayers TINYINT(1) UNSIGNED NOT NULL,
    AnyPlayerStarts TINYINT(1) UNSIGNED NOT NULL,
    MinimumRating SMALLINT(4) NOT NULL,
    GPrivate TINYINT(1) UNSIGNED NOT NULL,
    Password VARCHAR(20),
    PRIMARY KEY(Game)
) CHARACTER SET utf8 COLLATE utf8_general_ci

At the moment all of my tables are MyISAM (no support for foreign keys). At some point I plan to change my tables to InnoDB at which point I'll add to this FOREIGN KEY(Game).

When a game starts, obviously, the corresponding row in LobbySettings should be deleted - manually for now, while I've no foreign keys.

Is this normal practice? Is there any reason not to do this?

# ¿ Jul 18, 2009 18:35

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Triple Tech posted:

If Game is your only noun, sounds pretty okay. Do you not have a bit[fixed] or [fixed]boolean data type? Also, might want to salt your password.

There is no Boolean data type in MySQL. You can specify BOOLEAN as the data type for a column, but it is just an alias for TINYINT. (one-byte integer)

The "password" is just a password someone can set in order to restrict access to a given game he has started. He sets a password and then gives it to everyone he wants to be able to join his game. I couldn't be bothered to add password encryption to this feature! Users are notified on the "create new game" screen that if they choose to give their game a password it won't be encrypted. Users have a password to log into their account, which of course IS encrypted and all that jazz.

# ¿ Jul 18, 2009 20:50

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Hammerite posted:

The reason I originally set some of my longer string type columns to TEXT was because I had the impression (I do not know where from) that the maximum size you could give to a VARCHAR column was 255, as in MessageText VARCHAR(255). Reading http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html I see that this impression was erroneous. Do you suggest (say) MessageText VARCHAR(10000) or similar, with the limit being some large number I don't want messages to exceed?

I know this is from a few weeks ago, but I just tried

SELECT MessageText FROM Message PROCEDURE ANALYZE()

and the "Optimal_fieldtype" was given as TEXT NOT NULL. So MySQL seems to reckon it would be best to keep the column as TEXT. Any good reason to disregard its advice? (I have set a limit of 50,000 characters for the column at the application level.)

# ¿ Jul 22, 2009 22:18

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Sweeper posted:

Does anyone if a .dbf file is easily used with a mysql? Either easily converted or whatever. I have one and I need to be able to access it with mysql.

Thanks

Well, I did a Google search for "dbf mysql" and got quite a few results promising ways to convert DBF files to something MySQL can read. So I guess so.

Not being snarky.

# ¿ Aug 16, 2009 00:19

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

I have written a stored procedure that, when passed the ID number of a user, calculates that user's rating (a number that's meant to reflect how good a player that user is, based on how many games he has played and how many he has won) and updates the user's row with the rating. So when I send MySQL the query "CALL CalculateRating(12345)" I am getting it to do something of the form "UPDATE User SET Rating = (result of some subquery) WHERE UserID = 12345".

What I would like to know is, can I write a single query that will do this for each of my users at once? At the moment I have a query "SELECT UserID FROM User", and I loop through the results in PHP and issue a bunch of queries that say "CALL CalculateRating($userid)". Is there SQL syntax to say, "for each UserID in such and such a results set, CALL CalculateRating(UserID)"?

Or could I go even better: could I write a single query that updates each of the rows of User with the value calculated for that user from a subquery involving a "GROUP BY UserID" clause?

# ¿ Sep 2, 2009 00:08

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Carthag posted:

Something like:

UPDATE users u SET rating = (SELECT SUM(s.wins)/SUM(s.games_played) FROM stats s WHERE s.user_fk = u.id);

(change the select to how your actual formula works naturally)

Thanks for this. I didn't realise that it was legal to refer in a subquery to a table that is only part of the outside query.

One thing I wonder: Is MySQL doing this "sensibly" (meaning: it creates the result set you call s only once), or is it doing it the dumb way (creating s over again for each row in u)? I would hope it's doing it properly, as opposed to doing thousands* of redundant calculations, but I've no idea how I'd check.

*if not millions - there are close to a thousand rows in User, so if it creates s r times, where r is the number of rows in User, then I guess it is doing r*(r-1) too many calculations.

Hammerite fucked around with this message at 15:54 on Sep 2, 2009

# ¿ Sep 2, 2009 15:49

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

MoNsTeR posted:

But that result set is different for every row in "u"

It isn't the way I've written it. My s gets the necessary information to return each user's rating, and uses a GROUP BY clause. So s will be the same every time.

Full disclosure - my query looks like this:

code:

UPDATE User SET Rating = (
    SELECT CASE
               WHEN NumPlays IS NULL THEN 0
               WHEN NumPlays = 0 THEN 0
               ELSE ROUND(10000*POW((1-1/NumPlays), 2)*NumWins/NumPlays)
           END AS SubjectUserRating
    FROM (
        SELECT PlayerGameRcd.User,
               SUM(
                   CASE Game.EffectiveNumPlayers
                       WHEN 2 THEN 0.8
                       ELSE 1
                   END
               ) AS NumPlays,
               SUM(
                   CASE
                       WHEN PlayerGameRcd.GameResult = 'Finished 1st' AND
                            Game.EffectiveNumPlayers = 2 THEN 0.8
                       WHEN PlayerGameRcd.GameResult = 'Finished 1st' AND
                            Game.EffectiveNumPlayers > 2 THEN 1
                       WHEN PlayerGameRcd.GameResult = 'Finished 2nd' AND
                            Game.EffectiveNumPlayers = 3 THEN 0.44
                       WHEN PlayerGameRcd.GameResult = 'Finished 2nd' AND
                            Game.EffectiveNumPlayers = 4 THEN 0.63
                       WHEN PlayerGameRcd.GameResult = 'Finished 3rd' AND
                            Game.EffectiveNumPlayers = 4 THEN 0.26
                       ELSE 0
                   END
               ) AS NumWins
        FROM PlayerGameRcd LEFT JOIN Game ON PlayerGameRcd.Game = Game.GameID
        WHERE PlayerGameRcd.GameResult <> 'Game Aborted' AND
              PlayerGameRcd.GameCounts = 1 AND
              Game.Friendly = 0 AND
              TIMESTAMPADD(MONTH, 5, Game.LastMove) > UTC_TIMESTAMP()
        GROUP BY PlayerGameRcd.User
    ) AS SubqueryA
    WHERE UserID = User    *
)

* Table User has a column UserID and table PlayerGameRcd has a corresponding column User which is the column given under GROUP BY.

So my s (called SubqueryA) does not need to be recalculated every time - are you telling me that it will be?

# ¿ Sep 2, 2009 23:01

Adbot: ADBOT LOVES YOU

# ¿ May 17, 2024 18:11

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

MoNsTeR posted:

Yes it will be. When you issue a statement of the form

update <table>
set <column> = <select statement>

That select statement will be run for each row in <table>. So generally when you write this kind of statement, your subquery should be correlated to the rows in the table being updated. If it's significantly slower to do this than to calculate the results for all rows in one shot, then you should do that, and loop through the results issuing an update for each row, rather than trying to issue a single update.

Thanks. Is there a way to do this using a stored procedure? As in: can I write a stored procedure that calculates my SubqueryA and then loops through it as you suggest - or do I need to go back to looping through a result set in PHP?

EDIT: Never mind, I have found out how to do this using MySQL cursors.

Hammerite fucked around with this message at 01:01 on Sep 3, 2009

# ¿ Sep 2, 2009 23:28

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›11 »