Only one row returned in mysql data retrieval

Question

Firstly, sorry about the long question... but this is doing my head in. Any help would be greatfully accepted.

I've written the following function to return data from a mysql database:

function injuryTable()
{
    # get all players that are injured and their injuires...
    $sql = "SELECT players.id, players.injury_id, players.pname, injuries_date.name, start_date, end_date
            FROM players INNER JOIN injuries_date
            ON injury_id = injuries_date.id";

    $result = sqlQuery($sql);

    return $result;
}

The sqlQuery function is as follows:

function sqlQuery($sql)
{
    $products = array();
    $link = dbConnect('localhost', 'root', '', 'umarrr');
    $result = mysqli_query($link, $sql); 

    while ($row = mysqli_fetch_array($result))
    {
        $products[] = $row;        
    }

    #   return each row:
    return $products;
    #mysqli_close($link);
}

It's all connected to the database, and everything works fine. However when I try to iterate through the results: it only returns one row:

$injury_table = injuryTable();

// make it more readable:
foreach ($injury_table as $table);
{
    echo $table['pname'];
    echo $table['name'];
    echo $table['start_date'];
    echo $table['end_date'];
}

The sql statement I wrote above works perfectly in mysql query browser, so does anyone know what the problem here might be?

Output of print_r($injury_table)

Array ( [0] => Array ( [0] => 1 [id] => 1 [1] => 6 [injury_id] => 6 [2] => person [pname] => person [3] => wrist [name] => wrist [4] => 2008-11-21 [start_date] => 2008-11-21 [5] => 2010-11-11 [end_date] => 2010-11-11 ) [1] => Array ( [0] => 2 [id] => 2 [1] => 5 [injury_id] => 5 [2] => woman [pname] => woman [3] => neck [name] => neck [4] => 2009-11-12 [start_date] => 2009-11-12 [5] => 2010-09-09 [end_date] => 2010-09-09 ) [2] => Array ( [0] => 3 [id] => 3 [1] => 4 [injury_id] => 4 [2] => girl [pname] => girl [3] => groin [name] => groin [4] => 2010-11-27 [start_date] => 2010-11-27 [5] => 2010-12-01 [end_date] => 2010-12-01 ) [3] => Array ( [0] => 4 [id] => 4 [1] => 1 [injury_id] => 1 [2] => boy [pname] => boy [3] => achilles [name] => achilles [4] => 2010-02-01 [start_date] => 2010-02-01 [5] => 2010-03-23 [end_date] => 2010-03-23 ) [4] => Array ( [0] => 5 [id] => 5 [1] => 2 [injury_id] => 2 [2] => man [pname] => man [3] => toe [name] => toe [4] => 2010-01-01 [start_date] => 2010-01-01 [5] => 2010-02-02 [end_date] => 2010-02-02 ) )

Answer 1

Some things to check:

1. Check the return value of mysqli_query(). You assume the query succeeds, and there's far too many reasons for a query to fail to NOT check each time. It will return boolean FALSE on failure.
2. If you're doing many queries in your script, you'll be opening a new connection handle for each. By default these will NOT be persistent connections, so any transactions you may start will be automatically rolled back after the query results are consumed. Generally it's better to connect to the database ONCE (you can store the handle in a static variable inside your sqlQUery() function if you'd like and reuse it. There aren't many situations where you'd want (or need) multiple handles at the same time, or a brand new sparkling clean handle each time.
3. Have you tried doing a print_r()/var_dump() of the rows as they're retrieved in the while() loop? Tried spitting out a mysqli_num_rows() to see how many the PHP version of the query is returning?
4. Are you viewing the results in a browser? Perhaps something in the first or second result rows has an HTML tag (or something that your browser's interpreting) as an HTML tag and is "hiding" the output. View the page's source to make sure they're really not being output, and not just being hidden.
5. And finally, on the security front, it is very poor practice to allow a web-facing application to connect to the database as 'root'. Any flaws in your code will leave the DB server wide open for anyone to play in. Create an account with the least amount of privileges necessary to get the job done (probably insert/update/select/delete only) and use that instead of the root account. Even if you're just playing around with this on a private server, it's a good habit to get into.

Answer 2

Why are you opening a new connection to the database for each call? This is a very inefficient way of executing queries. I would pass the database connection as a parameter, or since you are using mysqli, just pass the $link as a parameter.

As to why your code is not working, I do not know, but you can try some basic error reporting with the following:

$result = mysqli_query($link, $sql) or 
                 trigger_error('Query Failed: ' . mysqli_error($link));

I would also add the MYSQL_ASSOC to the fetching function, as you are not using the index-based array, this will make your script that much more efficient.

Hope this helps you out.

Answer 3

Really bad (and maybe insulting) answer...

foreach ($injury_table as $table);
{
    echo $table['pname'];
    echo $table['name'];
    echo $table['start_date'];
    echo $table['end_date'];
    echo '<br/>'; // HTML new line
}

Or

foreach ($injury_table as $table);
{
    echo $table['pname'];
    echo $table['name'];
    echo $table['start_date'];
    echo $table['end_date'];
    echo "\n"; // Console new line
}