mysql-query

Limit characters for mysql_query(select) lookup

Is there an efficient way to limit the number of characters for a mysql query lookup? So, if a text column field had 18000 characters, but I only need to query 500 characters, is there a way to mysql_query("select text_column from random_table limit_char_count 500")? ...

Need help with a MySQL query

Please help me with this MySQL query. I've been on it for long enough. Perhaps it needs a fresh pair of eyes. Two tables: locks and sessions locks -------------- id session_id -------------- 1 sajf4$Jf9422jd 2 2jf*4j2okg9092 3 J8j4j4ffss93o2 ------------------ sessions ------------------------- id user_id -------------...

How do I preserve data when changing column datatypes in MySQL?

For example, suppose I have a TINYINT column that I want to change into an ENUM. Is it possible to write a MySQL query that changes the datatype of the column while mapping the existing data so that (for example) 0 becomes No, 1 becomes Yes, and 2 becomes Maybe? ...

I need to select a timestamp and insert a date

Hi there, I have a timestamp in a database table. Now everytime a record is amended the timestamp changes - this isn't really what I want as the record represents a sale so everytime the data is amended it looks like the sale time has changed! Thus I have added a new field to the database table called 'sale_date' and I want to select th...

MySQL Nested Query Select Statement

I've got a table that helps me keep track of the delay times between my slaves and the master. My question is how can I craft a select statement that: 1. gives me the latest delay values, without repeating (or skipping) ip addresses 2. doesn't need to be updated if I add additional servers, or as servers become unresponsive The goal of ...

improving speed of query processing

hi, having major issues with my query processing time :( i think it is because the query is getting recompiled evrytime. but i dont see any way around it. the following is the query/snippet of code: private void readPerformance(String startTime, String endTime, String performanceTable, String interfaceInput) throws SQLException...

How can I select all ids with the pattern lastname.firstname

I am looking to select ids that are backwards from some standards we have outlined. What I would like to do is this: SELECT iid FROM login WHERE iid LIKE lastname'.%' In theory this should catch everyone where there id is lastname.firstname instead of firstname.lastname. Either way any suggestions would be awesome. Thank you. ...

MySQL Query: Select an int field multiple times based on range the numbers fall into

I have an int field in my table scores. It can be a number between 0 and 100. There are over a 1000 rows in that table. I want to select the number of rows that fall between ranges 0-25, 26-50, 51-75 and 76-100. So basically, if this is my scores table score (int) ------------ 10 20 12 56 43 90 87 The output of the query shoul...

Effectively (in terms of performance) store and use set values in MySQL

I need to store set value in MySQL column. I completely like the build-in SET type, but it seems that FIND_IN_SET() function requires table scan which is bad. It seems that SET uses binary values under the hood. If I use binary value to represent a set, for example for a set of four elements it could be something like this: 0100 0101 0...

mysql_num_rows for an mysql object?

Hi. How do I find mysql_num_rows for an object. This gives an error: $query = mysql_query($sql) or die(mysql_error()); $row = mysql_fetch_object($query); echo mysql_num_rows( $row ); Warning: mysql_num_rows() expects parameter 1 to be resource, object given ...

How can I write a SQL query which will return me all categories of an item?

I have a SQL query: SELECT b . * , CONCAT( GROUP_CONCAT( c.name ) ) categories, CONCAT( GROUP_CONCAT( c.id ) ) cids FROM books b JOIN categories_of_books cb ON b.id = cb.book_id JOIN categories c ON c.id = cb.category_id GROUP BY b.id And it returns to me in categories alias all categories of books and in cids all categories ids. A...

How can I optimize a query that does an ORDER BY on a derived column in MySQL?

I am having trouble optimizing a relatively simple query involving a GROUP BY, ORDER BY, and LIMIT. The table has just over 300,000 records. Here's the schema (I added some extra indexes to experiment with): CREATE TABLE `scrape_search_results` ( `id` int(11) NOT NULL auto_increment, `creative_id` int(11) NOT NULL, `url_id` int(...

selecting previous and next rows in mysql - how?

I can't figure out how to select a previous/next row IF the current row does not have any numeric identifiers. With numeric value I always use 2 queries: SELECT min(customer_id) FROM customers WHERE `customer_id` < 10 GROUP BY customer_status ORDER BY customer_name ASC LIMIT 1; SELECT max(customer_id) FROM custo...

Question on multi-row insertion with subqueries

Say I have the following 2 tables, CREATE TABLE t1( name VARCHAR(25) NOT NULL, time INT, a INT ); CREATE TABLE t2( name VARCHAR(25) NOT NULL, time INT, b INT ); and Im looking to pull all the values (a) out of t1 with a given time, all the values with the previous time (say just time-1 for convenience) then for each name ...

MySQL SOURCE Error

Hello, I have an issue with the MySQL SOURCE command. I am getting the following error: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SOURCE /var/www/apps/modx_install.sql' at line 1 The query I am executing is as follows: mysql_query('SOU...

mysql query not showing results

Newish to mysql. I have a query and it is not showing the value of the cell just the row name: $sql="SELECT 'first' from `users` WHERE `id`= $userid "; $res=mysql_query($sql) or die(mysql_error()); $row=mysql_fetch_assoc($res); echo $row['first'] ; What am I doing wrong???? ...

mysql: select maximum year value from my table?

Hi all, very good morning to everybody. ID Value Year 10 Singapore 2010 10 Malaysia 2009 10 India 2008 This is my table. Now I want to retrieve maximum year record only. Example: ID value year 10 Singapore 2010 how can i write query in MySql?...

Select from all tables

I have a lot of tables in my data base all with same structure. I want to select from all tables without having to list them all like so: SELECT name FROM table1,table2,table3,table4 And I tried but this doesn't work: SELECT name FROM * Is there a way to select all tables in a database without listing each table in the query? ...

MySQL Dynamic Query Challenge - Help please!!

Hello, I have this query: SELECT userlist.USERID, (case when (sum( CASE WHEN track.OFFER_ID = 221 THEN 1 ELSE 0 END) > 1) then 1 else 0 end) offer_211 FROM userlist INNER JOIN track ON userlist.USERID = track.USERID group by userid This is the output: +------------+---------- | USERID | offer_211 | +-...

What is an efficient way to do pattern match in MySQL?

We have a huge pattern match in a query (about 12 strings to check). Now I have to do this query in MySQL, but it seems that the query from PostgreSQL has not the same syntax. From PostgreSQL: SELECT value FROM ... WHERE ... AND `value` !~* '.*(text|text2|text3|text4|text5).*'; How do I do this in MySQL in a efficient way? ...