Changing 2 IDs and COMMAs to a variable in PDO

[joon1]

$poster=11;
$like='%key%';
$idOr='and (id=? or id=?)';
$idComma='5836, 5837, '; 

$sql="SELECT id, title, contents
FROM myTable
WHERE member=? $idOr and ( title like ? or contents like ?) ";
$searchQ=$dbc-> prepare ($sql);
$searchQ->execute([$poster, 5836, 5837, $like, $like]);

The code above works fine.

I like to change the id number "5836, 5837, " to a variable named “$idComma”.
The following is one of my trials for it.

$searchQ->execute([$poster, $idComma$like, $like]);

However, the code above produces the error below.

syntax error, unexpected variable “$like”, expecting “]”

The following is the 2nd trials of mine for it.

$searchQ->execute([$poster, $idComma $like, $like]);

However, the code above also produces the error below.

syntax error, unexpected variable “$like”, expecting “]”

The following is the 3rd trials of mine for it.

$searchQ->execute([$poster, $idComma.$like, $like]);

However, the code above produces the Fatal error below.

Fatal error : Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number

How can I change the id and comma “5836, 5837,” to a variable $idComma" in the code below?

$searchQ->execute([$poster, 5836, 5837, $like, $like]);

[John_Betong]

@joon1

$searchQ->execute([$poster, $idComma$like, $like]);

Try echo $sql and also try curly braces around the $like:

$searchQ->execute([$poster, $idComma{$like}, $like]);

[m_hutley]

@joon1

I like to change the id number "5836, 5837, " to a variable named “$idComma”.

$idComma = array(5836,5837);
then
$searchQ->execute([$poster, ...$idComma, $like, $like]);

(This is called the “Splat Operator” or “Spread Operator”)

[mabismad]

Building a string that looks LIKE the php syntax, doesn’t work.

You would dynamically build an array, $params for example, that contains the values that correspond to the place-holders. Where is $idComma actually coming from?

$params[] = $poster;
$params[] = 5836; // dynamically add the values from wherever they are coming from
$params[] = 5837; // ...
$params[] = $like;
$params[] = $like;
$searchQ->execute($params);

[joon1]

@mabismad

Where is $idComma actually coming from?

$sql="SELECT kid
FROM momKid
WHERE mom=?"
$kidQ=$dbc-> prepare ($sql);
$kidQ->execute([$postID]);

$idOr='and (postID=?';  // initializing of $idOr
$idComma=$postID. ', '; // initializing of $idComma
while ($kidL=$kidQ->fetch()) {
$idOr=$idOr. ' or postID=?';
$idComma=$idComma.$kidL['kid']. ', ';
}
$idOr=$idOr. ') '; // finalizing of $idOr

The post which has postID “5836” has a kid post “5837” in this example.

[mabismad]

Rather than building strings, with the ANDs, ORs, and commas all in the correct place, I recommend that you build the various terms in arrays, then implode the arrays with the ’ AND ’ or ’ OR ’ keywords to build the actual sql statement.

<?php

// while this should be done using one query, it would require having all the relevant information...

$postID = 5836;
$poster=11;
$like='%key%';

// i'm wondering of this query can match zero or more rows?
// at a minimum, the $postID term/value will be in the main query
$sql="SELECT kid
FROM momKid
WHERE mom=?";
$kidQ=$dbc-> prepare ($sql);
$kidQ->execute([$postID]);

// use arrays for these
$idTerms[] = 'postID=?';  // initializing of $idOr
$idValues[] = $postID; // initializing of $idComma
while ($kidL=$kidQ->fetch())
{
	$idTerms[] = 'postID=?';
	$idValues[] = $kidL['kid'];
}

// i'm also wondering if some of the WHERE ... terms are conditional and may not be present?
// using the following method, you can put conditional statements around any section to only include it if an input value is present
$where_terms = []; // an array to hold the WHERE terms that will be ANDed
$params = []; // an array to hold the bound input values

// build the WHERE terms
// member
$where_terms[] = "member=?";
$params[] = $poster;

// postID
$where_terms[] = "(".implode(' OR ',$idTerms).")";
$params = array_merge($params, $idValues);

// title/contents like
$where_terms[] = "(title LIKE ? OR contents LIKE ?)";
$params[] = $like;
$params[] = $like;


$where = '';
if(!empty($where_terms))
{
	$where = "WHERE " . implode(' AND ',$where_terms);
}

$sql="SELECT id, title, contents
FROM myTable
$where";
$searchQ=$dbc-> prepare ($sql);
$searchQ->execute($params);

[joon1]

I am afraid it produces the following.

Fatal error : Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘)’ at line 3

I guess the line 3 means “$where” in the following.

$sql="SELECT id, title, contents
FROM myTable
$where";

[joon1]

Please, I am sorry, ignore my post #7, I am still working on it.

[joon1]

Now it says the following.

Warning : Array to string conversion

Fatal error : Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number

$sql="SELECT id, title, contents
FROM myTable
$where";
$searchQ=$dbc-> prepare ($sql);
$searchQ->execute([$params]);  // Waring and Fatal error line

echo $params[0];
echo $params[1];
echo $params[2];
echo $params[3];
echo $params[4];
echo $where;

As I echo like the above for checking, it produces the following

11
5836
5837
%key%
%key%
WHERE poster=? AND (postID=? OR postID=?) AND ( title LIKE ? OR contents LIKE ?)

I don’t see what’s wrong.
Why does it say Warning and Fatal error?
Do you see any?

[mabismad]

The execute() call accepts an array of values. $params is an array.
Your line of code - $searchQ->execute([$params]);
My line of code - $searchQ->execute($params);

[joon1]

@mabismad

My line of code - $searchQ->execute($params);

Thank you, it works fine.