1

My database looks like this:

enter image description here

I have a variable that looks like this:

$following = "John, Sarah";

I would like to get the rows where the column 'username' is in the variable $following (in this case, John and Sarah). To do this, I had a look at the answer https://stackoverflow.com/a/1356018/5798798 which suggested I use IN in my query, which I have attempted:

$following = "John, Sarah";
$stmt = $con->prepare("SELECT * FROM events WHERE username IN ('$following')");
$stmt->execute();
while($row = $stmt->fetch()) {
   echo $row['eventtype'];
}

The problem is that the query is returning no data. My desired result would be:

spoke walked

The Codesee
  • 3,714
  • 5
  • 38
  • 78
  • you didn't choose the `eventtype` column in the echo'd row, far as I can tell. – Funk Forty Niner Mar 09 '18 at 18:00
  • oh so that changed. – Funk Forty Niner Mar 09 '18 at 18:00
  • `echo $following = join("', '", $following);` - Tell me what that shows. I don't know how you're building your array, but that should be failing you here, being the single quotes.... *maybe*. @TheCodesee – Funk Forty Niner Mar 09 '18 at 18:07
  • @FunkFortyNiner Actually I originally had a string... but now I realising I'm converting a string into an array to just turn it back into a string. The string is simply `John, Sarah` I'll edit my question – The Codesee Mar 09 '18 at 18:11
  • `$following = array("John", "Sarah"); echo $following = join(", ", $following);` this for me produced `John, Sarah` @TheCodesee should this be what you're looking for. But I may be wrong about it. – Funk Forty Niner Mar 09 '18 at 18:12
  • can you try this to see if this is what you're after? TBH, I haven't worked with arrays in years. `$following = "John, Sarah"; $following = explode(",", $following); $string = implode(", ", $following); echo $string;` @TheCodesee then use `$string` in the query; just an example here. Edit: might not add the quotes for each name in the query :( – Funk Forty Niner Mar 09 '18 at 18:29
  • @FunkFortyNiner Thanks, think I managed to do it by slightly modifying your answer to `$following = explode(", ", $following); $following = implode("','",$following);` Thank you! – The Codesee Mar 09 '18 at 18:37
  • @TheCodesee you're welcome. Funny that; I was thinking (doing similar) the same thing but just couldn't get that `implode()` to work the way I wanted it to. – Funk Forty Niner Mar 09 '18 at 18:38
  • @TheCodesee what would you like to do with the question? since there are other answers posted. I'm not for points (anymore, heh). Either you or I can post one to mark it off. If you want me to do it, I'll give kudos to you, or vice-versa. Either way is fine with me. – Funk Forty Niner Mar 09 '18 at 18:41
  • I take it you logged out then. – Funk Forty Niner Mar 09 '18 at 18:55
  • How is `$following` generated? Is this coming from user supplied data? Or are you hardcoding this? – mickmackusa Mar 13 '18 at 04:29

3 Answers3

1
$following = join("', '", $following);

join no more returns an array. It is a string now.

You can use like this:

$in  = str_repeat('?,', count($following ) - 1) . '?';
$stmt = $con->prepare("SELECT * FROM events WHERE username IN ($in)");
$stm->execute($following);
Thamilhan
  • 13,040
  • 5
  • 37
  • 59
  • @TheCodesee if you use placeholders (`?`) as shown in the second example here, you'd need to either pass your array of values to `execute`, like `$stmt->execute($following);`, or bind the values individually before executing. – Don't Panic Mar 09 '18 at 18:28
  • @Don'tPanic Yeah thought OP will find from the link. Updated! – Thamilhan Mar 09 '18 at 18:42
  • @Thamilan Well, I was only guessing that that was why they said it didn't return anything. Who knows, though. :) – Don't Panic Mar 09 '18 at 18:43
1

From what I suggested in comments to use the following:

$following = "John, Sarah"; 
$following = explode(", ", $following); 
$string = implode(", ", $following);

It ended up that I didn't include the quotes for the implode()'ing.

The final solution was to add the single quotes in the first parameter for the implode() function:

$following = implode("','",$following);
Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
-2

with out using join you directly implode array by the following way

$stmt = $con->prepare('SELECT * FROM events WHERE username IN ("'. implode('","', $following).'")');
$stmt->execute();
while($row = $stmt->fetch()) {
   echo $row['eventtype'];
}

Note: $following always should be in array

jvk
  • 2,133
  • 3
  • 19
  • 28