mysql insert into function

Question

I am creating a simple PHP webpage with MySQL database. The MYSQL database have the following contents: (illustration on left) I just want to know a possible way of doing some Data Manipulation to achieve the results on the right portion below.

 (initial value)                                                 (final result)

    ID | MINUS '1'(to "next" ID's)                ID |               ID |

     1 |   0                                       1 | ------------>  1 | 
     2 |   1 --> (will minus '1' to ID 3           2 | ------------>  2 | 
     3 |   0     up to the last)            3-1 =  2 | ------------>  2 | 
     4 |   0                                4-1 =  3 | ------------>  3 | 
     5 |   1 --> (minus '1' AGAIN to ID 6   5-1 =  4 | ------------>  4 | 
     6 |   0      up to the last)           6-1 =  5      -1    --->  4 | 
     7 |   0                                7-1 =  6      -1    --->  5 |

It is just COUNTING the number of minus and then subtracting it to ALL the ID that follows it. ID 2 has tells the next ID, which is 3 to subtract 1 to itself up to the last ID, (3-1 , 4-1, 5-1, 6-1, 7-1 ) but another instance came. ID 5 tells ID 6 to subtract itself again up to the last, so MINUS 2 from ID 6 up to the last ID. from just 6-1, 7-1 ---> 6-2, 7-2.

I know it sounds very easy for you, but I'm just a newbie and find this thing hard. Sorry for the headache. Hope someone helps, Thanks!

my DML is like (just for illustration, this should be in php, I will convert it, just help me in the LOGIC)

for($x=0;$x<num_rows;$x++)
   {

   if(MINUS = 1){
      query(UPDATE table_name SET ID=ID-1 WHERE ID = $x);}
    }

Something like that, i am still a newbie in decision making loops. I will be grateful if you could fix my loop. I am new to this community, I beg for your understanding.

Can you share the code that you've tried so far? It would be easier for us to help you if we know what you've tried. — aashnisshah
– aashnisshah, Commented Mar 16, 2014 at 16:37

Gordon Linoff · Accepted Answer · 2014-03-16 17:07:56Z

1

You can do this in MySQL either using variables or standard SQL. Here is the standard SQL:

select id, minus,
       (id - coalesce((select sum(minus) from table t2 where t2.id < t.id), 0)) as newId
from table t;

Here is the variable version:

select id, minus,
       (id - minus + (@value := @value + minus)) as newId
from table t cross join
     (select @value = 0) const
order by id

edited Mar 16, 2014 at 17:07

answered Mar 16, 2014 at 16:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

16 Comments

user3425991 Over a year ago

Hello, thanks for the response. May I ask what is the 'value' you are referring in select id, value. I know its a very noob question, sorry

aashnisshah Over a year ago

the 'id' and 'value' is what will be returned when the sql query is finished running. I think Gordon meant to use minus in there, so change it to 'minus' and give it a shot

user3425991 Over a year ago

Sorry for the hassle, but i really can't fully get your answer. Don't get me wrong, Its My bad, I am a newbie. Could you just explain a little bit further. What are you referring as "value" and "t2.id" and "t.id"? Kindly, Please help

user3425991 Over a year ago

@aashnisshah ok, ill give it a try. Thank you for elaborating.

user3425991 Over a year ago

@aashnisshah i'm stuck, where or how should I get that value? should I initialize it, get it from something e.g row?

|

aashnisshah · Accepted Answer · 2014-03-16 17:16:37Z

0

You can use a MySQL implementation as follows:

select id, minus,
   (id - coalesce((select sum(minus) from table t2 where t2.id < t.id), 0)) as newId

from table t;

The code idea came from Gordon's post, here is an elaboration.

The first part is select which states that you want to select certain input from mysql. id, minus follows, and means that you want to return the values of id and minus when the query has finished running.

The rest of it falls under the third part, where you perform the subtraction, and return the value of the subtraction as newId. So when the sql returns, it will get a row with ID, MINUS and NEWID as it's headings.

The subtraction works like this: coalesce returns the first non-null value (i.e. 1) in the list. the table t2 is using a second version of the table and calling it t2 so that you can compare information.

I hope this clarified things for you!

answered Mar 16, 2014 at 17:16

aashnisshah

4764 silver badges10 bronze badges

10 Comments

user3425991 Over a year ago

2. could I test the standard SQL directly? cause I'm using MySQL Query Browser

user3425991 Over a year ago

im giving you headache but please dont get mad, what is the difference of t2 and t? how should i put it a query browser.

user3425991 Over a year ago

i mean how should I query t2, how could i make t2 refer to the second version of table?

user3425991 Over a year ago

Kindly answer it. take your time always

user3425991 Over a year ago

thanks for the response again. i mean how should I query t2, how could i make t2 refer to the second version of table? Thanks you for all your help. Hope you answer again. I'll be back tomorrow and I'll update you asap. Thanks for the time, Good night.

|

Collectives™ on Stack Overflow

mysql insert into function

2 Answers 2

16 Comments

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

16 Comments

10 Comments

Your Answer

Sign up or log in

Post as a guest