I need to count the number of days that fall completely or partially within a set of time ranges. The ranges do not overlap but there may be many in the same day. For instance, given: +---------------------+---------------------+ | time_start | time_end | +---------------------+---------------------+...
Hello I'm uploading image file to storage server. Before uploading I should compose filename, which contains AUTOINCREMENT VALUE in it (for example, 12345_filename.jpg). How could I get autoincrement value before inserting into DB? I see only one solution insert empty row get it's autoincrement value delete this row insert row with ...
I am using MySQL fulltext and PHP (codeigniter) to search a database containing RSS items. Problem is some of these items's titles use underscores instead of spaces. Since MySQL considers underscores as part of a word, these items will never be matched in the search, unless the user types the exact title including underscores. Server is...
I have a database, with several tables. The "main" table, called contacts, stores information about contacts, each having an id, a name etc... I also have like, 20 other tables, each representing a certain function, like staff, media ... Each of these function-tables, references the contact table, with a foreign key. I've recently ad...
Hello, I am still having trouble with my mysql server. It seems that since i optimize it, the tables were growing and now sometimes is very slow again. I have no idea of how to optimize more. mySQL server has 48GB of RAM and mysqld is using about 8, most of the tables are innoDB. Site has about 2000 users online. I also run explain on...
Hi, I took over a database with two tables, lets name them entries and comments. The entries table contains a column named comment_count which holds the amount of rows with entry_id in comments corresponding to that row in entries. Lately this connection has become terribly out of sync due to version switching of the codebase. I need he...
Hi - I have a website and want to display search results dynamically meaning that as the user interacts with controls and selects options, the search results are populated in realtime - i.e. the user doesnt need to click the search button. The data is stored in a MySQL relational data base. Now I know this is likely to lead to a large ...
I am trying to run a nested query but I am getting this error, #1241 - Operand should contain 1 column(s) this is the query that I am trying to run, SELECT * FROM `categoryTable` WHERE `categoryId` NOT IN ( SELECT `categoryTable`.`categoryId` , `categoryTable`.`categoryTitle` , `userMenuTable`.`menuEntryId` FROM ( `categoryTable` ) L...
I have 10 application servers and 1 mysql database. I would like to do connection pooling for mysql, but simply enabling mysql_pconnect() will not work since it'll cause some servers to have more connections than others. What's the best solution for this scenario..? ...
I recently saw someone post this as part of an answer to an SO query question: SELECT DISTINCT a, b, c FROM t1 WHERE (a,b,c) NOT IN ( SELECT DISTINCT a,b,c FROM t2 ) I'm a bit confused, as I always thought that you can't use multiple columns for "NOT IN" ("where(a,b,c)", etc.). Is this correct SQL syntax? And how about MySQL? ...
I have a hefty db server with lots of very similar InnoDB databases. A query that I run often simply updates a timestamp on one row in a small table. This takes like 1-2 ms most of the time. Occasionally, at night, probably while backups and maatkit replication tools are running, one or more of these queries may show "Updating" for se...
Hello, I am currently trying to build a navigation that is semi built by the user, basically the user has a primary navigation in which if they click on a category name a block is created on the page for that category. If they click the same link again the block is removed from the page, the website is set up to remember which blocks the...
We're currently using a 3rd party API that provides datetime in the following format: Sat Mar 06 09:00:00 ICST 2010 Fri Feb 19 19:30:00 JDT 2010 Fri Feb 19 19:30:00 PST 2010 However, we want to store these datetime objects in MySQL in a standard datetime field which requires the following format: YYYY-MM-DD HH:MM:SS Right now we're...
i am sending an email to 1000 people. i have a php send email. it works fine. i was wondering, in my query, how can i send it 100 at time. is it like this: select * from test limit 0, 100 select * from test limit 100, 100 select * from test limit 200, 100 select * from test limit 300, 100 etc... thanks. ...
Hi all, I'm working on a searchengine within a website where a visitor can provide some keywords and then the searchengine performs some queries over a datatable. I know how to query a table, but how can you build a single query which provides a full resultlist and per matching row in the datatable the number of occurences. So say, i ...
Im using the symfony framework with mysql. I have a field in mysql that is generated by doctrine as: weight: { type: double, notnull: false, default: NULL } `weight` double(18,2) NULL DEFAULT NULL The value is entered using a textbox and the generated sql trys to insert '' into this field if no value is given. This produ...
Hello, I have a mysql query, that does the following, SELECT * FROM categoryTable LEFT JOIN userMenuTable ON userMenuTable.categoryId = categoryTable.categoryId this returns only results that match this condition ON userMenuTable.categoryId = categoryTable.categoryId I was hoping it would be possible to pull all the results and ...
I am getting the dreaded PHP error Parse error: syntax error, unexpected T_STRING in /path/to/the/file in line 14 Here is the code: function getSomeList($userid) { $query = "SELECT list_id, username_id FROM user_list WHERE username_id = 1"; $result = mysql_query($query, $this->db); if (! $result ) { $message = ...
Background I'm writing a web application and I have a stored procedure which inserts some data into a table. As a programmer I am suspect of IO so my gut tells me I need to do some exception handling in case this write fails. The intended effect of the error handling within the stored procedure would be to set a flag to be consumed by...
Hello, I have a customers table with information about our customers (ID, login, name, contacts info, different options, TS column, and so on, ~15 columns, ~few hundreds of customers). Now we need to send every-day-updates to our biggest customers (<10% of all customers). And I need to store timestamp of the latest update which were s...