MYSQL issue

ChicoMendez

hi

im doing a select form a db table asking for all the entries that start with L:

select * FROM #__model AS m LEFT JOIN
#__mb_link AS mb ON (mb.modelid=m.id)
WHERE mb.productid=4500 AND m.modelbrand>0 AND (m.modeltitle LIKE 'L%' OR m.modeltitle LIKE ' L%') ORDER BY m.modeltitle

however it does not bring them all back

i then go and look into the table and see that there seems to be a space before alot of the titles

see attachment

however whenever i go to edit the entry that seems to have the space , it does not have a space! - when i save/update it, it seems to get sorted


anyone know whats wrong here- and how i may ajust my select so as it pulls back all entries starting with L
?

tnx

Eoin

Off the top of my head, run an update on that column that does an LTRIM to get rid of any leading spaces.

HTML will only display one space (unless the text is in a <pre> tag, or   is used), so that could be why it's not displaying in the UI.

jmcc

ChicoMendez wrote: »

hi
however whenever i go to edit the entry that seems to have the space , it does not have a space! - when i save/update it, it seems to get sorted


anyone know whats wrong here- and how i may ajust my select so as it pulls back all entries starting with L?

MySQL has a trim function to remove spaces but the problem could lie in the schema. Is the field with the number defined purely as an int or is it a varchar?

Regards...jmcc

ChicoMendez

jmcc wrote: »

MySQL has a trim function to remove spaces but the problem could lie in the schema. Is the field with the number defined purely as an int or is it a varchar?

Regards...jmcc

heres the table attached


i did the trim on the table field - it changed about 30 but the majority of them remain the way they were.


tnx

ChicoMendez

any ideas ?