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'

«‹›303 »

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

Is there a way I can validate a user id actually exists before I do something like relating a row in a different table to it?

Like when I'm going to insert a new photo for user id #5

INSERT INTO photo (filename, caption, owner) VALUES ("pic.jpg", "vacation", 5)

can I put something in the WHERE clause that will cause this query to fail if there is no user with id 5?

# ? Mar 10, 2008 03:14

Adbot: ADBOT LOVES YOU

# ? May 12, 2024 15:30

minato: Jun 7, 2004; cutty cain't hang, say 7-up.; Taco Defender

fletcher posted:

Is there a way I can validate a user id actually exists before I do something like relating a row in a different table to it?

Like when I'm going to insert a new photo for user id #5

INSERT INTO photo (filename, caption, owner) VALUES ("pic.jpg", "vacation", 5)

can I put something in the WHERE clause that will cause this query to fail if there is no user with id 5?

You want to put a "foreign key constraint" between the photos table and users table.

In PostgreSQL it looks something like this:

code:

ALTER TABLE photo ADD CONSTRAINT "user_exists"
FOREIGN KEY owner REFERENCES users (user_id)
ON UPDATE CASCADE
ON DELETE CASCADE;

If you attempt to insert a row into the photo table with an invalid user_id, the DB will throw an error.

The "ON UPDATE CASCADE" bit means that if the user ID gets updated to a different value, the photo table's user_id will be updated too.

The "ON DELETE CASCADE" bit means that if a user ever gets deleted from the user table, their rows in the photo table will be automatically removed too.

# ? Mar 10, 2008 03:27

noonches: Dec 5, 2005; It represents my lifestyle and stratus as a street savy irreverent youth, who lives large, and yet hungers for the next level in life.

Orbis Tertius posted:

code:
SELECT * FROM demographics WHERE IF($age IS NOT NULL, AND age = $age, '')
 IF($gender IS NOT NULL, AND gender = $gender, '')
I know that's way off track, but I hope it shows what I'm trying to do. I'd MUCH rather have a huge SQL statement that gives me an array with all the right entries than have to create all the possible SQL queries in PHP to do the same.

Try using both PHP and SQL together rather than separately.

php:

<?

$sql = "SELECT * FROM demographics WHERE 1=1 ";
if(isset($age)&&$age!='')
   $sql .= " AND age = $age";
if(isset($gender)&&$gender!='')
   $sql .= "AND gender = $gender";

mysql_query($sql);
?>

The "WHERE 1=1" is just there so you can attach either of the other conditions, both, or neither without worrying about whether and AND goes in front.

noonches fucked around with this message at 05:11 on Mar 10, 2008

# ? Mar 10, 2008 03:28

minato: Jun 7, 2004; cutty cain't hang, say 7-up.; Taco Defender

Orbis Tertius posted:

I want to have a form that lets the user search a number of variables that are in several different tables and I want the resulting table to only display columns for the fields that were filled in on the form.

I'm having trouble figuring out how to do a SELECT query that will join only the necessary tables and search only the necessary variables based on what form fields the user fills in.

We have to deal with a similar problem. As the number of tables increases, the idea of having a single SQL statement becomes less appealing because we ended up with a huge illegible SQL statement. So ultimately we just ended up building the SQL on the fly.

This is not quite so simple, because it's necessary to keep track of which tables are required for the JOINs and SELECTs. Here's a very simple example, and it helps that we always use table "a" in the query:

php:

<?
$from_tables = array();
$selects = array();
$filters = array();

if($_REQUEST['some_b_param']) {
  $from_tables[] = "JOIN b USING (a.id = b.a_id)";
  $selects[] = "b.*";
  $filters[] = "b.foo = ".escape_sql($_REQUEST['some_b_param']);
}

if($_REQUEST['some_c_param']) {
  $from_tables[] = "JOIN c USING (a.id = c.a_id)";
  $selects[] = "c.*";
  $filters[] = "c.bar = ".escape_sql($_REQUEST['some_c_param']);
}

$sql = "SELECT a.* ".implode(",", $selects)."
       FROM a ".implode(" ", $from_tables)."
       WHERE true
         AND ".implode(" AND ", $filters);
?>

# ? Mar 10, 2008 03:34

Victor: Jun 18, 2004

Orbis Tertius and noonches, please stop with the table-breaking and SQL injection-vulnerable code.

# ? Mar 10, 2008 05:03

