CODEWITHSUNDEEP
phpmysqlarrays
grantiago
grantiago

Reputation: 175

Split mySQL result on comma

I have a mySQL table that looks like this(with some other columns I didn't select): sql table

I have a mysqli php query this:

$sql=('SELECT point FROM placemarks WHERE id < 5');

A result: 

while ($row = $rs->fetch_assoc()) {

     printf ("%s <br />", $row["point"]);

-157.798176, 21.254402, 0

-157.804087, 21.253042, 0

-157.805289, 21.252882, 0

First, I want to split the result on the comma. Once I have that I want to insert the values back into the 'lat' and 'lng' columns with php and mysqli. I've searched and tried for a couple hours. At this point I'm sure I am on the completely wrong path.

Upvotes: 0

Views: 1516

Answers (4)

Professor Abronsius
Professor Abronsius

Reputation: 33813

Unless you need to split and then manipulate in php simply to update the lat/lng in the db then you could do it in one query like this perhaps:

update `placemarks` set `lat`=substring_index(`point`,",",1 ), `lng`=substring_index(`point`,",",-1 );

damnit - never noticed or acknowledged the trailing zero

update `placemarks` set 
    `lat`=substring_index( substring_index(`point`,",",2 ),",",1 ),
    `lng`=substring_index( substring_index(`point`,",",2 ),",",-1 );

Upvotes: 0

Pete
Pete

Reputation: 1309

UPDATE placemarks 
SET lat = SUBSTRING_INDEX(point, ',', 1)
,lng =  SUBSTRING(SUBSTRING_INDEX(point, ',', -1), 1, -2)

Upvotes: 1

LuckyLue
LuckyLue

Reputation: 158

In php explode maybe.

$point = explode(',', $row['point']);

Upvotes: 0

Atilla Arda A&#231;ıkg&#246;z
Atilla Arda A&#231;ıkg&#246;z

Reputation: 830

Use explode and break it into array:

The explode() function breaks a string into an array.

 print_r(explode(",",$row["point"]));//breaks into pieces at comma

Then created your desired query and put them in your database as you asked

Upvotes: 1

Related Questions