Quicker way of doing this (MySQL)?

[LemonInflux]

<?php

$query = mysql_query("SELECT * FROM `table` WHERE `id` = '1'") or die(mysql_error());

while($row = mysql_fetch_assoc)
{
$sql = mysql_query("UPDATE `table` SET `field` = '". $row['field'] + 1 ."' WHERE `id` = '1'");
}

?>

 

There are probably a hundred mistakes, but this is an example. I'm trying to add 1 to a field's value, but the only way I can think how to do it is to start a loop and then use $row['field'] + 1. Surely there's a quicker way? (For example, $v + 1 is the same as $v++ in PHP)

[fotobleu]

hi

 

this might help

http://www.tech-recipes.com/rx/2139/mysql_increment_an_exisitng_value

[LemonInflux]

Will the same kind of thing work in MSSQL?

 

I'm building software with support for multiple types of database, so if it doesn't work for like, MySQL, MSSQL and PostgreSQL, I can't use it :{

[fotobleu]

i dont know about that, but you can test it , hope it works

[PC Nerd]

UPDATE table SET field = table.field+1 WHERE id=table.id

 

 

someone will need to check this because its just a guess, however it should work for you.

 

gdlk

[Barand]

UPDATE table SET field = table.field+1 WHERE id=table.id

 

 

someone will need to check this because its just a guess, however it should work for you.

 

gdlk

 

That will update all rows (id always equals id)

<?php
$id = 1;
mysql_query ("UPDATE table SET field = field+1 WHERE id = $id");

 

MySQL, MSSQL compatible. I don't use postgres but should be OK.

 

You will probably only have difficulty across dbms when you try using SQL functions, especially datetime ones. Date formats can also be a problem with different dbms

[LemonInflux]

For the sake of tidiness, would:

 

UPDATE `table` SET `field` = 'field+1' WHERE `id` = '$id'

 

also work (with the ` and ' added)?

[Barand]

that will set field to the string value "field+1". Use

UPDATE `table` SET `field` = `field`+ 1 WHERE `id` = '$id'

 

But now it is no longer compatible with MSSQL. The equivalent MSSQL (to allow spaces/resrved words in column names) is

 

UPDATE [table] SET [field] = [field] + 1 WHERE [id] = $id

 

(Note MySQL allows '$id' even if $id is numeric; other dbms do not.

[LemonInflux]

Ok, thanks!

[PFMaBiSmAd]

Since you asked about other database types, the back-tick ` is mysql specific and will result in a syntax error with other databases.

[LemonInflux]

so if I get rid of ` and ', it'll work on MySQL/MSSQL/PostgreSQL?

 

Edit: Post 600 =D

[Barand]

So long as table/col names do not contain spaces or reserved words, the version I gave originally should work with all

[LemonInflux]

Great! Well, now to go through all the files in my project and get rid of ` and '