MYSQL Query Comparison

[sp@rky13]

Sorry for bad title but unsure of what to put. if you want I'll edit it with a better title if you can think of it.

 

Ok so I am using MYSQL to do some queries. Here is one query that works:

 

<?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);
?>

 

So basically in the above code. It takes the player name entered in what I named vpt_sub (could just be a player name instead) and then it verifies that there was a result returned. Then it creates an id named id which stores the info from the id column in the row.

Then in another table named "village_en28", the value in id is looked up and every row that includes that id in the player column has the x column value echoed followed by a "|" and then the y value for each row returned. That's it.

 

But then in my next query it gets more complicated:

 

<?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);
?>

 

So that's what I tried. The aim of that code is to look up $vpt_sub or any other tribe name and then it adds the id column of that row to this id named "id". Then the query looks in the table players_en28 for all rows where the value of id is listed in the ally column. Then all these rows are put in id2. Then the query looks in the villages_en28 for any value in the player column that equals id2. Then it echoes the x column then a | then the y column then a space.

 

So the first code works the second doesn't. The codes are similar but are not use at the same time FYI. Basically the second code only returns the first player's x and y columns with the | and space.

 

So what's wrong? sorry for the long post but thought it better to get all the info out from the start

[kickstart]

Hi

 

Working through it, the first could be simplified to something like:-

 

<?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");

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

 

That would bring back a single row contain all the X and Y coordinates. However suspect you might want some other details output so maybe grouping the output isn't a good idea.

 

You second example the basic problem is that you only store the last id that you find in each loop. If you want to keep the 3 seperate queries (which is likely to be pretty inefficient) then you could do:-

 

<?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);
?>

 

However cleaned up and using a single piece of SQL (which should be far more efficient):-

 

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

mysql_select_db("wwwspark_tribalwars", $con);

$result = mysql_query("SSELECT c.player AS playerid, CONCAT_WS('|',c.x,c.y) AS xy FROM ally_en28 a INNER JOIN players_en28 b ON a.id = b.ally INNER JOIN villages_en28 c ON b.id = c.player WHERE a.tag = '$vpt_sub', ORDER BY c.player");

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

 

All the best

 

Keith

[sp@rky13]

totally confused . The second one works but as you said is laggy, basically. Anyway the 1st one turns up blank and the last one gives this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/wwwspark/public_html/test/query4.php on line 16

 

line 16 is:

while($row = mysql_fetch_array($result))

I fixed up: SSELECT to SELECT but other than that couldn't find a solution. why won't it work. So why won't it work. I actually don't get how you've done the ode but FYI, x and y are seperate columns in the village table.

 

Sp@rky13

ty for all and any help

[kickstart]

Hi

 

Might be because I have put an extra , in by mistake.

 

Try this, with the comma removed and an "or die" if there is an error with the SQL

 

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

mysql_select_db("wwwspark_tribalwars", $con);

$result = mysql_query("SSELECT c.player AS playerid, CONCAT_WS('|',c.x,c.y) AS xy FROM ally_en28 a INNER JOIN players_en28 b ON a.id = b.ally INNER JOIN villages_en28 c ON b.id = c.player WHERE a.tag = '$vpt_sub' ORDER BY c.player") or die(mysql_error());

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

 

The SQL has combined the values of x and y with a "|" between them and returned that as a single column called xy.

 

All the best

 

Keith