Hello, I'm trying to get a pick from my DB that would last for a day (daily pick). I use the following code: $query = 'SELECT * FROM table ORDER BY rand() LIMIT 1 But as you can see it only gives me a random pick from the table, and every time I refresh the page it gets me a new random pick. How can I make the pick to last for a whole...
Hi, I have a query which contains a subquery which is something like this IFNULL((SELECT id FROM mytable WHERE id = 1), 'No') It works OK, but I would like to return the string 'Yes' on success rather than the actual id of the row. Is there any way to override the value it returns? Thanks. ...
This shows me all the first names and last names that have exactly two entries that are identical SELECT `firstname`,`lastname`,COUNT(*) AS Count FROM `people` GROUP BY `firstname`,`lastname` HAVING Count = 2 How do I turn this into a DELETE FROM WHERE statement with a LIMIT to only remove one of each of the entries and leave the o...
Given the following models: class Room < ActiveRecord::Base has_many :contracts end class Contracts < ActiveRecord::Base # columns # start_date Date # end_date Date belongs_to :room end The contracts of one room are not overlapping. My question is, how i am be able to find gaps between contracts. An Example: room = Room...
Hi, I am trying to deploy a solution on a Mono Hosting Service, and I would like to use the MySql connector... The problem is that I can't use the MySql connector, it says: Could not load file or assembly 'MySql.Data, Version=6.2.2.0, Culture=neutral, PublicKeyToken=C5687FC88969C44D' or one of its dependencies. The system cannot find ...
I'm having a problem on a master MySQL (5.0, Linux) server: I tried to add a comment to a table row, which translates into an ALTER TABLE command. Now the process is stuck on 'copy to tmp table', copying the 100'000'000+ rows. Disk IO usage is uncomfortably high. Since the master is using replication, I'm unsure if I can kill this proce...
Firstly, many parts of the solr wiki is not very useful for somebody who just learned how to index and search fields. It seems it is written for experts! It uses terms that is RELATIVE to solr, so it's very difficult to understand it without reading it several times over. NOTE: I have a classifieds website where the most recent ads alwa...
I have the following MySQL query: SELECT SQL_CALC_FOUND_ROWS p.* FROM product AS p LEFT JOIN productCategory AS c ON FIND_IN_SET(c.id, REPLACE(TRIM(p.categories), ' ',',')) WHERE ( c.id IS NULL OR c.status = 'D' OR p.categories IS NULL OR TRIM(p.categories) = '' ) AND p.deprecated = 'N' LIMIT 0,30 This query is to pick...
There is a query I've just made SELECT * FROM users INNER JOIN ratings ON ratings.rateable=users.id ORDER BY SUM(ratings.rating)/COUNT(ratings.rating) But, it doesn't work, I just get one person in result, although there are 3 people in ratings table! I'm using php 5. I think sum(), count() doesn't work at all! Please,...
is its somehow possible to do a select for empty strings and NULL values in Mysql without using or? this: select * from table where col IN (null, ""); doesnt work, it ignores the null (or possibly matches it with the string 'null' thanks, P.V. Goddijn ...
I am using MySQL (MyISAM) 5.0.41 and I have this query: SELECT `x`.`items`.id, `x`.`items`.name, COUNT(*) AS count FROM `x`.`items` INNER JOIN `x`.`user_items` ON `x`.`items`.id = `x`.`user_items`.item_id GROUP BY name HAVING count > 2 ORDER BY count DESC I have about 36,000 users, 175,000 user_items and 60,000 items which...
Hi, I've got a dataset of city names with their corresponding latitudes/longitudes that I've loaded into a MySQL table, like so: city_id | city_name | latitude DECIMAL(9,6) | longitude DECIMAL(9,6) Typical latitude/longitude coordinates might look like this: 54.284758 / 32.484736. However, I'm only getting values with a scale of 2 to...
Hello, For a scheduling system, what's the best way to save the timezone of client/event in a central server database coming from multiple sources mobile,web,client app. How do you store the dates? Alarms, reminders etc... How do you handle the DST setting? How do you handle the events if client has traveled to a different locat...
I have a simple array in PHP, say [1, 2, 4, 5, 6, 7] and I wish to query a MYSQL database [1, who] [2, where] [3, some] [6, there] [9, too] I only wish to receive the rows of intersection between the PHP array and the database's indices column. So this would result in [1, who] [2, where] [6, there] Any ideas? Thanks! ...
Whats this?? my query is: Doctrine_Query::create() ->select('rec.*') ->from('Records rec') ->execute(); Records table contains more than 20k rows. On executing query on page i am getting an error: Fatal error: Maximum execution time of 60 seconds exceeded in...
I am having a table in which there is a column in which various values are stored.i want to retrieve unique values from that table using dql. Doctrine_Query::create() ->select('rec.school') ->from('Records rec') ->where("rec.city='$city' ") ...
Quick and simple (I hope) question; If a user inputs new lines in a text field, ie: 43 Dennis Beeston How can I save the new line rather than have it transferred to the mysql server as one line?! I am using PHP and mysql. Many thanks ...
Best way to create on the fly, date ranges, for use with report. So I can avoid empty rows on my report if there's no activity for a given day. Mostly to avoid this issue: http://stackoverflow.com/questions/75752/what-is-the-most-straightforward-way-to-pad-empty-dates-in-sql-results-on-either ...
I have the following data being returned from my database query: +---------------+-----------+------------------+--------------+-------+ | district_name | school_id | school_name | section | score | +---------------+-----------+------------------+--------------+-------+ | My ISD | 11 | My First School | English...
As part of trying out django CMS (http://www.django-cms.org/), I'm struggling with getting Python-MySQLdb to work (http://pypi.python.org/pypi/MySQL-python/). I have installed Django CMS and all of its dependencies (Python 2.5, Django, django-south, MySQL server) I'm trying out the example code within Django CMS code with MySQL as chos...