0

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.

(SELECT ? searchstring) S

but the following will result in Unknown column 'S.searchstring' in 'on clause'.

<?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->real_connect('raspi', 'root', 'xxxx', 'dbtest');
$sqlStatement = $con->prepare($sqlQuery);
if ($sqlStatement === false)
{
    exit($con->error);
}
else
{
    exit("success");
    // Bind parameter on statement ...
}
?>

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:

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

Any ideas - i think its a problem from mysqli itself maybe?

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

Following post seem to be a related problem: mysqli-- prepare statement failing with error "no table used"

SELECT * FROM (SELECT ? AS searchstring) S

give a error "no tables used".

SELECT * FROM (SELECT ? AS searchstring FROM mysql.user LIMIT 1) S

still does not "use" the table mysql.user so it is still the same error.

SELECT * FROM (SELECT ? AS searchstring, user FROM mysql.user LIMIT 1) S

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

Community
  • 1
  • 1
René W.
  • 150
  • 1
  • 9
  • Look at this revised `mysqli` answer of mine [Here](http://stackoverflow.com/a/32145480) half-way down where it says `Revision1 (PHP shown)`. And look at the heredoc `<< – Drew Sep 11 '16 at 00:34
  • I don't think you can use a parameter for column names. `SELECT ? AS searchstring` it's much easier to concatenate it in, as long as you white list the columns and don't put user input directly in the sql ( say with a switch ) then it should be fine. This is something you should know before executing the query, even if there is a range of acceptable column names. – ArtisticPhoenix Sep 11 '16 at 03:36
  • Thank you for your Infos, until now i think there is no comfortable solution so i made it like adding the same parameter 3 times and doing the 3 compares with each one of them instead of making the parameter a available "columnname" to compare it 3 times in a join. – René W. Nov 14 '16 at 09:05

0 Answers0