mysql-query

Optimizing a MySQL query to avoid "Using temporary" and "Using filesort"

I know there's a thousand similar questions out there, but none deal with as convoluted a query as mine (and my MySQL skills aren't to the point to really understand how to adapt them.) Here it is: explain select `ev`.`EventID` AS `EventID` ,`ev`.`EventName` AS `EventName` ,concat(`ev`.`EventDate`,' ',`ev`.`StartTime`) AS `EventD...

MySQL Stored Procedure dynamic change name of table.

Hello, I wanna change dynamicaly name of table in sql query. For example I have next stored procedure: CREATE PROCEDURE NewProc(IN tableName varchar(64),IN message text) BEGIN INSERT INTO tableName VALUES (message); END; I need to change tableName in runtime, Can I to do it or not? Thanks. ...

MySQL -- Better way to do this query?

This query will be done in a cached autocomplete text box, possibly by thousands of users at the same time. What I have below works, bit I feel there may be a better way to do what I am doing. Any advice? UPDATED -- it can be 'something%': SELECT a.`object_id`, a.`type`, IF( b.`name` IS NOT NULL, b.`name`, IF( c.`name` IS...

INSERT new row and MKDIR with the name of that inserted row ID

The page has to INSERT a row and MAKE a directory with the name of the ID of that row when the user clicks the upload button, will this script work?, what could possibly go wrong?. (when the upload button has been clicked) //insert the new row $query="INSERT INTO photoalbum (userid) VALUES ($userid)"; mysql_query($query); ...

How to get unique result in mysql.

I have table: name marks aaa 100 aaa 56 aaa 120 bbb 56 bbb 60 The result should be: aaa 120 bbb 60 ie unique name with maximum marks. Is there any query in mysql? ...

How to use PHP Session to View MYsql Database Tables?

Hi there! I am currently trying to create a PHP script whereby there is already a login script created and the user that successfully logs into the home.php is able to view the tables of the database he is logged into. Is there any ways of retrieving the tables using the mySQL query like "SHOW TABLES"? Here are some of my codes: <?php...

Mysql Sorting, Sort An Query's Result set

Hi all, I have a QUERY SELECT p.*, b.sBoutiqueName, (SELECT SUM( nViewCount ) FROM `COV_PRODUCT_STATS_COUNT` WHERE nProductID = p.nProductID) AS nViewCount, (SELECT SUM( nLinkClickCount ) FROM `COV_PRODUCT_STATS_COUNT` WHERE nProductID = p.nProductID) AS nLinkClickCount, (SELECT SUM( ...

Overlapping date range MySQL

Hi guys, I have the following data; ID startDate endDate ----------------------------------------------- 1 2010-03-01 10:00:00 2010-03-01 12:00:00 2 2010-03-01 12:30:00 2010-03-01 15:30:00 3 2010-03-01 15:30:00 2010-03-01 18:30:00 What I want to do is check that a start and end date don't fall inside ...

Mysql Join and Order

Hello, I have 3 tables that have same columns,but different data ( deposits,withdrawals,transfers ) CREATE TABLE IF NOT EXISTS withdrawals ( id int(11) NOT NULL auto_increment, user varchar(12) default NULL, amount double(12,2) default NULL, date timestamp NULL default CURRENT_TIMESTAMP, time varchar(50) defau...

access performance, mysql nested functions

I have a table in access with about 180k records. I have a bunch of sql statements that I tested and work perfectly on a smaller version of my data. With access & that amount of records, things are pretty slow, but a sql statement with a Dconcat function I even let operate overnight didn't get even close to 1/4 way through. I assumed ...

wordpress/mysql problem for building nav menus

I am working on a wordpress theme and need to select posts that are in multiple categories to build a menu. I could set it up to work off of the tags, or a combination of tags and categories, but in the end I need to be able to search by multiple cat/tag. The only built-in wordpress function that I have been able to find that allows for...

MySQL Query using substr() and instr() fails if needle is not found in haystack

I need to generate a pseudo-key column in a MySQL table that's defined as the first part of the point_title field (up to a space, if any) concatenated with the record id. Example data: id point_title pseudo_key _____________________________________ 123 Scott R. scott123 124 John R. john124 125 ...

Combining two select queries

A the moment I am using two queries one is called initially, and the second is called during a loop through the results of the first. I want to combine both queries, but have been unable to so far. The tables the queries are pulling from are: +--------------+ +--------------+ +--------------------------+ | table_1 | | t...

mysql INSERT into multiple tables with one statement

Is it possible to insert data into multiple tables in one statement? For example, I have two tables (live_products and products) they are the same, except live_products is highly transactional and I keep the record count low (delete once done), where as products holds all historical data, used for reports, etc. Currently, I'm doing two...

MySQL email templates.. Is it possible?

Not sure if it's possible but it's worth a shot.. I am trying to insert into a MySQL 'TEXT' field some text.. Some of the words within the text I want to change depending on other fields from some other tables in the MySQL database.. Something similar to a php email template where the 'Dear ${first_name}' can be changed depending on who ...

only retrieving 1 row of data from MYSQL database

Hello, I am trying to input multiple pieces of data through a form and all the data will be separated by (,). I plan to use this data to find the corresponding id for further processing through an sql query. Below is the code I use. $key_code = explode(",", $keyword); //$key_count = count($key_code); $list = "'". implode("','", $ke...

is it possible to have php function() and sql run togather ?

Say there is a special php function named foo($number) that returns double the value of its input. Further, say there is a table that contains the column "number." Is there a way so that I can run a query and get the following: Number | Double ================ 1 | 2 5 | 10 ...

AJAX to check the login name exists or not

i have a login box... when the user starts typing.. i want to check whether the LOGIN NAME entered exists in the database or not... if the login name is exist i am going to set the login button active... if it doesnot exist i am going to set the login button deactive... offcourse i am going to need AJAX to perform my mySQL via PHP t...

query works on MySQL, but no in PHP, why ??

Hi, I need to display the results of this query : SELECT * FROM projects WHERE PrestaCmd LIKE '% A - CREP - DPE - %' but in PHP, this query doesn't work :s This is my code : $req = "SELECT * FROM ".$table." WHERE PrestaCmd LIKE '%".$ch."%'"; echo $req; //returns : SELECT * FROM jos_projectlog_projects WHERE PrestaCmd LIKE '% A ...

Issue with Inserting a record into a MySql database

Hello all, I am having an issue with a simple form uploading script. On this upload script I built to upload data to a MySql database, I can't seem to get the record to insert into the database when I include this one variable. I figured that perhaps I am overlooking some minor coding issue, and I'm working on a deadline to get this s...