noonches: Dec 5, 2005; It represents my lifestyle and stratus as a street savy irreverent youth, who lives large, and yet hungers for the next level in life.

Victor posted:

Orbis Tertius and noonches, please stop with the table-breaking and SQL injection-vulnerable code.

Hey, I just threw his code in there the way he set it up, if he wants to make his queries vulnerable to injection, that's his problem. Besides, how do you know he didn't already escape the values for $age and $gender?

_{and yeah, the table breaking was my bad}

# ? Mar 10, 2008 05:16

Nibelheim: Jul 7, 2006

Good day,

I need some help on a (relatively) simple SQL query. Here's the situation:

I have one table (Table1) containing an immense ammount of data (think: 100,000+ records). This table has a field called "affected_item" which containt a certain PIN, and random gibberish characters.

I have a second table (Table2) which contains one column, which is a list of PINs.

The goal is to find how records from Table1 contain a pin thats in Table2. Here's my query:

code:

SELECT COUNT(*)
FROM Table1
WHERE exists (SELECT pin from Table2 where Table1.affected_item LIKE '%' + Table2.pin + '%')

It's unfortunaly returning 0. Is my logic faulty?

I've tried the JOIN approach, but to no avail. The problem comes from the fact that Table1's 'affected_item' field has 'RANDOM GIBERISH' + PIN + 'RANDOM GIBERISH'

Thanks !

# ? Mar 10, 2008 20:34

bitprophet: Jul 22, 2004; Taco Defender

Really stupid potential response, what RDBMS are you using, and are you positive that '+' is that database's operator for string concatenation? E.g. MySQL uses double pipes '||' last I checked. I'm not sure what the expected behavior is for using '+' with strings, but it might well be happily trying (and failing) to do addition with them instead, and just not informing you of such.

If this doesn't apply, then all I can say is that a good troubleshooting step is to try individual parts of your code/query before trying the whole. Make sure your sub-select works as expected for good known pin/affected_item values, and only then move up to trying out the WHERE EXISTS type of stuff.

# ? Mar 10, 2008 23:15

Xae: Jan 19, 2005

Nibelheim posted:

Good day,

I need some help on a (relatively) simple SQL query. Here's the situation:

I have one table (Table1) containing an immense ammount of data (think: 100,000+ records). This table has a field called "affected_item" which containt a certain PIN, and random gibberish characters.

I have a second table (Table2) which contains one column, which is a list of PINs.

The goal is to find how records from Table1 contain a pin thats in Table2. Here's my query:
code:
SELECT COUNT(*)
FROM Table1
WHERE exists (SELECT pin from Table2 where Table1.affected_item LIKE '%' + Table2.pin + '%')
It's unfortunaly returning 0. Is my logic faulty?

I've tried the JOIN approach, but to no avail. The problem comes from the fact that Table1's 'affected_item' field has 'RANDOM GIBERISH' + PIN + 'RANDOM GIBERISH'

Thanks !

Using like can kill performance quickly. I would suggest finding a way to extract the PIN from the affected_item column. Play around wiht it a bit and see what performance different methods give you on small samples. Separating the PIN can be done through either by truncating, or built in functions like Oracle's 'TRANSLATE'. If this comes up often you might want to consider going to First Normal Form and placing the PIN in its own column.

# ? Mar 10, 2008 23:40

noonches: Dec 5, 2005; It represents my lifestyle and stratus as a street savy irreverent youth, who lives large, and yet hungers for the next level in life.

bitprophet posted:

Really stupid potential response, what RDBMS are you using, and are you positive that '+' is that database's operator for string concatenation? E.g. MySQL uses double pipes '||' last I checked. I'm not sure what the expected behavior is for using '+' with strings, but it might well be happily trying (and failing) to do addition with them instead, and just not informing you of such.

I wasn't sure about the '+' either, so I tried it on a mysql server. Turns out it works. I tried like queries with "LIKE '%'+somefield+'%'" as well as "LIKE CONCAT('%',somefield,'%')" and both worked. Of course that was what drew my attention first and, since it worked for me, I couldn't see an outright answer. I even have tables set up roughly the way his pin gibberish is, so I tried his whole query with just fieldnames changed, and the only conclusion I could come to was that the pins just did not match anywhere, because everything else seemed to work.

Also, I've actually never seen pipes used for string concatenation in mysql before, news to me.

noonches fucked around with this message at 00:14 on Mar 11, 2008

