Link to home
Create AccountLog in
Avatar of perpetualm

asked on

PHP Mysql Query using inner joins returning unexpected array results

Hello Experts,

I've got a quick question regarding a query I'm trying to run on my mysql database using php. The query uses INNER JOINS. The query is returning an array that contains only one record (even though it should return 3 records) and the array seems to have duplicated each index (an index containing the value, followed by another index containing the fieldname for the previous value). It repeats that behavior for each field (ie querying on 14 columns/fieldnames will return 28 indexes).

The following is the query code I used and then the print_r() output showing the array result.

It's important to note that the query and result worked fine in both Navicat and PHPMyAdmin so I don't believe the issue is with the query, the database, or the server... I'm using MySQL 5.5.8 on a locally-hosted instance of WAMPSERVER.

I have reasonable experience with php, queries, and mysql but I've never seen this before. Any help you could provide would be greatly appreciated.

Thank you.

//Query to get pull league, sport, and season registration information(tables are linked properly through referential integrity) for seasons which are set to be available for registration in the season table.
$query_text= "SELECT
Inner Join `sports` ON `sports`.`sport_identifier` = `leagues`.`sport_identifier`
Inner Join `seasons` ON `seasons`.`season_identifier` = `leagues`.`season_identifier`
WHERE `seasons`.`bin_season_available_for_registration`=1
$query = mysql_query($query_text);
$array = mysql_fetch_array($query);
print "<pre>";
print "</pre>";

Open in new window

    [0]=> 4
    [league_identifier] => 4
    [1] => 1
    [season_identifier] => 1
    [2] => 1
    [sport_identifier] => 1
    [3] => 1
    [league_day] => 1
    [4] => Monday Beach Volleyball
    [league_name] => Monday Beach Volleyball
    [5] => 1000
    [league_fee] => 1000
    [6] => 1
    [7] => Beach Volleyball
    [sport_name] => Beach Volleyball
    [8] => /test
    [sport_master_logo] => /test
    [9] => 12
    [sport_number_of_players_for_registration] => 12
    [10] => 1
    [11] => Spring
    [season_description] => Spring
    [12] => 2011
    [season_year] => 2011
    [13] => 1
    [bin_season_available_for_registration] => 1
    [14] => 0
    [bin_season_available_in_score_reporter] => 0
Avatar of Bernie Bsen
Bernie Bsen
Flag of Germany image

$array = mysql_fetch_array($query, MYSQL_ASSOC) avoids duplication of rows.

There are three parameters for the mysql_fetch_array function available:

$array = mysql_fetch_array($query, MYSQL_ASSOC) // brings only fieldnames as index values
$array = mysql_fetch_array($query, MYSQL_BOTH) // brings fieldnames as well as indexnumbers (0,1,2,3,4....)
$array = mysql_fetch_array($query, MYSQL_NUM) // brings indexnumbers ....

in your case default MYSQL_BOTH is choosen as default.

from the PHP manual:

Returns an array of strings that corresponds to the fetched row, or FALSE if there are no more rows. The type of returned array depends on how result_type is defined. By using MYSQL_BOTH (default), you'll get an array with both associative and number indices. Using MYSQL_ASSOC, you only get associative indices (as mysql_fetch_assoc() works), using MYSQL_NUM, you only get number indices (as mysql_fetch_row() works).

If two or more columns of the result have the same field names, the last column will take precedence. To access the other column(s) of the same name, you must use the numeric index of the column or make an alias for the column. For aliased columns, you cannot access the contents with the original column name.
Avatar of perpetualm


Thank you for the reply brb6708,

I will accept your solution, but do you have any idea why my query is not pulling the expected number of records? As I mentioned originally, the query pulls the correct results in both Navicat and PHPMyAdmin but the returned array in PHP displays only one record.

Any suggestions?

Thank you again
You can also use mysql_fetch_assoc() to get only the name of the column and the value of the column.

I might try running the query like this...
$query_text = "SELECT * FROM leagues, sports, seasons WHERE seasons.bin_season_available_for_registration = 1
AND sports.sport_identifier   = leagues.sport_identifier
AND seasons.season_identifier = leagues.season_identifier";

Open in new window

Nice suggestion Ray,

It's a different way of doing the same query but, as expected, it returned the same result...

The query you suggested, as with the one I wrote originally, returns expected values in both Navicat and PHPMyAdmin, just not my code!!

Note: I just had an idea and tried to trouble shoot my code. A simple one-line query of the database (seeking just the league_identifiers from the lone table `league`) only returned 1 result as well when there should be 4. It seems the problem is not the format of the query, but the querying-function and/or interactions between php and my db.

This is my first time using WAMPSERVER, could there be a setting in there that I need to change to allow queries to return more than one value?

Thank you!
I'm not much of a windows guy, but it would seem counterintuitive, even for Microsoft, to have a setting that needed to be changed to get more than one row back from a query!

Try doing something like this:

$result = mysql_query($query_text) or die( mysql_error() );
while ($array = mysql_fetch_assoc($result)

Open in new window

Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
The problem turned out to be how I was handling arrays. Guess I was a bit rusty on the syntax!

Your while loop reminded me of where my mistake was,
Much appreciated
Glad to help.   Thanks for the points and Happy New Year! ~Ray