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'

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.

fletcher posted:

What does the INNER do? What happens if you take it out?

The INNER JOIN will select all rows from both tables as long as there is a match between the columns you are matching on. If you take it out and just say JOIN, it will default to an INNER JOIN. I may have read what you are trying to do wrong, but maybe you should try a RIGHT JOIN.

noonches fucked around with this message at 14:51 on Nov 21, 2007

# ¿ Nov 21, 2007 14:48

Adbot: ADBOT LOVES YOU

# ¿ Apr 29, 2024 08:29

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.

Since foo is not distinct, it is not part of the result. try a GROUP BY on search and select MAX(timestamp)

# ¿ Dec 8, 2007 05:59

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.

I disagree, I don't think it's wrong, although in my attempt to figure out a good reason to debate your point, I couldn't think of anything that couldn't really just be solved with a MIN, MAX, or GROUP_CONCAT, or a well defined query. So it may be wrong, but it definitely make things easier at to just have MySQL overlook and just give you whichever column it feels like.

# ¿ Dec 8, 2007 06:17

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.

SELECT instructor, max(date) FROM flights WHERE pilot_id = '50' GROUP BY instructor

# ¿ Dec 17, 2007 06:08

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.

It would seem that based on your query, you want multiple rows for one picture, each with a different comment, and get a count on a grouping of comments by the picture ID, if that makes any sense and I'm right, a LEFT JOIN should return one picture with many comments.

# ¿ Dec 28, 2007 22:29

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.

fletcher posted:

Nah I don't want any of the actual comments, just how many there are for that picture id.

But thats what I meant, just one item in the pictures table, multiple times, matched to different rows from the comments table each time, then grouped by the item from the pictures table to get a count of items from the comments table.

So basically your same query with a LEFT JOIN.

# ¿ Dec 28, 2007 22:41

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.

I have a table of survey submission answers set up like this,

ID| answers
--|---------
1 | 1,3,4
2 | 3,4,7
3 | 2
4 | 3,7

where each number in the answers matches up to a question that they said yes to.

I need to pull the count for each time an answer shows up, like 4 shows up twice, 3 shows up 3 times, and so on, I can do a count for each different answer (ie SELECT count(answer1), count(answer2), count(answer3)..), but I can't even get that far, all I can seem to wrap my head around is:

SELECT count(IF(find_in_set(1, answers)),"what goes here?") ....

I am pretty sure this is the way to go but not positive, can anyone help me fix this or suggest something better.

# ¿ Jan 8, 2008 21:08

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.

cletus42o posted:

I think you're better off having a unique record per survey per answer. Then you would only need to do a COUNT(*) WHERE answer_id = 1 to get what you're looking for.

Unfortunately, I did not set up the table or the framework I'm using for this. So I can't change the way the table is set up, and I can't use a PHP script to tear it apart and get the count because I need to feed a single query into a class that spits out a styled html table for the website. Is this as hard as it seems to me, or am I just having an off day?

am I any closer with this?:
SUM(IF(find_in_set(1, answers),1,0))

# ¿ Jan 8, 2008 21:37

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.

cletus42o posted:

I don't have access to MySQL to test it out, but it seems like you could do a COUNT(*) with WHERE find_in_set(1, answers) > 0. I don't know if what you wrote would work, or how it would compare performance-wise.

Thanks for your help, I managed to hack this one out somehow, but through alot of shots in the dark. The SUM works, but for some reason it gave me a row for each question and some numbers in each row that added up to the answer, but that was fixed with a GROUP BY {blank} for some reason, my final query:

SELECT SUM(IF(find_in_set(1, s.answers),1,0)) as count1 ,
SUM(IF(find_in_set(2, s.answers),1,0)) as count2,
SUM(IF(find_in_set(3, s.answers),1,0)) as count3
FROM survey_submissions s
LEFT JOIN survey_questions q
ON q.id IN (s.answers)
WHERE s.answers <> ''
GROUP BY ''

That was awful, and to think if the rear end in a top hat who set up table had done it right, I would have been done long ago. Also, I'm lucky, performance doesn't make a bit of difference for most of the things I do, in this case just display survey submissions (so far a grand total of 30 over the past month) for a mid to low traffic website that an on-site admin will visit once every couple days.

# ¿ Jan 8, 2008 22:56

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.

cletus42o posted:

You shouldn't have to join to your questions table, that's probably why you're having to do the group by.

Without a join there was no way it was working, and honestly, I don't want to mess with something that works, because it's almost 6 and I'm about to leave, but if someone has suggestion on a better/simpler/more elegant way I'd of course like to hear it, but also if I never think about this task again, I'll be happy.

# ¿ Jan 8, 2008 23:59

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.

It seems like you have it set up right, but maybe there's a flaw in your code you are overlooking. What do your while loops and calls to mysql_data_seek() look like.

Also, did you mean each loop, except the first is trying to loop thru empty result sets, or that the result sets are empty except for the first row.

# ¿ Jan 15, 2008 22:17

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.

You could do a SELECT * FROM exampletable ORDER BY id DESC LIMIT 0,2

# ¿ Jan 21, 2008 17:06

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're using php, you can call mysql_insert_id() after the insert.

# ¿ Jan 24, 2008 23:34

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.

