I have a MySQL database where users can list books they've read, want to read etc. I'm trying to write a query to find the most common book that users have listed. My current query is: $result = mysql_query("SELECT title, COUNT(title) AS counttitle FROM books GROUP BY title ORDER BY counttitle DESC LIMIT 1"); echo "<p>The ...
I've developed an application that users can use to store lists of their favourite books, books they've read, books they'd like to read, etc., and it's all working fine, but it's just suddenly struck me that users may want to print off their list or download their list to use in another format such as a CSV file to import into a spreadsh...
I am working on a rankings page for a game and am looking to order the rankings first by wins, and then by losses (in case of people having the same number of wins). The following query works fine in order to make a list in order by wins, but I am not sure how to put losses into this query. SELECT username, COUNT(id) AS wins FROM tb...
Is there a difference between the following queries, assuming there is a primary field 'id' in the table (as in speed, etc)? SELECT COUNT(id) FROM table vs. SELECT COUNT(*) FROM table ...
I have a list of dates in a table in a MySQL database (the dates when a charity bookstall is to be held), which I want to display on a page. On one page I'm displaying the date of the next stall, and on another the dates of the stall in the next month. (Currently I'm using an unordered HTML list and selecting the dates with PHP, but it's...
Is there a preferred way? There are currently 1640 rows in the game table to go through and will be adding about 1200 every year. SELECT `date` FROM `game` WHERE `date`<'2009-11-09' ORDER BY `date` DESC LIMIT 1; 0.0004 seconds SELECT MAX(`date`) AS `date` FROM `game` WHERE `date`<'2009-11-09' LIMIT 1; 0.0006 seconds The speeds we...
I think this is an escaping issue or something. When I execute the query and populate all variables, everything is peachy and all row is updated properly in the DB. I looked on StackOverflow to get me rolling with these dynamic/contructed on the fly queries and I'm at the end of my rope. My stuff looks like this: $sql="UPDATE users S...
Hi! I have some relational MySQL database with dozens of tables... My query is 1000 ft long and selects data from almost all the tables... I also have my 'main' table, and all other tables are referring to that one. When I enter one row in my main table and when I create all relations for that row in other tables my query works fine. How...
I have a requirement to store all versions of an entity in a easily indexed way and was wondering if anyone has input on what system to use. Without versioning the system is simply a relational database with a row per, for example, person. If the person's state changes that row is changed to reflect this. With versioning the entry shou...
I'm trying to optimize the following query without success. Any idea where it could be indexed to prevent the temporary table and the filesort? EXPLAIN SELECT SQL_NO_CACHE `groups`.* FROM `groups` INNER JOIN `memberships` ON `groups`.id = `memberships`.group_id WHERE ((`memberships`.user_id = 1) AND (`memberships`.`status_code` = 1 A...
I want to count the number of applications by a student split by month since their first application. Say I have the table structure as follows : Student ApplicationDate ------- --------------- Barry 2009-01-01 Barry 2009-01-20 Barry 2009-01-23 Barry 2009-02-01 Barry 2009-02-15 Barry 2009-03-...
Hello. I'm having a little trouble getting this query to work: $userId = mysql_real_escape_string( $_SESSION['user_id'] ); $userPassProvided = mysql_real_escape_string( $_POST['oldPassword'] ); $query = "SELECT user_id, AES_DECRYPT( user_pass, '".$db_aes_key."' ) AS user_pass "; $query .= "FROM users_tbl WHERE MATCH( user_id, user_pas...
Given the following table structure: CREATE TABLE user ( uid INT(11) auto_increment, name VARCHAR(200), PRIMARY KEY(uid) ); CREATE TABLE user_profile( uid INT(11), address VARCHAR(200), PRIMARY KEY(uid), INDEX(address) ); Which join query is more efficient: #1, SELECT u.name FROM user u INNER JOIN user_profile p ...
If it is how can i query on double and date, I mean there has to be a way to cast them back and them sort them for example. ...
Hi, I was hoping someone could help with a complicated date range SQL query. I need to find a whole bunch of records that could be: 1) within the searched date range 2) outside of the searched range but run through it 3) outside left of the searched range, starting before but ending during it 4) outside right of the searched range, star...
In a certain TABLE, I have a VARTEXT field which includes comma-separated values of country codes. The field is named cc_list. Typical entries look like the following: 'DE,US,IE,GB' 'IT,CA,US,FR,BE' Now given a country code, I want to be able to efficiently find which records include that country. Obviously there's no point in indexi...
Hi there, I've got a large table (~10,000) and I need one column to take up exactly three spaces. It almost always only takes up one space, but I need the other two spaces to be filled in with zeros (it's an integer column). is there a function for that? ...
select userid from cw_users where NOT EXISTS(Select userid from cw_users_data) The previous query won't do the job of course. Trying to find only a missing 'userid' from tableb by doing a comparison. Both tables contain 'userid' and there should be one similar on each. I'm trying to find which one is missing on second table. ...
I've got a script for creating a CSV file from a database table, which works fine except that it outputs all the data in the table, and I need it to output data only for the current logged in user. The app I'm developing is for users to be able to store lists of books they've read, want to read, etc., and I want to be able to allow them ...