Hi folks! Can somebody give a hint on this one? : I have a table, let's say tblA, where I have id1 and id2 as columns and index(id1,id2). I want to select the id1´s where id2´s belong to several sets. So I would want to say select id1 from tblA where id2 in (val1,val2,val3 ...) union select id1 from tblA where id2 in (val4,val2,val3...
I would like to update a table in mySql with data from another table. I have two tables "people" and "business". The people table is linked to the business table by a column called "business_id". The necessary table structure, primary key is starred (Table: columns): People: *business_id, *sort_order, email Business: *business_id, em...
What does adding ranked to a mysql query do? I'm trying code from this post SELECT * FROM ( SELECT @row := @row +1 AS rownum, [column name] FROM ( SELECT @row :=0) r, [table name] ) ranked WHERE rownum % [n] = 1 ...
Is there a cleaner way to perform this query in MySQL? SELECT * FROM table WHERE id != 1 AND id != 2 AND id != 7 like: SELECT * FROM table WHERE id != (1,2,7) ...
In addition to this question http://stackoverflow.com/questions/1202668/problem-with-sql-query which had very neat solution, I was wondering how the next step would look: DOCUMENT_ID | TAG ---------------------------- 1 | tag1 1 | tag2 1 | tag3 2 | tag2 3 | tag1 3 ...
I am using the following MySQL query in a PHP script on a database that contains over 370,000,000 (yes, three hundred and seventy million) rows. I know that it is extremely resource intensive and it takes ages to run this one query. Does anyone know how I can either optimise the query or get the information in another way that's quicker?...
Here's what I got- $awards_sql_1 = mysql_query('SELECT * FROM categories WHERE section_id = 1') or die(mysql_error()); $awards_rows_1 = mysql_num_rows($awards_sql_1); $awards_sql_2 = mysql_query('SELECT * FROM categories WHERE section_id = 2') or die(mysql_error()); $awards_sql_3 = mysql_query('SELECT * FROM categories WHERE section...
Is there a limit on the SQL query string you can pass to MySQL for it to execute? ...
Through a webservice my application receives a list of identifiers. With this list, I have to look up a field, one per identifier. If the field does not exist, the value should be null (it has to be shown). I am wondering what would be the best approach. First I thought it was best to create a temporary table holding the id's and then j...
I'm tying to execute a query in phpMyAdmin. The query takes approximately two minutes to load, but at the top of the query results it says that the "Query took 0.2768 sec". How is this possible? ...
I have three tables named Item, Event, and Seat, constructed like this: Item Id (int) EventId (int) Section (int) Event Id (int) VenueId (int) Configuration (int) Home_team(int) Seat Id (int) VenueId (int) Configuration (int) Section (int) I am using the following query to select all items that are tied to particular home teams: ...
My table has two columns: id and name. The data looks like this: id | name ---------- 1 Jeff 2 Sam 3 Carl 4 Sam 5 Carl 6 Jeff 7 Dave 8 Jeff What I want to obtain is this: name | frequency ---------------- Jeff 3 Carl 2 Sam 2 Dave 1 Essentially, I need an SQL query that counts the unique ...
My query currently gets yesterdays data but I now want to get all of last months data. (E.g.from 1st to 31st) $res = mysql_query("SELECT FROM_UNIXTIME( enquiry_time ), `id` , `fullname` , `address1` , `citytown` , `postcode` , `telno` , `property_value` ,`total_secured_debt` , `email` , `on_market` , `agent` , `reason` , `price_conce...
Got the following simple query which works fine through phpmyadmin but when I add it to my php website no results are returned and no error/warning messages either. If I remove "SET @N=-1;" then it works fine. <?php $db_connect = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD, true); mysql_select_db(DB_NAME, $db_connect); $test_query = m...
I have a mysql_query result that I am looping through multiple times in different parts of code, each time using a mysql_data_seek( $result, 0 ) to reset to the beginning of the result. I am using mysql_fetch_array on those results, and would like to remove a few specific rows from the $result. Basically the equivalent to unset( $resul...
I am developping a site where users can post comments and each comment is categorized. I have a page where users can go and see a list of all the categories on the site with the latest 5 comments posted in them. The information I need to retrieve from the database is: A list of categories 5 comments in each categories This is what...
I have a table 'foo' with a timestamp field 'bar'. How do I get only the oldest timestamp for a query like: SELECT foo.bar from foo? I tried doing something like: SELECT MIN(foo.bar) from foo but it failed with this error ERROR 1140 (42000) at line 1: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if...
This code selects cell values in MySQL and manually adds them to PHP variables: $sql = "SELECT * FROM table LIMIT 0,1"; $result = mysql_query($sql); while($rows = mysql_fetch_array($result)) { $col1 = $rows['col1']; $col2 = $rows['col2']; $col3 = $rows['col3']; ..... } This is obviously unmanageable for multiple and la...
I'm working on a small banner-rotation script that loads a random banner from the database. I am tracking impressions in the database and would like to know if I can select a random record and update its impression-value in a single query, or would I need to select a random record, and then update based upon the record pk. Using MySQL. ...
Hi, this is a follow up from http://stackoverflow.com/questions/1242223/mysql-find-rows-matching-all-rows-from-joined-table Thanks to this site the query runs perfectly. But now i had to extend the query for a search for artist and track. This has lead me to the following query: SELECT DISTINCT`t`.`id` FROM `trackwords` AS `tw` IN...