I have been experiencing this problem for some time now and as I am pretty new to MySQL having been a MS SQL developer for some time so I am scratching my head due to not being able to find an explanation.
I have some PHP code that is building an XML file using a Wordpress DB (myISAM tables) and it is running the following SQL statement which concats all the columns together and inserts the relevant value (in this case the tag slug) into the URL.
SELECT CONCAT(' <url> <loc>',REPLACE('
http://www.mysite.com/tag/%tag%/','%tag%',t.slug),'</loc>; <lastmod>',REPLACE(NOW(),' ','T'),'Z</lastmod> <changefreq>always</changefreq> <priority>1.0</priority> </url>') as XML FROM wp_terms AS t JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('post_tag') ORDER BY Name;
When I run this query in Navicat or with the Wordpress SQL functions or the standard mysql_query functions it runs correctly returning all the rows with the appropriate tag values inserted into the correct %tag% place-holders within the XML e.g
<url> <loc>
http://www.mysite.com/tag/Sales/</loc>; <lastmod>2010-09-08T00:37:25Z</lastmod> <changefreq>always</changefreq> <priority>1.0</priority> </url>
<url> <loc>
http://www.hottospot.com/tag/Spain/</loc>; <lastmod>2010-09-08T00:37:25Z</lastmod> <changefreq>always</changefreq> <priority>1.0</priority> </url>
<url> <loc>
http://www.hottospot.com/tag/2020/</loc>; <lastmod>2010-09-08T00:37:25Z</lastmod> <changefreq>always</changefreq> <priority>1.0</priority> </url>
However when I use mysql_unbuffered_query to run this I get the problem that all rows contain the same data e.g
<url> <loc>
http://www.mysite.com/tag/Sales/</loc>; <lastmod>2010-09-08T00:37:25Z</lastmod> <changefreq>always</changefreq> <priority>1.0</priority> </url>
<url> <loc>
http://www.hottospot.com/tag/Sales/</loc>; <lastmod>2010-09-08T00:37:25Z</lastmod> <changefreq>always</changefreq> <priority>1.0</priority> </url>
<url> <loc>
http://www.hottospot.com/tag/Sales/</loc>; <lastmod>2010-09-08T00:37:25Z</lastmod> <changefreq>always</changefreq> <priority>1.0</priority> </url>
Even if I break the query down to something simple like this REPLACE without the CONCAT it still misbehaves
SELECT t.slug,REPLACE('
http://www.hottospot.com/tag/%tag%/','%tag%',t.slug)
FROM wp_terms AS t
JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id
WHERE tt.taxonomy IN ('post_tag') ORDER BY Name;
I get the same value in the 2nd column e.g
Col 1 Col 2
Sales
http://www.mysite.com/tag/Sales
Spain
http://www.mysite.com/tag/Sales
2012
http://www.mysite.com/tag/Sales
It definitely seems to be connected to the execution of mysql_unbuffered_query as I can have Navicat open on my office PC connected to my remote server and all these queries run correctly but as soon as on my laptop at home I run the mysql_unbuffered_query query through the website to build the XML everything goes tits up. If I hit the refresh button on the query open in Navicat on my office PC which had been returning the correct results they then come back like I have described with all the values for rows 2 onwards displaying the value from the first row.
When I run the mysql_unbuffered_query I am providing a link identifier parameter so I was under the impression that the connection was sandboxed and not able to interfere with existing connections but I must be wrong as it seems to interfere with my existing open connection in Navicat.
Am I right to assume that mysql_unbuffered_query cannot be used with MySQL statements that GROUP, REPLACE or CONCAT? I don't know much about the MySQL engine but it seems that there might be an issue regarding the point in time MySQL applies these functions as if the results have already been sent to the client its unable to carry out the necessary replace operations?
Any information regarding this issue would be great.