Need Help Connecting to 2nd Database Table

Dave 5 · Joined: 08 May 2004 Posts: 108 Location: Seattle

This is driving me nuts! It should be so simple, yet I can't find the problem.

I have a really nice script that includes scripts for displaying database tables with varying table row colors and giving them sortable columns. It's a little too complex for me to understand, but it works quite well.

However, I now need to connect it to a second table, and I'm having no success at all.

The second table I need to connect to is named famarea. I need to access a field named IDParent. The tables famarea and cia_people share a field named IDArea in common.

Consider this portion of my source code:

[code:1]
$res = mysql_query("SELECT IDArea, Name, Pop, Nationality, NationalityPlural, NationalityAdjective
FROM cia_people as C
WHERE C.Nationality is not null
ORDER BY C." . $order . $direction);
[/code:1]

I need to change it to something like this:

[code:1]
$res = mysql_query("SELECT IDArea, Name, Pop, Nationality, NationalityPlural, NationalityAdjective
FROM cia_people as C, famarea as F
WHERE C.Nationality is not null AND C.IDArea = F.IDArea AND F.IDParent = 'xeu'
ORDER BY C." . $order . $direction);
[/code:1]

But every effort I make to introduce table famarea yields this error message:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\sites\geoworld\about\guide\world\eur\remote.php on line 96

Where line 96 is while ($row = mysql_fetch_array ($res)) {

I'm so sick of that error message. I've been seeing it so many times, and it doesn't really explain anything; what does it mean? I posted the entire code below. Does anyone have any suggestions? Is there some way of modifying or simplifying this script so I don't have to jump through so many hoops when trying to do something as simple as join with another table?

Thanks.

[code:1]
<head>[DATABASE CONNECTION]</head>
<body>
<div class="formdiv">
<form action="remote.php" method="GET">
<select name="order">
<option value="1">Country, etc.</option>
<option value="2">Population</option>
<option value="3">Nationality</option>
<option value="4">Nationality: Plural</option>
<option value="5">Nationality: Adjective</option>
</select>
<input type="radio" name="direction" value="0">+
<input type="radio" name="direction" value="1">-
<input type="submit" name="submit" value="Submit">
</form>
</div>
<?php
$colors = array( '#eee', '', '#ff9', '', '#cff', '', '#cfc', '' );
$n=0;
$size=count($colors);

$result = mysql_query('select count(*) from cia_people');
if (($result) && (mysql_result ($result , 0) > 0)) {
// continue here with the code that starts
//$res = mysql_query ("SELECT * FROM type.....
} else {
die('Invalid query: ' . mysql_error());
}
{

$order = isset($_REQUEST['order']) ? intval($_REQUEST['order']) : 0;
switch($order)
{
case 1:
$order = 'Name';
break;
case 2:
$order = 'Pop';
break;
case 3:
$order = 'Nationality';
break;
case 4:
$order = 'NationalityPlural';
break;
case 5:
$order = 'NationalityAdjective';
break;
case 6:
default:
$order = 'Name';
break;
}
if (isset($_REQUEST['direction']) && intval($_REQUEST['direction'])) {
// if (isset($_REQUEST['direction']) && intval($_REQUEST['direction'])) {
$direction = ' DESC';
} else {
$direction = '';
}

$res = mysql_query("SELECT IDArea, Name, Pop, Nationality, NationalityPlural, NationalityAdjective
FROM cia_people as C
WHERE C.Nationality is not null
ORDER BY C." . $order . $direction);

echo '<table class="sortphp" id="tab_cia_people_peo">
<thead>
<tr><th>Country</th><th>X</th></tr>
</thead>
<tbody>';
//
$rowcounter=0;
while ($row = mysql_fetch_array ($res)) {
$c=$colors[$rowcounter++%$size];
echo "<tr style=\"background-color:$c\"><". $_SERVER['PHP_SELF'] .'?id='. $row['IDArea'] .">
<td>". $row['Name'] ."</td>
<td> </td></tr>\n";
}
}
?>
</tr>
</tbody>
</table>
</body>
</html>
[/code:1]

lostboy · Joined: 02 May 2004 Posts: 6059 Location: toronto, canada

check the sql statement for wellformed ness

[code:1]
$res = mysql_query("SELECT IDArea, Name, Pop, Nationality, NationalityPlural, NationalityAdjective
FROM cia_people as C
WHERE C.Nationality is not null
ORDER BY C." . $order . $direction) or die (error: ".mysql_error());
[/code:1]

I think you see that the syntaxt is a little screwy at the end

try this:

ORDER BY C." . $order ." ". $direction)

papercrane · Joined: 16 Jul 2004 Posts: 637 Location: California, US

This is happening because the query isn't going through correctly. You need to check for errors and output the *real* mysql error to see the problem. COmehting like this:

[code:1]$res = mysql_query('...');
if (!$res) {
die(mysql_error());
}[/code:1]

You may also want to use the ON syntax which mysql uses for joins (when you select from more than one table, it's called a join).

[code:1]'
SELECT
IDArea
, Name
, Pop
, Nationality
, NationalityPlural
, NationalityAdjective
FROM
cia_people as C
LEFT JOIN famarea as F ON C.IDArea = F.IDArea
WHERE
C.Nationality is not null
AND F.IDParent = "xeu"
ORDER BY
C.'.$order.' '.$direction;
$res = mysql_query($query);
if (!$res) {
die('Error running query: '.mysql_error().'<br/>
<pre>'.$query.'</pre><br/>');
}[/code:1]

I think you were also missing a space between $order and $direction.

Whenever you get errors like that, output the query and try to run it in another program, such as the mysql command-line, PHPMYAdmin, or Mysql Control Center (The latter is my preferred method).

Dave 5 · Joined: 08 May 2004 Posts: 108 Location: Seattle

Hooray - it works! I've been working on this for about a day and struck out on three different forums. Thanks for figuring it out for me!