[SOLVED] Fixing improperly inserted data

[mikeg542]

So I inserted some 2000 rows of data (as in, I'd rather not have to do this manually), and all the values in the 'name' field were inserted with a link break somehow.

 

As in, if I go into php my admin and click edit the row, it allows me to move the cursor down to the next row (which isn't supposed to happen.)

 

This currently makes it so that if I search ("SELECT * from table where name='user1') for example, it doesn't find it because user1 was somehow submitted as 'user1 ' (but a line break I THINK instead of a space)

 

Is there an easy way to just change my select statements to look for it WITH a line break? Or, is there a quick piece of code that would get rid of the extra space in all my tables?

 

Thanks!

[nadeemshafi9]

you will have to get the results with php one by one and then ereg replace the /r /n or woteva with '' and update the record one by one, make a copy of the db and test it out. some of the experts here may be able to show you regex functions in sql if there are any

[mikeg542]

Is it possible to display the result with the \r, \n , \whatever as text instead of parsing? Because I'm not 100% sure what I actually need to get rid of yet.

[nadeemshafi9]

well with regular expressions you can say get rid of the /n/r and naything else you want to get rid of and then you can re update the record with the new value, use php to do your reg exp

 

 

result = sql = get all from table

 

while (record = mysqlgetarray(result)){

      new_username = ereg_replace("/r/n", "", result['name']);

    // add other things to ur reg exp aswell [1-9][a-z] i think \z* means 1 from last and you can replace it with ""

    update table set name = 'new_username' where id = result['id']

}

[nadeemshafi9]

i think \z+ means 1 from last and you can replace it with ""

[mikeg542]

Didn'tr work, here's a picture of what I mean

 

 

If you do that on any other field the cursor can't go to the second line like that.

[nadeemshafi9]

Didn'tr work, here's a picture of what I mean

 

 

If you do that on any other field the cursor can't go to the second line like that.

 

if its windows it may be a vbCrlf that is the new line, otherwise that is definatly a new line and teh above is teh way to remove it

[trq]

I'm not sure if trim will remove new lines but its worth a try.

 

UPDATE tbl SET name = TRIM(name);

[mikeg542]

Trim didn't work, nor did the ereg replace, regretably.

 

I have a temporary fix in using LIKE 'name%' Which would work in the program as none of the names would double trigger that search function. But I would prefer to find a fix incase it does come up

[trq]

UPDATE tbl SET name = REPLACE(REPLACE(name, '\r', ''), '\n', '');

[nadeemshafi9]

Trim didn't work, nor did the ereg replace, regretably.

 

I have a temporary fix in using LIKE 'name%' Which would work in the program as none of the names would double trigger that search function. But I would prefer to find a fix incase it does come up

 

 

listen you need to do an reg exp that says anything that isnt a number and and isnt a characher alpha i dont know how to negate but it is as follows [a-z 1-9]

[nadeemshafi9]

UPDATE tbl SET name = REPLACE(REPLACE(name, '\r', ''), '\n', '');

 

what if its a vbCrlf not a /r or /n or what if its a /r/n

[trq]

UPDATE tbl SET name = REPLACE(REPLACE(name, '\r', ''), '\n', '');

 

what if its a vbCrlf not a /r or /n or what if its a /r/n

vbCrlf is a constant used in languages based around MS basic.

[nadeemshafi9]

UPDATE tbl SET name = REPLACE(REPLACE(name, '\r', ''), '\n', '');

 

what if its a vbCrlf not a /r or /n or what if its a /r/n

vbCrlf is a constant used in languages based around MS basic.

 

im not with you my knowledge is not vast on that side, but i remember trying to remove new lines in ASP a few years back and it took me ages until i removed the vbCrlf, so i was thinking it may have gone in as that, or would that be what ASP calls it in the code ?

[trq]

asp is typically written in vbscript. So yeah, vbCrlf is simply a constant with the value of \n\r or whatever a new line is in windows.

 

It has no meening in php or mysql.

[nadeemshafi9]

this dudes using a textfeild for username

[trq]

this dudes using a textfeild for username

 

And?

[mikeg542]

Thorpe's solution worked

 

Thanks a bunch