# ? Mar 11, 2008 00:11

Sergeant Hobo: Jan 7, 2007; Zhu Li, do the thing!

var1ety posted:

Run something like this. You can instantiate it as a procedure for easy reuse.
code:
begin
for row in (select table_name from user_tables)
loop
    execute immediate 'drop table ' || row.table_name;
end loop;
end;

This is from within SQL Plus?

# ? Mar 12, 2008 00:00

chocojosh: Jun 9, 2007; D00D.

noonches posted:

Also, I've actually never seen pipes used for string concatenation in mysql before, news to me.

I believe that's in oracle. I recall seeing | used for concatenation in oracle for my school courses.

# ? Mar 12, 2008 04:12

Orbis Tertius: Feb 13, 2007

Victor posted:

SQL injection-vulnerable code.

Being an SQL novice I've no clue what you're talking about, but it sounds bad; would you mind explaining what in particular about the code makes it injection-vulnerable? I'm pretty much teaching myself all this stuff on a case-by-case basis using the internet when I can't figure something out, and info about vulnerabilities isn't something I've looked into.

noonches posted:

escape the values for $age and $gender

Would you mind explaining this?

# ? Mar 13, 2008 23:00

noonches: Dec 5, 2005; It represents my lifestyle and stratus as a street savy irreverent youth, who lives large, and yet hungers for the next level in life.

mysql_escape_string() or mysql_real_escape_string() to escape characters that might break your sql query.

Injection is a way user input can possibly break your SQL or cause changes to your query. An example being a login, if you sql is set up like "WHERE username='$username'", and injection attack would be something along the lines of entering:

" asdf' OR 1=1 OR a='a"

into the username field which would cause the SQL to be:

" WHERE username = 'asdf' OR 1 = 1 OR a='a' "

which would evaluate to true no matter what.

This is a horrible example, but should give you an idea of what injection is about. Escaping the characters causes their input to be " asdf\' OR 1=1 OR a=\'a " and the SQL should not read their input as statements. So anytime you allow user input into an SQL statement, you should escape the string.

noonches fucked around with this message at 23:20 on Mar 13, 2008

# ? Mar 13, 2008 23:12

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

noonches posted:

mysql_escape_string() or mysql_real_escape_string() to escape characters that might break your sql query.

Isn't using an abstraction layer and prepared statements better to avoid the whole get_magic_quotes_gpc() disaster (even though I think it's removed in PHP6)?

code:

$query = $db->prepare("SELECT username FROM user WHERE id = :id");
$query->bindParam(":id", $_POST['id']);

# ? Mar 13, 2008 23:49

hitze: Aug 28, 2007; Give me a dollar. No, the twenty. This is gonna blow your mind...

I think this is the thread most appropriate for this question, I have a field that has entries like "Song1,Song2,Song3,Song4" etc, and It needs to turn into a list. After trying some black magic I came up with this:

php:

<?php $kablooey = explode(",", $setlist); foreach($kablooey as $tracks): ?>
<li><?=$tracks?></li>
<?php endforeach; ?>

Is this the best way to go about taking my field of comma seperated values and getting them unseperated, or is this going to make all the computers in the world explode 42 times?

# ? Mar 14, 2008 12:37

IsaacNewton: Jun 18, 2005

hitze posted:

Is this the best way to go about taking my field of comma seperated values and getting them unseperated, or is this going to make all the computers in the world explode 42 times?

You might run into problem if you want tracks with ',' in it, it's ok otherwise.

Mind if I ask why the tracks are not entered in their own table, and separate? (Assuming you have an SQL backend)

# ? Mar 14, 2008 15:15

Sergeant Hobo: Jan 7, 2007; Zhu Li, do the thing!

var1ety posted:

Run something like this. You can instantiate it as a procedure for easy reuse.
code:
begin
for row in (select table_name from user_tables)
loop
    execute immediate 'drop table ' || row.table_name;
end loop;
end;

Update: after getting some time to play around with this some more(i.e. not having any assignments due), I tried this out again with a / after end; (as examples on Google show). It now only complains about foreign keys not:

code:

ORA-02449: unique/primary keys in table referenced by foreign keys
ORA-06512: at line 4

I'm not exactly familiar with this logic in SQL (is this what is colloquially known as PL/SQL?) but it would seem to me that the execute line needs to be something like:

code:

execute immediate 'drop table ' || row.table_name || 'cascade constraints /';

