**solved** INNER JOIN Problem

[willwill100]

here's the code concerned:

[code]
/////code, if table submitted then display else dont//
$willitgo = $_REQUEST['display'];
if ($willitgo=="Submit"){
?>
<!-- Display table data -->
<table width="100%" border="1">
<tr>
<td>Position</td>
<td>Name</td>
<td>Sail Number</td>
<td>Class</td>
<td>Score</td>
</tr>
<?php



$q= "SELECT * FROM `$comp` ORDER BY `score` ASC";
    $result = mysql_query ($q) or die('Problem with query: ' . $q . '<br />' . mysql_error());
$count = 1;
while ($row = mysql_fetch_assoc($result)){
    $name=$row['Name'];
    $snum=$row['Sail Number'];
    $class=$row['Class'];

$sql = "SELECT e.Name, SUM(r.Position) as Score FROM '$compl' e INNER JOIN $compres r ON e.Sail Number = r.Sail Number GROUP BY e.Name ORDER BY Score DESC";    
    
$result = mysql_query($sql);
$score= mysql_fetch_array($result);

echo ("<tr>" . "<td>" . $count . "</td>" . "<td>" . $name . "</td>" . "<td>" . $snum . "</td>" . "<td>" . $class . "</td>" . "<td>" . $score . "</td></tr>");

$count = $count + 1;
}
?>
</table>
<?php
}?>
[/code]

it's the inner join part in the middle that's confusing me...

anyways, i get this error:

[code]
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Documents and Settings\Will\My Documents\xampp\htdocs\sailing\displayres.php on line 74

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\Documents and Settings\Will\My Documents\xampp\htdocs\sailing\displayres.php on line 66
[/code]
(my code started on line 46)

the following tables are concerned:
"topper main points results"
[img src=\"http://i7.photobucket.com/albums/y254/willwill100/tmpr.gif\" border=\"0\" alt=\"IPB Image\" /]

"topper main points"
[img src=\"http://i7.photobucket.com/albums/y254/willwill100/tmp.gif\" border=\"0\" alt=\"IPB Image\" /]

what i want is for all the results from the same sail number to be added together and then take the equivalent name and class(that is type of boat) and then put all these details into a results table with a results column with ascending positions.

Thanks for your time,
Will

[mb81]

[code]
$q= "SELECT * FROM `$comp` ORDER BY `score` ASC";
[/code][code]
$sql = "SELECT e.Name, SUM(r.Position) as Score FROM '$compl' e INNER JOIN $compres r ON e.Sail Number = r.Sail Number GROUP BY e.Name ORDER BY Score DESC";    
[/code]

WillWill,

1) Where are you defining the variables you are using for your tablenames ($comp, $compl, $compres)?

If these are incorrectly set, that could be a reason for the error.

2) Just after the $sql = "....." line, but before the $result = mysql_query($sql); line, write echo $sql; and see what comes out on your browser and let us know, then we can work from there.

[willwill100]

hi mb81,


I found i havent declared all variables

ill post results in a sec

[willwill100]

ok, still got problems

[code]SELECT e.Name, SUM(r.Position) as Score FROM 'topper main points' e INNER JOIN topper main points results r ON e.Sail Number = r.Sail Number GROUP BY e.Name ORDER BY Score DESC
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Documents and Settings\Will\My Documents\xampp\htdocs\sailing\displayres.php on line 78

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\Documents and Settings\Will\My Documents\xampp\htdocs\sailing\displayres.php on line 69[/code]

[mb81]

