Strange Results from MySQLi Prepared Statement With Wildcard (Procedural)

Question

Slightly strange one this... I'm using mysqli prepared statements (including wildcard) with bound results but although the SQL statement works in phpmyadmin, I can't get it to output correctly in the php file.

Can anyone spot what I'm doing wrong?

$servername = "XXXX"; $username = "XXXX"; $password = "XXXX"; $dbname = "XXXX";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

if (!isset($_GET['searchfield'])) {
    $query_string = NULL;
} else {
    $query_string = $_GET['searchfield'];
}

$stmt = mysqli_prepare($conn, "
SELECT CONTACTS.CONTACTID, CONTACTS.COMPANY, CONTACTS.FORENAME, 
CONTACTS.SURNAME, CONTNOTES.NOTESID, CONTNOTES.NOTESCONTACTID, 
FILEATT.ATTNOTEID, FILEATT.LONGNOTE, FILEATT.CREATEDATE
FROM CONTACTS
INNER JOIN CONTNOTES
   ON CONTACTS.CONTACTID = CONTNOTES.NOTESCONTACTID
INNER JOIN FILEATT
   ON CONTNOTES.NOTESID = FILEATT.ATTNOTEID
WHERE FILEATT.LONGNOTE LIKE CONCAT('%',?,'%')
ORDER BY FILEATT.CREATEDATE ASC
");

mysqli_stmt_bind_param($stmt, "s", $query_string);// bind parameters
mysqli_stmt_execute($stmt);// execute query
mysqli_stmt_bind_result($stmt, $CONTACTID, $COMPANY, $FORENAME, $SURNAME, $NOTESID, $NOTESCONTACTID, $ATTNOTEID, $LONGNOTE, $CREATEDATE);
$rowcount = mysqli_stmt_num_rows($stmt);

if($rowcount > 0){

    while (mysqli_stmt_fetch($stmt)) {

        echo stuff i.e $COMPANY;
    }

}

mysqli_stmt_close($stmt);
mysqli_close ($conn);

Firstly, I'm not getting a value in $rowcount

Secondly, if I comment out if($rowcount > 0){ then it seems to loop through 2 rows (there should be hundreds) before throwing an error:

Warning: mysqli_stmt_fetch() expects parameter 1 to be mysqli_stmt, null given in filename.php

If I echo a longnote field, it just turn out as non-recognisable gibberish.

Any ideas? It's probably something simple I've missed.

I dont see any handling of mysqli errors. Add this to the top of your script: `mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` — IncredibleHat, Jul 11 '18 at 12:57
@IncredibleHat - thanks - no difference to the output though. — Richard Owens, Jul 11 '18 at 12:58
That seems odd. Because the reason $stmt would be null, is if mysqli crapped out, and the error reporting should be tossing out a spew of why it did. — IncredibleHat, Jul 11 '18 at 13:01
If I understand your query right, you can't concat prepared params like this, you need to do it like: `where `field` like ?` and `('%'. $foo .'%',)` — treyBake, Jul 11 '18 at 13:02
@ThisGuyHasTwoThumbs Yes but you have it a little off. It actually would be just sql: `FILEATT.LONGNOTE LIKE ?` ... and then adjust the variable content: `$query_string = '%'. $query_string .'%';`... and then bind by reference as normal: `mysqli_stmt_bind_param($stmt, "s", $query_string);` — IncredibleHat, Jul 11 '18 at 13:05
^^ ah used to PDO shizzle (though maybe still the case for PDO in this scenario) either way OP.. do that^^ — treyBake, Jul 11 '18 at 13:07
However in all fairness to the OP ... I do believe the weird `LIKE CONCAT` method *still works* ... its just something I've never done or seen as it just seems very weird ;) — IncredibleHat, Jul 11 '18 at 13:10
@IncredibleHat in isolation, either method actually works and I have tried both. I'm not sure the wildcard aspect is what's at fault here. - also, please note I'm working with MySQL(i) version 5.0.27 which might explain the lack of error report. — Richard Owens, Jul 11 '18 at 13:54
You could also try `ehco mysqli_error($conn);` Right after the prepare or execute, or both, or all ;) — IncredibleHat, Jul 11 '18 at 13:58
@ThisGuyHasTwoThumbs thanks for the feedback - I've opted for the MySQLi method as is (should) be easier upgrading old MySQL statements with santitised inputs. Also - I'm not so good with OOP so this procedural process seemed easier at the time... in hindsight! — Richard Owens, Jul 11 '18 at 13:59
I did notice you began with OOP in `$conn = new mysqli...` but then switched to procedural for the rest. I generally suggest not mixing them. Go with one way or the other (whichever you are comfortable with). — IncredibleHat, Jul 11 '18 at 14:02
@IncredibleHat - OK - this get weirder - when I insert the echo mysqli_error($conn), it breaks the page! "This page isn’t working 200.1.1.20 didn’t send any data.ERR_EMPTY_RESPONSE" WTF? Good call with the mix of procedural and OOP. — Richard Owens, Jul 11 '18 at 14:34
Do I need to store a result (mysqli_store_result())? I've not had this problem before... I think I'll have to break it down to really basic components and test it until I get a good result. — Richard Owens, Jul 11 '18 at 14:38

score 0 · Answer 1 · answered Jul 12 '18 at 08:43

Finally worked this out.

It's an anomaly with longtext fields. My FILEATT.LONGNOTE field is longtext and the output is just garbage which was throwing out the rest of the page. You do have to use mysqli_stmt_store_result($stmt); after executing. That seems to have solved the problem.

See Prepared mysqli select statement on longtext field is coming back empty

Strange Results from MySQLi Prepared Statement With Wildcard (Procedural)

1 Answers1