473,320 Members | 1,982 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 3775
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

19
by: Westcoast Sheri | last post by:
To keep track of how many fruits my visitors buy, I use a mySQL database (2 columns: "fruit" and "quantity")....so can we make these following mySQL queries work somehow? (visitor buys 5...
6
by: Paul Eden | last post by:
Hello all. I'm totally stumped. Please de-stump me! I've read my data in from the database, changed it according to what i want to do and now I want to write it back. But it seems I can only...
0
by: braedsjaa | last post by:
Just starting out with mySql / php ... hope this is a simple question and my description is understandable: I want to present table data from one row in one webpage with links to the previous...
33
by: Lee C. | last post by:
I'm finding this to be extremely difficult to set up. I understand that Access won't manage the primary key and the cascade updates for a table. Fine. I tried changing the PK type to number and...
8
by: Maxi | last post by:
There is a lotto system which picks 21 numbers every day out of 80 numbers. I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21) Here is the structure and sample data: ...
5
by: Wing | last post by:
Hi all, I am writing a function that can change the value "Quantity" in the selected row of MS SQL table "shoppingCart", my code is showing below ...
6
by: Tom Allison | last post by:
I seemed to remember being able to do this but I can't find the docs. Can I run a sql query to insert new or update existing rows in one query? Otherwise I have to run a select query to see if...
1
by: Hexman | last post by:
Hello Again, I have a table that contains data that needs to be ranked. There are actually 2 columns that need to be ranked. I have already completed the task but did it in a "Brute Force" way....
5
by: logina1 | last post by:
hi all, This command update table1 set col4 = rownum; updates all the records with auto increment number.But i want to increment based on date. i.e when the date changes it need to start from 1....
1
by: jimilives | last post by:
I forgot to turn on auto increment when I reinstalled this database and now I have a bunch of NULL values in my ID field for last few hundred inserts, how can I update this table to replace the NULLS...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.