[SOLVED] MYSQL Querries

[sp@rky13]

Ok, so I've worked on this off and on in the posts you can see that I've posted. I finally got back to it. Anyway so this is the idea I started with:

 

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

  mysql_select_db("wwwspark_tribalwars", $con);

$result = mysql_query("SELECT * FROM players_en28 WHERE name = '$vpt_sub'");

while($row = mysql_fetch_array($result))
  {
  $id = $row['id'];
  }

$result2 = mysql_query("SELECT * FROM villages_en28 WHERE player = '".$id."'");

while($row2 = mysql_fetch_array($result2))
  {
  echo $row2['x']."|".$row2['y']." ";
  }
mysql_close($con);
?>

 

and then I was told to try this:

 

<?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 = '$vpt_sub' GROUP BY a.id");

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

 

I was able to customize the original to how I wanted but not the second. What I did with the original was this:

 

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

  mysql_select_db("wwwspark_tribalwars", $con);

$result = mysql_query("SELECT * FROM players_en28 WHERE name = '$vpt_sub'");

while($row = mysql_fetch_array($result))
  {
  $id = $row['id'];
  }

$result2 = mysql_query("SELECT * FROM villages_en28 WHERE player = '".$id."'[b] AND x BETWEEN $x1 AND $x2 AND y BETWEEN $y1 AND $y2[/b]");

while($row2 = mysql_fetch_array($result2))
  {
  echo $row2['x']."|".$row2['y']." ";
  }
mysql_close($con);
?>

 

How can I do this in a code similar to the concat one?

 

I also need to do that all with one like this:

 

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

mysql_select_db("wwwspark_tribalwars", $con);

$result = mysql_query("SELECT * FROM ally_en28 WHERE tag = '$vpt_sub'");

while($row = mysql_fetch_array($result))
{
$id = $row['id'];
$result2 = mysql_query("SELECT * FROM players_en28 WHERE ally = '".$id."'");
while($row2 = mysql_fetch_array($result2))
{
$id2 = $row2['id'];
$result3 = mysql_query("SELECT * FROM villages_en28 WHERE player = '".$id2."'");

while($row3 = mysql_fetch_array($result3))
{
echo $row3['x']."|".$row3['y']." ";
}

}
}
mysql_close($con);
?>

[anatak]

I am sorry I really cann't answer your problem but I don't think it is wise to post your db connection details online.

[sp@rky13]

Why can't I edit my post. So stupid. Time to change the password and sch. It only gives access, not editing for that pass. Mod please edit the post for me ASAP.

 

EDIT: So I can edit this but not the other. So stupid

[kickstart]

Hi

 

You should be able to just put the check for x and y within the WHERE clause.

 

All the best

 

Keith

[sp@rky13]

Dude, I'm stupid. Thank you. I will try that tomorrow No more

[anatak]

hey Sparky you can only edit your post for a certain time.

[sp@rky13]

Yah, I found that out afterwards. Why???? I now have had that password up for 12 hours and I can't get rid of it

[sp@rky13]

Ok, so I've used that and it works fine. There is one more thing I'd like to do though. Is there a way I can as such rap this around the result. So instead of outputting this: 410|662 it would output this: [village]410|662[/village]

 

I've looked through the code but have only found the thing for the middle ("|") and then the seperator. How can I add this in for this code:

 

<?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 = 'vpt_sub' AND x BETWEEN 0 AND 999 AND y BETWEEN 0 AND 999 GROUP BY a.id");

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

[kickstart]

Hi

 

In the SQL or the  PHP?

 

In PHP it is easy:-

 

while($row = mysql_fetch_array($result))
{
   echo '[village]'.$row['xy'].'[/village]'; 
}

 

All the best

 

Keith

[sp@rky13]

I really don't understand why I don't see these things. It's so obvious. sorry for wasting your time. Thank you