[SOLVED] trim space (" ")

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
ddragas
Forum Contributor
Posts: 445
Joined: Sun Apr 18, 2004 4:01 pm

trim space (" ")

Post by ddragas »

Hi all

In database is column mail. How to trim space (" ") from begining and from end of each record.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

PHP command trim - trim before you place in DB
SQL (POSTRES) trim
So if you want to trim all rows

Code: Select all

UPDATE tablename set mail=trim(mail);
User avatar
ddragas
Forum Contributor
Posts: 445
Joined: Sun Apr 18, 2004 4:01 pm

Post by ddragas »

Thank you for quick reply, but it doesn't work

Number affected rows= 0

here is code:

Code: Select all

<?php
include("../hr/con_db.php");

$sqlquery = "UPDATE mailovi SET mail = trim(mail)";
	
mysql_query($sqlquery) or die(mysql_error()); 

$broj_mailova_poslanih = mysql_affected_rows();

echo "updejtani redovi: " . $broj_mailova_poslanih;

include("../hr/disconect.php");
?>

<?php

?>
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

Try a ; at the end of the SQL statement.

Code: Select all

$sqlquery = "UPDATE mailovi SET mail = trim(mail);";
As I stated this works in Postgres. I am not sure what the command is for MySQL but it should be the same.
User avatar
ddragas
Forum Contributor
Posts: 445
Joined: Sun Apr 18, 2004 4:01 pm

Post by ddragas »

I'm sorry to say but it doesn't work.
User avatar
Jean-Yves
Forum Contributor
Posts: 148
Joined: Wed Jul 02, 2003 2:13 pm
Location: West Country, UK

Post by Jean-Yves »

TRIM() should work fine in MySQL.

See manual:

http://dev.mysql.com/doc/mysql/en/Charset-result.html
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

OK, well I don't know why, maybe an MySQL bod could tell you....

Have you tried just the SQL statement, using SQL directly and see if it works ?
User avatar
m3mn0n
PHP Evangelist
Posts: 3548
Joined: Tue Aug 13, 2002 3:35 pm
Location: Calgary, Canada

Post by m3mn0n »

[php_man]trim[/php_man] ()

[big_search]mysql database updating[/big_search]
User avatar
ddragas
Forum Contributor
Posts: 445
Joined: Sun Apr 18, 2004 4:01 pm

Post by ddragas »

There is nothing wrong with statement

Code: Select all

<?php
$sqlquery = "UPDATE mailovi SET mail = trim(mail);";
<?php
?>
It is something else

I've imported in db from "csv" file these emails, and at the end of each record, i've putted "\r\n".

I think that is couse of my problem.
User avatar
m3mn0n
PHP Evangelist
Posts: 3548
Joined: Tue Aug 13, 2002 3:35 pm
Location: Calgary, Canada

Post by m3mn0n »

Try using the WHERE cause as outlined on the pages I linked to.

A little research doesn't hurt.
User avatar
ddragas
Forum Contributor
Posts: 445
Joined: Sun Apr 18, 2004 4:01 pm

Post by ddragas »

"\r\n" means

Carriage return: \r
Linefeed: \n

So here is nothing to trim. What I need is to delete "new row" from each record.

Only problem is I don't know how
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

Postgres only...

Code: Select all

UPDATE mailovi SET mail = btrim(mail,'\r\n');
Can't find btrim in MySQL manual though and don't have any method to test it anyway...
http://dev.mysql.com/doc/mysql/en/String_functions.html

Alternative: Check before you place it in the DB to start with.
User avatar
ddragas
Forum Contributor
Posts: 445
Joined: Sun Apr 18, 2004 4:01 pm

Post by ddragas »

Found nothing

I've stoped in midle of problem

If somebody can help me

Regards-ddragas
User avatar
ddragas
Forum Contributor
Posts: 445
Joined: Sun Apr 18, 2004 4:01 pm

Post by ddragas »

Come on folks

Any sugestions?
User avatar
ddragas
Forum Contributor
Posts: 445
Joined: Sun Apr 18, 2004 4:01 pm

Post by ddragas »

CoderGoblin thank you for help


solved


truncated table and imported again
Post Reply