Leading space with INSERT in MySql

Question

I am doing search results page with PHP & MySql.When you go to the state page, it should call up DISTINCTly every city in which I have a record. It won't list any city for the cities column if they have a leading whitespace.

I trim the variable before INSERTing into the database.

$prcity = trim($_SESSION['prcity']);

Yet I still have a leading whitespace in the column. The only way a city will show on a particular state results page is if I go into each record and delete the leading whitespace.

Also, I have an array in a form I am imploding that throws this warning message

Warning: implode() [function.implode]: Invalid arguments passed in C:\wamp\www\mydomain.com\register\checkform.php on line 48

if I use this bit of code.

if (!$errorstring) {foreach( $_POST as $key=>$value ) {$value =ltrim($value);
    $_SESSION[$key] = $value;}

Any clues on trimming whitespace for the INSERT or trimming the whitespace on the search result w/o throwing the warning?

echo ("<a class = \"city_search\" href =\"../search_results.php?search=".$row['prcity']."&prstate=".$search."\">".$row['prcity']."</a>");}

Answer 1

Well, if you're having an issue with leading/trailing spaces making it to your column, you can just clear them out with a query like this:

UPDATE cities SET city_name = TRIM(city_name);

Now.. as to why the spaces are even being INSERTed in the first place.. I'd have to see the actual code that is doing it.

Answer 2

Chris,

Mysql have a TRIM function so you can use it to make sure the data is ok. Also you can update all your city by doing

UPDATE city SET city = TRIM(city)

so your database would be repaired