Search form and substring_index

[donboe]

I need a very simple search form where people should be able to search for products on one hand and the condition of those products (new or used) on the other. Simple one would think! You use a textfield for the product name/description and a dropdown for the condition, and that is what I thought. But the side owner don’t want the dropdown in the form. He would like to use two text fields, so I created the following form and thought of using substrin_index in the query:

Form:

<form action="/products/search" method="post" name="search-form" id="search-form">
  <input name="search_new" id="search_new" type="text" placeholder="Products new">
  <input name="search_used" id="search_used" type="text" placeholder="Products used">
  <button type="submit">Search</button>
</form>

Query:

SELECT P.*
     , PP.photo
  FROM products P
  JOIN product_photos PF ON P.product_id = PP.product_id
 WHERE (condition = SUBSTRING_INDEX('search_new', '_', -1) OR
    condition = SUBSTRING_INDEX('search_used', '_', -1))
   AND (product_name LIKE 'search_new%' OR 
        product_name LIKE 'search_used%')

But that isn’t working unfortunately. What would be my best options. Thank you in advance

[r937]

it appears you have applied the functions incorrectly

for example, this –

SUBSTRING_INDEX('search_new', '_', -1)

simply pulls out the string ‘new’ because it’s operating on the string ‘search_new’

what you want is to have the function operate on the actual contents of the form field instead

that’s an issue for how you construct the sql in your php app

p.s. your table alias is PF but you use PP on your columns --that’s a paddlin’