Search form not returning any results

donboe · September 19, 2015, 7:17am

I have a search form to seach for motor parts. The form consists of two text fields one for new parts and one for used:

    <form action="/artikelen/zoeken" method="post" name="zoek-form" id="zoek-form">
      <input name="nieuw" type="text" placeholder="Onderdelen nieuw">
      <input name="gebruikt" type="text" placeholder="Onderdelen gebruikt">
      <button type="submit">Zoeken</button>
    </form>

I use the following model to query the database:

public function get_zoek_resultaten($term,$status)
{
    $sql = "SELECT P.*
                 , PF.foto
              FROM producten P
              JOIN product_fotos PF ON P.product_id = PF.product_id
             WHERE (product_naam LIKE :term OR product_omschrijving LIKE :term)
               AND product_status = :status";

    $stmt = $this->pdo->prepare($sql);
    $stmt->execute(array(':term' => $term.'%', ':status' => $status));                  

    return $stmt->fetchAll();    
}

and I have the following in Controller zoeken

$nieuw        = filter_input(INPUT_POST, 'nieuw', FILTER_SANITIZE_STRING);
$gebruikt     = filter_input(INPUT_POST, 'gebruikt', FILTER_SANITIZE_STRING);
        
if ( $nieuw )
{
    $conditie = $nieuw;
}
        
elseif ( $gebruikt )
{
    $conditie = $gebruikt;
}

$zoek_resultaten = $this->artikelen->get_zoek_resultaten($conditie,$conditie);

But no matter what I enter in one of the two fields, no records are returned. What is wrong with this approach?

Thank you in advance

megazoid · September 19, 2015, 9:04am

Hi

Is it ok to pass same variable twice here?

$this->artikelen->get_zoek_resultaten($conditie,$conditie);

donboe · September 19, 2015, 10:43am

The both represend the same value for two different rows in the table.

I have no idea how to do it differently

Drummin · September 19, 2015, 3:58pm

product_naam LIKE :term OR product_omschrijving LIKE :term)

You can’t use the same placeholder name twice.
Add the third item to your array,e.g.

':term2' => $term.'%',

And change query accordingly.

donboe · September 19, 2015, 4:37pm

Sorry for asking but what is accordingly. in this case

Edit: What would this mean in the controller?

megazoid · September 19, 2015, 5:10pm

That means change second :term in the query to :term2 and add

'term2' => $term

in the bindings array

donboe · September 20, 2015, 8:56am

Hi Drummin and megazoid. Thank you both for the reply. I adjusted the Model as you suggested

public function get_zoek_resultaten($term,$status)
{
    $sql = "SELECT P.*
                 , PF.foto
              FROM producten P
              JOIN product_fotos PF ON P.product_id = PF.product_id
             WHERE (product_naam LIKE :term OR product_omschrijving LIKE :term)
               AND product_status = :status";
    $stmt = $this->pdo->prepare($sql);
    $stmt->execute(array(':term' => $term.'%', ':term2' => $term.'%', ':status' => $status));                  

    return $stmt->fetchAll();
}

and kept the controller as described in my opening post, but the query is still not returning any results? Should I adjust the Controller as well?

Thank you in advance

megazoid · September 20, 2015, 12:13pm

You didn’t change your query:

WHERE (product_naam LIKE :term OR product_omschrijving LIKE :term)

Replace second :term with :term2 here

donboe · September 20, 2015, 12:49pm

Not sure what I am doing wrong bit the query still doesn’t return any results. Here is what I have now:

Model

    public function get_zoek_resultaten($term,$term2,$status)
    {
        $sql = "SELECT P.*
                     , PF.foto
                  FROM producten P
                  JOIN product_fotos PF ON P.product_id = PF.product_id
                 WHERE (product_naam LIKE :term OR product_omschrijving LIKE :term2)
                   AND product_status = :status";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array(':term' => $term.'%', ':term2' => $term2.'%', ':status' => $status));                  

        return $stmt->fetchAll();    
    }

Controller

    public function zoekenAction()
    {
        $this->pageId = 15;
        
        $nieuw        = filter_input(INPUT_POST, 'nieuw', FILTER_SANITIZE_STRING);
        $gebruikt     = filter_input(INPUT_POST, 'gebruikt', FILTER_SANITIZE_STRING);
        
        if ( $nieuw )
        {
            $conditie = $nieuw;
        }
        
        elseif ( $gebruikt )
        {
            $conditie = $gebruikt;
        }
    
        $zoek_resultaten = $this->artikelen->get_zoek_resultaten($conditie,$conditie,$conditie);                
 
    }

