I have the following query: SELECT a.catalogID, d.catalogFileID, e.catalogFileID FROM catalog_topics a LEFT JOIN catalog_files_join b ON a.catalogID = b.foreignKey LEFT JOIN catalog_files_join c ON c.foreignKey = b.catalogFileID LEFT JOIN catalog_files d ON d.catalogFileID = c.catalogFileID LEFT JOIN catalog_files e ON e.catalogFileID ...
I have three tables setup: POSTS, DISCUSSIONS, and COMMENTS. The layout is as follows: POSTS id | user_id | title | body | created DISCUSSIONS id | post_id | user_id | title | body | created COMMENTS id | post_id | discussion_id | user_id | title | body | created A post hasMany DISCUSSIONS, which then hasMany COMMENTS. What I need ...
Just installed snow leopard and Xcode, i downloaded version 5.1.38 from the mysql website and installed it, then i ran sudo gem uninstall mysql and when i try to gem install mysql as below i get a documentation error, also when i try to run mysql by simply typing mysql into terminal i get -bash: mysql: command not found. When i try to co...
I want to write a query to find all the items in my 'schedule_items' table that match a YYYY-MM-DD value of a DATETIME field. something like: SELECT * FROM schedule_items WHERE start ?matches? '$date' Where start would be a DATETIME field with data like 2009-09-23 11:34:00 and $date would be something like 2009-09-23 Can I do this or...
I have a mysql database of entries with dates. So what I want is to show all the dates in my database and then under each date, I want to show all the entries in the database entered on the specefic date. I am thinking of two loops but I don't know how to write the condition to display all the dates in my database before I loop out the e...
Wordpress / Custom MySQL queries I've got a query to return all posts from a specific category: SELECT ID, post_title, post_name, guid, post_date, post_content, guid FROM wp_posts as p INNER JOIN wp_term_relationships AS tr ON (p.ID = tr.object_id AND tr.term_taxonomy_id IN (4) ) INNER JOIN wp_term_taxonomy AS tt ON (tr.term_taxonomy_...
I have a query similar to the following: SELECT users.id FROM users LEFT JOIN sales ON installations.customer = users.id What I would like to say is something like "WHERE count(sales.id) > 4" - meaning that if the user has more than 4 sales assoc with them. I am not sure if I am going about this the wrong way or not though ...
Is it possible to let MySQL LIMIT have an offset of the total number of rows divided in 2, so that the query would look something like this: SELECT * FROM test LIMIT COUNT(*) / 2, 5 Where 5 is just a number. ...
I'm looking at a database diagram generated by MySQL Workbench (actually, DBDesigner4), but I'm not familiar with it so I couldn't figure out some of the symbols it uses for relationships and fields. Does anyone know a good legend? ...
Hello All: I've collected a number of entries in a table for a sweepstakes. I've been asked by the client to provide: The number of unique entries The number of entries that occur twice. The number of entries that occur three times. I'm able to determine the number of unique entries, but not sure how to pull out the number of entri...
I have a mysql database of entries with dates. So what I want is to show all the dates in my database and then under each date, I want to show all the entries in the database entered on the specefic date. I am thinking of two loops but I don't know how to write the condition to display all the dates in my database befo...
When updating a row, I want to have a built-in check to do some bounds checking. Most languages have a MAX() function to return the maximum of the arguments passed, but MySQL seems to use MAX() for something else. For example: UPDATE person SET dollars = MAX(0, dollars-20) WHERE id=1 I want to subtract 20 dollars from person id 1, b...
I have a select in which I do a "group by". One of the columns is an enum, and I want to select the biggest value for each group (i.e the one with the largest index in the enum). I can do select MAX(enum_column+0) as enum_index to get the the largest index in the group, but how can I turn the enum index back to the enum item? Exam...
I have looped data from mysql, and it's a pretty long list. What's the most efficient way to do pagination? Currently, I am looking at this one: http://www.evolt.org/node/19340 Feel free to recommend a better one, thanks ! ...
I have a field in a table which contains bitwise flags. Let's say for the sake of example there are three flags: 4 => read, 2 => write, 1 => execute and the table looks like this*: user_id | file | permissions -----------+--------+--------------- 1 | a.txt | 6 ( <-- 6 = 4 + 2 = read + write) 1 | b.txt | 4 ...
I have a pretty large site and every page is built from several included files, my site is 100% in a procedural format and I am trying to learn to use classes and a more OOP approach in PHP. Currently my site has a header file that is included into every page, in this header is a mysql connection that is made and last the duration of ...
Below is just a mockup of 2 codes in php/mysql. The first one returns a mysql result and run a while loop to iterate the results, the second one does almost the same thing but instead puts the result into a query and then puts it to screen. Now for a more OO approach the second method would be better I think (not sure, i'm learning) how...
I'm using MySQL. I have a table which looks like that: id: primary key content: varchar weight: int What I want to do is randomly select one row from this table, but taking into account the weight. For example, if I have 3 rows: id, content, weight 1, "some content", 60 2, "other content", 40 3, "something", 100 The first row has 3...
i am on half of url shortening system i get the url from user then create code in mysql for that.Then i have to append coming code to my domain name(now i am working on localhost) like http://localhost/a5c3 then redirect it to real domain.I stuck in here.Code snippet would be good for me at least to understand what i am going to do or yo...
Always open in my editor is a tab with a text version of MySQL's manual (~100k lines in a readable layout), which is extremely helpful. The version I have is very old (when 5.0 was still in beta), and this valuable text-file does not exist in mysql.com anymore. Is there a newer version, or some tool that can create it out of the vario...