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
– Gordon Linoff, Commented Nov 19, 2015 at 17:14
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
– Funk Forty Niner, Commented Nov 19, 2015 at 17:19
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
– Funk Forty Niner, Commented Nov 19, 2015 at 17:22
You might want to use association tables rather than storing the information the way you are storing it. — Maximus2012
– Maximus2012, Commented Nov 19, 2015 at 17:26
@Drew I found a few that might be duplicates stackoverflow.com/questions/12100434/… and 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
– Funk Forty Niner, Commented Nov 19, 2015 at 17:41

4 revs, 2 users 87% · Accepted Answer · 2017-05-23 10:30:28Z

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?

Collectives™ on Stack Overflow

LIKE SQL with parameters throwing Invalid Parameter Number

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related