[!--quoteo(post=358634:date=Mar 26 2006, 03:22 PM:name=WillWill)--][div class=\'quotetop\']QUOTE(WillWill @ Mar 26 2006, 03:22 PM) [snapback]358634[/snapback][/div][div class=\'quotemain\'][!--quotec--]
ok, still got problems

[code]SELECT e.Name, SUM(r.Position) as Score FROM 'topper main points' e INNER JOIN topper main points results r ON e.Sail Number = r.Sail Number GROUP BY e.Name ORDER BY Score DESC
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Documents and Settings\Will\My Documents\xampp\htdocs\sailing\displayres.php on line 78

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\Documents and Settings\Will\My Documents\xampp\htdocs\sailing\displayres.php on line 69[/code]
[/quote]

You need to add quotes around topper main points results, so you're code will look like this:
[code]
$sql = "SELECT e.Name, SUM(r.Position) as Score FROM '$compl' e INNER JOIN '$compres' r ON e.Sail Number = r.Sail Number GROUP BY e.Name ORDER BY Score DESC";    
[/code]

[willwill100]

still getting an error:

[code]
SELECT e.Name, SUM(r.Position) as Score FROM 'topper main points' e INNER JOIN 'topper main points results' r ON e.Sail Number = r.Sail Number GROUP BY e.Name ORDER BY Score DESC
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Documents and Settings\Will\My Documents\xampp\htdocs\sailing\displayres.php on line 79

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\Documents and Settings\Will\My Documents\xampp\htdocs\sailing\displayres.php on line 69[/code]

any ideas?

[willwill100]

does no one know why my script doesn't work?

[redbullmarky]

[!--quoteo(post=358789:date=Mar 27 2006, 07:44 AM:name=WillWill)--][div class=\'quotetop\']QUOTE(WillWill @ Mar 27 2006, 07:44 AM) [snapback]358789[/snapback][/div][div class=\'quotemain\'][!--quotec--]
does no one know why my script doesn't work?
[/quote]

not exactly, but you can help us/yourself more by adding to this line:

[code]
$result = mysql_query($sql) or die(mysql_error());
[/code]

the error youre getting is a result of a query failing, so $result is not being given the sort of value that stuff like mysql_fetch_row, mysql_num_rows, etc need. put: or die(mysql_error()); and it should reveal the source of your problems, and my money is on your query syntax.

cheers
Mark

[Barand]

If you have table name or column names that contain spaces (bad idea) then you need to put back-ticks ` around them, not qoutes. Only put quotes round data values or string literals in SQL.

[code]SELECT e.Name, SUM(r.Position) as Score
FROM `topper main points` e INNER JOIN `topper main points results` r
ON e.Sail Number = r.Sail Number
GROUP BY e.Name
ORDER BY Score DESC[/code]

The same applies if the names are held in variables eg

[code]$comp1 = 'topper main points';

SELECT e.Name, SUM(r.Position) as Score
FROM `$comp1` ........[/code]

[willwill100]

ok, here's my error message

[code]Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Program Files\xampp\htdocs\sailing\displayres.php on line 79
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''topper main points' e INNER JOIN 'topper main points results' r ON e.Sail Numbe' at line 1SELECT e.Name, SUM(r.Position) as Score FROM 'topper main points' e INNER JOIN 'topper main points results' r ON e.Sail Number = r.Sail Number GROUP BY e.Name ORDER BY Score DESC[/code]

hope u can help...

[Barand]

See my post immediately before your last one, we both posted at about the same time.

[willwill100]

okay, ive put the backticks in but i still am getting an error when i query the db:

[code]$sql = "SELECT e.`Name`, SUM(r.`Position`) as Score FROM `$compl` e INNER JOIN `$compres` r ON e.`Sail Number` = r.`Sail Number` GROUP BY e.`Name` ORDER BY Score DESC"; [/code]

i don't get a mysql error but it just has a list of the same participent going up to 8126 or something and in score column it said "Array"!!??

what's going on?

[Barand]

You certainly need them round e.`Sail Number` as it contains a space.

[willwill100]

i think we posted at the same time again, could you look back two posts

and i did put the backticks around sail number, just incase u missed it

[Barand]

try running the snippet below

[code]<?php
// db connection here

$sql = "SELECT e.`Name`, SUM(r.`Position`) as Score
        FROM `$compl` e
        INNER JOIN `$compres` r ON e.`Sail Number` = r.`Sail Number`
        GROUP BY e.`Name`
        ORDER BY Score DESC";
$res = mysql_query($sql) or die (mysql_error());
echo "<TABLE border='1'><TR>
       <TH>Name</TH>
       <TH>Score</TH>
       </TR>\n";
while (list($name, $score)=mysql_fetch_row($res)) {
       echo "<TR>
       <TD>$name</TD>
       <TD>$score</TD>
       </TR>\n";
}
echo "</TABLE>\n";
?>[/code]

PS Don't forget to define $comp1 and $compres - I did

[willwill100]

Nice! It works. Thanks for all your help, top man!