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'

«‹›4 »

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

Is there any reason to use PDO for my abstraction layer if I'm using mysql and will always use mysql for this app?

# ¿ Nov 9, 2007 04:17

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 20:23

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

Let's say I've got a simple users table like

code:

id   name
----------------------------
1    bob
2    joe
3    frank

and a messages table like

code:

id   recipient    author    message
---------------------------------------
1    1            2         hello bob its joe
2    3            1         hey frank its bob

And I want a query that will output

code:

recipient    author    message
----------------------------
bob          joe       hello bob its joe
frank        bob       hey frank its bob

I can get one of the user id columns to return the name using this query, but how do I get both of the user id columns to be returned as the users name? Am I doing this completely the wrong way? Is this query the same as doing a JOIN?

SELECT users.name, messages.to, messages.message FROM messages, users WHERE messages.author = users.id

I'm running MYSQL5.

# ¿ Nov 21, 2007 11:12

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

Bonus posted:

My SQL is a little rusty but here's a shot at it

code:

SELECT recipient.name, author.name, messages.message
FROM users AS recipient
INNER JOIN messages ON messages.recipient = recipient.id
INNER JOIN users AS author ON author.id = messages.author
WHERE recipient.id = [the id of the guy who is looking at his messages]

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

# ¿ Nov 21, 2007 12:00

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

How taxing is something like a SELECT COUNT(id) FROM messages WHERE owner = me if there are hundreds of thousands of entries in the messages table? Is it better to just keep a count in the users table and update that?

fletcher fucked around with this message at 23:19 on Nov 23, 2007

# ¿ Nov 23, 2007 23:11

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

Similar to the yay/nay voting, if you are trying to track who has voted on what, where each user is only allowed to vote once on each item being voted on, is it a bad move to just make a 1 column table with the primary key is a concat of userId_contentId? Or should you split it up to 3 columns like id, userId, contentId? What yields better performance?

# ¿ Dec 3, 2007 22:45

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

How do I use the mysql EXPLAIN to my advantage?

# ¿ Dec 4, 2007 20:06

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

Bonus posted:

You can see how efficient your query is. How much records needed to be traversed to get the results of the query. For instance, you almost never want to do full table scans. It's good to check your queries with explain to see if full table scans are happening at certain queries so you can introduce indices or reform the queries.

So how do I read and interpret the results of the EXPLAIN to do this?

# ¿ Dec 4, 2007 20:17

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

If you have a mysql database of hundreds of thousands of articles what is the best way to pull up the top 100 articles ordered by number of views?

I'm guessing something like this is pretty taxing on the db server since it does a full table scan?

SELECT id, title FROM articles ORDER BY views DESC LIMIT 100

How do you get around doing this? A temporary table that gets updated via a cron job or something?

# ¿ Dec 12, 2007 04:56

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

Victor posted:

Not if you index the views column. Now, depending on how often the number of views is updated, this index might be a performance problem. Try it out!

So does that mean the views column shouldn't be updated every time a unique user views the article?

I guess this also means that I don't really understand what an indexed column is. Is there an article that will help me out?

# ¿ Dec 12, 2007 09:34

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

Tap posted:

I'm wondering if there's a better way to use mysql console in windows. The command prompt window consistently screws up my output of tables because I can't widen the window (unless I'm a dumbass and don't realize there's a way to widen the window).

Edit: Nevermind, I am a dumbass.

Right click -> properties -> layout tab -> Window Size

The mysql admin client for windows on their website is pretty decent. There's also a replacement console for windows (called console2 I think, I don't have it on the machine I am on right now) which is pretty cool, and allows for tabbed console windows and stuff. I am pretty sure it is also resizeable.

edit: added links

http://dev.mysql.com/downloads/gui-tools/5.0.html

http://sourceforge.net/projects/console/

I do prefer phpMyAdmin over the other choices though

fletcher fucked around with this message at 10:08 on Dec 16, 2007

# ¿ Dec 16, 2007 08:04

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

PDO/MySQL question:

If I'm executing the following query: SELECT id, name, detail FROM user WHERE detail = :filter LIMIT 0, 25 is there an easy way to get the total number of results or know if there are more than 25 for pagination without running the query twice, one time without the LIMIT portion and just doing a SELECT COUNT(id) FROM user WHERE detail = :filter?

# ¿ Dec 21, 2007 09:48

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

Alex007 posted:

