I'm writing a complicated database query using PHP and MySQLi.
$SQLstring = "
SELECT * FROM (SELECT `Index`, `Type`, `Date`, `User`, `Site`, `acronym`, `ShiftIndex`, `TaskName`, `Text` FROM `shiftentry`
INNER JOIN (SELECT `Index` AS `shiftIndex`, `Date`, `User`, `Site`, `Status` FROM `shiftreports`) `shiftreports` on `ShiftReport` = `shiftIndex`
INNER JOIN (SELECT `Index` AS `siteIndex`, `acronym` FROM `sites`) `sites` ON `Site` = `siteIndex`
INNER JOIN (SELECT `Index` AS `taskIndex`, `TaskName` FROM `tasks`) `tasks` on `Status` = `taskIndex`) AS a
WHERE `Type` < '4' ".$searchstring."
UNION
SELECT * FROM (SELECT `Index`, `Type`, `Date`, `User`, `Site`, `acronym`, `ShiftIndex`, `TaskName`, `process`.`Text` AS `Text` FROM `shiftentry`
INNER JOIN (SELECT `Index` AS `shiftIndex`, `Date`, `User`, `Site`, `Status` FROM `shiftreports`) `shiftreports` on `ShiftReport` = `shiftIndex`
INNER JOIN (SELECT `Index` AS `siteIndex`, `acronym` FROM `sites`) `sites` ON `Site` = `siteIndex`
INNER JOIN (SELECT `Index` AS `taskIndex`, `TaskName` FROM `tasks`) `tasks` on `Status` = `taskIndex`
INNER JOIN (SELECT `Index` AS `statusIndex`, `process`, `progress`, `Comment` FROM `site-status`) `site-status` on `shiftentry`.`Text` = `statusIndex`
INNER JOIN (SELECT `Index` AS `processIndex`, `Processname` AS `Text` FROM `process`) `process` on `process` = `processIndex`) AS b
WHERE `Type` = '4' ".$searchstring."
UNION
SELECT * FROM (SELECT `Index`, `Type`, `Date`, `User`, `Site`, `acronym`, `ShiftIndex`, `TaskName`, `site-status`.`Text` AS `Text` FROM `shiftentry`
INNER JOIN (SELECT `Index` AS `shiftIndex`, `Date`, `User`, `Site`, `Status` FROM `shiftreports`) `shiftreports` on `ShiftReport` = `shiftIndex`
INNER JOIN (SELECT `Index` AS `siteIndex`, `acronym` FROM `sites`) `sites` ON `Site` = `siteIndex`
INNER JOIN (SELECT `Index` AS `taskIndex`, `TaskName` FROM `tasks`) `tasks` on `Status` = `taskIndex`
INNER JOIN (SELECT `Index` AS `statusIndex`, `process`, `progress`, `Comment` AS `Text` FROM `site-status`) `site-status` on `shiftentry`.`Text` = `statusIndex`) AS c
WHERE `Type` = '4' ".$searchstring."
ORDER BY `Date` DESC;";
echo $SQLstring;
$shiftentries=mysqli_query($conn, $SQLstring);
echo mysqli_num_rows($shiftentries);
while($shiftentry = mysqli_fetch_array($shiftentries)) {
...
}
The php is printing the SQL string in the very end. When I take exactly this string and copy&paste it to the phpMyAdmin SQL input field it will return a single result line
SELECT * FROM (SELECT `Index`, `Type`, `Date`, `User`, `Site`, `acronym`, `ShiftIndex`, `TaskName`, `Text` FROM `shiftentry` INNER JOIN (SELECT `Index` AS `shiftIndex`, `Date`, `User`, `Site`, `Status` FROM `shiftreports`) `shiftreports` on `ShiftReport` = `shiftIndex` INNER JOIN (SELECT `Index` AS `siteIndex`, `acronym` FROM `sites`) `sites` ON `Site` = `siteIndex` INNER JOIN (SELECT `Index` AS `taskIndex`, `TaskName` FROM `tasks`) `tasks` on `Status` = `taskIndex`) AS a WHERE `Type` < '4' AND `Text` LIKE '%matching%' UNION SELECT * FROM (SELECT `Index`, `Type`, `Date`, `User`, `Site`, `acronym`, `ShiftIndex`, `TaskName`, `process`.`Text` AS `Text` FROM `shiftentry` INNER JOIN (SELECT `Index` AS `shiftIndex`, `Date`, `User`, `Site`, `Status` FROM `shiftreports`) `shiftreports` on `ShiftReport` = `shiftIndex` INNER JOIN (SELECT `Index` AS `siteIndex`, `acronym` FROM `sites`) `sites` ON `Site` = `siteIndex` INNER JOIN (SELECT `Index` AS `taskIndex`, `TaskName` FROM `tasks`) `tasks` on `Status` = `taskIndex` INNER JOIN (SELECT `Index` AS `statusIndex`, `process`, `progress`, `Comment` FROM `site-status`) `site-status` on `shiftentry`.`Text` = `statusIndex` INNER JOIN (SELECT `Index` AS `processIndex`, `Processname` AS `Text` FROM `process`) `process` on `process` = `processIndex`) AS b WHERE `Type` = '4' AND `Text` LIKE '%matching%' UNION SELECT * FROM (SELECT `Index`, `Type`, `Date`, `User`, `Site`, `acronym`, `ShiftIndex`, `TaskName`, `site-status`.`Text` AS `Text` FROM `shiftentry` INNER JOIN (SELECT `Index` AS `shiftIndex`, `Date`, `User`, `Site`, `Status` FROM `shiftreports`) `shiftreports` on `ShiftReport` = `shiftIndex` INNER JOIN (SELECT `Index` AS `siteIndex`, `acronym` FROM `sites`) `sites` ON `Site` = `siteIndex` INNER JOIN (SELECT `Index` AS `taskIndex`, `TaskName` FROM `tasks`) `tasks` on `Status` = `taskIndex` INNER JOIN (SELECT `Index` AS `statusIndex`, `process`, `progress`, `Comment` AS `Text` FROM `site-status`) `site-status` on `shiftentry`.`Text` = `statusIndex`) AS c WHERE `Type` = '4' AND `Text` LIKE '%matching%' ORDER BY `Date` DESC;
The php mysqli_query unfortunately returns zero lines for exactly the same query string.
echo mysqli_num_rows($shiftentries);
returns 0 and no data is displayed. This is an example where the query should just return a single entry when the keyword I'm searching for is "matching". In case I use different keywords it will return multiple lines but always one line less than phpMyAdmin.
It's strange! Do you have any idea what's going on here?