PHP MySql search over multiple columns

a_m0d · 07-05-2008, 01:59 AM

Hi all,
Hope this question is relevant enough for here.
I have a MySql database that I am trying to do a search on. The problem is that the search has to be over a few different fields. Here is what I mean:
My table contains the fields

Code:

job_lot (INT), job_address (CHAR), job_suburb (CHAR), job_unit (UNIT)

. When I display these on my web-page, I join them with a space, and if the job_unit column is non-zero, then I add that to the end, prefixed with a "U". The final address will look something like this:

Code:

123 Abc Street Letterland

or

Code:

123 Abc Street Letterland U3

if job_unit is not 0.
However, I now need to make a search on this table, and the search string will look similar to what I have above. My question is, what sort of SQL statement can I use so that "123 Abc Street Letterland" will only return those rows that match this? I guess that it would have something to do with using LIKE, but I am not sure if I need to split this query string up or not.
The other problem is that the unit number is stored as a number, not a string, and so the "U" in front of it is added when I display it on screen.
Can anyone help me with this, or is it too unclear?
Thanks.

jlinkels · 07-05-2008, 08:32 AM

I guess your problem is that street address and suburb consist of an unknown number of space separated fields, is that right?

I would do it this way: First split your search string in a number of separate fields excluding the job unit. You can use the split function for that.

Then build a query string like this:

PHP Code:



"SELECT CONCAT (job_lot, job_address, job_suburb) AS job  
WHERE job LIKE \"%{$split[0]}%\" AND job LIKE \"%{$split[1]}%\"...."

Of course what appears in the query string must be the contents of $split[0]: ... WHERE job LIKE %123% ... Print out the query during development as it usually goes wrong with array and quotes etc.

In PHP you can also process the presence of the U3 part and decide whether or not you should include it in the query. PHP can process regular expressions so you might recognize the unit with U[0-3]+ or so.

jlinkels

a_m0d · 07-06-2008, 09:23 AM

Hi jlinkels,
thanks for your reply. I managed to work this one out after posting, and my solution was similar to what you have posted, except that it involves a mixture of php and mysql. I will post it here so that others can possibly learn from it:
I first of all replace all the spaces in the query string with '%' characters so that I can use the LIKE statement. I know that this is not the most optimal way of doing this, but it worked for me, and I am not the most skilled php programmer, so I left it at this:

Code:

$search = implode ("%", explode (" ", $_GET["q"]));

I then use the following query string to select all the rows (note that I do not have to worry about the order, because the lot number will always be entered first if it entered at all) - I have also changed my query a bit so that more processing is done in SQL - since I combined a number of the fields before I displayed them on screen, and that was the same way that the search would occur, it now all happens in SQL:

Code:

$addressString = "TRIM(TRAILING ' U0' FROM CONCAT_WS(' ', job_lot, job_address, job_suburb, CONCAT('U',job_unit)))";
$selectQuery = "SELECT id, builder, $addressString AS address, supervisor, measure_date, fix_date FROM jobsTable WHERE $addressString LIKE '%$search%'";

This works exactly the way I needed it to - even displaying the unit numbers on the end when there is a unit number, and searches for a certain unit also work.
Note that at present, this search is case sensitive, but this is a very small matter to overcome.
Once again, thank you for your reply, I really appreciate the fact that I am able to post on here and get such a prompt response.
Also, if you can think of a way to improve my code or something that is wrong in it, please let me know so that I can fix it.
Thank you very much,
a_m0d
p.s. Just re-read your post, and noticed that in your query you select those columns AS job and then refer to job later in the query, but I have read that you can't do this, and instead have to call WHERE CONCAT(job_lot, job_address, job_suburb) LIKE \"%{$split[0]}%\" AND ...

jlinkels · 07-09-2008, 07:19 AM

Quote:

Originally Posted by a_m0d

p.s. Just re-read your post, and noticed that in your query you select those columns AS job and then refer to job later in the query, but I have read that you can't do this, and instead have to call WHERE CONCAT(job_lot, job_address, job_suburb) LIKE \"%{$split[0]}%\" AND ...

Unfortunately you are correct. Aliases can be used almost everywhere except in WHERE clauses.

jlinkels