Jump to content

Mergin queries


pedro84

Recommended Posts

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?

Link to comment
https://forums.phpfreaks.com/topic/97474-mergin-queries/
Share on other sites

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

Link to comment
https://forums.phpfreaks.com/topic/97474-mergin-queries/#findComment-498731
Share on other sites

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)) {

Link to comment
https://forums.phpfreaks.com/topic/97474-mergin-queries/#findComment-498735
Share on other sites

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());

Link to comment
https://forums.phpfreaks.com/topic/97474-mergin-queries/#findComment-498741
Share on other sites

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                      |

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

Link to comment
https://forums.phpfreaks.com/topic/97474-mergin-queries/#findComment-498814
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.