Help with using mysql SELECT to gather multiple results. I think.

[ArnMan]

ok, I am not sure how to explain what I am wanting to so I will give an example of what I am looking for.

Lets say I have 3 circles

2 are side by side  -->  O  O

and one underneath  --> O

we'll name them A, B, C  -- A, B at the top and C at the bottom

and also lets say A has a ID=1 -- B has a ID=2 -- and C has a ID=3

 

also pretend that a line is drawn from C ID3 to A ID1 and B ID2

O  O

  \  /

  O

like so, this is just a simple idea, imagine many circles all over each with a different ID #

Now here is what I am wanting to find out

is there a way to use SELECT to select so I can print on the screen, all the circles, that relate to one specific circle.

like let say my current location is circle C and I want to put on the screen buttons to take me to circle A and B, but, with many circles going to be used, i dont want to make a query based on these three, I want it to look for id numbers in the database under each circle and pull a list up.

here is what I have done before I loose your attention lol

i made extra fields for each circle, or city, named them northid, northeastid, eastid, southeastid, southid, southwestid, westid, and northwestid.

Each circle of course has its own id numbers.

now A and B are North of C so the southid for A and B would 1, and the northid of C would be 1 and northeastid would be 2.

ok I am wanting to put on the screen buttons or links that would take me to A and B or whatever shows up.

Here is the code that pulls up

this is the whole file

 

<?php
include "globals.php";
$_GET['to'] = abs((int) $_GET['to']);
if(!$_GET['to'])
{
print "Welcome to the Rail Station. It costs \$1000 for a ticket.<br />
Where would you like to travel today?<br />";
$q=$db->query("SELECT * FROM cities WHERE cityid != {$ir['location']} AND cityminlevel <= {$ir['level']}");
print "<table width=75% cellspacing=1 class='table'><tr style='background:gray'><th>Name</th><th>Description</th><th>Min Level</th><th> </th></tr>";
while($r=$db->fetch_row($q))
{
print "<tr><td>{$r['cityname']}</td><td>{$r['citydesc']}</td><td>{$r['cityminlevel']}</td><td><a href='monorail.php?to={$r['cityid']}'>Go</a></td></tr>";
}
print "</table>";
}
else
{
if($ir['money'] < 1000)
{
print "You don't have enough money.";
}
else if( ((int) $_GET['to']) != $_GET['to'])
{
print "Invalid city ID";
}
else
{
$q=$db->query("SELECT * FROM cities WHERE cityid = {$_GET['to']} AND cityminlevel <= {$ir['level']}");
if(!$db->num_rows($q))
{
print "Error, this city either does not exist or you cannot go there.";
}
else
{
$db->query("UPDATE users SET money=money-1000,location={$_GET['to']} WHERE userid=$userid");
$r=$db->fetch_row($q);
print "Congratulations, you paid \$1000 and travelled to {$r['cityname']} on the Rail!";
}
}
}
$h->endpage();
?>

 

right now this code just looks for all cities and lists them all.

I just want it to show only the cities that are directly connected to the current city you are in, through the use of the current cityid

I have tried several variations but can usually only get it to find just one.

[Ken2k7]

Let me see if I understand you. You have some number of itineraries that you want to select that can be accessed from one city ID? For that, you'll need some way to reference one city to another. Maybe a table that has city to another city like so -

 

Table itinerary

id

from_city_id

to_city_id

 

?