Meet your new friend SQL_CALC_FOUND_ROWS !!!

http://www.arraystudio.com/as-workshop/mysql-get-total-number-of-rows-when-using-limit.html

Is there an easy way to use this with PDO?

# ¿ Dec 21, 2007 21:10

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

Alex007 posted:

Meet your new friend SQL_CALC_FOUND_ROWS !!!

http://www.arraystudio.com/as-workshop/mysql-get-total-number-of-rows-when-using-limit.html

Hmm, I tried this, but every time I do a SELECT FOUND_ROWS() it always comes back with 1, even though the query where I put SQL_CALC_FOUND_ROWS into returns > 1 rows

# ¿ Dec 27, 2007 23:10

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

Land Mime posted:

If your not already, try using the command line client instead of something like mysql query browser.

Works fine from the command line. Returns 1 when trying to do it with PDO and PHP.

# ¿ Dec 28, 2007 00:33

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

code:

SELECT	COUNT(comment.id),
		picture.id
FROM	picture
JOIN	comment ON 
		picture.id = comment.pictureId 
WHERE	picture.owner = :id
GROUP BY	comment.pictureId

Why is this not returning the correct count? It's close, but still a few off for each picture. Am I grouping by the wrong column?

# ¿ Dec 28, 2007 22:23

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

m5 posted:

What happens when you group on "picture.id"? (What DBMS is that?)

Returns the same thing. I'm using MySQL5

noonches posted:

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.

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

# ¿ Dec 28, 2007 22:37

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

Victor posted:

fletcher, unless I'm blind, that code should work. Perhaps you could provide code for creating the picture and comment tables with enough sample data to demonstrate the problem?

Ahhh I'm retarded, my data got hosed up when I loaded it. The query works fine. I suck.

# ¿ Dec 28, 2007 23:22

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

Can somebody post an example of using sql_calc_found_rows with PDO? I still can't get mine to work. It always returns 1.

# ¿ Jan 3, 2008 02:53

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

Do you guys usually name your tables with the singular or plural form of the object?

# ¿ Jan 25, 2008 01:08

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

Thanks Felonious Drunk.

Another question about using PHP/PDO: If my $dbh->prepare() statement fails, how do I get the information about the error? I'm having a hard time finding this with google.

edit: bah found it 2 seconds after I post this, of course.

It's:

code:

$stmt = $dbh->prepare("SELECT id FROM table");
if (!stmt)
 print_r($dbh->errorInfo());

edit2: Another PDO question now that I have figured out the error. I want to iterate over every row of a table and update information in another table, like this:

code:

$stmt = $dbh->query("SELECT id, thisfield FROM table1");
while ($row = $stmt->fetch()) {
	$substmt = $dbh->prepare("UPDATE table2 SET thatid = :id WHERE this = :that");
}

but I run into this error:

quote:

Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.

The table is massive, I think fetchAll would be a bad idea. I'd like my code to be more portable so I want to avoid the mysql specific way. What do I do?

fletcher fucked around with this message at 02:13 on Jan 25, 2008

# ¿ Jan 25, 2008 01:45

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

Nobody knows the answer to my question?

# ¿ Jan 25, 2008 18:42

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

kalleboo posted:

Most databases don't let you run queries at the same time, mysql is the exception. Either store the queries you want to run in an array and run them after you've closed the query, or open a second database connection.

The problem is that I need the data from the first query in order to execute the second query. I upped the allowed memory size in php to 256MB and it let me do a fetchAll(), I was just trying to avoid doing that though.

# ¿ Jan 25, 2008 23:50

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

kalleboo posted:

You can store the queries you want to run using the data from the first query in an array, and when the query-building is one, run them after you've closed the query. If there are too many queries to do that, you can open a second database connection to do the insertions. I've used both these methods in C# when I had to work with MSSQL.

Ahhh I didn't think about storing the second queries, that makes sense. I will try this!

# ¿ Jan 26, 2008 00:09

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

brae posted:

Is the row you want to update in the second table (with the value from the first table) selected by a different column in the first table? I mean a variation on:
code:
UPDATE table2, table1 SET table2.thatid = table1.id WHERE table2.this=table1.thisfield;

I didn't know you could do that! thank you, it was exactly what I needed

# ¿ Jan 27, 2008 09:20

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

SuckerPunched posted:

