11 Replies - 323 Views - Last Post: 23 April 2020 - 10:12 AM Rate Topic: -----

#1 vipery2kx   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 03-May 19

Array with MySQL "WHERE ... IN"

Posted 23 April 2020 - 08:12 AM

I'm having some problems understanding some StackOverflow answers with this.

A friend set me up fine with the first query (this is only an abridged version).
$getconacctssql = "SELECT `uid`, `username` FROM `accounts`";
$getconacctsstmt = $conn->prepare( $getconacctssql );
$getconacctsstmt->execute();
$conacctsrow = $getconacctsstmt->fetchAll();

print_r($conacctsrow);
// OUTPUT: Array ( [0] => Array ( [uid] => 1 [username] => vipery2kx ) [1] => Array ( [uid] => 2 [username] => nattwenty ) [2] => Array ( [uid] => 3 [username] => ordogon ) )



I need to pass the $conacctsrow array into a 2nd query that has the "IN" keyword(?), which is where I'm having a bit of trouble.
$getconspostssql = "SELECT * FROM `statusupdates` AS s WHERE `user` IN (?)";
$getconspostsstmt = $conn->prepare( $getconspostssql );
$getconspostsstmt->bindParam( 1, implode( ', ', $conacctsrow ) );
$getconspostsstmt->execute();

while ( $conspostrow = $getconspostsstmt->fetch() ) {
 // html output here, but no output
}



Is This A Good Question/Topic? 0
  • +

Replies To: Array with MySQL "WHERE ... IN"

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15781
  • View blog
  • Posts: 63,246
  • Joined: 12-June 08

Re: Array with MySQL "WHERE ... IN"

Posted 23 April 2020 - 08:16 AM

Elaborate on "having a bit of trouble" as that could be a multitude of things.


https://www.mysqltut...org/sql-in.aspx
https://www.w3resour...in-function.php
https://dev.mysql.co...-operators.html
Was This Post Helpful? 0
  • +
  • -

#3 vipery2kx   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 03-May 19

Re: Array with MySQL "WHERE ... IN"

Posted 23 April 2020 - 08:19 AM

After my 2nd query in my WHILE loop, there should be an output but there isn't. That is my trouble/problem. The table has data, but it isn't getting displayed.
Was This Post Helpful? 0
  • +
  • -

#4 Ornstein   User is offline

  • D.I.C Head

Reputation: 105
  • View blog
  • Posts: 216
  • Joined: 13-May 15

Re: Array with MySQL "WHERE ... IN"

Posted 23 April 2020 - 08:27 AM

$getconspostsstmt->bindParam( 1, implode( ', ', $conacctsrow ) );


Presumably the value you pass to implode needs to be an array of IDs, not an array of rows returned from the database.
Was This Post Helpful? 0
  • +
  • -

#5 astonecipher   User is offline

  • Enterprise Software Architect
  • member icon

Reputation: 3149
  • View blog
  • Posts: 11,954
  • Joined: 03-December 12

Re: Array with MySQL "WHERE ... IN"

Posted 23 April 2020 - 08:29 AM

You didnít put anything that shows you are trying to output anything
Was This Post Helpful? 0
  • +
  • -

#6 vipery2kx   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 03-May 19

Re: Array with MySQL "WHERE ... IN"

Posted 23 April 2020 - 08:30 AM

View PostOrnstein, on 23 April 2020 - 08:27 AM, said:

$getconspostsstmt->bindParam( 1, implode( ', ', $conacctsrow ) );


Presumably the value you pass to implode needs to be an array of IDs, not an array of rows returned from the database.

If you mean, doing $getconspostsstmt->bindParam( 1, implode( ', ', $conacctsrow['uid'] ) );, then I see this error "Warning: implode(): Invalid arguments passed"
Was This Post Helpful? 0
  • +
  • -

#7 Ornstein   User is offline

  • D.I.C Head

Reputation: 105
  • View blog
  • Posts: 216
  • Joined: 13-May 15

Re: Array with MySQL "WHERE ... IN"

Posted 23 April 2020 - 08:31 AM

It also just occurred to me that "WHERE ... IN (?)" might not work by itself. You might need to bind each value individually.
Was This Post Helpful? 1
  • +
  • -

#8 astonecipher   User is offline

  • Enterprise Software Architect
  • member icon

Reputation: 3149
  • View blog
  • Posts: 11,954
  • Joined: 03-December 12

Re: Array with MySQL "WHERE ... IN"

Posted 23 April 2020 - 08:46 AM

$placeholder = join(",", array_pad(array(), count($values), "?"));
$sql = "SELECT blah FROM table WHERE column IN ($placeholder)";
$stmt = $dbh->prepare($sql);
$stmt->execute($values);



I think the syntax is something like that.
Was This Post Helpful? 1
  • +
  • -

#9 Ornstein   User is offline

  • D.I.C Head

Reputation: 105
  • View blog
  • Posts: 216
  • Joined: 13-May 15

Re: Array with MySQL "WHERE ... IN"

Posted 23 April 2020 - 08:52 AM

$placeholder = join(",", array_pad(array(), count($values), "?"));


This works, but I imagine it'll be a lot slower than it needs to be.

$placeholder = '?'.str_repeat(',?', count($values) - 1);


Something like this should be faster.

This post has been edited by Ornstein: 23 April 2020 - 08:53 AM

Was This Post Helpful? 0
  • +
  • -

#10 Ornstein   User is offline

  • D.I.C Head

Reputation: 105
  • View blog
  • Posts: 216
  • Joined: 13-May 15

Re: Array with MySQL "WHERE ... IN"

Posted 23 April 2020 - 09:04 AM

View Postvipery2kx, on 23 April 2020 - 08:30 AM, said:

If you mean, doing $getconspostsstmt->bindParam( 1, implode( ', ', $conacctsrow['uid'] ) );, then I see this error "Warning: implode(): Invalid arguments passed"


I almost missed this one. What you've tried to do here is wrong on so many levels. :P You may benefit from reading up more on how arrays and such work in PHP.

To get the IDs in their own array, you're probably looking for something closer to:

$ids = array_column($conacctsrow, 'uid');

Was This Post Helpful? 0
  • +
  • -

#11 justawebuser   User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 83
  • Joined: 09-February 14

Re: Array with MySQL "WHERE ... IN"

Posted 23 April 2020 - 09:50 AM

I think you better do it this way
SELECT s.* FROM `statusupdates` AS s inner join `accounts` on s.user = accounts.uid

Was This Post Helpful? 2
  • +
  • -

#12 astonecipher   User is offline

  • Enterprise Software Architect
  • member icon

Reputation: 3149
  • View blog
  • Posts: 11,954
  • Joined: 03-December 12

Re: Array with MySQL "WHERE ... IN"

Posted 23 April 2020 - 10:12 AM

justawebuser's solution is the absolute cleanest based on what I am seeing.


I got lost in the forest and wasn't focusing on the actual goal.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1