469,352 Members | 1,766 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,352 developers. It's quick & easy.

UPDATE and increment by 10

Hi,

i want to update each field in a database table and increment by 10.

so it will look like

10, 20, 30, 40, 50

if anyone can help it would be great

Sep 4 '06 #1
16 3620
version2 napisal(a):
NTG, ...

update my_table set field = (field+10);

that should do.

Sep 4 '06 #2
i am looking at resetting all the values in the column and giving the
first value 10 htne 2 value 20 and so on

can you help?
Ac1d^ wrote:
version2 napisal(a):
NTG, ...

update my_table set field = (field+10);

that should do.
Sep 4 '06 #3
Ac1d^ wrote:
>NTG, ...

update my_table set field = (field+10);

that should do.
version2 wrote:
i am looking at resetting all the values in the column and giving the
first value 10 htne 2 value 20 and so on
Why don't you start by explaining what problem you're trying to solve
with this? It smells like a nasty hack to me.

Assuming you already have the incremental numbers, you can do

UPDATE my_table
SET field=(field*10)

In addition, this has nothing to do with PHP. In future, you might want
to try comp.databases.mysql (or the appriate group for whatever DB you use)

Tim
Sep 4 '06 #4
"version2" <co************@gmail.comwrote:
>
i am looking at resetting all the values in the column and giving the
first value 10 htne 2 value 20 and so on
"First" has no meaning in SQL. You can just do a SELECT with ORDER BY to
get the records in the order you want, then build the UPDATE commands one
by one.
--
- Tim Roberts, ti**@probo.com
Providenza & Boekelheide, Inc.
Sep 4 '06 #5
Does any know where a good change order for navigation script or
tutorial is?

Cause this is what i want to accomplish.

thanks

Sep 5 '06 #6
version2 wrote:
Does any know where a good change order for navigation script or
tutorial is?

Cause this is what i want to accomplish.
You sent me a private email on the subject, but if I'm going to help you
I'll do it in public so that other people can (potentially) benefit from it.

If you don't mind, I'll repeat what I understand of your problem to see
whether I've got it right. You have some a table with some elements that
you want to order in some way:

A 10
B 20
C 30
D 40
E 50

The second column being used to sort in the order you want. You're
leaving gaps between the numbers so that you can easily shuffle lines
around and get a different ordering, e.g.

A 10
C 30
B 35
D 40
E 50

Having done that you want to renumber the sort field so there are again
even gaps in it

A 10
C 20
B 30
D 40
E 50

The thing is, I think this is the wrong way to go about the task. If
what you want is an ordering, SQL can do that without you having to keep
track of the gaps between numbers yourself. Off the top of my head, you
could do something like:

A 1
B 2
C 3
D 4
E 5

And if you want to swap B and D, simply

-- Shunt all the contents after the target space up
UPDATE table
SET order_key=order_key + 1
WHERE order_key >= 4

-- Move target element to target space
UPDATE table
SET order_key=4
WHERE other_col='B'

-- Shunt back to fill the hole
UPDATE table
SET order_key=order_key - 1
WHERE order_key >= 3

There are probably better ways to do this, but maybe this puts you on
the right track.

Of course, with very large tables this solution may perform worse. But
the kind of data on which you would want to do this sort of manual
ordering is (I would imagine) not going to have large enough numbers to
make this significant. YMMV. Always test before you optimise.

Tim
Sep 5 '06 #7
version2 wrote:
Does any know where a good change order for navigation script or
tutorial is?

Cause this is what i want to accomplish.

thanks
OK, now what you're trying to do makes a lot more sense.

I think the easiest way to do this would be a combination of PHP and
SQL. You can do it all in SQL depending on the release, but this should
work with all releases.

Let's say your table contains, among other things:

id rank
1 10
2 20
3 30
4 40
5 50

And you want to swap 3 and 4.

You can add 15 t0 #3, as you indicated:

id rank
1 10
2 20
3 45
4 40
5 50

Now, to get them in the order:

