Query=>Array=>Output = PHP Warning: Illegal offset type

Question

I try to select mysql data, save them in a two dimensional array and output the data. I get the error message mentioned above for each column and row.

Could someone please point out my mistake. How can I get it to work?

This is the code:

// connect database
    $db_connection = new mysqli (MYSQL_HOST, MYSQL_USER, MYSQL_PASS, MYSQL_DB);

    // execute query 
    $query_result = $db_connection->query($sql_career);

    // save carrer data
    while( $row = $query_result->fetch_assoc() )
    {
        $career[]['id'] = $row['career_id'];
        $career[]['season'] = $row['career_season'];
        $career[]['matches'] = $row['career_matches'];
        $career[]['goals'] = $row['career_goals'];
        $career[]['team_name'] = $row['career_team_name'];
    }

    // output
    echo '<table border="1">';
    foreach ($career as $i)
    {
      echo "<tr>";
      echo "<td>". $career[$i]['id'] . "</td>";
      echo "<td>". $career[$i]['season'] . "</td>";
      echo "<td>". $career[$i]['team_name'] . "</td>";
      echo "<td>". $career[$i]['matches'] . "</td>";
      echo "<td>". $career[$i]['goals'] . "</td>";
      echo "</tr>";
    }
    echo "</table>";

Each [] in your while loop creates a new array index. To keep each record in the same array index use an incrementing variable - like in - $career[$i]['id']; - then do $i++; before the end of each iteration. — Karlo Kokkak
– Karlo Kokkak, Commented Apr 6, 2018 at 12:26

mickmackusa · Accepted Answer · 2018-04-06 13:00:22Z

If you have a successful query with rows, then just implode the rows and only loop once.

if (!$query_result = $db_connection->query($sql_career)) {
    // check error
} elseif (!$query_result->num_rows) {
    // no rows
} else {
    echo '<table border="1">';

        // I assume you have column headings in mind, add them here

        while($row = $query_result->fetch_assoc()) {
            echo "<tr><td>", implode("</td><td>", $row), "</td></tr>";
        }
    echo "</table>";
}

Or if you have an actual requirement for storing the resultset as a multidimensional array, you can use Miller's top voted comment in the manual.

for ($set = array (); $row = $query_result->fetch_assoc(); $set[] = $row);

from the above one-liner, you can apply the same "loop and implode" logic like this:

if (sizeof($set)) {
    echo '<table border="1">';
        foreach ($set as $row) {
            echo "<tr><td>", implode("</td><td>", $row), "</td></tr>";
        }
    echo "</table>";
}

Or you can write it all out:

echo '<table border="1">';
    foreach ($set as $row){
        echo "<tr>";
            echo "<td>{$row['career_id']}</td>";
            echo "<td>{$row['career_season']}</td>";
            echo "<td>{$row['career_team_name']}</td>";
            echo "<td>{$row['career_matches']}</td>";
            echo "<td>{$row['career_goals']}</td>";
        echo "</tr>";
    }
echo "</table>";

And if you want to manipulate the associative keys in advance, you can adjust your SELECT clause like:

SELECT career_id AS 'id', career_season as 'season', career_team_name AS 'team_name', career_matches AS 'matches', career_goals AS 'goals' FROM ...

This is my preferred solution. Check my answer below to see how i realized it in my own code.

Mayank Majithia · Accepted Answer · 2018-04-06 12:00:48Z

1

Just Replace this code,

You had already used foreach ,that gave current array.

foreach ($career as $i)
    {
      echo "<tr>";
      echo "<td>". $i['id'] . "</td>";
      echo "<td>". $i['season'] . "</td>";
      echo "<td>". $i['team_name'] . "</td>";
      echo "<td>". $i['matches'] . "</td>";
      echo "<td>". $i['goals'] . "</td>";
      echo "</tr>";
    }

answered Apr 6, 2018 at 12:00

Mayank Majithia

1,98618 silver badges21 bronze badges

1 Comment

Christian Over a year ago

Removing "career" inside the loop worked. I got an error message because missing index, but was able to solve it with an additional counter like proposed by Karlo Kokkak.

Dilip Solanki · Accepted Answer · 2018-04-06 12:04:09Z

1

You can try this one...

while( $row = $query_result->fetch_assoc() )
{
    $career[] = $row;
}

// output
echo '<table border="1">';
foreach ($career as $i)
{
  echo "<tr>";
  echo "<td>". $i['id'] . "</td>";
  echo "<td>". $i['season'] . "</td>";
  echo "<td>". $i['team_name'] . "</td>";
  echo "<td>". $i['matches'] . "</td>";
  echo "<td>". $i['goals'] . "</td>";
  echo "</tr>";
}
echo "</table>";

