Accumulating Values Inside A Query
I only have experience of simple queries, and this one is a bit beyond me. I've done some research, and I think it might be possible to do within a single query, but I'm not sure.
I have a single table. It contains columns (among others): date, number. I want to produce a table of DATE, and new_number_count, where 'new_number_count' is the number of numbers on day with date DATE that do not have rows on previous DATEs.
For example, if this is my table:
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Trying To Avoid Using Query Inside While Loop
I've done a lot of reading on here and I learned from some of Rudy's posts that it's a bad idea to do a query inside a while loop. I don't know why this is but he seems like an expert so I'll listen What I am trying to do is take the top ten points from a player and display them. First I will post the tables then the code. CREATE TABLE `tournament` ( `gameid` int(11) unsigned NOT NULL auto_increment, `gametype` tinyint(2) unsigned NOT NULL default Ɔ', `gamedate` datetime NOT NULL default ��-00-00 00:00:00', `leagueid` int(11) unsigned NOT NULL default Ɔ', `seasonid` int(11) unsigned NOT NULL default Ɔ', `roomid` int(11) NOT NULL default Ɔ', `gamename` varchar(50) NOT NULL default '', `cost` mediumint(4) NOT NULL default Ɔ', `seats` mediumint(4) NOT NULL default Ɔ', `notes` tinytext NOT NULL, PRIMARY KEY (`gameid`) ) ; CREATE TABLE `tournament_results` ( `id` bigint(20) unsigned NOT NULL auto_increment, `gameid` int(11) unsigned NOT NULL default Ɔ', `memberid` int(11) NOT NULL default Ɔ', `place` smallint(4) NOT NULL default Ɔ', `earnings` smallint(5) default Ɔ', `bounties` float unsigned default Ɔ', `points` float NOT NULL, PRIMARY KEY (`id`) ); Now the follow code would be what I would use if I just wanted to add all the points and not limit it PHP mysql_query("SELECT members.firstname, members.lastname, SUM(tournament_results.points) AS tpoints, COUNT(*)as numgames, AVG(tournament_results.points) AS apoints, members.memberid AS membersid FROM members, tournament_results, leaguemembers, tournament WHERE members.memberid = tournament_results.memberid AND leaguemembers.memberid = members.memberid AND leaguemembers.leagueid = '$lid' AND tournament_results.gameid = tournament.gameid AND tournament.seasonid = '$sid' GROUP BY members.memberid ORDER BY tpoints DESC"); That will total all of their games but I want to limit it their top 10 scores how can I do this without introducing a query inside of the while loop?
Preventing A Count Query Inside A Loop How?
I have an idea in my head and im pretty sure its possible but I don't have enough mysql knowledge to apply. Basically, I have this: SELECT user_id FROM ml_group_subscriptions WHERE user_group = $id then in php I initialise a loop go over the results: PHP while ($row = mysql_fetch_array($get_users)) { $this_user = $row['user_id']; and then an insert query: INSERT INTO ml_subscriptions (`user_id`, `campaign_id`) VALUES ($this_user, $add_to_campaign) while this is all dandy, I don't want duplicate data. My only current method would be PHP $get_users = mysql_query("SELECT user_id FROM ml_group_subscriptions WHERE user_group = $id"); while ($row = mysql_fetch_array($get_users)) { $this_user = $row['user_id']; $check = mysql_result(mysql_query("SELECT COUNT(user_id) FROM ml_subscriptions WHERE user_id = $this_user"),0); if ($check == 0) { $insert_users = mysql_query("INSERT INTO ml_subscriptions (`user_id`, `campaign_id`) VALUES ($this_user, $add_to_campaign)"); } } as you can see this is putting a query inside a loop which could be potentially huge. Can I not adapt the first SELECT query to only accept data that is not about to be duplicated?
Calling A Php Funtion Inside A Mysql Query
hi there, im trying to call a php function that i have created inside a query and im getting an error.when i remove the funtion statment the query is successful. i was wondering is it possible to call a php function inside a query, if so how do i go about doing so. this is my code:
How To Apply Php Or User-defined Functions Inside A Query
Instead of applying a php or user-defined function to data after fetching, I want to apply functions inside queries, not sure if this is possible. This is how it's done with MySQL function: SELECT FROM_UNIXTIME(table_articles.article_pubdate, %M %d, %Y) AS pub_date FROM... Is it possible to replace FROM_UNIXTIME() with the php function date() ? so it's something like SELECT ".date("table_articles.article_pubdate", 'F d, Y')." AS pub_date FROM... The above is just an example, actually I want to apply another user-defined function, which formats text of the field article_content.
Query Inside Query?
I would like to know if its practical to Insert a Select statement inside a previous select statements (array). With that said, the 'inner' select having a WHERE statement thats dependant upon the array results...
Distinct Values In Mysql Query
I have mysql query but i am not sure about it,bcoz it return many rows.. I want like distinct values from three tables with ic number. Which is posted by user... "SELECT distinct loguser.icnumber,access.acccode,dealerdtl.name,loguser.fullname,access.period,loguser.staffcode, DATE_FORMAT(access.actdate, '%d-%m-%Y ')As formatteddate1, DATE_FORMAT(loguser.creationdate, '%d-%m-%Y %H:%i:%s')As formatteddate FROM dealerdtl,loguser,access where access.icnumber=loguser.icnumber AND loguser.icnumber = '$custic'"; This query return many rows i dont know why, i want only one row which is inserted by the user as customer id.
Reference To Already Retrieved Values In Query
As a simple example, say there is table 'namelist' with column 'names' as char(20). I would like to do something akin to: select namelist.names as mynames, left(mynames,2) as initials; In this example, I could just do left(namelist.names,2), but in more complex cases a value retrieved may have had a more complex logic behind it, e.g., if a bunch of nested if() statements. It would seem logical that if a value is already retrieved then I should be able to refer to it within the same query, assuming that the original value does not need to be recalculated. I know I could use a view to do this, but it adds another layer of complexity, and I am not sure that the values would not be recalculated each time the value is needed. If someone could point out to me the correct terminology for this kind of thing I believe I should then be able to look up the information myself. My efforts to find this information on google and the mysql website were unsuccessful.
Query For Counting Unique Values
I'm trying to find out if I can construct a query to MySQL that will return the number of unique values in a given column. The reason is because I have a column that just contains just the YEAR of a given report. I want to see how many different values populate the YEAR column through the entire query result so I know how many tables to render on screen (one table for each year).
NULL Values In SELECT Query
I need to know how to push null values to the bottom of a query sorted by ASC. I know if you sort by DESC the null values are placed last. How can I push the null values to the end of a query while still sorting using ASC? Anyone?
Update Query Where Values Will Come From Other Table
I'm creating an update query which the value will come from another table. I have here my current query which unfortunately makes the system hangs. Probably because of the query itself is not properly coded. update boxes b inner join messages m on b.ctnnumber = m.ctnno set b.consigneerecv = m.CName, b.consigneerecvdate = m.DateRcv, b.phrecventered = "Y", b.PhilStatus = "delivered", b.prevreleasestatus = b.releasestatus, b.releasestatus = "delivered", b.PhilStatusDate = m.smsrecvdate, b.phdelprice = "0.00", b.phdelamt = "0.00", b.recvrelation = m.Relation, b.APRecventered = m.smsRecvDate where b.consigneerecv = '' or b.consigneerecv = 'NA' or b.consigneerecv is null; I'm thinking revising it so that it will not cause the system to hang but I don't know how. Guys please help me with this one. I also have this another idea which probably will not work. My idea was something like this: Update table1 set table1.column1 = (select table2.column1 where table2.column1 = table1.column1), table1.column2 = (select table2.column2 where table2.column1 = table1.column1), .....
How To Generate Sequence Values During Query
I have query like below: SELECT part_no,part_nm,qty FROM tb_stok_out ORDER BY part_no as result: part_no part_nm qty aaa asdfd 3 abab sdfsdf 4 abab adfdf 5 Is it possible in mysql to generate sequence number using query, so the result will be like below : 1 aaa asdfd 3 2 abab sdfsdf 4 3 abab adfdf 5 ... etc
Difficult Query - Need To Group Results By Id And Sum Values
I have got the meat of this query done but I am facing a problem. I am doing a VAT analysis whereby I have every shoe that is over size 7 I pay tax on and every shoe below that I don't pay tax on. I also pay tax on accessories. What I want the query to return is this: Date | ShopperID | VATable Amount | Non VATable Amount| 2006-3-1 | 802135 | 146.95 | 54.00 | Basically each shopperID will only appear once which is why I am grouping the results (which I have managed). But I also need it to total up all the VATable amounts that it finds too for both the fields on the right above. I can get the date and order number without difficulty and I have managed to get the data like this so far where I have the same ShopperID where a shopper has bought more than 1 product: +------------+----------+-----------------+---------------------+ | Date | ShopperID| VATable Amount | Non VATable Amount | +------------+----------+------------+---------------+ | 2006-09-04 | 805284 | 0 | 64.00 | | 2006-09-04 | 805287 | 2.95 | 0 | <-- Here a | 2006-09-04 | 805287 | 3.25 | 0 | customer has | 2006-09-04 | 805287 | 3.45 | 0 | bought 4 products | 2006-09-04 | 805287 | 4.95 | 0 | - I need total | 2006-09-04 | 805327 | 0 | 53.95 | under each | 2006-09-04 | 805335 | 0 | 58.95 | ShopperID | 2006-09-04 | 805414 | 0 | 64.95 | | 2006-09-04 | 805414 | 3.25 | 0 | | 2006-09-04 | 805414 | 0 | 64.00 | | 2006-09-04 | 805414 | 0 | 69.00 | | 2006-09-04 | 805423 | 0 | 64.95 | | 2006-09-04 | 805423 | 0 | 69.00 | | 2006-09-04 | 805423 | 0 | 64.00 | | 2006-09-04 | 805423 | 3.25 | 0 | +------------+-----------+----------------+--------------------+ Heres the query currently getting these results (without the GROUP BY ShopperID). SELECT Date,orders.ShopperID, CASE WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9][7-9]( )?(' THEN items_ordered.price -- If size 7 or above add price WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9]10( )?(' THEN items_ordered.price -- If size 7 or above add price WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9]11( )?(' THEN items_ordered.price -- If size 7 or above add price WHEN SUBSTRING_INDEX(Product,',',1) REGEXP 'Insole|Helmet|Laces|Wheels|Removal|Protection|Bag' THEN items_ordered.price -- If it is an accessory add VAT ELSE 0 END AS 'VATable Amount', CASE WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^1-9][1|2|3|4|5|6]( )?(' THEN items_ordered.price -- If size 1 - 6 add price to other column WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?( )?(UK)?( )?( )?Kids( )?( )?(UK)?( )?12|13( )?( )?(' THEN items_ordered.price -- If size kids 12/13 add price to other column ELSE 0 END AS 'Non VATable Amount' FROM items_ordered,orders WHERE (items_ordered.ShopperID = orders.ShopperID) AND (Date >= ��-1-30') AND (Date <= ��-9-31');
A Query To Select A Column When A Percentage Of Values Non Zero?
I wounder whether some of the experts out there might be able to help me with a problem I'm having. I do not know whether this is possible or not... I have a large table of stock price data which is straight-forward enought. I can select prices based on a ticker and date ranges. However, what I'd like to do is to select prices only when, say 75% of them are non-zero (with the goal of eliminating new/suspended/delisted stocks). Of course I could just select where price > 0, but then I might get only a few rows where this is the case. What I would like to do is always get the full date range of prices, but only if >75% are there.
Showing Query Result With Default Values When No Data Available
I have a scenario that user can ask for certain data say income & expense for last 3 months on monthly basis. I have wrote following query which works well but it has an issue: Let say I have asked for data of FEB 2007, JAN 2007, DEC 2006 using the following query it shows me correct result when all three months have some data but it doesn't show me the desired result when you don't have data in one or more months. Any suggestion to get result when there is no data:
Truncated Time Values Using TIMEDIFF With ORDER BY Query
I'm using the following query: SELECT glider, timestamp, TIMEDIFF(timestamp, UTC_TIMESTAMP()) AS last_contact FROM surfacings INNER JOIN (SELECT MAX(timestamp) AS most_recent FROM surfacings GROUP BY glider) AS tmp WHERE surfacings.timestamp = tmp.most_recent; to calculate the amount of time that has elapsed since the last inserted timestamp for each glider. Everything works fine: ....
Inside IF NULL
I have this table: ------------------ id lang name 1 en name1 1 de NULL 2 en name2 2 de name3 I want to return all (WHERE lang='de') rows but if the (name=NULL) then replace it with name from the same id but lang='en'. Is it possible? I have this query: SELECT IFNULL(name, SELECT name FROM table WHERE lang='en' AND id=????) name FROM table WHERE lang='de' Can you fix it?
Looking For Tokens Inside A String
I'm trying to do a weird (?) thing with regexp and MySQL. For example, i have a column with: token1|token2|token3 (three tokens separated by pipes) I'm trying to write an SQL query in order to look for the presence of one of these tokens, that is "look for a string like 'tokenX' that is preceded by the beginning of the line OR a pipe, AND that is followed by the end of the line OR a pipe". I have tried this: SELECT * FROM ... WHERE (... REGEXP "(^||)tokenX($||)"); but, of course, it doesn't work. So my question is: what's wrong ? My regular expression, or my idea ?? There is a another way to obtain what i want, that is to select a token within a string when tokens are separated by anything but a comma ?
COUNT (*) Inside Select
I am doing something wrong i would like to do a count inside this select for example: SELECT th.tid, th.tname, th.tmain (SELECT COUNT(*) as postnumber FROM forumpost AS post WHERE post.ptid=th.tid) FROM thread as th WHERE tmain=1
Use SQL Code From Table Inside Trigger
I have the WHERE clause that I would like to use in constructing larger statements stored inside a database table as a string. Inside the INSERT Trigger for a different table, I would like to be able to: <get the tuple with the WHERE clause I want to use>; SELECT * FROM t WHERE <use code in database>; Is there a way I can pull out this data and append the data to the second statement and execute it?
Format Text Inside Database
I have a news-type database. I pull the data out with PHP. I was wondering if I could format/bold/ certain words in the database when I enter them. For example: Today JOHN CAESAR won his first race. I'd like the name "JOHN CAESAR" to show up bold on my web page. Is there a way to enter it in the database as bold?
Execute A Shell Command Inside SQL
I wonder if there is anyway you can run a shell command inside SQL. for example "SELECT * FROM table;execute shell command" I had googled around and didn't find anything.
Finding User Details Inside UDF
i heared that its possible to get user details inside the UDf by adding the mysql_priv.h header file. But where will this file be available. i am using MySql 3.23.52 on linux8.0 when i try to compile it says :"mysql_priv" file not found. If i can include this file, i can get the thread constructor called...
How To Sort Rows Inside The Group
First step: SELECT ID, Priority, RealID FROM MyTable WHERE ParentID='0' ORDER BY Priority; The result is +----------+----------------+--------------+ | ID | Priority | RealID | +----------+----------------+--------------+ | 1 | 1 | 1 | | 9 | 1 | 1 | | 2 | 2 | 2 | | 11 | 2 | 3 | | 3 | 3 | 3 | | 10 | 3 | 2 | +----------+----------------+--------------+ Then try to obtain the rows grouped by RealID sorted by ID from high to low. This is very important thing and I'm sticking with this: Let's say we have | ID | Priority | RealID | | 1 | 1 | 1 | | 9 | 1 | 1 | I need to obtain second row (the row with max ID in the RealID group). What I worked out is the following: SELECT MAX(ID) as ID, RealID FROM MyTable WHERE ParentID='0' GROUP BY RealID ORDER BY Priority; And Get +----------+--------------+ | ID | RealID | +----------+--------------+ | 9 | 1 | | 10 | 2 | | 11 | 3 | +----------+--------------+ But I'm expecting the following order. +----------+--------------+ | ID | RealID | +----------+--------------+ | 9 | 1 | | 11 | 3 | | 10 | 2 | +----------+--------------+ 1. Can't understand why the rows sorted incorrectly. 2. How to "select" the rows according to my need (I have 10 fields in the MyTable and I think this is not good to select all fields using MAX(...) function). Maybe use subrequests?
Storing The Image Inside Mysql
i read it affect performance by 1 third or something but isnt that worth it for lets say a online hardware store? or is it actually faster maybe?
Html Links Inside Varchar
I have to put html links inside varchar and text fields. They have to be (somehow) fulltext searchable (of course a substring, %keyword% search, would pick them up - I realize that) and they have to render as clickable links when I output the fields via PHP. How would I do this?
Regexp To Return All Number Inside A Field
does this is possible? all i just want to manipulate is the phone number in my database. i want to get the numbers inside this format text (125) 125-1268 or in this format 225.265.4649. how can i accomplish that in the query?
Multiple Prepare Statement Inside The Procedure
I'm trying with the multiple dynamic queries inside the stored procedure means, inside my stored procedure i want to execute Multiple queries (DDL and DML). for that i'm storing query in variable and then executting it by prepare stmt. It works fine with singal prepare stmt and failes for the multiple prepare stmt. Is there any solution to execute multiple queries inside the Stored procedures or Functions?
Ignoring Content Inside HTML Tags
Is it possible to set MySQL to by default ignore all content contained within HTML tags? For instance, if a database full of HTML pages has the "class" attribute on a lot of the tags and someone searches for "class" expecting information on classes, wouldn't that also return all those documents just because they had a "class" attribute? It would be great if I could set MySQL to ignore all content between < and >.
Store Links Inside The Main Text In A Database
we are trying to set up a mySql database while rebuilding a webpage with lots of content.we actually dont know how to set up the tables for that and especially how to store links inside the main text?should we use xml files to store the text with embedded links?
Next And Prev DisplayOrder Values (was "Help With MySQL Query")
I have a table 'Album' containing columns 'PageID' and 'DisplayOrder' (both INT). On each page that is displayed I want to have 'previous' and 'next' links to other pages, which are arranged via the DisplayOrder column. At the moment I'm doing this in 2 separate queries: SELECT DisplayOrder FROM Album WHERE PageID = $currentPage $currentNo = [result of last query] SELECT PageID FROM Album WHERE DisplayOrder IN ($currentNo-1, $currentNo+1) Highly simplified but you get the idea. Would it be possible to combine this into one query?
Inside Firewall Access To Outside Firewall MySQL Server
I have 2 Suse 9.1 boxes with similar configurations. I'm in the process of moving some PHP code from one server (192.168.0.100) to another (192.168.0.102). MySQL is running on each server, and the same PHP code can access its respective localhost databases and make queries with no problem. However, the code on the old server (.100) can access a MySQL server (12.xx.xx.50) outside the firewall (192.168.0.1), while the code on the new server cannot. There is NO firewall rule regarding the two servers -- they are NOT being accessed from outside the firewall.
How Do You Enter An Entry With A Newline Inside The Entry?
I'm trying to enter an entry into the table. However, my entries must have newlines inside each entry. Further more, i'm trying to do this from an excel file(ie. first by saving it to a text file and then using LOAD DATA INFILE..).
GROUP BY With Order Inside Group
I have a problem when grouping records - I can't manipulate data inside group. For example, I have table `images` with fields `name` (name of image, not unique) and `dtadded` (date of image adding). Then, I need to get all images names with distinct names where each name must be latest added name. Sample: ------------------------------ id, name, dtadded ------------------------------ 1 name1 2007-10-15 00:00:00 2 name2 2007-10-15 00:00:00 3 name1 2007-10-16 00:00:00 ------------------------------ I need to receive 2 results (for each name) 3 - name1 - 2007-10-16 00:00:00 2 - name2 - 2007-10-15 00:00:00 If I use SQL code: SELECT images.id, images.dtadded, images.name, count(name) FROM images GROUP BY `name` ORDER BY dtadded DESC I get results 1 - name1 - 2007-10-15 00:00:00 2 - name2 - 2007-10-15 00:00:00 It groups records with first row in database, but I need last row in table for each name. Question: How can I order results in side group to get needed results as described above?
How Can I Make A Query Like Microsoft Access, And A Query From A Query
I am new to MYSQL and am trying to understand how to make queries... I am moving from Microsoft Access where it is GUI driven and easy! I can make a simple single query using MYSQL Query Browser, say: qry1: SELECT ID, Area FROM data GROUP BY Area How can I store this as a query inside MYSQL, rather than having to code it each time? In Microsoft Access I could enter a variable ($VARIABLE) and then pass by code to the query: qry2: SELECT ID, $VARIABLE FROM data GROUP BY $VARIABLE How can I store this as a query and then pass the variable from code? In Microsoft Access I could base a query on the results of another query, so following example above: qry3: SELECT qry1.Area, data.ID FROM qry1 INNER JOIN data ON qry1.Area = data.Area; How can I store this as a query in MYSQL.
Get All Values
Is it possible to just return all the values that are in a column?
Two WHERE Values
I dont even know what to search for on this. Is it at all possible to have two WHERE values on a query? Would I maybe put an and symbol or something? what is the proper syntax for something like this, or is it even possible? SELECT * FROM Hotlines WHERE date_opened='20050203'
First And Last Values
Is this the most efficient way of performing this action? SELECT (SELECT `Date` FROM mytable ORDER BY `Date` LIMIT 1) AS FirstDate, (SELECT `Date` FROM mytable ORDER BY `Date` DESC LIMIT 1) AS LastDate
|
TRACKER
