MYSQL query change

[sp@rky13]

Ok, so I was given the following code here but I don't get it. ok so I've looked into CONCAT_WS and what it is but still don't get how to use it. so what I want to be able to do is instead of having the result like this:

397|593 505|657 433|658

I need it like this:

 

[village]397|593[/village]

[village]505|657[/village

[village]433|658[/village]

 

I've made this change: to the code to do add in the line break:

SEPARATOR '</br>'

 

but I need to add in the [village][/village]

 

Here's the full code I'm working with before any changes I've tried:

<?php
$con = mysql_connect("localhost","","");
if (!$con)
{
   die('Could not connect: ' . mysql_error());
}

mysql_select_db("wwwspark_tribalwars", $con);
  
$result = mysql_query("SELECT GROUP_CONCAT(CONCAT_WS('|',b.x,b.y) SEPARATOR ' ') AS xy FROM players_en28 a INNER JOIN villages_en28 b ON a.id = b.player WHERE a.name = 'sp@rky13' GROUP BY a.id");

while($row = mysql_fetch_array($result))
{
echo $row['xy']; 
}
mysql_close($con);
?>

[Bjom]

I don't know about group concat and concat ws, but I believe a simple concat will do...(getting rid of the group by at the same time...)

 

try this:

 

$result = mysql_query("SELECT CONCAT('[village]', b.x, '|', b.y, '[/village]') AS xy FROM players_en28 a INNER JOIN villages_en28 b ON a.id = b.player WHERE a.name = 'sp@rky13' ORDER BY a.id;

[sp@rky13]

I'll try that. Can you link me a site that explains how it works and such. I don't gwet it so can't really edit it or if you don't know one can you explain each but please. tyvm

 

EDIT: Also how do I add in the seperator in there?

[Bjom]

the best site with explanations is the mysql site:

for example:

CONCAT

 

have a good read and if you still got questions, we're here to help

[fenway]

Show us some sample output without all that group by stuff.

[sp@rky13]

That's the site I've looked at before but as it doesn't include some of the stuff in the code I was still confused

 

Sample output: (pressuming you mean what I am trying to have outputted)

 

(Was in the first post)

[village]397|593[/village]

[village]505|657[/village]

[village]433|658[/village]

[sp@rky13]

BUMP. So how can I do this?

[Bjom]

I've sent you a query that does it. You have not yet commented on whether or not it worked as you liked or what's the problem.

[sp@rky13]

This one: http://www.phpfreaks.com/forums/index.php/topic,264144.msg1245326.html#msg1245326. It worked fine except I can't get it to have a line break/break of any kind. Also is there a way to hav the results be in 2 seperate columns. So have the x and y column seperate. Then I can more easily mess with it

[Bjom]

you dont seem to be clear yourself as what you want and in what place. if you want it all in one line - this query is the way to go. if you need single values to "mess" with, then you need no concatenation at all, simply get the values and do the rest in php. I suggest you look for a few tutorials on how to connect and query databases and to read the chapter on SELECT in the MySQL manual and alos the chapter on mysqli in the PHP manual.