Echo merged data into one row

[dezkit]

Hello guys.

 

I have a mysql structure like this

 

nickname server_id

1 1

1 2

1 3

1 4

1 5

2 1

2 2

2 3

 

How do I echo it automatically merged into the rows into one nickname if there is more than one server_id assigned to a nickname, something like this is what I need:

 

1 - 1 2 3 4 5

2 - 1 2 3

 

My current code:

<?php
$query = "
SELECT admin_id, server_id, nickname, hostname
  FROM amx_amxadmins a, amx_serverinfo s, amx_admins_servers c
WHERE c.admin_id = a.id
   AND c.server_id = s.id
   AND a.id = c.admin_id"; 
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
echo $row['nickname']. " - ". $row['server_id'];
echo "<br />";
}


?>

 

Thank you so much for any responses!

[taquitosensei]

something like this

$query = "
SELECT admin_id, server_id, nickname, hostname
  FROM amx_amxadmins a, amx_serverinfo s, amx_admins_servers c
WHERE c.admin_id = a.id
   AND c.server_id = s.id
   AND a.id = c.admin_id"; 
$result = mysql_query($query) or die(mysql_error());
$oldnick="";
while($row = mysql_fetch_array($result)){
echo ($oldnick!=$row['nickname'])?"<br>".$row['nickname']:$row['server_id']." "; 
$oldnick=$row['nickname']; 
}

[dezkit]

Thank you so much for the response but I have a certain problem, it doesn't echo the server_id if the person only has 1 server_id assigned to the nickname

 

$query = "
SELECT admin_id, server_id, nickname, hostname
  FROM amx_amxadmins a, amx_serverinfo s, amx_admins_servers c
WHERE c.admin_id = a.id
   AND c.server_id = s.id
   AND a.id = c.admin_id"; 
$result = mysql_query($query) or die(mysql_error());
$oldnick="";
while($row = mysql_fetch_array($result)){
echo ($oldnick!=$row['nickname'])?"<br>".$row['nickname']:$row['server_id']." "; 
$oldnick=$row['nickname']; 
}

 

current code if needed, thanks again

[dezkit]

Nevermind, I got another code to do something else, here it is

 

$query = "
SELECT admin_id, server_id, nickname, hostname
  FROM amx_amxadmins a, amx_serverinfo s, amx_admins_servers c
WHERE c.admin_id = a.id
   AND c.server_id = s.id
   AND a.id = c.admin_id
   GROUP BY a.id"; 
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
echo "<tr><td>".$row['nickname']."</td>";
if($row['server_id'] == "1"){
echo "<td><img src='http://xxx.net/yes.gif' alt=''></td>";
} else {
echo "<td><img src='http://xxx.net/mo.gif' alt=''></td>";
}
echo "</tr>";

}

 

I am making so that if the nickname has a server_id with a "1" assigned to it, it will be displayed with an image, but the problem is, is that it shows to only a certain amount of nicknames, how i fix?

 

Thank you.

[ozestretch]

I assume nicks with more than 1 'a.id' only display once?

 

GROUP BY will do that. Why do you need GROUP BY?

[dezkit]

I have no idea.

 

Why does this show up anyways?

 

 

I changed GROUP BY to ORDER BY.

[dezkit]

Sorry the last image didn't show what i want to do,

this is what i have now

 

 

i want to have all the checkmarks on one line for nickname, how do i do this?

[ozestretch]

Now you get to use GROUP BY 

<?php
$query = "
SELECT admin_id, server_id, nickname, hostname
  FROM amx_amxadmins a, amx_serverinfo s, amx_admins_servers c
WHERE c.admin_id = a.id
   AND c.server_id = s.id
   AND a.id = c.admin_id
   GROUP BY a.nickname";
$result = mysql_query($query) or die(mysql_error());
   
while($row = mysql_fetch_array($result)){
echo "<tr><td>".$row['nickname']."</td>";
if($row['server_id'] == "1"){
   echo "<td><img src='http://xxx.net/yes.gif' alt=''></td>";
   } else {
   echo "<td><img src='http://xxx.net/mo.gif' alt=''></td>";
   }
echo "</tr>";
   
}?>

[dezkit]

OMG this is so frustrating, to keep things simple now, is there a way to turn the nicknames into an array?

 

for example

 

nickname server_id

1 1

1 2

1 3

1 4

1 5

2 1

2 2

2 3

 

into

 

nickname server_id

1 1|2|3|4|5

2 1|2|3

 

 

thank you guys if you reply.

[ozestretch]

Ehmm, Yep. GROUP BY ... but this time not by id, by nickname. Did you try that above? What happened?

[dezkit]

I changed my mind, and yeah it didn't work. my problem will be solved if only i knew how to make nickname into an array containing all the server_id's itslinked to.

[ozestretch]

Just for the record, you been asking mysql question in the php forum...

 

while($row = mysql_fetch_array($result)){
// code here
}

 

that is actually creating an Array, hence mysql_fetch_array

 

 

[dezkit]

Yeah i know, but you don't understand, this is what i want something like

 

array("1"=>"1,2,3,4,5","2"=>"1,2,3")

 

not

 

array("1"=>"1","1"=>"2","1"=>"3","1"=>"4","1"=>"5","2"=>"1","2"=>"2","2"=>"3")

[ozestretch]

Ohhh, forgot about GROUP_CONCAT ... I will have a quick play

[ozestretch]

Since I know mysql can do it, there is no need making a messy loop.

 

Like I said, this is a sql question, but I giving a try till someone better comes along.

 

$query = "SELECT GROUP_CONCAT(nickname ORDER BY id DESC) AS `nickname`, 
GROUP_CONCAT(server_id ORDER BY id DESC) AS `server_id` 
FROM amx_amxadmins a, amx_serverinfo s, amx_admins_servers c 
WHERE c.admin_id = a.id 
AND c.server_id = s.id 
AND a.id = c.admin_id";

 

but I may be off still 

[taquitosensei]

you first query would do that

$query = "
SELECT admin_id, server_id, nickname, hostname
  FROM amx_amxadmins a, amx_serverinfo s, amx_admins_servers c
WHERE c.admin_id = a.id
   AND c.server_id = s.id
   AND a.id = c.admin_id"; 
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
$nicks[$row['nickname']][]=$row['server_id']; 
}

then

foreach($nicks as $nickname=>$serverids)
{
echo $nickname." ".implode(" ",$serverids); 
}

[dezkit]

you first query would do that

$query = "
SELECT admin_id, server_id, nickname, hostname
  FROM amx_amxadmins a, amx_serverinfo s, amx_admins_servers c
WHERE c.admin_id = a.id
   AND c.server_id = s.id
   AND a.id = c.admin_id"; 
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
$nicks[$row['nickname']][]=$row['server_id']; 
}

then

foreach($nicks as $nickname=>$serverids)
{
echo $nickname." ".implode(" ",$serverids); 
}

 

HOLY JESUS.

 

It worked! Thank you soo much!!

 

I've been looking into my laptop screen for 4 hours thinking how to solve my problem, and you came and solved it. Thank you and to the others in the thread!

 

Is there also a way to make so that the nicknames that have the most server_id's assigned to them get to be on the list first? Thank you again!

[dezkit]

Bump before i got to bed

[dezkit]

anybody?