CODEWITHSUNDEEP
phpmysqlsql
Tim C
Tim C

Reputation: 5714

PHP SQL query error - Unknown array column in WHERE clause

My page displays the name of players of a certain sports team using drop down menus. The coach can log in to pick his team and select the opposition which his team will play against. When user has selected the team and opposition he clicks submit and the isset function is triggered.

Now I capture the values from the drop down menus and upload it to the correct table in the DB. Everything is pretty straight forward however when I click submit I get the error in the tittle. Any help would be appreciated

if ( isset($_POST['submit']) ) {
    $player_ids = array_map('intval', $_REQUEST['players']);
    $opponents_id = $_REQUEST['players'];

    var_dump($player_ids);
    var_dump($opponents_id);

    $query = 'SELECT `name`, `position` 
        FROM `player_info` 
        WHERE `player_id` IN (' . implode(',', $player_ids) . ')';

    $return_names = mysql_query($query) or die(mysql_error());

         while ( $row = mysql_fetch_assoc($return_names) ) 
        {
            $selected[] = $row['name'];
            $position[] = $row['position'];
        }

    $query = ("SELECT `fixture_id` 
                FROM `fixtures`     
                WHERE `fixture_id` = $opponents_id") 
                or die (mysql_error()); 

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

                while ($row = mysql_fetch_array($query))
                {
                    $fixture_id[] = $row['fixture_id']; 

                }
                        for ($i=0; sizeof($selected) > $i; $i++){
                             $sql = mysql_query("INSERT INTO `team` (`selection_id`, `fixture_id`, `player_position`,`player_name`) 
                                                VALUES ('$fixture_id[$i]','$position[$i]','$selected[$i]')") 
                                                or die(mysql_error());
                                echo $selected[$i]; 
                                echo $position[$i];
                                echo $fixture_id[$i];
                                echo'<br>';


}

enter image description here

enter image description here

Upvotes: 2

Views: 5618

Answers (4)

Morteza Soltanabadiyan
Morteza Soltanabadiyan

Reputation: 638

bodi0 is right, your $opponents_id is an array , if it must be an array so do some things like that 

$opponents_id_text=implode(',',$opponents_id);
$query = ("SELECT `fixture_id` 
                FROM `fixtures`     
                WHERE `fixture_id` in ($opponents_id_text)") 
                or die (mysql_error());

Upvotes: 1

Jeremy Harris
Jeremy Harris

Reputation: 24549

The Unknown column 'Array' in 'where clause' error means literally what it says -- you tried to put an array value into your where clause.

In this line:

$query = ("SELECT `fixture_id` 
            FROM `fixtures`     
            WHERE `fixture_id` = $opponents_id") 
            or die (mysql_error());

You are using the $opponents_id variable which your var_dump shows is an array containing five values. So, you need to use the IN clause and list them out (just like you did for $player_ids):

$query = ("SELECT `fixture_id` 
            FROM `fixtures`     
            WHERE `fixture_id` IN (" . implode(",", $opponents_id) . ");") 
            or die (mysql_error());

Note: Hopefully you are aware of the tiring subject of the mysql_* family of functions. These are being deprecated and are insecure. I wrote about it a while back: http://www.jorble.com/2012/06/you-are-vulnerable-for-sql-injection/

Upvotes: 3

Harika Choudary Kanikanti
Harika Choudary Kanikanti

Reputation: 757

you did not specified Array properly in sql query.

when we use Arrays we have to specify array number in query

Upvotes: -1

Bud Damyanov
Bud Damyanov

Reputation: 31829

The problem is in your second SQL query: 

WHERE `fixture_id` = $opponents_id" -

Here the $opponents_id is array (it is converted automatically to array when you assign the value to it from the $_REQUEST, because the HTML component select sends the options as array). Just implode() it also, like you did for $player_ids.

Upvotes: 0

Related Questions