Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
except that I just added the ORDER BY clause onto each SELECT segment, and now I get this error: query failed: Incorrect usage of UNION and ORDER BY How can I order the results while still doing the UNION ALLs? You should only need one ORDER BY clause at the end of the whole query: (SELECT...) UNION ALL (SELECT...) UNION ALL (SELECT...) ORDER BY... I'm not sure if this syntax is portable to other SQL-based DBMSes, though. I'm certain that one system we use at work doesn't support it, but it's kind of a dinosaur so I don't like to draw too many conclusions from what it doesn't support. Ben -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date(DAY!)* in a timestamp column?
Just one other tiny point of style here: having given the expression date(solarAWDateTime) the alias uniqueDate, you should probably use that alias to refer to the same thing elsewhere in your query, such as in the GROUP BY column. So: [8] That's a mysqlism :( It's not portable to other db's (apparently it's not part of the sql-spec). I think I've even seen MySQL reject it in some cases. Ben -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Can someone point me to understand why? I thought that: SELECT COUNT(*) AS landing_count, date(solarLandingDateTime) AS solarLandingDate, 't7solar_landingALIAS' AS t7solar_landing FROM t7solar_landing GROUP BY DATE(solarLandingDateTime) would, among other thing, assign the number of records counted in that first table to the alias 'landing_count'. No? Yes, but when you UNION that query with others it gets a bit more complicated. The UNION keyword adds more rows to the result-set, not more columns. So, when you join queries with a UNION, corresponding columns in each of those queries should all have the same aliases. So you probably don't want to say: SELECT nameABC AS `name1` FROM table1 UNION ALL SELECT nameDEF AS `name2` FROM table2 Here you're asking the DBMS to give you a result-set with just one column, in which the column is called `name1` some of the time, and `name2` the rest of the time. Doesn't make much sense, and MySQL will silently ignore one or the other of these aliases. Instead, say: SELECT nameABC AS `name` FROM table1 UNION ALL SELECT nameDEF AS `name` FROM table2 This will produce a result-set with just one column, called `name`. The number of rows in the result-set will equal the number of rows produced by the first SELECT, plus the number of rows produced by the second SELECT. Does that help make sense of why you need to add a second column to each query, with the name of the table? Like so: SELECT nameABC AS `name`, 'table1' AS `table_name` FROM table1 UNION ALL SELECT nameDEF AS `name`, 'table2' AS `table_name` FROM table2 This query will produce a result-set with two columns. The first column will be called `name` and the second will be called `table_name`; for example, supposing that table1 contains only boys' names and table2 contains only girls' names, you might get a result-set that includes these rows: name| table_name Robert | table1 James | table1 Lucy| table2 Teresa | table2 Then for each row, you would need to examine the value of the `table_name` column in PHP, to figure out which table the name is from. It looks like your current code is operating as though each row contains results from all three of your tables, which it doesn't. Each row only contains a result from one table. BTW, mysql_fetch_assoc() returns an array, not an object, so you'd need to use this syntax: $row['column'] As opposed to: $row-column If you prefer the latter syntax, you can use mysql_fetch_object(). Ben -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Govinda wrote: .. so my thought is to want to do nested query(ies), where: *within* the while loop of the first recordset (which is now successfully returning just rows with unique dates), I do other query(ies) which will (in their own code block) find all rows of the date we are iterating.. so I can, for example, count number of records for each unique date, do math/statistics, etc. I had to do something similar in code of my own a little while ago, and got some very good guidance on Stack Overflow. Here's the thread, you might find it helpful: http://stackoverflow.com/questions/946214/one-sql-query-or-many-in-a-loop The user whose answer is marked as the correct one (Quassnoi) also writes a helpful blog on SQL. You should be able to find the blog by clicking on the username. Ben -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
...which will echo: trtd#records in 't7solar_landing' matching the given (iterating) date (in the 'solarLandingDateTime' column)/tdtd#records in 'aw_7solar_confirm' matching the given (iterating) date (in the 'solarAwConfDateTime' column)/tdtd#records in 'aw_7solar_aw' matching the given (iterating) date (in the 'solarAWDateTime' column)/td/tr... If you just need to count the records with a particular date you should be able to use this construction: SELECT COUNT(*) AS `record_count`, DATE(date_column) AS `date_field` FROM table GROUP BY `date_field` You could probably write a generalized PHP function (called 'build_query()' or something) that would construct this query given a table name and a date-column name, and call it once for each table/column pair. Then you could stitch the three query strings together, in PHP, into one large query using SQL's UNION ALL, which concatenates the results of multiple queries into one large result-set: (query 1) UNION ALL (query 2) UNION ALL (query 3) And then pass that one large query to the database. So I need to read/learn more MySQL. Can you guys point me to where in the mysql docs I should be burying myself? In my experience the MySQL manual isn't a great resource for learning SQL, at the level you're looking for. It's a fine reference if you already have a solid understanding of the basics. But to get that understanding, you might try the O'Reilly book called Learning SQL: http://oreilly.com/catalog/9780596520830/?CMP=AFC-ak_bookATT=Learning+SQL%2c+Second+Edition%2c Someone else here might know of some good online resources. I've not seen any, but then I haven't spent a whole lot of time looking. The parts of Learning SQL that I've seen are excellent. Ben -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