Snozzberry Smoothie posted:

Using MySQL, how can I update a table column to make all the data UPPERCASE?

I believe upper() is what you are looking for.

# ¿ Feb 5, 2008 01:52

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.

UltraRed posted:

Why don't I want to store passwords using an md5() or SHA1() hash? What's insecure about them that makes everyone suggest going elsewhere?

They can be cracked, that's what makes them insecure. By adding salt you can make it alot more secure tho.

# ¿ Mar 5, 2008 03:51

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.

Yeah, as far as I know, if you need a hash to generate the same output every time given a specific input, then it can be cracked using lookup tables. Of course salting a hash is not a function of the hash itself to make it more secure, so that kind of means any hash can be cracked given time and effort put into it.

# ¿ Mar 5, 2008 06: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.

Use the SUM() function on the charge and group by the account number.

# ¿ Mar 5, 2008 18: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.

Uziel posted:

That would work if my sample size was all there is, but unfortunately I have a few hundred thousand records and I need it to only show one instance of an account number and the total charges tied to that account number. Simply summing charges and grouping the account number doesn't decrease the amount of data that needs to be looked at.

I have it working via reports, but I need to be able to query the data.

Any ideas?

You only want one account number? Then just filter by that account number. Maybe I don't understand what you need to do.

# ¿ Mar 6, 2008 17: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.

You should certainly created a linked table for the comments with keys pointing to the item and the user who made comments. It's a much better way than trying to create a blob holding all the info.

If you created a text blob, how would you generate a count of comments? Or pull all the comments made by a specific user?

# ¿ Mar 7, 2008 17:46

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

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

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

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

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

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

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.

I'm pretty sure there's a boolean type for column values.

However, I use a tinyint(1) for flags like that, just in case a third option creeps up at a later date.

# ¿ Mar 20, 2008 22:12

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.

functional posted:

DISTINCT keyword

It sounds like what you want can be achieved with a group by statement.

code:

SELECT count(*), avg(t.vote) from (SELECT avg(vote) as vote FROM table GROUP BY user, ip_address) as t

That's kinda rough, but it should pull the average vote from a selection of each users average vote and the number of votes.

noonches fucked around with this message at 00:39 on Mar 26, 2008

# ¿ Mar 26, 2008 00:31

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.

functional posted:

OK, what if I want each person's most recent vote?

I don't think I can just add ORDER BY timed_added DESC LIMIT 1 onto the end of that query you've given me. That would just sort the already DISTINCT items...?

That wouldn't work, and I think what you need would make the query a bit more complicated, maybe not tho, I could be forgetting something simple. I can't put it together in my head at the moment.

Here's a question, why would people be allowed to have more than one vote? Especially if you're not going to use them all like in this example.

# ¿ Mar 26, 2008 01:07

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.

I'm hesitant to post this, because I am almost positive there's a better way, and this is gonna get me scolded in here...

but assuming you have an auto-incrementing field for the votes table, this should work.

code:

SELECT count(*), avg(t.vote) 
   FROM 
     (SELECT avg(vote) AS vote 
        FROM table WHERE id IN
          (SELECT max(id) from table 
              GROUP BY user, ip_address) 
      GROUP BY user, ip_address) as t

# ¿ Mar 26, 2008 03:20

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.

functional posted:

Yes, I'm going to give up doing this in MySQL. It's simply not equipped for the job.

I think you might be making the right choice, but I'm not sure it's MySQL's fault. You'd be better off trying to determine some of this by evaluating it in a script or changing your table structure.

# ¿ Mar 26, 2008 19: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.

You could make a linked table that is an EAV model. Your description sounds pretty much like it falls in the one scenario where you'd want to implement that.

# ¿ Apr 9, 2008 20:47

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.

Maybe I'm lost in the definition somewhere, but isn't that essentially an EAV model?

# ¿ Apr 10, 2008 18:47

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.

The best way, I think, to go about that is make a third table called users_courses or something like that and just have it map keys from the users table to keys from the courses table.

# ¿ Apr 21, 2008 05:05

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.

w_rogers82 posted:

Ok. Maybe it's because it's Monday or because I'm stupid... possibly both. I'm trying to do a select statement with a subquery to pull all rows where the user_id = 114 and either the mgr_status is 'DECLINED' or worker_status is 'COMPLETED'. Here is what I have.

Wait, there seems to be many problems with that query. First off, even if the exists statement was necessary, I don't think you'd need to pull all those fields specifically, a SELECT * should do. I could be missing something, but what do you need the subquery for, it's pulling the exact same table you're comparing already, it seems like it could be accomplished with:

code:

SELECT id,request_submit_date,request_name,request_start_date,request_submitter_id,last_viewed 
FROM REQUESTS 
WHERE request_submitter_id = 114 
    AND (mgr_status = 'DECLINED' 
          OR worker_status = 'COMPLETED');

# ¿ Apr 21, 2008 20:33

Adbot: ADBOT LOVES YOU

# ¿ Apr 29, 2024 08:29

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.

I'd choose #3, because I think it would be the easiest way to relate tags for like posts to eachother, the joins would really not be that bad, and you can still implement your cool cloud of tags. Also, #1 would just end up being a huge mess after a while.

# ¿ Apr 30, 2008 04:50

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