Jump to content


Photo

**solved** INNER JOIN Problem


  • Please log in to reply
15 replies to this topic

#1 willwill100

willwill100
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts

Posted 26 March 2006 - 07:44 PM

here's the code concerned:

/////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
}?>

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

anyways, i get this error:

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
(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

#2 mb81

mb81
  • Members
  • PipPipPip
  • Advanced Member
  • 120 posts

Posted 26 March 2006 - 08:02 PM

$q= "SELECT * FROM `$comp` ORDER BY `score` ASC";
$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";    

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.




#3 willwill100

willwill100
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts

Posted 26 March 2006 - 08:10 PM

hi mb81,


I found i havent declared all variables

ill post results in a sec

#4 willwill100

willwill100
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts

Posted 26 March 2006 - 08:22 PM

ok, still got problems

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


#5 mb81

mb81
  • Members
  • PipPipPip
  • Advanced Member
  • 120 posts

Posted 26 March 2006 - 08:29 PM

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

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
[/quote]

You need to add quotes around topper main points results, so you're code will look like this:
$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";    


#6 willwill100

willwill100
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts

Posted 26 March 2006 - 08:33 PM

still getting an error:

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

any ideas?

#7 willwill100

willwill100
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts

Posted 27 March 2006 - 06:44 AM

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

#8 redbullmarky

redbullmarky
  • Staff Alumni
  • Advanced Member
  • 2,863 posts
  • LocationBedfordshire, England

Posted 27 March 2006 - 08:33 AM

[!--quoteo(post=358789:date=Mar 27 2006, 07:44 AM:name=WillWill)--][div class=\'quotetop\']QUOTE(WillWill @ Mar 27 2006, 07:44 AM) View Post[/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:

$result = mysql_query($sql) or die(mysql_error());

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
"you have to keep pissing in the wind to learn how to keep your shoes dry..."

I say old chap, that is rather amusing!

#9 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 27 March 2006 - 10:44 AM

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.

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

The same applies if the names are held in variables eg

$comp1 = 'topper main points';

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

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#10 willwill100

willwill100
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts

Posted 27 March 2006 - 10:45 AM

ok, here's my error message

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

hope u can help...

#11 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 27 March 2006 - 10:50 AM

See my post immediately before your last one, we both posted at about the same time.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#12 willwill100

willwill100
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts

Posted 27 March 2006 - 10:57 AM

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

$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";

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?

#13 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 27 March 2006 - 11:05 AM

You certainly need them round e.`Sail Number` as it contains a space.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#14 willwill100

willwill100
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts

Posted 27 March 2006 - 11:08 AM

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

#15 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 27 March 2006 - 11:21 AM

try running the snippet below

<?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";
?>

PS Don't forget to define $comp1 and $compres - I did
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#16 willwill100

willwill100
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts

Posted 27 March 2006 - 11:30 AM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users