0

I am trying to search 2 columns in the same table with a like statement. I am new to using PDO.

It is searching and returning the result as intended but it only seems to be searching the "courseTitle" column. The search seems to be ignoring the "longTitle" column.

My example makes sense to me but doesn't work. What am I missing here?

try{
    if(isset($_REQUEST["term"])){
        // create prepared statement
        $sql = "SELECT * FROM courseCodes WHERE courseTitle LIKE :term OR longTitle LIKE :term";
        $stmt = $pdo->prepare($sql);
        $term = $_REQUEST["term"] . '%';
        // bind parameters to statement
        $stmt->bindParam(":term", $term);
        // execute the prepared statement
        $stmt->execute();
        if($stmt->rowCount() > 0){
            while($row = $stmt->fetch()){
                echo "

<tr onclick='selectRow(this)'>
<td>".$row['courseNumber']."</td>
<td>".$row['courseTitle']."</td>
<td>".$row['longTitle']."</td>
<td><input type='checkbox' id='".$row['id']."' name='courseNumber[]' value='".$row['courseNumber']."'></td>
</tr>   
            
                ";
            }
        } else{
            echo "<p>No matches found</p>";
        }
    }  
} catch(PDOException $e){
    die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}
 
// Close statement
unset($stmt);
 
// Close connection
unset($pdo);

I MADE THESE CHANGES AND NOTHING IS RETURNED ... UUGGGGG

$sql = "SELECT * FROM courseCodes WHERE courseTitle LIKE :term OR longTitle LIKE :term2"; // <--- ADDED term2
$stmt = $pdo->prepare($sql);
$term = $_REQUEST["term"] . '%';
// bind parameters to statement
$stmt->bindParam(":term", $term);
$stmt->bindParam(":term2", $term); // <--- ADDED
// execute the prepared statement
$stmt->execute();
if($stmt->rowCount() > 0){
Anthony
  • 35
  • 6
  • Your query is valid. How does your data looks like? How your `:term` looks like? – Justinas Aug 09 '21 at 10:37
  • Could you add more code? because the query is fine... – Gert B. Aug 09 '21 at 10:37
  • Please show sample table data, sample data for :term and expected results. We cannot guess what is going wrong or whether you have made a wrong assumption. "doesn't work" tells us nothing about the issue. – ADyson Aug 09 '21 at 10:37
  • Thanks for the update but that wasn't actually the information I asked for. The most important information you need to provide is what I mentioned in my previous comment. – ADyson Aug 09 '21 at 10:52
  • That's because you can have only one placeholder `:term`. Use `:term1`, `:term2` and bind same value to them. – u_mulder Aug 09 '21 at 10:53
  • Does this answer your question? [Use bound parameter multiple times](https://stackoverflow.com/questions/18511645/use-bound-parameter-multiple-times) – Zoli Szabó Aug 09 '21 at 10:54
  • Ah yes, that's a good point actually, I hadn't noticed that. You need to use two placeholders and set the same information into each one of them. – ADyson Aug 09 '21 at 10:54
  • Unless `ATTR_EMULATE_PREPARES` is enabled. – u_mulder Aug 09 '21 at 10:56
  • Will you provide an example of binding :term1 and :term2 to the same value? I'm not that great with PDO yet. Thank you in advance! – Anthony Aug 09 '21 at 10:59
  • See the link Zoli provided. But basically it's just another parameter: `$stmt->bindParam(":term", $term); $stmt->bindParam(":term2", $term);`. And obviously change the `OR longTitle LIKE :term";` ti `OR longTitle LIKE :term2";`, to match. – ADyson Aug 09 '21 at 11:00
  • Thanks for the update. So maybe something else is still wrong. What does `$stmt->rowCount()` return? Do you get any errors? (And did you enable PDO error reporting and PHP error reporting so you'll definitely see errors)? – ADyson Aug 09 '21 at 11:44
  • i could be wrong, but i have a feeling i've had a similar problem and solved it by using `bindValue` rather than `bindParam` – imposterSyndrome Aug 13 '21 at 18:20

0 Answers0