Jump to content

Archived

This topic is now archived and is closed to further replies.

willwill100

**solved** INNER JOIN Problem

Recommended Posts

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

Share this post


Link to post
Share on other sites
[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.


Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
[!--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]

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
[!--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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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...

Share this post


Link to post
Share on other sites
See my post immediately before your last one, we both posted at about the same time.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
You certainly need them round e.`Sail Number` as it contains a space.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.