CODEWITHSUNDEEP
phpmysql
Jez
Jez

Reputation: 260

MySQL Procedures using Parameters

I'm calling a Procedure from the PHP form, which the user inputs an Address or Postcode to search on in order to return details around the customer.

My issue is that within my WHERE clause I've set the fields to search LIKE the input, but when I open my search form it returns all rows from the table, when this WHERE clause below is used.

WHERE (AddressLine1 LIKE CONCAT('%', street_name, '%')) OR (Postcode LIKE CONCAT('%', post_code, '%'))

If I changed it and the user put the specific Postcode or Address like below, 

WHERE ((Postcode = post_code OR street_name IS NULL)
            OR (AddressLine1 = street_name OR post_code IS NULL))

then the search page would return no rows of data until the query had run. How can I stop the query from showing all rows in the table until the query has run?

Upvotes: 1

Views: 30

Answers (1)

andy
andy

Reputation: 2002

Instead of modifying the SQL query, you should check whether to execute the query or not in the PHP code.

For example, you could use this condition to check if both parameters where specified:

if (!empty($searchpostcode) && $searchaddress) {
    sp_jobsearch('".$searchpostcode."', '".$searchaddress."');
}

However, I'm fairly certain that you could directly decide whether you are showing the input form or the query result page.

Upvotes: 1

Related Questions