but then it gives this error back:

code:

ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at line 4

Did I miss something with adding the cascade constraints command into that line?

# ? Mar 14, 2008 17:26

var1ety: Jul 26, 2004

No forward slash at the end, and a space before the word "cascade". Yes, adding "cascade constraints" will handle foreign keys.

# ? Mar 14, 2008 19:17

hitze: Aug 28, 2007; Give me a dollar. No, the twenty. This is gonna blow your mind...

IsaacNewton posted:

You might run into problem if you want tracks with ',' in it, it's ok otherwise.

Mind if I ask why the tracks are not entered in their own table, and separate? (Assuming you have an SQL backend)

I figured it would be easier if its in the same table as the rest of the information for each set, theres gonna be around 300 of them. I don't know which way is more efficient for the dater-base :downs:

# ? Mar 14, 2008 20:18

noonches: Dec 5, 2005; It represents my lifestyle and stratus as a street savy irreverent youth, who lives large, and yet hungers for the next level in life.

If you have 300 tracks, you really should have a linked table. That way might be justifiable for just a few tracks, tho not really, but having an imploded set of 300 or so means you should really be setting up a linked table. I am not sure about performance differences in the two ways, but I can almost guarantee that if you try to use a table like that in any way other than the very specific way you have it working now, you're gonna find yourself doing alot of code to get your data when you could have just added an appropriate join to the query.

# ? Mar 14, 2008 20:25

IsaacNewton: Jun 18, 2005

hitze posted:

I figured it would be easier if its in the same table as the rest of the information for each set, theres gonna be around 300 of them. I don't know which way is more efficient for the dater-base

