SQL PHP ever run into this issue?

[Guest]

I am trying to allow searches on several dbase fields. No problem, except the way the data has been entered into the dbases. I can have a field that contains the value ”66 CYG”, for example, but it could also contain any number of spaces between the “6” and the “C” other than just the one that you would normally expect.

The following code is an example of what I am using:

$sql = "SELECT SQL_CALC_FOUND_ROWS * FROM globular_parameters_one LEFT JOIN (globular_parameters_two CROSS JOIN globular_parameters_three) ON (globular_parameters_two.jim_number = globular_parameters_one.jim_number and globular_parameters_three.jim_number = globular_parameters_one.jim_number) WHERE (globular_parameters_one.jim_number= \"$jim_number\") LIMIT ". $row_start .", ". $howmany ."";

Is there any sql function that can be used within this code to modify the value in the globular_parameters_one.jim_number field to remove all but a single imbedded space?

The extra spaces will always be contiguous, i.e. XXspacespacespacespacespacespace XX there is never anything like:XXspacespacespaceXspaceXX.

If there is no function, how would you tackle this problem sans re-ntry of all problematic data – (not an option)?

[bregent]

Can you give an example of the type of search criteria that would be expected?

[Guest]

Thanks for the response. I am not sure what you mean. The problem is I need only one or no embedded spaces in the value from the database field

in the query so I can make sure the user's input is in the same pattern.

In the code above the field globular_parameters_one.jim_number could contains a name (string value) from one of several catalogs i.e. “GCC 1900” or “Pal 1” or “omega cen”.

They are compared against the value in the variable $jim_number.

The user places the value (string) into a form and selects the catalog from a group of radio buttons. On submit I do the query.

The issue is the user would naturally type “NGC 1902”, for example, but the value in the dbase could be “GCC 1902” or  “GCC         1902”. If it IS “NGC 1902” the code works fine,

The query also provide detailed information on the object so the user may actually see PAL 4 in the list of objects displayed on the page (because the extra white-space doesn’t show on the web of course), enter it that way, and not PAL        4 as it might be in the dbase. I need to get all but one (or all the spaces) out of the database value so the search values will match.

Hope that answers your question.

[bregent · Accepted solution]

Here's how I would handle it, but I keep in mind that I don't use MySQL which may have some non standard sql functions you could use instead.

Parse the user input into two fields

Trim any left and right spaces from the fields

Use the following SQL (example)

Select * from MyTable where

jim_number like field1% AND

jim_number like %field2

Of course, the real solution is to cleanse your data. You should be able to do this with a simple SQL script and not have to re-input the data. And then, of course, use good validation to ensure that the data entered is always in the correct format.

[Guest]

Thanks, I never thought of that. I spent some time looking for a more specific function but mysql doesn't have it (FYI).

I agree completly about good data. But since I never ran accros this I was looking for a non data-fix solution.

I appreciate the response.

[David_Powers]

Why not use a regular expression?

Use PHP to split the user input on the space, and then put both parts together as a regular expression.

if (isset($_GET['jim_number']) && strpos($_GET['jim_number'], ' ')) {
  $jim_number = explode(' ', $_GET['jim_number']);
  $jim_number = $jim_number[0] . '\s+?' . $jim_number[1];
}

In the query:

WHERE (globular_parameters_one.jim_number REGEXP \"$jim_number\") 

[David_Powers]

It would probably be more efficient if you add anchors to the regex:

if (isset($_GET['jim_number']) && strpos($_GET['jim_number'], ' ')) {
  $jim_number = explode(' ', $_GET['jim_number']);
  $jim_number = '^' . $jim_number[0] . '\s+?' . $jim_number[1] . '$';
}