Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

Here is my Array:

array (size=2)
  100 => 
    array (size=2)
      'trade' => int 5
      'gold' => int 10
  101 => 
    array (size=2)
      'trade' => int 10
      'gold' => int 20

The key of the Array is the ID of the users which I want to run in a query. I figured out I probably need to use IN, example:

    SELECT * FROM `users` WHERE `id` IN (:id)

(:id is replaced by $id in the script)

Now when I do 

     SELECT * FROM `users` WHERE `id` IN (100,105)

(for test purposes) it works.

Although it needs to be prepared so when I set 

$id = 100,101;

or 

$id = ‘100,101’;

it only displays the 100 record.

Any ideas? Thanks

share|improve this question
Unsure what you are asking. Are you asking how you would go about changing this to a prepared statement? – Shane Nov 30 '12 at 9:32
I am asking why it works when I put it directly into the query and not when assigned in a prepared statement - Thanks. – MrGuitarWizard Nov 30 '12 at 9:34
If you are passing the details through mysqli I think what has happened is that it has passed over that you want an id of "100, 101" rather than an id of 100 or an id of 101. Wouldn't expect that to work, but if it has I would assume that mysql has translated the string "100, 101" to an integer by dropping the first non numeric and anything after (ie the comma and 101), so matching against just 100. Passing variable length lists of items for a like is a pain as a parameter! – Kickstart Nov 30 '12 at 9:54

2 Answers

up vote 0 down vote

they are both wrong what u have done $id = 100,101; and $id = ‘100,101’; try this 

  $id = "100,101";

or this

  $id = '100,101';

or even can do this also

    $id = 100;
    $id .= ",";
    $id .= 101 ;
share|improve this answer
up vote 0 down vote

You can use implode and array_keys:

"SELECT * FROM `users` WHERE `id` IN (" . implode(",", array_keys($array)) . ")"

You can test this by using / specifying the array manually:

$array = array(
    100 => array(
        'trade' => 5,
        'gold' => 10,
    ),
    101 => array(
        'trade' => 10,
        'gold' => 20,
    )
);

$sql = "SELECT * FROM `users` WHERE `id` IN (" . implode(",", array_keys($array)) . ")";
$result = mysqli_query($sql);

while($row = mysqli_fetch_assoc($result)){
    var_dump($row);
}

It's been actually tested and it works.

share|improve this answer
Great idea but this works like that, loading it directly into the query, but when I prepare it in a statement it just loads the first one in the list, any ideas? Thanks – MrGuitarWizard Nov 30 '12 at 10:51
foreach(implode(",", array_keys($array) as $value) - Try a foreach loop. Use $value in the bind. – Shane Nov 30 '12 at 10:57
Have you tried it yet? This should be the same as SELECT * FROM users WHERE id IN (100,105) – SubRed Nov 30 '12 at 10:58
This doesn't work I get: Warning: Invalid argument supplied for foreach() Don't know why though $array is defined: $array = $_COOKIE['cart']; – MrGuitarWizard Nov 30 '12 at 12:59
Try to var_dump($array); and see what actually your $array is. Make sure that your $array does have keys like what you write on your question. – SubRed Nov 30 '12 at 13:49
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.