edited Apr 6, 2018 at 12:04

answered Apr 6, 2018 at 12:02

Dilip Solanki

4373 silver badges13 bronze badges

5 Comments

Mayank Majithia Over a year ago

$career multidimensional array

Dilip Solanki Over a year ago

But not a requirement of multidimensional.

Christian Over a year ago

Good hint to store the comlpete array in one line. I want to keep the option to use different variable names, but will keep it in mind.

mickmackusa Over a year ago

@Christian you can use aliases in your query to manage that. Write aliases with AS.

mickmackusa Over a year ago

@DilipSolanki You need to adjust your key naming to suit the resultset's column names.

Christian · Accepted Answer · 2018-04-06 14:29:53Z

0

While this is not the best practice, it does solve the warning problem that was my original question.

// save carrer data
$row_counter = 0;
while( $row = $query_result->fetch_assoc() )
{
    $row_counter++;
    $career[$row_counter]['id'] = $row['career_id'];
    $career[$row_counter]['season'] = $row['career_season'];
    $career[$row_counter]['matches'] = $row['career_matches'];
    $career[$row_counter]['goals'] = $row['career_goals'];
    $career[$row_counter]['team_name'] = $row['career_team_name'];
}

// output
echo '<table border="1">';
foreach ($career as $i)
{
  echo "<tr>";
  echo "<td>". $i['id'] . "</td>";
  echo "<td>". $i['season'] . "</td>";
  echo "<td>". $i['team_name'] . "</td>";
  echo "<td>". $i['matches'] . "</td>";
  echo "<td>". $i['goals'] . "</td>";
  echo "</tr>";
}
echo "</table>";

After some further tests with @mickmackusa proposal i came to the following solution. The output function is just for testing. The main focus is preparing and providing the data for a later useage.

function get_career($player_id) {

    // sql placeholder to save code here
    $sql_career = "...";

    // connect database
    $db_connection = new mysqli (MYSQL_HOST, MYSQL_USER, MYSQL_PASS, MYSQL_DB);

    if (!$query_result = $db_connection->query($sql_career)) {
        // check error
    } elseif (!$query_result->num_rows) {
        // no rows
    } else {
        for (
            $career = array (); 
            $row = $query_result->fetch_assoc(); 
            $career[] = $row);
    }

    return $career;     

} // function end

function output_career($player_id) {

    // function call, get career data
    $career = get_career($player_id);

    // output
    echo '<table border="1">';
    foreach ($career as $career)
    {
      echo "<tr>";
      echo "<td>". $career['id'] . "</td>";
      echo "<td>". $career['season'] . "</td>";
      echo "<td>". $career['team_id'] . "</td>";
      echo "<td>". $career['team_name'] . "</td>";
      echo "<td>". $career['country_name'] . "</td>";
      echo "<td>". $career['country_abbreviation'] . "</td>";
      echo "<td>". $career['league_name'] . "</td>";
      echo "<td>". $career['matches'] . "</td>";
      echo "<td>". $career['goals'] . "</td>";
      echo "<td>". $career['status_name'] . "</td>";
      echo "</tr>";
    }
    echo "</table>";

} // function end

edited Apr 6, 2018 at 14:29

answered Apr 6, 2018 at 12:30

Christian

11711 bronze badges

6 Comments

Christian Over a year ago

Not completely. You directly output the result from the query? How is the order of the columns made? In my question i put everything together to make the question more simple. In my application, i have only the data processing in my model files and want to prepare the data for a later use. But not sure if that makes a difference.

mickmackusa Over a year ago

The order of the columns is dictated by your query SELECT column order. How you write your SELECT clause is how you get it. If you need to store the resultset as a multidimensional array, that is what my for loop does and that is what $set is in my lower snippet.

Christian Over a year ago

Roger that! My code samples above are simplified and do not give exactly what I intend to do in the application. I just tried do understand why the array didnt worked. I am a PHP Newbie and thankful for all the tips and will try them out to improve the kind how i handle the data processing.

Christian Over a year ago

I really appreciate your efforts. I am not yet far enough to ask a question, still try to understand what happens and how i can use it to improve my code. Please give me some time, i will report back to you. Ty!

Christian Over a year ago

@mickmackusa: your example works well, see code above. But i ask me, what is the difference between "for ($career = array (); $row = $query_result->fetch_assoc(); $career[] = $row);" and the former variant "while( $row = $query_result->fetch_assoc() ) {$career[] = $row;}"

|

Collectives™ on Stack Overflow

Query=>Array=>Output = PHP Warning: Illegal offset type

4 Answers 4

1 Comment

1 Comment

5 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

5 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related