LIKE SQL with parameters throwing Invalid Parameter Number

Question

So I'm trying to use a "LIKE" in the SQL, basically to see if a player is in a team already. Here's the code I have:

    $checkifonlytwo = "SELECT * FROM sg_turn_teams WHERE joinid = :joinid AND players LIKE '%:ownerid,%'";
    $paramstwo = array(
        ":joinid" => $joinid,
        ":ownerid" => $_SESSION['user']['id']
    );
    try{
    $stmttwo = $db->prepare($checkifonlytwo);
    $resulttwo = $stmttwo->execute($paramstwo);
    }
    catch(PDOException $ex){
        die("Failed to run query #2: " . $ex->getMessage());
    }

Also as you can see I want it to be LIKE '%1,%' for example, so the comma at the end too.

My table structure looks like this.

EDIT, the players is going to be like "1,2,3" without the names because the users are able to change their name. The picture is with names, but it's supposed to be 1,2,3

Fix your data structure so you are not storing integer foreign keys in a string list. — Gordon Linoff, Nov 19 '15 at 17:14
@GordonLinoff How do you mean? I do so because it's the IDs of the users, but there can be several so I need a list of them, like "1,2,3,4" — Odin, Nov 19 '15 at 17:15
echo $checkifonlytw first and paste the same in sql and check whether data exist on same scenario?? — PRANAV, Nov 19 '15 at 17:16
you're going about it the wrong way with the comma in there `ownerid,`. What you need to do is implode on the array and use `IN()`, far as I can tell. — Funk Forty Niner, Nov 19 '15 at 17:19
@PRANAV How would I go about echoing it with the parameters? I've actually tried echoing a duplicate string that used for example `LIKE '%".$_SESSION['user']['id'].",%'` and that looked perfect. — Odin, Nov 19 '15 at 17:19
@Maximus2012 My table structure looks like [this](http://i.imgur.com/Ou71LBa.png) — Odin, Nov 19 '15 at 17:20
I think you're trying to make it way more complicated than it needs to be. It'd be helpful if you could post your table structure along with some sample data and the desired output. — Maximus2012, Nov 19 '15 at 17:20
Please update the question to include that information rather than adding a comment that points to an external link. — Maximus2012, Nov 19 '15 at 17:21
if you only have the one id, use a regular `WHERE id = :ownerid` *n'est-ce pas?* Why use LIKE? Your question is unclear. — Funk Forty Niner, Nov 19 '15 at 17:22
Because I'm trying to look up if the person is in a team rather than owning one. If he is in the "players" column. I've posted sample data in the question. — Odin, Nov 19 '15 at 17:23
ok, my mistake. Again, see my first comment above. That's what you need to do. We need to know where those strings are coming from though and if the session was started. — Funk Forty Niner, Nov 19 '15 at 17:24
You might want to use association tables rather than storing the information the way you are storing it. — Maximus2012, Nov 19 '15 at 17:26
Alright thanks guys. I guess I thought this would work but I'll just make another table for players. If any of you want to leave that as an answer I'll be happy to mark it correct. — Odin, Nov 19 '15 at 17:28
@Maximus2012 perhaps bang out an example for him. Fame and fortune will shortly follow :) — Drew, Nov 19 '15 at 17:36
@Drew I found a few that might be duplicates http://stackoverflow.com/questions/12100434/php-pdo-select-array-within-in and http://stackoverflow.com/questions/2814466/php-pdo-mysql-in it's anybody's game. I'd hate to be the one having to justify code as examples and to get it to work with their present code. I think a wiki would be more the thing to do here. — Funk Forty Niner, Nov 19 '15 at 17:41
The moral of the story is it is treated like a `concat` because an `IN` with csv can't be bound — Drew, Nov 19 '15 at 17:46

score 4 · Accepted Answer · edited May 23 '17 at 10:30

Foreword. I decided to make this a community wiki. I did not want to gain anything from this, except for the OP and others visiting the question.

As I said in comments, you're going about it the wrong way with the comma in there ownerid,.

What you need to do is implode on the array and using IN().

Borrowed from https://stackoverflow.com/a/12151295/

$e = 0;

while($e<$num1){
$units = 0;
$r = 0;
$SO_Line_Item=mysql_result($result1,$e,"SO_Line_Item");

    foreach ($Boxes[$e] as $a => $b)  
    {

        $zzz[] = $Boxes[$e][$r];

$ce = count($Boxes[$e]);        

    $r++; 
    }
//end inner foreach

$products = implode(',', array_fill(0,$ce, '?'));

$db = new PDO('mysql:host=192.168.1.197 ;dbname=Tracking', $dbuser,$dbpass);
$stmt = $db->prepare("SELECT Box_Num,Timestamp,E3_SN,Assy_Status FROM Current_Box WHERE Box_Num IN( $products )");
$stmt->execute($zzz);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
unset($zzz);
$e++;
}

and from https://stackoverflow.com/a/2814703/

$ids = array(2, 4, 6, 8);

// prepare a string that contains ":id_0,..,:id_n" and include it in the SQL
$plist = ':id_'.implode(',:id_', array_keys($ids));
$sql   = "SELECT * FROM someTable WHERE someId IN ($plist)";
// prepare & execute the actual statement
$parms = array_combine(explode(",", $plist), $ids);
$stmt  = $PDO->prepare($sql);
$rows  = $stmt->execute($parms);

From comments:

"You might want to use association tables rather than storing the information the way you are storing it. – Maximus2012"

As mentioned in the comments, some questions/answers from SO that demonstrate the concept of Association tables along with composite primary keys:

Mysql : Association table

How to use an MySQL association table to return categories not currently assigned to an entry

If OP decides to go with this structure then the queries would need to be changed to make use of LEFT JOIN and/or use a sub-query with IN clause. There are plenty of examples of both on SO. One that I could find with a simple search (please ignore the slow query part and look at the examples to demonstrate the concept):

MySQL - SELECT WHERE field IN (subquery) - Extremely slow why?

LIKE SQL with parameters throwing Invalid Parameter Number

1 Answers1