MySQLi using LIKE with URL parameter not working correctly

I am transitioning from Dreamweaver CS6 PHP/MySQL to Dreamweaver CC 2015 and MySQLi using the WebAssist Suite (it's been a VERY frustrating experience). I have a very simple recordset and it takes the URL parameter as the variable, so I send to the page something like this:

http://69.16.243.60/~dijkstr1/books-by-subj_TEST.php?subject1=history

And it works fine because the category = history. But my query has LIKE, not = so when the category is, say, Literary Fiction and I use:

http://69.16.243.60/~dijkstr1/books-by-subj_TEST.php?subject1=literary

It delivers no results, and I need to have this functionality for a number of purposes on the site. If I use subject1=Literary Fiction it works, but that's not what I need, especially when I start searching two different fields with that one word (can't wait to see how much fun that will be).

Here is the recordset:

<?php
$rsBySubject = new WA_MySQLi_RS("rsBySubject",$connProjMain1,1);
$rsBySubject->setQuery("SELECT isbn13, coverimage, title, full_title, subtitle, subject1, subject2, active_web, pub_date, auth_id, publisher FROM books WHERE subject1 LIKE ? ORDER BY pub_date ASC");
$rsBySubject->bindParam("s", "".(isset($_GET['subject1'])?$_GET['subject1']:"") ."", "-1"); //colname
$rsBySubject->execute();
?>

This is about as simple as you can get and I generated it using the "Simple" interface in the WebAssist MySQLi recordset creator. When I hit "Test" in the dialogue box it initially works, but when I save it and open the browser using the link above I get no results. Interesting aside... when I then reopen the dialogue box to edit the recordset (after it crashes DW CC a few times) it will no longer go back to the simple interface - it says the query is too complex. But I did nothing to it. So it will still return results for subject1 = but not for subject1 LIKE.

I would appreciate any help. I used to rip through this stuff in DW CS6 and MySQL and now everything is a struggle and I'm getting further behind on this project.

Thanks,
John Burke

Use:

$rsBySubject->bindParam("c", "".(isset($_GET['subject1'])?$_GET['subject1']:"") ."", "-1"); //colname

"c" stands for "contains" which should add the necessary wildcards before and after to work with like.

TAHNK YOU!!! That worked. I really appreciate the prompt response.

But why doesn't your MySQLi recordset generator create it that way?

It is an option when you choose the parameter type. You can do a straight text comparison, begins with, ends with, or contains.

I chose Contains and that's what I got.

I see... yes it appears the simple view doesn't work with contains properly. I just tested and had to go to advanced view and change it to contains. I'll look into that and get it fixed.

Well, I hope you can help me with another variation on this. Ok, so I finally got this working and it returns a book with the word "Science" in the subject1 field. This is the link:

http://69.16.243.60/~dijkstr1/books-by-subject.php?subject1=Science

But then I expand the query to also search for books that have Science in the subject2 field (secondary book subject) it returns nothing:

<?php
$rsBySubject = new WA_MySQLi_RS("rsBySubject",$connProjMain1,0);
$rsBySubject->setQuery("SELECT books.isbn13, books.coverimage, books.title, books.full_title, books.subtitle, books.subject1, books.subject2, books.auth_id, books.publisher, books.`description`, authors.authfirst, authors.authlast, authors.auth_id, authors.author_full FROM books, authors WHERE books.active_web = '1' AND (books.subject1 LIKE ? OR books.subject2 LIKE ?) AND authors.auth_id = books.auth_id ORDER BY authors.authlast");
$rsBySubject->bindParam("c", "".(isset($_GET['subject1'])?$_GET['subject1']:"") ."", "-1"); //colname
$rsBySubject->execute();
?>

Pretty simple but it doesn't return any books. It should return 4 books - the one with Science in subject1 and the three with Science in subject2.

The error message is:

Warning: mysqli_stmt::bind_param() [mysqli-stmt.bind-param]: Number of variables doesn't match number of parameters in prepared statement in /webassist/mysqli/rsobj.php on line 128

Again, any help would be appreciated. I have this working on about a dozen other sites, but I didn't realize migrating from MySQL to MySQLi would be so tricky and unfortunately I need to show the client the site tomorrow.

Thanks.

You need a bindParam() call for each ? in the SQL statement... so you probably need:

<?php
$rsBySubject = new WA_MySQLi_RS("rsBySubject",$connProjMain1,0);
$rsBySubject->setQuery("SELECT books.isbn13, books.coverimage, books.title, books.full_title, books.subtitle, books.subject1, books.subject2, books.auth_id, books.publisher, books.`description`, authors.authfirst, authors.authlast, authors.auth_id, authors.author_full FROM books, authors WHERE books.active_web = '1' AND (books.subject1 LIKE ? OR books.subject2 LIKE ?) AND authors.auth_id = books.auth_id ORDER BY authors.authlast");
$rsBySubject->bindParam("c", "".(isset($_GET['subject1'])?$_GET['subject1']:"") ."", "-1"); //colname
$rsBySubject->bindParam("c", "".(isset($_GET['subject1'])?$_GET['subject1']:"") ."", "-1"); //col2name
$rsBySubject->execute();
?>

Once again, Thank you! I had tried that with a few variations, but obviously not the correct one.