Re: newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



> 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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux