Looping An Array Of Dates In One Query
$newdates is a simple array of dates (like 2008-10-01)
the 'comm' column is decimal 2 places
'trans_date' is a datetime column
PHP Code:
foreach($newdates as $newdate){    Â
$linequery = "SELECT SUM(comm) AS `linecomm` FROM `table` WHERE DATE(trans_date) = '" . $newdate . "'";Â
$lineresult = mysql_query($linequery) or die("Could not execute comm totals query" . mysql_error());    Â
$row2Â =Â mysql_fetch_row($lineresult);Â Â Â Â Â
array_push($data_1, $row2[0]);Â
}Â
View Complete Forum Thread with Replies
Related Forum Messages:
SQL Looping Query
I am pulling my hair out trying to crack a bit of SQL to query some tables. I could easily sort it out if I could just do a simple subquery like this: SELECT DISTINCT(ArtistNameTable2.ArtistName) AS ANm, ArtistNameTable2.ID FROM SongTable2, ArtistNameTable2, SongPaidFor WHERE SongTable2.ArtID = ArtistNameTable2.ID AND SongPaidFor.SongID = SongTable2.ID AND SongPaidFor.CustID IN (SELECT DISTINCT(CustID) AS CID FROM SongPaidFor WHERE SongID = 205 AND CustID <> 0) But! I can't use subqueries on the version of MySQL I'm using. So, I have 3 tables: SongPaidFor ArtistNameTable2 SongTable2 When a song is paid for, the following type of data gets entered into the SongPaidFor table e.g let's assume we're looking at SongID 205: SongID..CustID 205.....128 205.....218 205.....388 205.....392 205.....396 205.....397 Fine so far -- what I am trying to do is to say, for songID 205, customers also bought songs from the following artists... So - to start with, we have the SongPaidFor table which shows which customers bought which song. I also have the following tables: ArtistNameTable2 - containing: ================ ID (ArtistID) ArtistName and also: SongTable2 - containing: ========== ID (SongID) SongName So these 2 tables can be accessed via a simple bit of SQL - e.g - to find the Artists customer 128 also purchased tracks from, I could do this: SELECT DISTINCT(ArtistNameTable2.ArtistName) AS ANm, ArtistNameTable2.ID FROM SongTable2, ArtistNameTable2, SongPaidFor WHERE SongTable2.ArtID = ArtistNameTable2.ID AND SongPaidFor.SongID = SongTable2.ID AND SongPaidFor.CustID = 128 But - how on earth can I loop through the output to generate a DISTINCT list of artists that the customers who bought track 205 also like? I have done a simple bit of SQL to loop through to work out the ArtistName and ArtistID that the customers also like: ID = request("ID") ''e.g.songID 205 SQL1 = "SELECT DISTINCT(CustID) AS CID FROM SongPaidFor WHERE SongID = "&ID&" AND CustID <> 0" SET RS1 = oConn.Execute(SQL1) DO WHILE NOT RS1.EOF CustID = RS1("CID") SQL2 = " SELECT DISTINCT(ArtistNameTable2.ArtistName), ArtistNameTable2.ID FROM SongTable2, ArtistNameTable2, SongPaidFor " SQL2 = SQL2 & " WHERE SongTable2.ArtID = ArtistNameTable2.ID " SQL2 = SQL2 & " AND SongPaidFor.SongID = SongTable2.ID " SQL2 = SQL2 & " AND SongPaidFor.CustID = "&CustID SET RS2 = oConn.Execute(SQL2) name_loop = "" DO WHILE NOT RS2.EOF thisname = RS2("ArtistName") name_loop = name_loop & thisname & "<br />" RS2.MoveNext Loop RS2.Close:set RS2=nothing total_loop = total_loop & name_loop RS1.MoveNext Loop RS1.Close:Set RS1=nothing But - it's still a completely crap approach, because at the end of it, I just get a big list of text, which cannot be manipulated in any way: e.g: The High Teas semble Gavin Molton (instrumental) Gavin Molton Ruin the Rain Charlies Horse The Blue Review Water Middle Men akimbo The Barnacles StrangeCloud Gavin Molton Flame Into Being Gavin Molton (instrumental) Ray Saunders Flame Into Being Gavin Molton Gavin Molton (instrumental) Au Revoir Simone Cut Copy Seamless Pink rinôçérôse Zap Mama Nada Surf DJ Shadowman Nermin feat. Ayaah Nomad Gavin Molton With this - I cannot strip out duplicates, or anything else - it's just a list of text. Any ideas? Can anyone else see a way out of this? Sorry this is so long and messy. If you've read this far, thanks! Jim
View Replies !
Query Help Looping Through Records
The below Query loops through two tables in mysql and outputs all records where a match_date in a reports table (re) is equal to a match_date in a fixtures table (f). Now this works well, but what I want to do is say to my query is find all of these dates BUT - As soon as the FIRST f.date is found that does not have a re.date matching it output that information and limit it to 1 .....
View Replies !
Query Within An Array?
Is it possible to store an array of values in a database column and then do a query for a single value within that array? for example row 1: 1,2,3,4 row 2: 5,6,7,8 row 3: 1,3,5,7 row 4: 2,4,6 return all rows containing a 7 return = 2 and 3
View Replies !
Create An Array From Sql Query
I need help with a mysql query, I am adding some java code to a page which requires a list in array format: array("value1", "value2", "etc") I need to be able to return the results from a sql query and convert them into an array. The query looks something like: SELECT value1, value2 from some_table ORDER BY value1 ASC" I only need one of the values returned from the query in the array.
View Replies !
Query Against An Array In A BLOB?
I've inherited a problem that I'm struggling a bit with. My CMS sends email notifications (using a cron) to users when pages or comments are updated. The current problem is the system creates a new row in the notification table *every* time a change is made. So if 10 changes are made to a page between cron runs, the user gets 10 emails (when a single email would suffice). I was going to add an if statement to this function that checks to see if the $content['nid'] or $content['cid'] we are planning to insert has already been added to the table. If so, skip the insert. If not, perform the insert. Here's the function (This is MySQL via an abstraction layer. Hence the syntax): function subscriptions_hold( $content, $ptype , $op, $pid ) { $strqry = 'INSERT INTO {subscriptions_holding} ( content, ptype, op, pid ) VALUES ('%s', '%s', '%s', %d)' db_query($strqry, serialize($content), $ptype, $op, $pid); } Here's the table structure: CREATE TABLE `subscriptions_holding` ( `rid` int(11) NOT NULL auto_increment COMMENT 'Unique row ID', `content` blob NOT NULL COMMENT 'The node array', `ptype` tinytext NOT NULL COMMENT 'post type - node or comment', `op` tinytext NOT NULL COMMENT 'The operation on the node', `pid` int(11) NOT NULL default Ɔ' COMMENT 'The ID of the poster', `skip_date_first` datetime default NULL COMMENT 'First Date skipped', `skip_date_last` datetime default NULL COMMENT 'Last Date skipped', PRIMARY KEY (`rid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
View Replies !
Mutiple Table Query - Array Within Row?
I'm trying to build a query that (if it's possible to do in a single query) that can return: 1) mos_users.name 2) user1, user2, etc (which is just phone, email, address info from mos_user_extended) <- which I was able to do with LEFT JOIN 3) ires_tasks.id and ires_tasks.mls WHERE ires_map.taskID = ires_tasks.id AND ires_map.userID of mos_users.id Where I get stuck is there can be more than one "mls" returned. Can you have an array return within your row? If so, how would you break that down? I would like to get an output like ....
View Replies !
Query Limited By Contents Of An Array
I want to select all emails for volunteers that have signed up for any combination of open classes. The selected classes are contained in the array $_POST['class']. // create a comma-delimited list of classes selected $comma_separated_classes = implode(',', $_POST['class']); $query2 = "SELECT email FROM volunteers WHERE class_id IN '$comma_separated_classes' "; The WHERE . . . IN syntax isn't correct, but I can't find an example of what I need at mysql.com. What would be the correct query to select emails from all of the class_id's that are in the array?
View Replies !
Querying With Array On A Column That Contains An Array
One column of my table (that i didn't create) contains comma separated category IDs: parentlist -1 1,-1 -1 1,-1 3,1,-1 2,-1 For example, the red row is in category 3, which is in category 1 which is a parent category. I also have an array of values (2,3,4) called $catchoice. I want to find where my $catchoice and the parentlist values overlap. I know how to do this if the parentlist was only one value but since it contains multiple values I am at a lost. Do I need to combine a MATCH() function with the IN() function?
View Replies !
Query For Dates
Table Data: ----------- id__name___from_________to_______ ------------------------------------- 1___ABC___2006-10-01___2006-10-31 2___CDE___2006-11-01___2006-11-31 is there a syntax to select both rows at once? i.e. if I query for dates from 2006-10-15 to 2006-11-15, is there a possibility to have the two rows as a result of a single SQL Statement query?
View Replies !
Query Dates
I am trying to find records where my approved date is not NULL and my shipped date is NULL. Using phpMyAdmin the records show cells with italic NULL and others with dates ie. 2006-06-11, so I assume this is formated right. However using this query I get no records. SELECT * FROM records WHERE approved_date != NULL AND shipped_date = NULL I have used phpMyAdmin's search section with every conceivable way and I can't get any proper results.
View Replies !
Query Using Dates
I'm having trouble figuring out how to get two time periods in one query. Basically it's either one month at a time or a full year...So I need to get everything that starts and stops this month, or cases where this month falls between the start and stop dates. I couldn't figure out how to do the between bit, so I settled for calling an expiration date that is not older than this month.I get partially correct results. It's calling the five ads that start on 2006-12-01 and end 2006-12-31, and ignoring the ones that start 2007-01-01 and end 2007-01-31. However, I've got a test ad that starts 2006-01-01 and ends 2006-12-31 that it's ignoring...I think my timeframes are cancelling each other out or something. $sql = "SELECT ad_id, ad_filename,rally,affiliates,past,upcoming,start_date FROM ads WHERE Month(start_date)='$curmonth' AND Year(start_date)='$curyear' OR Month(end_date)>='$curmonth' AND Year(end_date)>='$curyear'ORDER BY rally='y' DESC";
View Replies !
Using Dates To Query
I have a table with a field for each record with a date in it. I want to get all records before September 12,2000 but I don't know what the query should be.
View Replies !
Dates NOT IN Query Confusion
I have a table of properties and a table of dates, each date relates to when a property is BOOKED. I am trying to write a search so that users can find available properties for their desired date range. I have a query that s working, but I think that it might need to change for other variables! Code: SELECT DISTINCT pid FROM properties WHERE pid NOT IN (SELECT pid FROM cal WHERE takendate between '2009-01-20' and '2009-01-29') AND property_publish='Y' AND property_sleeps >='1' cal table stores: pid (property id) takendate In this instance the dates 2009-01-20 -> , 2009-01-23 are TAKEN, however dates from 2009-01-24 -> 2009-01-29 are AVAILABLE. How would I flag it as available if only some of the dates are available? (eg. only hide property if ALL the date range is taken)
View Replies !
Select Query Between Two Dates
select * from table where Fri, 23 May between sdate and enddate Fri, 23 May is user given date table likes sno | sdate | enddate 1 | Mon, 19 May | Sat, 24 May 2 | Sat, 17 May | Mon, 19 May 3 | Fri, 23 May | Mon, 26 May so the above queries i need the 1 and 3 results only what is the problem in query and how to change it give solutions.
View Replies !
Looping One Record?
I have a table of records where every record has a starting date field, a time interval field, and a time spec field. Like this: id - startDate - timeInterval - timeSpec ---------------------------------------- 1 - 2008-03-05 - 2 - WEEK Now is there a way to see the records which occures in say October month using the Timeinterval field and the timeSpec field to increment through every date it occures depending on the startDate. How can this be done in SQL? Does it have to be a Procedure or can i do it in just plain SELECT queries? And is this possible at all?
View Replies !
Creating A Query Based On Dates
I am trying to write a query (in PHP) which selects from a database all of the items which are in the future. My query is as follows SELECT * FROM news WHERE ((news.date)>$today ORDER BY date where news is my database, news.date is the the field which holds the date for the item and $today will be replaced my current date. At the moment it seems to display all values, which suggest its not functioning properly.
View Replies !
Query Dates Within 1 Week From A Date
I'm fairly new to PHP and have done some basic work with mysql but am not sure how to approach this. I'm trying to figure out how to create a query with PHP to get all rows with a datetime from 1 week ago to the current timedate. Note: I'm also not sure how to find a date for 1 week ago with PHP. Any help would be great.
View Replies !
Comparing Dates Y, M, D , Time In Sql Query
ok.. ive finally got my nice little calender setup to output my dates now how do i query my database for example ive got 2 variables $startdate = 2006-1-1 00:00:01 $enddate = 2006-1-1 23:59:59 these represent the 1st and last second of the first of january 2006 i have a cell in my table (table called challenges) ( cell called time) that contains a date in the same format for each entry how do i get all the entries between the start time and the end time.. can i use less than < and more than > as these are not really integar values the column type is datetime but there is also another column of type datetime
View Replies !
Avoid Looping In Queries
I have a table structure as shown below slno | filename | parent | type 0 | Button | awt | file 1 | awt | java | folder I am making 2 database calls till I get to parent=java. Below is a very simple query that I am using select parent FROM api where filename=Button The above query would return me "awt" since awt is not equal to java, I am looping to make another query by taking the value select parent FROM api where filename=awt This query would return me "java" and the condition is satisfied.I wanted to avoid doing that and get that in a single statement.
View Replies !
Looping Through Database Objects
I am trying to create a process that loops through all the databases on my server (75) and run the same set of sql statements on each database. Here is what I have so far... begin declare dbname char(15); SELECT schema_name INTO dbname FROM information_schema.schemata WHERE schema_name like "acc340%" LIMIT 1; while dbname IS not null do (this is what doesn't work) SELECT dbname; SELECT schema_name INTO dbname FROM information_schema.schemata WHERE schema_name > dbname AND schema_name like "acc340%" LIMIT 1; end while; end The loop works but since the select into never changes the value to null after it runs out of data, my loop continues forever. Is there a way to get around this annoying problem?
View Replies !
Write A Script With Looping Constructs
I want to write a script to insert some rows in a database. The user of the script will login to mysql and paste the script at this point. Is there a way to use looping constructs, variables, etc. Iam thinking of a PL/SQL equivalent.
View Replies !
Looping Through Insert Statements Is Timing Out, Alternative?
I am looping through and creating about 600 inserts/executes in PHP and it's timing out. I have tried aggregating them into one string that ends up like: INSERT INTO table VALUES(val1, val2,...);INSERT INTO table VALUES(val3, val4,...);INSERT INTO table VALUES(val5, val6,...);... But is errors out. I am using PHP5 and MySQL 4.1. How can I get around that? Is there an alternative? If prepared statement is a way around it, any easy examples to look at?
View Replies !
Looping To Find Future 6 Month Intervals
I have been trying to find a way to set an interval of 6 months and then calculate 6 month intervals from a certain date. I know that I can do this one-time: Quote: SELECT DATE_ADD("2008-11-03", INTERVAL 6 Month ) as threshold however this will not work in a loop and using it this way only provides a proper value once. Does anyone know if this can be done in some kind of MySQL loop or some other way? BTW...I did try this using unix timestamps however It was not precise enough; by the time one year had been added the 2008-11-03 became 2008-11-01, so unfortunately it did not serve the purpose.
View Replies !
Converting MS Access Dates To MySQL Dates During LOAD DATA INPUT ?
My insert code is below. The MySQL server is on my local machine and is version 4.1.22. I am exporting from an access table with 6 fields to a mysql table with those 6 plus 5 more fields. The main problem I am having SO FAR, is converting dates. The data file dates are formated like: 2/2/2006 0:00:00, 12/20/2006 9:22:05 Any ideas how to format those into mysql friendly dates? Also, how do I convert currency fields in the load data process? I know you use SET by what kind of formula? =========================================== LOAD DATA INFILE 'C:Documents and Settingspath_to_filedata.txt' INTO TABLE auto (field2, field3, field4, field5, currency_field6, field7, date_field8, date_field9) SET id = MD5(UUID()) FIELDS TERMINATED BY ',' LINES TERMINATED BY ' '
View Replies !
Update String Dates Into Mysql Dates
I've got a table that someone created with varchar fields for month, day, and year. I've added a DATE field to the table, and now I want to combine all three of these strings into one DATE and stick it into the DATE field. Once I've got this done, I can delete those three varchar fields and just have the nice DATE field. Here's the command I've tried in several variations but no luck--syntax errors every time: UPDATE SET OCR_Entry_Date = date_format(str_to_date(concat_ws('-', OCR_MM, OCR_DD , OCR_YYYY) ,"%b-%d-%Y"), "%m-%d-%Y") WHERE OCR_YYYY = '1974' OCR_Entry_Date is a DATE field and the others are the varchar fields. My logic is basically: 1. concatenate the separate field values into a single string 2. convert the string into a date 3. format the date in the way I'd like it in the table
View Replies !
MSSQL Dates -> MySQL Dates Automatically?
We're using data feeds that were originally meant for MS SQL and the dates in the data feed (tab delimited text files) are formated like: Oct 21 2007 In MySQL, the date fields are formatted as datetime fields and when we do an import the dates all come through as 0000-00-00 00:00:00. We're importing using the MySQL "LOAD DATA LOCAL INFILE" command and just dumping the text files right into freshly truncated tables. Is there a way when loading the files to find and replace the dates maybe? Can MySQL convert the dates? I have a shell script downloading an archive and uncompressing, then it runs a PHP script that loops through the files runningt he LOAD DATA command. Any advice would be appreciated... I'm just looking for the easiest (and least server-intensive) way to get the date issue fixed. We've already tried begging the vendor but they're not yet ready to start supporting other date formats (even though their MS SQL db can output a date format friendly to MySQL). Thanks!
View Replies !
List All Dates Between 2 Diff Dates
I have a database which stored financial data daily exclude weekend, however sometimes i tend to forget to upload data into the database. How do i write a sql query that would detect the missing dates between from last updated date until the current date.
View Replies !
Dates Pre 1970 And Dates After 2050
i have MySQL version: 4.1.16-nt, when i try to insert a date pre 1970 or after 2050 into a timestamp field, the date is stored as all 0's, how can i save a date pre 1970 and after 2050 into a mysql field?
View Replies !
List All The Dates Between 2 Given Dates Using SELECT
how I can list all the dates between 2 dates (inclusive) using SELECT sql statement in mysql? I have a member table with the following columns: id int autoincrement name varchar(40) join_date date I want the following output (date, count of members joined on that date): 1 April 2009...... (2 members joined) 2 April 2009...... (0 members joined) 3 April 2009...... (3 members joined) .....so on ...upto 30 April 2009.....(1 members joined) For the above output I need the SQL command that is valid in mysql. Is there anyone who can produce the above result with a single SELECT command. Please don't suggest that is possible using php for loop or creating a mysqlorary table first and then inserting all the dates into it. Then make a join between mysql table and members table using date as GROUP by column etc. etc. I know this is possible using some user variables as I searched through the various SQL tutorial sites. But I am unable to figure out how I can produce a list of all dates between 2 given dates.
View Replies !
Stroring Dates Perior To 1 AD, BC Dates
I need to store some dates that can be BC, However I received a problem indicating Incorrect Datetime. I have read the documentation, and it says that Date&DateTime types can store from 1000-9999 AD, and Timestamp stores less year ranges. What other options do I have to store dates before 1000 AD !
View Replies !
How To List All Missing Dates Between 2 Dates
I have a database which stored financial data daily exclude weekend, however sometimes i tend to forget to upload data into the database. How do i write a sql query that would detect the missing dates between from last updated date until the current date.
View Replies !
Dates, Dates, Dates - Syntax Help Please!
OK, I have a MySQL table which contains, among others, a field called date which stores dates in the format YYYY-MM-DD HH:MM:SS. What I'd like to do is select a list of all the months present (without repetition), preferably in the format YYYY-mm
View Replies !
My SQL Array And PHP
I am using a select statement to obtain a table from mysql database. I want to store that table(with two columns Quesid, Numans) in an array which is sorted according to Numans. My table looks like this: Quesid Numans 3 5 2 3 4 2 1 2 5 1 Now I want to store first field of column Numans which is 5 into a variable, divide it by two and then parse through all the rows of the array and print that Quesid whcih is closest to 5/2. Once printed again go through the array with same condition and again print the next closest one.
View Replies !
Array
see i have two tables.technology and category.In technology i have the fields id(auto_increment primary) and name. In category i have id(auto_increment primary),categoryname and categoryid. In the first table i insert the name and the value of the id is auto_increment. now am going to insert the categoryname in the category table by using technology.id=category.categoryid.(the name in the technology table ). how can i use the array .
View Replies !
Where = Array(
Ok i was reading a book and i saw something i had never seen before. THey were using something like where `id` = array(1,2) Or atleast something similar,. UNfortunately i forgot the correct syntax and cant remember which book it was. Whats the correct syntax?
View Replies !
Array Parameter
since mysql's stored procedure does not accept an array as a parameter, is it a good practice to input a string delimited parameter eg. call stored_procedure(param1, param2, 'value1:value2:value3');
View Replies !
Creating An Array
I want to select a bunch of email addresses from a mysql table and format them to use with a php mail() function. I can select them from the database, but how do I format them so there's a comma and space after every one?
View Replies !
Use An Array Column
I am building a photo album webpage and am not sure exactly how I should organize the database. Here's where Im at so far... I have one table called 'images' which contains the columns 'id','albumID', 'url', 'title', and 'description'. Then another table called 'albums' which contains the columns 'id' and 'name'. The part where I am having the trouble is the 'images.albumID' column. Some photos can be apart of mutliple albums, so should the 'albumID' column be an array type?
View Replies !
When To Use An Array Column?
I am building a photo album webpage and am not sure exactly how I should organize the database. Here's where Im at so far... I have one table called 'images' which contains the columns 'id','albumID', 'url', 'title', and 'description'. Then another table called 'albums' which contains the columns 'id' and 'name'. The part where I am having the trouble is the 'images.albumID' column. Some photos can be apart of mutliple albums, so should the 'albumID' column be an array type?
View Replies !
Array Or List
I need to make a list of all the products a client has ever ordered or that they are interested in so that later I can see which clients might be interested in a specific product. What's the best way for me to do this? Probably with JOINS, but I'm not sure how. Doesn't seem right to create a new field for eachof 200+ products.
View Replies !
Store An Array
I'm using PHP to acces my MySQL database; Now, I would like to store an array in the database. I was very suprised to see that there is no array-type field (samen with boolean? why didnt they make a boolean field?!). Anyone an idea how I can save and recover my array from the db
View Replies !
|
TRACKER
