PHP SQL query error - Unknown array column in WHERE clause

Question

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

Don't you have a missing value in your insert? You want to populate 4 columns in the INSERT, but you provide only 3 values - value for SELECTION_ID is missing. — Przemyslaw Kruglej
– Przemyslaw Kruglej, Commented Oct 10, 2013 at 11:57
"WHERE fixture_id = $opponents_id". $opponents_id is an array, too. — bitWorking
– bitWorking, Commented Oct 10, 2013 at 11:59

Jeremy Harris · Accepted Answer · 2013-10-10 12:17:30Z

3

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/

edited Oct 10, 2013 at 12:17

answered Oct 10, 2013 at 12:01

Jeremy Harris

24.7k13 gold badges84 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jeremy Harris Over a year ago

No worries. Good luck on your project.

Tim C Over a year ago

can you please have a quick look at the code for your solution. There is a syntax error. I cant spot it...

Jeremy Harris Over a year ago

Sorry, forgot to add the right parenthesis wrapped around the query string. Fixed it.

Morteza Soltanabadiyan · Accepted Answer · 2013-10-10 12:29:42Z

1

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());

answered Oct 10, 2013 at 12:29

Morteza Soltanabadiyan

6383 silver badges15 bronze badges

Comments

Bud Damyanov · Accepted Answer · 2013-10-10 12:01:20Z

0

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.

answered Oct 10, 2013 at 12:01

Bud Damyanov

32.1k7 gold badges48 silver badges53 bronze badges

Comments

Harika Choudary Kanikanti · Accepted Answer · 2013-10-10 12:02:21Z

-1

you did not specified Array properly in sql query.

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

answered Oct 10, 2013 at 12:02

Harika Choudary Kanikanti

7572 gold badges7 silver badges16 bronze badges

1 Comment

James A Mohler Over a year ago

Do you have any suggestions on how to specify it properly?

Collectives™ on Stack Overflow

PHP SQL query error - Unknown array column in WHERE clause

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related