Trouble with prepared MySQL Statement in PHP using a subselect
<p>For using the same Parameter multiple times, i would like to use a subselect in a join to give the parameter to sql. Similar does work with MS Sql pdo, and it does work like the last codeblock in mysql workbench.</p>

<pre><code>(SELECT ? searchstring) S
</code></pre>

<p>but the following will result in Unknown column 'S.searchstring' in 'on clause'.</p>

<pre><code>&lt;?php
#create table mysearchtable (somecolumn NVARCHAR(255), othercolumn NVARCHAR(255), lastcolumn NVARCHAR(255));
$sqlQuery = "SELECT A.* FROM (SELECT ? searchstring) S INNER JOIN mysearchtable A ON A.somecolumn LIKE CONCAT('%', S.searchstring, '%') OR A.othercolumn LIKE CONCAT('%', S.searchstring, '%') OR A.lastcolumn = S.searchstring ";
$con = new mysqli();
$con-&gt;real_connect('raspi', 'root', 'xxxx', 'dbtest');
$sqlStatement = $con-&gt;prepare($sqlQuery);
if ($sqlStatement === false)
{
    exit($con-&gt;error);
}
else
{
    exit("success");
    // Bind parameter on statement ...
}
?&gt;
</code></pre>

<p>I tried to use a parameter but i the prepared statement seem to execute only one statement at once and it does not work in php:</p>

<pre><code>SET @var_X = 'searchcontent';
SELECT A.* FROM (SELECT @var_X searchstring) S INNER JOIN mysearchtable A
    ON A.somecolumn LIKE CONCAT('%', S.searchstring, '%')
    OR A.othercolumn LIKE CONCAT('%', S.searchstring, '%')
    OR A.lastcolumn = S.searchstring
</code></pre>

<p>Any ideas - i think its a problem from mysqli itself maybe?</p>

<p>Thanks to Drew to the comment using a variable instead.
this is maybe not what i want but maybe i have to use that.</p>

<p>Following post seem to be a related problem:
<a href="https://stackoverflow.com/questions/19258448/mysqli-prepare-statement-failing-with-error-no-table-used">mysqli-- prepare statement failing with error &quot;no table used&quot;</a></p>

<pre><code>SELECT * FROM (SELECT ? AS searchstring) S
</code></pre>

<p>give a error "no tables used".</p>

<pre><code>SELECT * FROM (SELECT ? AS searchstring FROM mysql.user LIMIT 1) S
</code></pre>

<p>still does not "use" the table mysql.user so it is still the same error.</p>

<pre><code>SELECT * FROM (SELECT ? AS searchstring, user FROM mysql.user LIMIT 1) S
</code></pre>

<p>does not give an error but surprising the column searchstring is not contained in the result :(</p>