DarthGuido · September 20, 2015, 1:04pm

What should the third variable you pass to get_zoek_resultaten contain?

donboe · September 20, 2015, 1:35pm

Hi Guido. Thank you for the reply. All three variable actually should come from the same form field as you can read in my opening post.

I have a form with two form fields one called nieuw (new) and one called gebruikt (used) to search the database for motor parts. All parts in the table products have a product_status (nieuw or gebruikt). If for example I use the search term Frame in the formfield gebruikt (used) the query should return all parts where the name contains the search term Frame or the description contains the search term Frame and the product_status from those parts is gebruikt (used).

Does this make any sense?

DarthGuido · September 20, 2015, 1:43pm

Yes, but that means the third variable shouldn’t contain the form value, but “nieuw” or “gebruikt”. So you’ll have to write the code for that.

donboe · September 20, 2015, 3:09pm

Hi Guido. Now I am loosing you.Sorry for my ignorance. I thought by using:

        if ( $nieuw )
        {
            $conditie = $nieuw;
        }
        
        elseif ( $gebruikt )
        {
            $conditie = $gebruikt;
        }

I would do right that?

megazoid · September 20, 2015, 3:33pm

Try to “run” your code in your mind to reproduce its logic.

For example, lets say I typed the text “foobar” into input with name “nieuw” and submitted the form. What will happen next? Controller will receive that value and put it into $neiuw variable:

$nieuw        = filter_input(INPUT_POST, 'nieuw', FILTER_SANITIZE_STRING);

Then this assignment will be made:

if ( $nieuw )
{
    $conditie = $nieuw;
}

Now we have text “foobar” inside $conditie.

After that controller runs next function:

$zoek_resultaten = $this->artikelen->get_zoek_resultaten($conditie,$conditie,$conditie);

As we know $conditie contains “foobar” so that line can be presented like:

$zoek_resultaten = $this->artikelen->get_zoek_resultaten("foobar","foobar","foobar");

Right? Ok, lets go ahead.

Now we go into function:

public function get_zoek_resultaten($term,$term2,$status)

All three arguments of that function contain the same value “foobar”. We bind that value to the placeholders:

$stmt->execute(array(':term' => "foobar".'%', ':term2' => "foobar".'%', ':status' => "foobar"));

And condition in your query becomes to:

WHERE (product_naam LIKE 'foobar%' OR product_omschrijving LIKE 'foobar%')
AND product_status = 'foobar'

Look at the product_status. It’s filtered by the same value as two other fields (because you pass the same value with all function arguments). Is it correct?

donboe · September 20, 2015, 4:41pm

Hi megazoid. Thank you for your time and patience. It’s very much appreciated :). I think I got you. I have one of those moments that in one part of your head you understand and in the other you don’t I changed the Controller the following way:

        if ( $nieuw )
        {
            $term   = $nieuw;
            $status = $nieuw;
        }
        
        elseif ( $gebruikt )
        {
            $term   = $gebruikt;
            $status = $gebruikt;
        }
        $zoek_resultaten = $this->artikelen->get_zoek_resultaten($term,$term,$status);

Do i have that right?

megazoid · September 20, 2015, 4:48pm

Almost. You should assign text representation of your status, like that:

    if ( $nieuw )
    {
        $term   = $nieuw;
        $status = "nieuw";
    }
    elseif ( $gebruikt )
    {
        $term   = $gebruikt;
        $status = "gebruikt";
    }

Otherwise you actually change nothing, because your $term and $status variables will contain the same value taken from $nieuw variable.

Also, there is no need to pass single variable to the function twice:

$this->artikelen->get_zoek_resultaten($term,$term,$status); // term and term?

Instead, you can pass it just once:

//function call
$this->artikelen->get_zoek_resultaten($term,$status);

//function definition
public function get_zoek_resultaten($term,$status)

and re-use here:

$stmt->execute(array(':term' => $term.'%', ':term2' => $term.'%', ':status' => $status)); //note that :term and :term2 take value from the same function argument

donboe · September 21, 2015, 10:55am

Hi megazoid. Thanks a again for your very clear explanation. This works great

system · December 21, 2015, 5:56pm

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.