mysql-query

MySQL Select: Get Main Record(s) by Multiple Attached Records?

Not sure how to ask this so it makes sense, but I'm trying to do a query like: SELECT * FROM PAGES WHERE an attached record exists in PAGE_FILTERS and that record has a FilterTypeID of 22 AND another attached record exists in PAGE_FILTERS for the same page ID and that record has a filter type id of 27. I have a structure like this: P...

Calling MySQL database using Java

Hi I am new to programming and trying to call a table from a MySQL database using Java. Here is what my Java code looks like: public static void main(String[] args){ double p1[] = {10000000,2,5,7,5,6,6,8,9,3}; double p2[] = {100,3,4,3,4,1,2,5,1,5}; double p3[] = {1,2,6,4,5,6,7,8,9,1}; Expert_Score x = n...

How to force mysql to cache a query for a specific period of time ?

Hi all, is there a way to force mysql to cache a particular query, for say 5 minutes, so executing that query will always return the same result even if the underlying db changes ? i'm running a dating site and i have a page that shows "newest matches" and it's hitting the db too much. thanks in advance. ...

Using multiple tables in iReport

I want to create a report that has to call multiple fields from different tables. From what I have read online so far, the only way to do this is to have subreports. Is this true, or is there a way that I am able to run multiple queries to different tables to retrieve data? ...

MySQL Specialists: Delete all rows older than x days but not the latest one.

first of all, this is the query which creates the "player history" it can be executed as often as you want and it will only create new history rows for the players if there is no history row for yesterday or if the values changed since the latest history entry in the past. INSERT INTO `player_history` (`player_id`, `date`, `races`, `map...

Is where/having going to be useful here?

I have a query that looks something like SELECT to_number FROM sent_texts WHERE to_number NOT IN(SELECT mobile FROM action_6_members); A WHERE is applied to the result set after the query is complete. What would the effect be (improve/degrade) if the sub query contained WHERE mobile = to_number A HAVING is applied to the result se...

Can this SQL query be made to run any faster?

I'm using mySQL InnoDB tables. The query is simple but it just takes too long to run. Its bound to run even slower once I upload it to a server (not to mention that these two tables will keep growing in size). The 'author' table size is 3,045 records. The 'book' table size is 5,278 records. SELECT author.*, count( auth_id ) AS author_c...

mysql - adding/concatenating text values within a SELECT clause

Is there a way in MySQL to concatenate text to values using a SELECT statement? (like in Oracle) For example, in Oracle you can write something like this: SQL> select 'The Year is '|| year, 'The month is '|| month from time where rownum < 2; 'THEYEARIS'||YEAR ---------------------------------------------------- 'THEMONTHIS'||MONTH ---...

query on %age share in sql

How do we calculate the percentage market share in sql ON MYSQL 5.1 version? I need to find the %age share of each manufacture.The market share is given by number of handsets. ...

Cakephp Containable or Tree ?

I have a db with the following tables user(id, name....) restaurant(id, name...) module (id, name ) status_messages(id, pid, message, module_id, ModuleID) Module Refers to either Users or Restaurant. Both Users and Restaurants can have status_messages. When a user (id=21) sets a status message (1, 1, 'Message', 1, 21) When a...

MySQL Insert Race Condition

I have a webapp that currently stores all of a user's searches into a search_log table. I now want to create another table called results_log that stores all the results we supply to the user. The search_log table contains a primary key called id_search and the results log table has the foreign key id_search, and one other field id_res...

Mysql: Date as string

I have a field in MySQL as varchar and need to do a select with a range. The date is in the following format "%m/%d/%Y" (Ej. 10/14/2010). ...

mysql Creating a temporary table not working...undeclared variable error...whats wrong with my code?

When I run the code below inside phpmyadmin I get an error "Undefined Variable tmpportfolio" what is the problem? SQL: CREATE TEMPORARY TABLE tmpportfolio (p_name VARCHAR(255),portfolio_id INT UNSIGNED NOT NULL); SELECT p_name, portfolio_id INTO tmpportfolio FROM portfolio WHERE parent_portfolio_id IS NULL AND portfolio_i...

Help with MySQL Query syntax: ERROR #1066 - Not unique table/alias

I have four tables, user, user_billingprofile, user_shippingprofile, and user_address. user: userId, dateCreated user_billingprofile: userId, address user_shippingprofile: userId, address user_address: random address crap Here is the query I have to get a users billing and shipping profiles in one shot. SELECT * FROM `user` JOIN `us...

Problem with MySQL and PHP

PHP: <?php if (isset($_POST['name'])){ mysql_connect("localhost", "db", "test") or die(mysql_error()); mysql_select_db("db") or die(mysql_error()); $tmp = mysql_query("SELECT commercial FROM Channels WHERE name='.$_POST[name].'"); echo $tmp[0]; } else { ?> <form method="post" action="<?php echo $_SERVER['PHP_SELF'];?>"> <input name=...

MySQL count occurences greater than 2

I have the following table structure + id + word + +------+--------+ The table gets filled with the words in lower cas of a given text, so the text Hello bye hello would result in + id + word + +------+--------+ + 1 + hello + +------+--------+ + 2 + bye + +------+--------+ + 3 + hello + +------+--------+ I w...

How to use OR in MySQL query and ignore second condition if first is true?

I am using a query like this: SELECT * FROM table1 WHERE countries LIKE '%US%' OR countries LIKE '%ALL%'; This returns all the entries that have "US" or "ALL" in countries column, but I want it to return ONLY those rows that contains "US", and if non is found then use the next condition and return all that contains "ALL". Is it possi...

Dynamic Validation: 0 or more per field, how to AND or OR validation rules?

Furthering: http://stackoverflow.com/questions/3910250/database-design-for-dynamic-form-field-validation/ How would I model the database when a particular field can have 0 or more validations rules AND that each validation rule is "related" to another rule via AND or OR. For example, say I have field1 that needs to be minimum of 5 char...

having problem in building sql query

Hi there, I am just confused in building a SQL query. I tried to run it in pieces successfully but just not able to build it as a whole. This is the case: In table 'userseaches', user Rushi has been searched three times with different searchID's(primary key in 'searchtable') 10, 11, 12 . When searched with searchID 10 got 110 results,...

Initiating the same loop with either a while or foreach statement

I have code in php such as the following: while($r = mysql_fetch_array($q)) { // Do some stuff } where $q is a query retrieving a set of group members. However, certain groups have there members saved in memcached and that memcached value is stored in an array as $mem_entry. To run through that, I'd normally do the following foreac...