I've never really dealt with parameterized queries in PHP, and Google is telling me that it's only available through the MySQLI classes - which don't really help me with MSSQL. I haven't dealt with MSSQL in years and even then it was in Classic ASP so I'm a bit lost

You can use prepared statements in PHP with PDO, which has a MSSQL driver for it. I've only used it for MySQL but I assume it's used the same way.

# ¿ Jan 28, 2008 22:27

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

I understand the idea of separating logic in a model-view-control system, but I don't understand the concept of separating the SQL with a templating system. I'm working in PHP, where can I find more info about how and why to do it?

# ¿ Jan 30, 2008 00:28

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

minato posted:

What do you mean by SQL templates exactly?

Just separating SQL from the rest of my code. Somebody mentioned that it's important and helpful to do that, and I just wasn't sure why or how you do it.

# ¿ Jan 30, 2008 01:52

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

Lets say you have products in one table and votes for the products in another table. How do you get the products and total votes for each product?

# ¿ Feb 4, 2008 05:31

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

PHP/PDO question: If I have a table with an auto-incrementing id field, what is the fastest way to get the id of a new row I just throw in? Just do a SELECT? Or is there some shortcut?

edit: Another question - This query only returns articles that have comments, how do I get it to return the ones that don't have any comments? Looking at the joins tutorial it looks like LEFT OUTER JOIN is what I want, but the query returns the same thing when I try it.

code:

SELECT 
	COUNT(comment.id) AS numComments, 
	article.id, 
	FROM article 
		JOIN comment ON article.id = comment.articleId 
	GROUP BY comment.articleId

fletcher fucked around with this message at 01:29 on Feb 27, 2008

# ¿ Feb 26, 2008 22:05

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

Xae posted:

Ghetto Method:
code:
    SELECT  article.id
    FROM    article
    WHERE   article.id NOT IN (SELECT comment.articleID FROM comment)
For the first question: What RDBMS are you using?

I'm running MySQL 5.

Whoops, I phrased my question a little wrong. I want it to return articles with comments AND articles without comments in the same query, but count the # of comments if it does have any.

edit: specifying LEFT or RIGHT join gives me the same results

# ¿ Feb 27, 2008 02:03

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

brae posted:

http://www.php.net/manual/en/function.PDO-lastInsertId.php

Or SELECT LAST_INSERT_ID() if you want to do it in SQL (you said you were using MySQL 5).

EDIT: Does this do what you want? It's a lot like your query above, but using an outer join, which you were right to think you needed to use.
code:
<snip>

Awesome, this worked great, thanks! And thank you to everybody else who answered as well.

# ¿ Feb 27, 2008 09:10

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

bitprophet posted:

You don't understand: a blatant disregard for doing things correctly, coupled with being way too lenient about accepted input, is what makes MySQL so easy and is the key to its popularity. And remember, popular = good!

What's a better free alternative?

# ¿ Feb 29, 2008 20:25

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

brae posted:

Postgres. Many people additionally consider it to be "freer" (speech-wise, not beer-wise) than MySQL since it has BSD licensing and because its development isn't controlled by a single, private corporation.

What makes it better? Why should I consider switching to it?

# ¿ Feb 29, 2008 20:47

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

var1ety posted:

Oracle 10G XE is free, with the following limitations:

4GB user data
1GB RAM
1 CPU

http://www.oracle.com/technology/products/database/xe/index.html

Interesting. Since my app is written with PDO, and they have an Oracle driver, does that mean I could drop this in a try it pretty easily? Or trying out postgres?

fletcher fucked around with this message at 00:18 on Mar 1, 2008

# ¿ Mar 1, 2008 00:03

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

Great info about postgres guys, thanks. If I was going to try something new should I go postgres or oracle 10g? I don't think I will be going beyond there computer limitations any time soon.

# ¿ Mar 3, 2008 06:02

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

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

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

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

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 20:23

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

In MySQL let's say you have two tables, users and articles, and articles have a column keeping track of the number of hits. I want to get a list of users and the total number of hits that articles they own have gotten, ordered by number of hits descending.

Something like:

SELECT user.username, SUM(article.hits) AS totalHits FROM user JOIN article ON article.owner = user.id GROUP BY article.id ORDER BY totalHits DESC LIMIT 20

What column am I supposed to be grouping by? article.id seems to work alright, but I don't really understand why. This query is also very slow, is there any way to speed it up aside from keeping a totalHits count in my user table?

# ¿ Mar 20, 2008 21:22

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

«‹›4 »