[SOLVED] replace strings from another mysql table and then echo

[dezkit]

hello guys.

 

i have 2 tables,

one has admin_id,server_id (amx_admins_servers)

and the other has id,nickname (amx_amxadmins)

 

is there a way to echo out the amx_admins_servers table but replace the admin_id with nickname in amx_amxadmins? Thank you

 

here is some failed relevant code:

<?php
$query = "SELECT * FROM amx_admins_servers ORDER BY `amx_admins_servers`.`admin_id` ASC"; 	
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){echo $row['admin_id'];echo "<br />";}

$query1 = "SELECT * FROM amx_amxadmins"; 
$result1 = mysql_query($query1) or die(mysql_error());
$row1 = mysql_fetch_array($result1);
while($row1 = mysql_fetch_array($result1)){echo $row1['id'];echo "<br />";}

str_replace($row['admin_id'],$row1['id'],$row);

?>

[ignace]

What do you have now in both tables and how does it need to be?

[dezkit]

amx_admin_servers

(admin_id server_id)

1 1

1 2

1 3

2 1

2 2

3 1

3 2

3 3

 

 

amx_amxadmins

(id nickname)

1 john

2 doe

3 danny

 

 

 

This is just an example,the real tables have around 100 data

[ignace]

And what do you want to change?

[dezkit]

change admin_id in amx_admin_servers to nickname in amx_amxadmins but echoing the server id beside it as well.

 

 

[ignace]

You can do that by using a simple join:

 

SELECT amx_amxadmins.id, amx_amxadmins.nickname
FROM amx_amxadmins
JOIN amx_admin_servers
ON id = admin_id
WHERE server_id = $server_id

[dezkit]

I keep getting an error

here is my current code:

<?
$query = "SELECT amx_amxadmins.id, amx_amxadmins.nickname FROM amx_amxadmins JOIN amx_admin_servers ON id = admin_id WHERE server_id = $server_id"; 	
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
echo $row['nickname']. " - ". $row['server_id'];
echo "<br />";
}

?>

[dezkit]

Oh nvm I got it thank you,

but now how do I get the hostname to replace the server_id from another table as well?

 

 

amx_admin_servers

(admin_id server_id)

1 1

1 2

1 3

2 1

2 2

3 1

3 2

3 3

 

 

amx_amxadmins

(id nickname)

1 john

2 doe

3 danny

 

amx_serverinfo

(id hostname)

1 hello

2 test

3 whatever

 

 

 

Relevant code:

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

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

?>

[micmania1]

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

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

?>

[dezkit]

I'm sorry, what is that? lol

[ignace]

SELECT admin_id, server_id, nickname, hostname
  FROM amx_amxadmins a, amx_serverinfo s, amx_admin_servers c
WHERE c.admin_id = a.id
   AND c.server_id = s.id
   AND a.id = $admin_id

[dezkit]

Still didn't work... thanks for the code though

 

Current code:

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

?>

[dezkit]

Nevermind, I got the code, thanks guys, another problem, how do i make so the server_id's are on the same line

 

AND: how do i do so that the people with the most server_id assigned to them get on top

i.e. (nickname - 1,2,3,4,5,6,7)

 

 

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 />";
}


?>

[dezkit]

Anybody can help?

[ignace]

Nevermind, I got the code, thanks guys, another problem, how do i make so the server_id's are on the same line

 

AND: how do i do so that the people with the most server_id assigned to them get on top

i.e. (nickname - 1,2,3,4,5,6,7)

 

ORDER BY server_id