[RESOLVED] show name rather than id - join?

etoast

Hello
Rather than taking the simple route and adding all the date to a single table I am trying to be good and follow some database normalisation rules. But I'm a bit stuck with the following. I have three tables:

location, contains cols: locationid (INT) and location (VARCHAR)
school, contains cols: schoolid (INT) and school (VARCHAR)
person, contains lots of cols, including locationid and schoolid.

But with the query below, how do I change the last two lines to echo the location name and school name rather than their IDs? Is it some kind of join?
The URL looks like this: results.php?location=3&school=6

$location=$_GET['location']; 
$school=$_GET['school'];
$sql = "SELECT * FROM people WHERE locationid='$location' AND schoolid='$school'"; 
$result=mysql_query($sql); 
while($row = mysql_fetch_array($result)) { 

echo "Person: {$row['person']}<br />";
echo "Location: {$row['locationid']}<br />";
echo "School: {$row['schoolid']}<br />";

}

Many thanks!!

Piranha

There is nothing wrong with your query. You should not use select * since it might take longer and might cause trouble if you add columns to the database, but it should work.

Try one of the following:

echo "School: {$row['school']}<br />"; 
echo "School: {$row['school.school']}<br />";

etoast

Sorry, I probably didn't explain it very well. At the moment I am querying just the 'people' database - which holds the school and location IDs against each person, but I don't want to echo the ID, I want to echo the school name and location name. These are held in different tables with corresponding IDs.

Using the example you gave doesn't echo anything, because at the moment my query isn't touching the school and location tables. I know it needs to, I'm just not sure how.

$location=$_GET['location'];
$school=$_GET['school'];
$sql = "SELECT * FROM people, school, location WHERE locationid='$location' AND schoolid='$school'";
$result=mysql_query($sql);
while($row = mysql_fetch_array($result)) {

echo "Person: {$row['person']}<br />";
echo "Location: {$row['location.location']}<br />";
echo "School: {$row['school.school']}<br />";

}

???

etoast

I figured it out.

Probably not the best piece of code, but it works.
If anyone is interested:

$location=$_GET['location'];
$school=$_GET['school'];
$sql = "SELECT school.school, school.schoolid, location.location, location.locationid, people.person FROM people, school, location WHERE school.schoolid = people.schoolid AND location.locationid = people.locationid AND people.locationid='$location' AND people.schoolid='$school'";
$result=mysql_query($sql);
while($row = mysql_fetch_array($result)) {

echo "Person: {$row['person']}<br />";
echo "Location: {$row['location']}<br />";
echo "School: {$row['school']}<br />";

}

Piranha

That seems to be a good query, quite normal if you ask me. I missed that you didn't use more than one table, but you figured it out anyway 🙂

laserlight

The only catch is that if it is actually used as-is, you need to check that the incoming variables $GET['location'] and $GET['school'] exist using [man]isset[/man] or [man]empty[/man]. After you know they exist, you need to use [man]mysql_real_escape_string/man to sanitise them before using them in your SQL statement.