Update sql with a loop (PDO)

Question

If somebody would please take a look at my code and tell what to do with it. I thought it was right but obviously it is not.

<? require_once("dblogin.php");
$sth = $conn->prepare("SELECT * FROM country_city_zip WHERE country = US");
$sth->execute();
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) { 

$sql ="UPDATE country_city_zip SET uni = '".$row['country']."-".$row['zip']."' WHERE country = '".$row['country']."' AND zip = '".$row['zip']."'"; 
$count = $conn->exec($sql);

} ?>

I want to create a unique ID based on the country code and the zip code: ex. US-28172

Jalpesh Patel · Accepted Answer · 2012-07-17 11:42:37Z

4

Try this query

$sql= "UPDATE country_city_zip SET uni = '".concat($row['country'],"-",$row['zip'])."' WHERE country = '".$row['country']."' AND zip = '".$row['zip']."'";

I assume that you have problem in the sql query and based on i will post my answer

I hope problem is solved and you get any problem then let me know..

answered Jul 17, 2012 at 11:42

Jalpesh Patel

3,17810 gold badges47 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Corfitz. Over a year ago

I don't seem to get any problems, but it still won't update the data in the table.

Jalpesh Patel Over a year ago

please echo your query and check what value are come and still you are not get then put the query over here @Philip Jens Bramsted

Corfitz. Over a year ago

it doesn't do anything.. but it works when I just use the sentence in the SQL field in the Database instead... No script needed. Thanks anyway..

Brian Adkins · Accepted Answer · 2012-07-17 11:37:22Z

2

Try this:

UPDATE country_city_zip SET uni = Concat(country,'-',zip)

No need for a loop...

answered Jul 17, 2012 at 11:37

Brian Adkins

6662 gold badges6 silver badges13 bronze badges

4 Comments

Corfitz. Over a year ago

So basically you just want me to do like this: $sql = "UPDATE country_city_zip SET uni = Concat(country,'-',zip)"; $count = $conn->exec($sql);

Brian Adkins Over a year ago

Yep... Never test out proposed solutions on real data though... Or at least have a backup available... Cant be too safe.

Corfitz. Over a year ago

This was actually the solution.. somehow.. I choose not to create a script to do it for me.. Because when I just typed that sentence you wrote - into the SQL field in the database it did exactly what I wanted.. i t just seems to be difficult if you want a script to do it for you... Thanks!

Ifthikhan Over a year ago

The query suggested by @BrianAdkins will update all the rows in your table. In the select statement above you seem to fetch records for which the value of the country column is "US", so I would suggest that you add a where clause to the query.

Collectives™ on Stack Overflow

Update sql with a loop (PDO)

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related