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.
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