The database should be as if not more efficient than whatever code you'll end using to split the string. Use the database the way it's meant to be used (as in, you have a one to many relationship there, so link the tables as such and use an independent query to get the album's tracks)

# ? Mar 14, 2008 20:33

hitze: Aug 28, 2007; Give me a dollar. No, the twenty. This is gonna blow your mind...

So what you're saying is make a new table, and then make a field for each track? or the same one field with commas seperating it? Just to make sure i'm understanding you

# ? Mar 14, 2008 20:44

nbv4: Aug 21, 2002; by Duchess Gummybuns

Whats the easiest way to get a total of a column by calendar months?

For instance, since today is 3-14-2008, I need the total from the last 6 calendar months, so that would be all items from 9-01-2007 to 3-14-2008.

# ? Mar 14, 2008 20:45

IsaacNewton: Jun 18, 2005

hitze posted:

So what you're saying is make a new table, and then make a field for each track? or the same one field with commas seperating it? Just to make sure i'm understanding you

No, you might want to read some sort of SQL primer.

Your album will need an unique identifier (ID) of some sort. From that, you can create the track table, each track having a reference to the unique identifier of the album. That way you can SELECT all the track WHERE the unique identifier is something specific.

# ? Mar 14, 2008 20:49

IsaacNewton: Jun 18, 2005

nbv4 posted:

Whats the easiest way to get a total of a column by calendar months?

For instance, since today is 3-14-2008, I need the total from the last 6 calendar months, so that would be all items from 9-01-2007 to 3-14-2008.

What DBA are you using?

In MySQL, you could GROUP BY year(date), month(date) (e.g. the two fields) example:

code:

SELECT sum(column) FROM sometable WHERE date >= date_add(now(), INTERVAL -6 month) GROUP BY year(date), month(date)

# ? Mar 14, 2008 20:55

nbv4: Aug 21, 2002; by Duchess Gummybuns

IsaacNewton posted:

What DBA are you using?

In MySQL, you could GROUP BY year(date), month(date) (e.g. the two fields) example:
code:
SELECT sum(column) FROM sometable WHERE date >= date_add(now(), INTERVAL -6 month) GROUP BY year(date), month(date)

yeah, I'm using mysql and that query you provided works wonderfully. Is there any way to get it to return it it all on one row, though?

edit:" nevermind, i figured it out. remove the GROUP BY part...

nbv4 fucked around with this message at 00:37 on Mar 15, 2008

# ? Mar 14, 2008 22:58

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

In MySQL is there a way to do a SELECT but have it omit rows where the value in a column is within a certain range of the previous row it returned? Like if I have a table with a timestamp column and I want to return rows that are atleast 10,000 seconds apart or something. Does that make sense? I'm terrible at trying to explain things.

# ? Mar 15, 2008 00:41

npe: Oct 15, 2004

fletcher posted:

In MySQL is there a way to do a SELECT but have it omit rows where the value in a column is within a certain range of the previous row it returned? Like if I have a table with a timestamp column and I want to return rows that are atleast 10,000 seconds apart or something. Does that make sense? I'm terrible at trying to explain things.

What you're looking for is something called an analytic function. Oracle (and I think now certain versions of MS SQL server) have these but to my knowledge MySQL does not. Specifically, Oracle provides SELECT foo, LAG (foo, 1) OVER (ORDER BY blah) FROM ... which will let you access data from a "window" of data. These are sometimes called "windowing" queries.

However, the last time this came up I did find this page which might have a passable way to fake it.

# ? Mar 15, 2008 00:57

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

yaoi prophet posted:

However, the last time this came up I did find this page which might have a passable way to fake it.

Interesting. Thanks for the information. That looks like a pretty messy query. I think I'm just gonna do it with PHP (if $timestamp > $lastTimestamp+x) { do stuff }) when I spit out the data as performance isn't really an issue for the page I need it for.

# ? Mar 15, 2008 02:14

RanmaGuy: Apr 13, 2004; DON'T CLOSE YOUR THREADS IN SH/SC

Can someone help me with this indexing problem (mySQL 5.1)? I can't seem to get this particular query to index (always is using filesort on the thoughts table), and it's bugging the hell out of me so much that the first person who can resolve this for me, I'll paypal $20 to.

code:

SELECT   NM_THOUGHTS.ID,
         NM_THOUGHTS.USER_ID,
         NM_THOUGHTS.CREATED_AT,
         NM_THOUGHTS.UPDATED_AT,
         NM_THOUGHTS.DATE_POSTED,
         NM_THOUGHTS.DATE_COMMENTED,
         NM_THOUGHTS.COMMENTS_COUNT,
         NM_THOUGHTS.EMOTIONS_COUNT,
         NM_THOUGHTS.THOUGHT,
         NM_THOUGHTS.BOX_BG_COLOR,
         NM_THOUGHTS.BOX_BORDER_STYLE,
         NM_THOUGHTS.BOX_BORDER_COLOR,
         NM_THOUGHTS.PRIVACY,
         NM_THOUGHTS.THOUGHT_TYPE,
         NM_THOUGHTS.MODERATION_LEVEL,
         NM_THOUGHTS.FRIEND_FILTER_ID,
         NM_THOUGHTS.GROUP_ID,
         NM_THOUGHTS.MOBILE_POST,
         NM_THOUGHTS.EDITOR_TYPE,
         NM_THOUGHTS.CAN_COMMENT,
         NM_THOUGHTS.ANON_CODE,
         NM_THOUGHTS.IP_ADDR,
         NM_THOUGHTS.IMAGE_EXT,
         NM_THOUGHTS.CREDITS,
         NM_THOUGHTS.CAPTION,
         SF_GUARD_USER.ID,
         SF_GUARD_USER.USERNAME,
         SF_GUARD_USER.ALGORITHM,
         SF_GUARD_USER.SALT,
         SF_GUARD_USER.PASSWORD,
         SF_GUARD_USER.DATE_REGISTERED,
         SF_GUARD_USER.CREATED_AT,
         SF_GUARD_USER.LAST_LOGIN,
         SF_GUARD_USER.IS_ACTIVE,
         SF_GUARD_USER.IS_SUPER_ADMIN,
         SF_GUARD_USER.EMAIL,
         SF_GUARD_USER.AVATAR_FILE,
         SF_GUARD_USER.HIDE_BOX,
         SF_GUARD_USER.HIDE_VISUAL,
         SF_GUARD_USER.VIEW_MODE,
         SF_GUARD_USER.THOUGHT_WIDTH,
         SF_GUARD_USER.COMMENT_LAST_CHECK,
         SF_GUARD_USER.OLD_PASSWORD
FROM     `NM_THOUGHTS`
         LEFT JOIN SF_GUARD_USER
           ON (NM_THOUGHTS.USER_ID = SF_GUARD_USER.ID)
ORDER BY NM_THOUGHTS.DATE_POSTED DESC
LIMIT    75;

From Explain:

code:

id 	select_type 	table 	        type 	possible_keys 	key     key_len	ref 	rows 	Extra
1 	SIMPLE 	        nm_thoughts 	ALL 	NULL 	        NULL 	NULL 	NULL 	57855 	Using filesort
1 	SIMPLE 	        sf_guard_user 	eq_ref 	PRIMARY 	PRIMARY 4 	notemine.nm_thoughts.user_id

Schema:

code:

CREATE TABLE IF NOT EXISTS `nm_thoughts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `date_posted` int(13) unsigned DEFAULT '0',
  `date_commented` int(13) unsigned DEFAULT '0',
  `comments_count` int(11) DEFAULT '0',
  `emotions_count` int(11) DEFAULT '0',
  `thought` text,
  `box_bg_color` varchar(12) DEFAULT NULL,
  `box_border_style` varchar(12) DEFAULT NULL,
  `box_border_color` varchar(12) DEFAULT NULL,
  `privacy` int(11) DEFAULT '0',
  `thought_type` smallint(6) DEFAULT '0',
  `moderation_level` int(11) DEFAULT '0',
  `friend_filter_id` int(11) DEFAULT '0',
  `group_id` int(11) DEFAULT '0',
  `mobile_post` int(11) DEFAULT '0',
  `editor_type` smallint(6) DEFAULT '0',
  `anon_code` varchar(10) DEFAULT NULL,
  `ip_addr` varchar(32) DEFAULT NULL,
  `can_comment` smallint(2) unsigned DEFAULT '0',
  `image_ext` varchar(30) DEFAULT NULL,
  `credits` text,
  `caption` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `userdp` (`moderation_level`,`user_id`,`date_posted`) USING BTREE,
  KEY `user_id` (`user_id`) USING BTREE,
  KEY `date_commented` (`date_commented`) USING BTREE,
  KEY `comments_count` (`comments_count`) USING BTREE,
  KEY `date_posted` (`date_posted`) USING BTREE,
  FULLTEXT KEY `thought` (`thought`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 PACK_KEYS=1 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=257517 ;

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

--
-- Table structure for table `sf_guard_user`
--

CREATE TABLE IF NOT EXISTS `sf_guard_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(128) NOT NULL,
  `algorithm` varchar(128) NOT NULL DEFAULT 'sha1',
  `salt` varchar(128) NOT NULL,
  `password` varchar(128) NOT NULL,
  `date_registered` int(11) DEFAULT '0',
  `created_at` datetime DEFAULT NULL,
  `last_login` datetime DEFAULT NULL,
  `is_active` tinyint(4) NOT NULL DEFAULT '1',
  `is_super_admin` tinyint(4) NOT NULL DEFAULT '0',
  `email` varchar(128) NOT NULL,
  `avatar_file` varchar(255) DEFAULT NULL,
  `comment_last_check` int(11) DEFAULT '0',
  `old_password` varchar(255) DEFAULT NULL,
  `hide_box` tinyint(4) unsigned DEFAULT '0',
  `hide_visual` tinyint(4) unsigned DEFAULT '0',
  `thought_width` tinyint(4) unsigned DEFAULT '0',
  `view_mode` tinyint(4) unsigned DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `sf_guard_user_email_unique` (`email`),
  UNIQUE KEY `sf_guard_user_username_unique` (`username`) USING BTREE
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 PACK_KEYS=1 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=8467 ;

I've tried many different index combinations on both the user and thoughts table without any real luck, and also have tried shortening the query to just SELECT nm_thoughts.* FROM .. (with table join to users) without much success.

# ? Mar 15, 2008 08:31

Lt Moose: Aug 8, 2007; moose

Lt Moose fucked around with this message at 05:37 on Apr 4, 2016

# ? Mar 15, 2008 19:37

Sergeant Hobo: Jan 7, 2007; Zhu Li, do the thing!

var1ety posted:

No forward slash at the end, and a space before the word "cascade". Yes, adding "cascade constraints" will handle foreign keys.

Excellent, this worked very nicely. Thanks for the help.

# ? Mar 15, 2008 20:19

noonches: Dec 5, 2005; It represents my lifestyle and stratus as a street savy irreverent youth, who lives large, and yet hungers for the next level in life.

Lt Moose posted:

The first one is this line isn't working. It does not error, but it does not update the row.
code:
$dbcnx = @mysql_connect('site', 'user', 'pass');

The @ sign should be suppressing any errors, if I remember correctly.

Edit: also, sanitize your input.

noonches fucked around with this message at 20:49 on Mar 15, 2008

# ? Mar 15, 2008 20:36

nbv4: Aug 21, 2002; by Duchess Gummybuns

I have a SQL problem that I can't even figure out where to begin. I need to get the date of the third from last occurance of a record. Say this is the table:

code:

date       |    items
--------------------------
3-11-2008  |     2
3-12-2008  |     0
3-13-2008  |     1
3-14-2008  |     1
3-15-2008  |     2

I want 3-14-2008 to be returned because thats where the third from last occurrence of the item was. If 0 items existed on 3-15-2008, 3-11-2008 would be returned. And if 3 were on 3-15-2008, then 3-15-2008 would be returned.

# ? Mar 17, 2008 07:34

minato: Jun 7, 2004; cutty cain't hang, say 7-up.; Taco Defender

nbv4 posted:

I have a SQL problem that I can't even figure out where to begin. I need to get the date of the third from last occurance of a record. Say this is the table:

Firstly, generate a SQL statement that shows the date, the items, and the sum of all items up after and including that date. (I've used ID as the date in this example)

code:

SELECT id, items, (SELECT SUM(items)
                   FROM item AS item2
                   WHERE item2.id >= item.id) AS total
FROM item
ORDER BY item.id;

 id | items | total
----+-------+-------
  1 |     2 |     6
  2 |     0 |     4
  3 |     1 |     4
  4 |     1 |     3
  5 |     2 |     2

So you could add some restrictions to that clause to give you the one where the total is '3', and there's your 3rd to last record.

But that won't work in all cases: for example if you wanted the 5th to last record (ID 1 in my example), you'll get nothing back because there's no total with id 5. What you want is the row where the total is either the position you want, or failing that, the the next number after that. So let's tweak the SQL again:

code:

SELECT id, items
FROM item
WHERE (SELECT SUM(items)
                   FROM item AS item2
                   WHERE item2.id >= item.id) >= 3
ORDER BY total
LIMIT 1

Bingo.

# ? Mar 17, 2008 08:11

nbv4: Aug 21, 2002; by Duchess Gummybuns

minato posted:

Bingo.

so if "item" is "approaches", and the name of the table is "flights" would the query look like this:

code:

SELECT `date`, `approaches`
FROM flights
WHERE (SELECT SUM(approaches)
                   FROM flights AS flights2
                   WHERE flights2.date >= flight.date) >= 3
ORDER BY total
LIMIT 1

...because its giving me a weird result where the date doesn't even contain an instance of 'approaches'.

# ? Mar 17, 2008 08:30

minato: Jun 7, 2004; cutty cain't hang, say 7-up.; Taco Defender

You might want to order by total, then by date, to avoid cases where there are some dates with 0 approaches.

# ? Mar 17, 2008 08:33

Charun: Feb 8, 2003

I have a date related problem.
There is table of downtime ranges, they are DateTime -
i.e.

code:

ID    DateDown     	DateUp
1     2008-01-31 13:00  2008-02-01 04:00
2     2008-01-30 08:00  2008-01-30 11:00

I need to create a report that'll divide this up into hours per month -
(based on the table above)

code:

Jan2008 14
Feb2008 4

The span over the month boundary is giving me headaches. Some may even span multiple months. The only way I can think of doing this dividing it up in to 4 cases, then adding them together. -
(quick pseudocode)

code:

	if (datedown.month == dateup.month == a.month)
		datediff dateup datedown
	if (dateup.month > a.month)
		datediff datedown endofa
	if (datedown.month < a.month)
		datediff beginningofa dateup
	if ((datedown.month < a.month) && (dateup.month > a.month))
		a.hoursinmonth

Is there any better ways? Perferably somehow in mysql? Maybe creating temporary tables or something?

# ? Mar 17, 2008 08:35

Adbot: ADBOT LOVES YOU

# ? May 12, 2024 15:30

Lancer383: Aug 15, 2007; Say what you want about the tenants of national socialism, Dude, at least it was an ethos.

Charun posted:

I have a date related problem.
There is table of downtime ranges, they are...
<snip>
Is there any better ways? Preferably somehow in mysql? Maybe creating temporary tables or something?

I think this will work, but it's just off the top of my head:

code:

select		DATE_FORMAT(dateDown, '%Y-%m') YearMonth
		,sum((unix_timestamp(dateUp) - unix_timestamp(dateDown)) / 3600) as HoursDown
from		table
group by	DATE_FORMAT(dateDown, '%Y-%m');

# ? Mar 17, 2008 13:04

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

«‹›303 »