id rank
1 10
2 20
4 30
3 40
5 50
$rank = 10;
$result1 = mysql_query('SELECT id, rank FROM mytable ORDER BY rank');
while ($data = mysql_fetch_result($result1)) {
$result2 = mysql_query("UPDATE mytable SET rank=$rank WHERE id =
{$data['id']});
$rank += 10;
}
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Sep 5 '06 #8
Jerry you are the only one so far to understand,

And i do think this is a good idea.

I have implemented the code but there was an error with
"mysql_fetch_result"

here is the current setup. Could you have a look and tell me what i am
doing wrong

index.php

$HTMLOutput .= "<tr>
<td align=\"center\">".$record[exerciseName]."</td>
<td align=\"center\"><a href =
'item_position.php?action=moveup&ID=".$record[ID]."&muscleName=".$record[ArtistType]."'>move
up</a>".$record[position]."</td>
</tr>";
item_position.php
<?

if ($_GET["action"] == "moveup") {

$muscleName=$_GET['muscleName'];
$ID = $_GET["ID"];
$rank = 10;

//this is where i am adding in the 15
$phase1 = "UPDATE ".DB_PREFIX."".TBL_PREFIX." SET position = position +
15 WHERE ID = $ID;";
//this is where i am calling the $phase1 to run
mysql_query($phase1);
//this is your code i am tring to implement
$result1 = mysql_query("SELECT id, position FROM
".DB_PREFIX."".TBL_PREFIX." ORDER BY position");

while ($data = mysql_fetch_result($result1)) {
$result2 = mysql_query("UPDATE ".DB_PREFIX."".TBL_PREFIX." SET
position=$position WHERE id = {$data['id']}");
$rank += 10;
}
header("Location: index.php");
exit;

}

?>

Sep 5 '06 #9
version2 wrote:
Jerry you are the only one so far to understand,

And i do think this is a good idea.

I have implemented the code but there was an error with
"mysql_fetch_result"

here is the current setup. Could you have a look and tell me what i am
doing wrong

index.php

$HTMLOutput .= "<tr>
<td align=\"center\">".$record[exerciseName]."</td>
<td align=\"center\"><a href =
'item_position.php?action=moveup&ID=".$record[ID]."&muscleName=".$record[ArtistType]."'>move
up</a>".$record[position]."</td>
</tr>";
item_position.php
<?

if ($_GET["action"] == "moveup") {

$muscleName=$_GET['muscleName'];
$ID = $_GET["ID"];
$rank = 10;

//this is where i am adding in the 15
$phase1 = "UPDATE ".DB_PREFIX."".TBL_PREFIX." SET position = position +
15 WHERE ID = $ID;";
//this is where i am calling the $phase1 to run
mysql_query($phase1);
//this is your code i am tring to implement
$result1 = mysql_query("SELECT id, position FROM
".DB_PREFIX."".TBL_PREFIX." ORDER BY position");

while ($data = mysql_fetch_result($result1)) {
$result2 = mysql_query("UPDATE ".DB_PREFIX."".TBL_PREFIX." SET
position=$position WHERE id = {$data['id']}");
$rank += 10;
}
header("Location: index.php");
exit;

}

?>
OK, what error do you get?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Sep 6 '06 #10
mysql_fetch_result

Fatal error: Call to undefined function:

Sep 6 '06 #11
version2 wrote:
mysql_fetch_result

Fatal error: Call to undefined function:
Sorry, should be mysql_fetch_array().

Too many interfaces in too many languages lately :-)

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Sep 6 '06 #12
Hey Jerry,

that doesnt cause any errors, but doesn't update all the fields to show
10,20,30,40 etc it only adds on 15 to the name that i want to add 15
onto.

could i not be calling it or re-arrange to code.

look below

if ($_GET["action"] == "moveup") {

$muscleName=$_GET['muscleName'];
$ID = $_GET["ID"];

$rank = 10;

$phase2 = "UPDATE ".DB_PREFIX."".TBL_PREFIX." SET position = position +
15 WHERE ID = $ID;";

mysql_query($phase2);

$result1 = mysql_query("SELECT id, position FROM
".DB_PREFIX."".TBL_PREFIX." ORDER BY position");

while ($data = mysql_fetch_array($result1)) {
$result2 = mysql_query("UPDATE ".DB_PREFIX."".TBL_PREFIX." SET
position=$position WHERE ID = {$data['ID']}");
$rank += 10;
}

header("Location: index_exercise.php?ID=$muscleName");
exit;

}

Sep 6 '06 #13
version2 wrote:
Hey Jerry,

that doesnt cause any errors, but doesn't update all the fields to show
10,20,30,40 etc it only adds on 15 to the name that i want to add 15
onto.

could i not be calling it or re-arrange to code.

look below

if ($_GET["action"] == "moveup") {

$muscleName=$_GET['muscleName'];
$ID = $_GET["ID"];

$rank = 10;

$phase2 = "UPDATE ".DB_PREFIX."".TBL_PREFIX." SET position = position +
15 WHERE ID = $ID;";

mysql_query($phase2);

$result1 = mysql_query("SELECT id, position FROM
".DB_PREFIX."".TBL_PREFIX." ORDER BY position");

while ($data = mysql_fetch_array($result1)) {
$result2 = mysql_query("UPDATE ".DB_PREFIX."".TBL_PREFIX." SET
position=$position WHERE ID = {$data['ID']}");
$rank += 10;
}

header("Location: index_exercise.php?ID=$muscleName");
exit;

}
That's because $position isn't defined. In fact, your update is
probably failing (check the value of $result2 - false indicates a failure).

Rather you should be setting position=$rank, or use $position instead of
$rank throughout your code.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Sep 6 '06 #14
I realised that before your last post :)

after making that change i removed the queery where i add 15 to a
number just to see if that query that you gave me works but
unfortunatley it doesnt

is it missing the query or something.

if ($_GET["action"] == "moveup") {

$muscleName=$_GET['muscleName'];
$ID = $_GET["ID"];
$rank = 10;
$result1 = mysql_query("SELECT ID,position FROM
".DB_PREFIX."".TBL_PREFIX." ORDER BY position");

while ($data = mysql_fetch_array($result1)) {
$result2 = mysql_query("UPDATE ".DB_PREFIX."".TBL_PREFIX." SET position
= $rank WHERE ID = {$data['ID']}");
$rank += 10;
}

header("Location: index_exercise.php?ID=$muscleName");
exit;

}

Sep 6 '06 #15
version2 wrote:
I realised that before your last post :)

after making that change i removed the queery where i add 15 to a
number just to see if that query that you gave me works but
unfortunatley it doesnt

is it missing the query or something.

if ($_GET["action"] == "moveup") {

$muscleName=$_GET['muscleName'];
$ID = $_GET["ID"];
$rank = 10;
$result1 = mysql_query("SELECT ID,position FROM
".DB_PREFIX."".TBL_PREFIX." ORDER BY position");

while ($data = mysql_fetch_array($result1)) {
$result2 = mysql_query("UPDATE ".DB_PREFIX."".TBL_PREFIX." SET position
= $rank WHERE ID = {$data['ID']}");
$rank += 10;
}

header("Location: index_exercise.php?ID=$muscleName");
exit;

}
But you're just putting the same values in it as previously existed, so
how would you know if it failed?

And if it does fail (mysql_query returns false), what does mysql_error()
show?

You need to do a little troubleshooting on this, also. I can't do it
all from here.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Sep 6 '06 #16
Thanks Jerry,

I managed to work it out and works well

really appriciate your help

J

Sep 6 '06 #17

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

19 posts views Thread by Westcoast Sheri | last post: by
6 posts views Thread by Paul Eden | last post: by
reply views Thread by braedsjaa | last post: by
6 posts views Thread by Tom Allison | last post: by
1 post views Thread by Hexman | last post: by
5 posts views Thread by logina1 | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.