REMOVE WhiteSpace BEFORE Update

[PriteshP23]

This query is not working. I need urgent help. Thanks a lot in adv.

ROWS affected: 0

I have cross checked the data in the table for the given condition.

$sql = 'UPDATE LTE_noria_cellule cel_bk_up SET cel.NAP_'.$site->ur().' = \'1\' '.
            'WHERE cel.nidt 
            IN (
                select gn 
                from LTE_noria_candidat_bk_up can 
                INNER JOIN LTE_noria_site_bk_up site ON (site.codesite = can.codesite)
                WHERE '.$site->get_param('zone_bde').')'; */

I believe problem is because of WHITESPACE.

When i search "codesite" in the table with "LIKE". I can't find it. But when i did "LIKE%" , it's working.

I tried this also but not working :(

$sql = 'UPDATE LTE_noria_cellule cel_bk_up SET cel.NAP_'.$site->ur().' = \'1\' '.
            'WHERE cel.nidt 
            IN (
                select gn 
                from LTE_noria_candidat_bk_up can 
                INNER JOIN LTE_noria_site_bk_up site ON trim(site.codesite) LIKE CONCAT('%',trim(can.codesite),'%')
                WHERE '.$site->get_param('zone_bde').')'; 

[cereal]

Show the query that you use to select.

Also, to remove white space use trim(). This function exists in PHP and MySQL, so when you insert from PHP you can just do something like this:

$var = trim($var);

if you want to fix the rows in your table, you can run an update query:

update table_name set field_name = trim(field_name);

This will remove leading and trailing spaces: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim

[PriteshP23]

@cereal: Thanks for quick reply.

I have expected this reply.

if you want to fix the rows in your table, you can run an update query:

    UPDATE LTE_noria_candidat_bk_up SET gn = TRIM(gn);
    UPDATE LTE_noria_candidat_bk_up SET codesite = TRIM(codesite);

Still, its not working :(

[paulkd]

What is displayed when you die($sql); ?

[cereal]

Also, are these values encoded? For example hello%20

In this case you can run:

update table_name set field_name = trim(BOTH '%' FROM field_name);

This will remove just % if you have other codes, as example an encoded empty space %20, you have to change it to:

update table_name set field_name = trim(BOTH '%20' FROM field_name);

[PriteshP23]

What is displayed when you die($sql); ?

UPDATE LTE_noria_cellule cel_bk_up SET cel.NAP_TO = '1' (x>220000 AND  x<910000 AND  y>1473000 AND  y<2460000))

Also, are these values encoded?

Well, values are: gn: 00000001D2 codesite: H003975

[paulkd]

Your first code block appears not to have been copy/pasted correctly. There is an end comment block which errors.

Please add the following line

echo $site->ur().'<br>'.$site->get_param('zone_bde').'<br><br>';

above the first $sql block that is not working and then copy paste the actual (not working) block.

and then die($sql); after the $sql block.

[PriteshP23]

Please add the following line

I run file with one parameter like: -->> php updateNap.php TO

TO<br>(x>220000 AND  x<910000 AND  y>1473000 AND  y<2460000)<br><br>UPDATE LTE_noria_cellule cel_bk_up SET cel.NAP_TO = '1' WHERE cel.nidt
                        IN (
                            select gn
                                from LTE_noria_candidat_bk_up can
                            INNER JOIN LTE_noria_site_bk_up site ON (site.codesite = can.codesite)
                            WHERE (x>220000 AND  x<910000 AND  y>1473000 AND  y<2460000))

Where is the error ?

How to remove whitespace in CSV file with LOAD DATA INFILE ? It may works.

[PriteshP23]

This is CSV file of LTE_noria_site_bk_up Table.

How to import by LOAD DATA INFILE ?

GN;CODESITE;CANDIDAT;VERSION;ZDVIE;CAT_ARCEP;BLOC|$|
00000001B2;G003125;1;11;BORDEAUX;;6|$|
00000001B1;G003135;1;10;BORDEAUX;;1,2,3|$|

[paulkd]

is your update table named

LTE_noria_cellulecel_bk_up

or

LTE_noria_cellule

?

[PriteshP23]

For test, I have used LTE_noria_cellule_bk_up table.

I have also LTE_noria_cellule table.

is your update table named

LTE_noria_cellulecel_bk_up

I rename the table. Still, not working :(

[PriteshP23]

TO<br>(x>220000 AND  x<910000 AND  y>1473000 AND  y<2460000)<br><br>UPDATE LTE_noria_cellule_bk_up SET cel.NAP_TO = '1' WHERE cel.nidt
                        IN (
                            select gn
                                from LTE_noria_candidat_bk_up can
                            INNER JOIN LTE_noria_site_bk_up site ON (site.codesite = can.codesite)
                            WHERE (x>220000 AND  x<910000 AND  y>1473000 AND  y<2460000))

[paulkd]

Your update column NAP_TO has an alias reference of cel (cel.nidt) but your table is not aliased this way.

[PriteshP23]

LTE_noria_cellule_bk_up cel

Thank you for ur input and time...!! :):)

TO<br>(x>220000 AND  x<910000 AND  y>1473000 AND  y<2460000)<br><br>UPDATE LTE_noria_cellule_bk_up cel SET cel.NAP_TO = '1' WHERE cel.nidt
                        IN (
                            select gn
                                from LTE_noria_candidat_bk_up can
                            INNER JOIN LTE_noria_site_bk_up site ON (site.codesite = can.codesite)
                            WHERE (x>220000 AND  x<910000 AND  y>1473000 AND  y<2460000))TO<br>TO<br>