Query results order - coming out funky??

[amites]

Hello,

 

I have a simple question, I have a query

			echo "<select name='locstate' onChange=\"dochange('cities', this.value)\" >"
			. "<option value='0'>".$txt_select_state."</option>\n";
		$result=mysql_query("select `id`, `english` FROM " . $database_prefix . "states ORDER BY `english`");

		while(list($id, $name)=mysql_fetch_array($result)){
			echo "<option value=\"$id\" >$name</option>" ;
		}

		echo "</select>";

 

that is spitting out results that are alphabetical, though the first few lines show up at the end,

 

for example:

 

<select onchange="dochange('location', this.value)" name="loccity"><option value="0">Select a City:</option><option value="1031">
Brookside</option><option value="1032">
Claymont</option><option value="1033">
Dover</option><option value="1034">
Glasgow</option><option value="1035">
Hockessin</option><option value="1036">
Middletown</option><option value="1037">
Milford</option><option value="1038">
Newark</option><option value="1039">
North Star</option><option value="1040">
Pike Creek</option><option value="1041">
Seaford</option><option value="1042">
Wilmington</option><option value="1043">
Wilmington Manor</option><option value="1030">Bear</option></select>

 

notice the last entry in the list?

 

any ideas?

 

in states with more cities in it I will get up to 8 or so of the first few rows showing up at the end in alphabetical order,

 

so a, b, c, d, e, f, g

 

would come out

 

d, e, f, g, a, b, c

 

 

this was working properly before and I don't remember changing anything, it just started running through this way, even after I reverted to an older copy of this script

 

it's called by AJAX if that makes a difference

 

any thoughts are greatly appreciated

[rhodesa]

It looks like the values in the 'english' column of the table have a new line character in from of them (and Bear doesn't).

[bpops]

It looks like the values in the 'english' column of the table have a new line character in from of them (and Bear doesn't).

 

That is brilliant. I was racking my brain looking at that one  ???

[amites]

Many thanks,

 

that was getting annoying,

 

now i have a new one...

 

any chance of getting some help building a query to remove the new lines?

 

hate to do it manually, just been staring at a screen to long to think it out right now...

[rhodesa]

Do a backup of the data first, but this SQL command should do it:

mysql_query("UPDATE " . $database_prefix . "states SET `english` = TRIM(`english`)");

 

TRIM is a mysql function that will remove leading and trailing whitespace just like PHP's trim function.

[amites]

thanks for the update,

 

I gave it a shot and it didn't give me any results, no errors though...

 

thinking I'm going to work on this tomorrow 16 hours is my limit

 

once again I appreciate the help

[rhodesa]

It won't return any results. It's an UPDATE which alters the data in the database. After the command, the values in there shouldn't have the new lines anymore. So just go back to your other script with does the SELECT, and see if those new line characters are still there.

[amites]

I should have been more specific,

 

checked on the results and the new lines were still there,

 

after running the script, directly in mySQL and through PHP

[rhodesa]

Ah, just tested and the default is only space characters I guess. Just have to tell it to do new lines too:

 

UPDATE states SET `english` = TRIM(LEADING '\n' FROM `english`)

[amites]

still not doing it,

 

thinking I might just dump the table and run it through dreamweaver,

 

become a huge fan of the search and replace function in that program 

 

many thanks for the help so far

[amites]

think I got it,

 

when I dumped the table it came out:

 

INSERT INTO bil_cities

  (`id`, `state_id`, `english`)

VALUES

  (3, 1, '\r\nAlexander City');

 

 

I think that \r was throwing things off

 

damn Windows

[rhodesa]

ah...in that case, there is no leading \n cus a \r is in front of it...so:

 

UPDATE states SET `english` = TRIM(LEADING '\r\n' FROM `english`)