MySQL Procedures using Parameters

Question

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?

What do you mean by 'until the query had run'? Of course there are no results if you don't run the query. — andy
– andy, Commented Nov 9, 2014 at 21:24
When I open my php page that the search form is on, the table where I show the results is empty on using the 2nd example, but on the first example the table shows all records — Jez
– Jez, Commented Nov 9, 2014 at 21:28
I'm just calling the Procedures as follows from the search page CALL sp_jobsearch('".$searchpostcode."', '".$searchaddress."'); — Jez
– Jez, Commented Nov 9, 2014 at 21:29
So the second version is exactly what you are looking for? I don't see the problem. Please specify the desired behavior in your question more clearly. — andy
– andy, Commented Nov 9, 2014 at 21:33
No, I want to use the 1st option, be able to use LIKE in the WHERE clause, but not show all results in the table when the page loads. If I have thousands of records in the table then this would show when the page opens, but I dont want this, I want an empty table until the input of either Address or Postcode is input in the search boxes — Jez
– Jez, Commented Nov 9, 2014 at 21:34

andy · Accepted Answer · 2014-11-09 21:39:31Z

1

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.

answered Nov 9, 2014 at 21:39

andy

2,0021 gold badge12 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Procedures using Parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related