UPDATE and increment by 10

[version2]

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

[Ac1d^]

Re: UPDATE and increment by 10

version2 napisal(a):
NTG, ...

update my_table set field = (field+10);

that should do.

[version2]

Re: UPDATE and increment by 10

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.

[Tim Martin]

Re: UPDATE and increment by 10

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

[Tim Roberts]

Re: UPDATE and increment by 10

"version2" <corbett.jarrod @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, timr@probo.com
Providenza & Boekelheide, Inc.

[version2]

Re: UPDATE and increment by 10

Does any know where a good change order for navigation script or
tutorial is?

Cause this is what i want to accomplish.

thanks

[Tim Martin]

Re: UPDATE and increment by 10

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

[Jerry Stuckle]

Re: UPDATE and increment by 10

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('SE LECT id, rank FROM mytable ORDER BY rank');
while ($data = mysql_fetch_res ult($result1)) {
$result2 = mysql_query("UP DATE mytable SET rank=$rank WHERE id =
{$data['id']});
$rank += 10;
}


--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[version2]

Re: UPDATE and increment by 10

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_re sult"

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=move up&ID=".$recor d[ID]."&muscleName=" .$record[ArtistType]."'>move
up</a>".$record[position]."</td>
</tr>";


item_position.p hp
<?

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

$muscleName=$_G ET['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($ph ase1);


//this is your code i am tring to implement
$result1 = mysql_query("SE LECT id, position FROM
".DB_PREFIX."". TBL_PREFIX." ORDER BY position");

while ($data = mysql_fetch_res ult($result1)) {
$result2 = mysql_query("UP DATE ".DB_PREFIX."". TBL_PREFIX." SET
position=$posit ion WHERE id = {$data['id']}");
$rank += 10;
}
header("Locatio n: index.php");
exit;

}

?>

[Jerry Stuckle]

Re: UPDATE and increment by 10

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_re sult"
>
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=move up&ID=".$recor d[ID]."&muscleName=" .$record[ArtistType]."'>move
up</a>".$record[position]."</td>
</tr>";
>
>
item_position.p hp
<?
>
if ($_GET["action"] == "moveup") {
>
$muscleName=$_G ET['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($ph ase1);
>
>
//this is your code i am tring to implement
$result1 = mysql_query("SE LECT id, position FROM
".DB_PREFIX."". TBL_PREFIX." ORDER BY position");
>
while ($data = mysql_fetch_res ult($result1)) {
$result2 = mysql_query("UP DATE ".DB_PREFIX."". TBL_PREFIX." SET
position=$posit ion WHERE id = {$data['id']}");
$rank += 10;
}
header("Locatio n: index.php");
exit;
>
}
>
?>
>

OK, what error do you get?

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[version2]

Re: UPDATE and increment by 10

mysql_fetch_res ult

Fatal error: Call to undefined function:

[Jerry Stuckle]

Re: UPDATE and increment by 10

version2 wrote:

mysql_fetch_res ult
>
Fatal error: Call to undefined function:
>

Sorry, should be mysql_fetch_arr ay().

Too many interfaces in too many languages lately :-)

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[version2]

Re: UPDATE and increment by 10

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=$_G ET['muscleName'];
$ID = $_GET["ID"];

$rank = 10;

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

mysql_query($ph ase2);

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

while ($data = mysql_fetch_arr ay($result1)) {
$result2 = mysql_query("UP DATE ".DB_PREFIX."". TBL_PREFIX." SET
position=$posit ion WHERE ID = {$data['ID']}");
$rank += 10;
}

header("Locatio n: index_exercise. php?ID=$muscleN ame");
exit;

}

[Jerry Stuckle]

Re: UPDATE and increment by 10

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=$_G ET['muscleName'];
$ID = $_GET["ID"];
>
$rank = 10;
>
$phase2 = "UPDATE ".DB_PREFIX."". TBL_PREFIX." SET position = position +
15 WHERE ID = $ID;";
>
mysql_query($ph ase2);
>
$result1 = mysql_query("SE LECT id, position FROM
".DB_PREFIX."". TBL_PREFIX." ORDER BY position");
>
while ($data = mysql_fetch_arr ay($result1)) {
$result2 = mysql_query("UP DATE ".DB_PREFIX."". TBL_PREFIX." SET
position=$posit ion WHERE ID = {$data['ID']}");
$rank += 10;
}
>
header("Locatio n: index_exercise. php?ID=$muscleN ame");
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.
jstucklex@attgl obal.net
=============== ===

[version2]

Re: UPDATE and increment by 10

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=$_G ET['muscleName'];
$ID = $_GET["ID"];


$rank = 10;
$result1 = mysql_query("SE LECT ID,position FROM
".DB_PREFIX."". TBL_PREFIX." ORDER BY position");

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

header("Locatio n: index_exercise. php?ID=$muscleN ame");
exit;

}