Mergin queries

[pedro84]

Got problem. I need to merge three queries, but when I use UNION I got

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in D:\EasyPHP 2.0b1\www\bootleg2\includes\functions.php on line 90

 

Line 90:

    while($r = mysql_fetch_assoc($query)) {
        echo "<h2 style='padding-bottom:8px; font-size:1.2em;'>";
	echo $r['tmp']; 

 

Queeries to be merged:

SELECT * FROM shows where (band='$band') and (format='video') ORDER BY date
SELECT *, COUNT(band) FROM shows where (band='$band') and (format='video') GROUP BY band
SELECT DISTINCT(band) AS band FROM shows where format='video' ORDER BY band asc

 

Any suggestions?

[Barand]

When you use UNION the result is a single set with one set of column names and types, therefore each select in the union must result in the same number of columns of the same types.

 

Doesn't work

SELECT a, b, c FROM X

UNION

SELECT g, h FROM Y

 

OK

SELECT a, b, c FROM X

UNION

SELECT g, h, 'dummy' FROM Y

[pedro84]

Thanks for reply.

 

This is first time I use UNION.

 

Got the same error:

$query = mysql_query("SELECT DISTINCT(band) FROM shows where format='video' ORDER BY band UNION SELECT * COUNT(band) FROM shows where format='video' ORDER BY band");
    while($r = mysql_fetch_assoc($query)) {

[Barand]

Thanks for reply.

 

You're welcome. Now read it.

 

And check for error messages

 

$query = mysql_query("SELECT DISTINCT(band) FROM shows where format='video' ORDER BY band UNION SELECT * COUNT(band) FROM shows where format='video' ORDER BY band") or die(mysql_error());

[Barand]

is this anything like what you are trying to do, because your queries just aren't suitable for union?

 

TEST DATA

[pre]

+----+---------+--------+------------+----------+

| id | band    | format | date      | venue    |

+----+---------+--------+------------+----------+

|  1 | band a  | video  | 2008-01-01 | London  |

|  2 | band b  | video  | 2008-01-02 | Madrid  |

|  3 | band a  | cd    | 2008-01-03 | Rome    |

|  4 | band c  | cd    | 2008-01-04 | Paris    |

|  5 | band d  | video  | 2008-01-05 | Brussels |

|  6 | band a  | video  | 2007-12-24 | Lisbon  |

+----+---------+--------+------------+----------+

[/pre]

 

Query

SELECT s.band, COUNT(*) as showcount,
GROUP_CONCAT(date, ' - ', venue ORDER BY date SEPARATOR '<br>') as dates
FROM shows s
WHERE format = 'video'
GROUP BY band 

 

Results -->

[pre]

+--------+-----------+--------------------------------------------+

| band  | showcount | dates                                      |

+--------+-----------+--------------------------------------------+

| band a |        2 | 2007-12-24 - Lisbon                        |

|        |          | 2008-01-01 - London                        |

| band b |        1 | 2008-01-02 - Madrid                        |

| band d |        1 | 2008-01-05 - Brussels                      |

+--------+-----------+--------------------------------------------+