Need help with hyperlink/update query.

cdoyle

I have a 1 table in my database called cities, and another table in my database called players.
Here are the fields I think are needed for what I want to do.

Table Cities
City_ID
City_Name
City_Description
Minimum_Level

In the players table
id
username
level
City_ID

Here is what I'm trying to do.
I want to give my users the ability to travel from city to city within the game. When they go to the drive.php, it looks at their level and then pulls from the city table any city they meet the minimum level requirement (I have this part working)

I want the names of the cities to be a link, that they can click on. When clicked it updates the City_ID to the location in the players table to the location they clicked on.

Here is the code I have so far, it displays the proper cities, but I don't know how to make the links update this players City_ID.

 
<?php

$querycity = $db->execute("SELECT * FROM Cities Where $player->level >= Minimum_Level");
echo "<table width=\"100%\">\n";
echo "<tr class=\"cellheader\"><td><div align=\"center\">Wanna Go Somewhere do ya?</div></td></tr><tr>\n";
echo "<td><div align=\"center\">Here are the cities that you can go too.</div></td></tr><tr>\n";
echo "<td>\n";
while ($getcity = $querycity->fetchrow()){
echo "<a href='#?cmd=go&id={$getcity['City_ID']}'>{$getcity['City_Name']}</a><br>";
echo "</td>\n";
echo "</tr></table>\n";

if($_GET['cmd']=="go")
{
$sql="UPDATE set City_ID='$City_ID' FROM players WHERE id='".$getcity['City_Id']."'";
$sql_result=mysql_query($sql)or die (mysql_error());
echo " ".$getcity['City_Name']." Updated!";
}
}
?>

When you move your cursor over the link, it shows an URL like this
http://www.caraudiocentral.net/CAC_Mafia_Life/drive.php#cmd=go&id=1

Now if I can somehow update City_ID in the players table with what shows after id=1, I think this will work great.

I'm also thinking I'm missing in my update query, to tell it which player to update?
I should have a $players->City_ID or some type of variable in there right? Just like I did in the select query?

Does anyone have any suggestions, or a better way to make this work?

Thanks

steamPunk

if your players log in to play then you presumably have a SESSION containing their id - you can use that in your UPDATE

$sql="UPDATE Players SET City_ID='".$getcity['City_Id']."' WHERE player_id='".$_SESSION['player_id']."'";

or have i misunderstood the question ?

steamPunk

$sql="UPDATE Players SET City_ID='".$_GET['id']."' WHERE player_id='".$_SESSION['player_id']."'";

cdoyle

Thanks for replying,

I'm still having problems, and can't seem to get it to update the field in the table.

here is my whole page, there isn't much more then this one piece, but maybe it will help?

cdoyle

I'm starting over with this, and back to what seems to work.

This code below, will display the proper cities depending on the players level.
What I need next, is to make it so the city names are links. When the player clicks on the link, it updates the City_ID of the players table.

So city 1 has an ID of 1, so if the player clicks on City 1 it updates the player ID City_ID to 1
and so on for each city.

<?php
$querycity2 = $db->execute("SELECT * FROM Cities Where $player->level >= Minimum_Level");
echo "<table width=\"100%\">\n";
echo "<tr class=\"cellheader\"><td><div align=\"center\">Wanna Go Somewhere do ya?</div></td></tr><tr>\n";
echo "<td><div align=\"center\">Here are the cities that you can go too.</div></td></tr><tr>\n";
echo "<td>\n";
while ($getcity2 = $querycity2->fetchrow())
echo $getcity2['City_Name'] . "<br />";
echo "</td>\n";
echo "</tr></table>\n";

?>
<?php

cdoyle

Here is what I have now

<?php

$querycity2 = $db->execute("SELECT * FROM Cities Where $player->level >= Minimum_Level");


while ($getcity2 = $querycity2->fetchrow())
echo "<a href='#?act=go&id={$getcity2['City_ID']}'>{$getcity2['City_Name']}</a><br>";


?>

This makes the cities names links, when you move the mouse over the link it shows an URL like this
http://www.caraudiocentral.net/CAC_Mafia_Life/drive.php#?act=go&id=1

What I need to do is make it so that '1' (which is the City_ID from the city table) when the link is clicked it updates the City_ID in the players table.

I've seen this done in other games, so it has to be possible I just don't know how to do it.