Jump to content

**solved** INNER JOIN Problem


willwill100

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
Link to comment
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.


Link to comment
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]
Link to comment
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]
Link to comment
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?
Link to comment
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
Link to comment
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]
Link to comment
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...
Link to comment
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?
Link to comment
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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.