Mysql fetch array on a join?

[jbradley04]

Hello,

I am trying to do a join and still output the results to a tpl file.  this is the orignal code before the join...

$gamestats = "SELECT * FROM game WHERE user='$user' ORDER BY Month DESC";

$res = mysql_query($gamestats);

$results = array();

$i=0;

while ($r=mysql_fetch_array($res)) {

            $tmp = array(

                'ID' => $r['id'],

                'date'=> $r['Date'],

'time' => $r['Time'],

                'opponent'=> $r['Opponent'],

'weather' => $r['Weather']

            );

            $results[$i++] = $tmp;

}

 

Can I do something like this??? and if so what is the correct way?

$gamestats = "SELECT * FROM game AS g, seas AS s WHERE g.user='$user' ORDER BY g.Month DESC";

$res = mysql_query($gamestats);

$results = array();

$i=0;

while ($r=mysql_fetch_array($res)) {

            $tmp = array(

                'ID' => $r['id'],

                'date'=> $r['Date'],

'time' => $r['Time'],

                'opponent'=> $r['Opponent'],

'weather' => $r['Weather'],

                                'field from seas' => $r['feild from seas']

            );

            $results[$i++] = $tmp;

}

 

I hope this makes sense!  Appreciate the help!

J 

[rajivgonsalves]

whats the connection between game and seas you have to have a common key which you can join on

[jbradley04]

Well, the game should have g.seas that should match the seas tables s.id

So if I included that would that work?

[rajivgonsalves]

your query should be then

 

$gamestats = "SELECT * FROM game g, seas s WHERE g.seas = s.id and g.user='$user' ORDER BY g.Month DESC";

[jbradley04]

Ok, I chnaged it and I am not getting any results but I know I should... 

So my question is...  Do I have to change anything here..

 

$res = mysql_query($gamestats);

$results = array();

$i=0;

while ($r=mysql_fetch_array($res)) {

            $tmp = array(

                'ID' => $r['id'],

                'date'=> $r['Date'],

            'time' => $r['Time'],

                'opponent'=> $r['Opponent'],

            'weather' => $r['Weather'],

                                'field from seas' => $r['feild from seas']

            );

            $results[$i++] = $tmp;

}

Should those have the g. or s. before them?

[rajivgonsalves]

try running the query in phpmyadmin and see what the field names are I am not too sure on this also you could put a print_r($r) to see what the array looks like

[roopurt18]

SELECT * FROM game g, seas s WHERE g.seas = s.id and g.user='$user' ORDER BY g.Month DESC

 

With the way you've written that query, you're leaving yourself open to some potential issues.

 

1)  Don't use SELECT *.  Only ask the database for columns you plan to use.

 

2) FROM game g, seas s, WHERE

Don't use that comma-oriented syntax for specifying joins.  Explicitly specify the join time and write it out, like so:

FROM game g

INNER JOIN seas s ON g.seas=s.id

 

3) WHERE g.seas = s.id ...

If you follow the advice in my second point, then you will remove the join logic from the where clause.  Avoid putting your join logic in the WHERE clause.

 

4) g.user='$user'

mysql_real_escape_string() will add the appropriate quotes for you.  This code might be an indication that you are not escaping your data properly.

 

5) Use the backticks, Luke!

Wrap your database identifiers in the appropriate enclosing characters, which would be backticks in MySQL.  For example:

SELECT

  `col1`, `col2`

FROM `mytable` a

INNER JOIN `myothertable` b on a.`fid`=b.`id`

WHERE a.`name`='Larry'

 

(edit) All of the mysql_fetch*() functions work with any type of statement that returns rowsets (as far as I know anyways).  So your topic title of "Mysql fetch array on a join" is inappropriate.  More appropriate would have been "Need help with join syntax".  Since this is a MySQL issue, the "PHP Coding Help" board is also not the best place for it.  I mention this because if you can place your questions in the proper boards with more useful descriptions, then you can get better help faster in the future.

[jbradley04]

I appreciate everyone helping out!  I also understand that this was placed in the wrong area, sorry for that. 

I did what you said and this is what I have...

$gamestats = "SELECT g.`id`, d.`Date`, g.`Time`, g.`Opponent`, g.`Weather`, s.`name` FROM `game` g INNER JOIN `seas` s ON g.`seas`=s.`id` WHERE g.`user`='$user' ORDER BY g.`Month` DESC";

 

Not pulling any results...  What am I doing wrong?  Also I did not know what to do with mysql_real_escape_string()

 

Thanks for all your help!!!

J

[roopurt18]

Code readability is very important, especially when you revisit your code 6 months later and forgot what it's doing.

 

I find writing my queries out like this to be most readable (just a suggestion, do what works for you):

<?php
$gamestats = "
    SELECT 
        g.`id`, d.`Date`, g.`Time`, g.`Opponent`, 
        g.`Weather`, s.`name` 
    FROM `game` g 
    INNER JOIN `seas` s ON g.`seas`=s.`id` 
    WHERE 
        g.`user`='$user' 
    ORDER BY g.`Month` DESC
    ";
?>

 

rajivgonsalves gave you good advice when he advised that you run the query directly in phpMyAdmin (or another "direct" MySQL interface).  As a general guideline, whenever you run into trouble with programming, remove as many of the complexities as possible and go back to the basics.  This will help you identify the source of your problem.

 

For example, if you run your query directly in MySQL (using phpMyAdmin) and it doesn't work, then we know the problem is with your query.  phpMyAdmin will even be kind enough to print error information.

 

If you run your query in phpMyAdmin and it works, then the problem must be in your PHP code.

 

See how this helps narrow it down?

 

I want you to find a user id for which you query should work.  Then replace $user in your query with that id and run it directly in phpMyAdmin.

 

For example, if user id 10 should work, run this query directly in phpMyAdmin without any interference from your PHP code:

    SELECT 
        g.`id`, d.`Date`, g.`Time`, g.`Opponent`, 
        g.`Weather`, s.`name` 
    FROM `game` g 
    INNER JOIN `seas` s ON g.`seas`=s.`id` 
    WHERE 
        g.`user`='10' 
    ORDER BY g.`Month` DESC

 

(As an aside, you'll notice that when you format your queries in your PHP code as I've suggested, copying and pasting them into external tools becomes much easier!)

 

Now, what does phpMyAdmin tell you?

[jbradley04]

Thanks for all your help!!!  I placed the code in phpMyadmin and messed around with it, and also looked at some tutorials in Joins...  Anyways!  here is the final code that worked for me!

 

"SELECT game.id, game.Date, game.Time, game.Opponent, game.Weather, seas.name

FROM game

INNER JOIN seas ON game.seas = season.id

WHERE game.user = '$user'

ORDER BY game.Month DESC";

 

I really appreciate all your time and help!

[roopurt18]

I don't understand how ON game.seas = season.id is working correctly.  season needs to be defined as a table before you use it like that and I can't see where you're doing that.