Copy MySQL column

Hi all.

I have 100 rows in a column i want to copy from column "score_1" to column "score_2", which are identical.
It should be ordered after the ID.
I tried to use the below code:

[/if(isset($_POST['overfor'])){
$UpdateQuery = "UPDATE tavlen SET score_2='$_POST[score_1]' ";
mysqli_query($link, $UpdateQuery);
};

Open in new window


This dossent Work.
Can you help me with some examples? That would greatly be appreciated.

I read about SELECT and maybe that should be a part of it? But i kinda lack examples, to fully understand what i need to make use of, in this situation?

I need to be able to copy the
Mike KristensenIT administratorAsked:
Who is Participating?
 
Julian HansenCommented:
This should do it

$query = "UPDATE tavlen SET score_2 = score_1";

Open in new window

0
 
Julian HansenCommented:
Your question is not clear.

Given a table
CREATE TABLE tavlen (
    score_1 int,
    score_2 int
);

Open in new window

Do you want this

UPDATE tavlen SET score_2 = score_1

Open in new window


Your php code appears to be trying to set the value of score_2 to a specific value for all rows in the table - please can you explain how that ties up with your original question?

Breaking down your question

"I have 100 rows in a column" This is confusing - tables have rows - rows have columns. Do you mean you have 100 rows in a table with a particular column?

"I want to copy from column "score_1" to column "score_2", which are identical. " I am assuming you mean their definition (types) are identical. Do you want to do this for all rows in the table (in which case the code pasted above will work) or only for certain rows (in which case you would need to append a WHERE clause)

The more information you can provide us the easier it will be to solve your problem.
0
 
Ray PaseurCommented:
Please take a moment to get some foundation in how PHP works. I promise it will save you huge amounts of time as you work on PHP issues.  PHP is like most things -- it helps if you know what you're doing.  This article can lead you to some learning resources that will help you get started and will provide good reference materials as you move forward with PHP.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html

The reason I urge you to take some time with the basics is simple: Terms like "column" and "row" are terms of art in mathematics and information technology.  If you know what these terms mean and use them correctly you will be able to ask questions that can get you clear and accurate answers.  There is also a special meaning for $_POST.  It is a "superglobal" request variable.  This is not something from your database.
$_POST[score_1]

Here are the man pages that tell about $_POST and similar variables.
http://php.net/manual/en/reserved.variables.php
http://php.net/manual/en/language.variables.external.php
http://php.net/manual/en/reserved.variables.post.php

PHP has special meanings for quote marks.  Almost certainly you do not want to write this...
$_POST[score_1]

... instead you probably want this (these two different ways of writing $_POST have different meanings, and the former injects a time-bomb into your code).
$_POST['score_1']

This is a common point of confusion, but it's important to get quote marks right when you're using PHP.  More on quote marks can be learned here:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_12241-Quotation-Marks-in-PHP.html

Finally, there is a principle in information technology that is called the DRY principle.  When you say that you want to copy data from one column to another, it's a clear violation of the DRY principle, and nobody would willingly do that.  So there must be something that you want to do with the copied data.  If you can tell us what you want to achieve in plain, non-technical language we may be able to suggest an approach that will help you get a good result.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Mike KristensenIT administratorAuthor Commented:
Oki. What i want to do:
Over a year, i store 1 decimal value, that contains max 2 characters for 100 users. This value can change and are different per user.

After a year is gone, i need to move the values and store them as "Last year". This should be done by 1 click.

Untitled.png
0
 
Ray PaseurCommented:
OK, I think I understand the goal.  We achieve the goal by storing a date field with each data element.  We retrieve the data according to a WHERE clause that contains the date field.  Thus you can answer all kinds of questions about the data, for example, when was the row of data created?  How many rows were created last week, last month, last year?  How many rows were created in February?  What are all the values from last year?  Or from two years ago.  This is a better design than storing things "by year" when you're dealing with a small number of data points.  With much larger data sets, it might make sense to "prune" the tables, but with this amount of data, standard techniques should prevail.
0
 
Ray PaseurCommented:
Without any WHERE or LIMIT clause how would the SQL engine know which rows to update?
0
 
Julian HansenCommented:
He has not given us any information on filtering - his last post showed a layout where he indicated (green) that all rows were affected - in his opening post he mentions 100 rows.

The interface indicated above also does not show buttons on each row implying that the button he refers to is at the bottom and therefore global. Finally, in his opening post his PHP script did not include a filter.

Based on the above information it seemed most likely that he wanted to update all the records in the table.
0
 
Ray PaseurCommented:
I was looking at this part of the question:
After a year is gone, i need to move the values and store them as "Last year". This should be done by 1 click.
That leads me to believe that you would need to know when a year has gone by.  That's why I would recommend a design that uses a date column in each row.  Perhaps that could be a TIMESTAMP, but we would need to know a little more about the application requirements to know for sure.
0
 
Mike KristensenIT administratorAuthor Commented:
#JulianH

$UpdateQuery = "UPDATE tavlen SET score_2 = score_1";

This worked, as i needed it to. Very perfect.
Now i just need to understand why. :)
It is a manual process. Its only 1 time per year, and it isent important if it is january or marts. Its something for a summer turism. :)



I started by using this: $UpdateQuery = "UPDATE tavlen SET score_2='$_POST['score_1']' ";
That wouldent do the job, and i was lost. Im still confused and i got a hard time figuring out why that wont work?
0
 
Ray PaseurCommented:
@Mikethk: I'll sign off now.  You might want to hire a professional developer to help you with this.  It won't cost you much money to get it right.  There are just too many gaps in understanding to continue down this path in an online forum like E-E.  Good luck with your project!
0
 
Julian HansenCommented:
UPDATE tavlen SET score_2='$_POST['score_1']' 

Open in new window


There is a bit of disconnect between what  you want to do and the code above.

The above code - assumes you are wanting to set ALL values of score_2 in the table to a single value posted to your page - which is not the same as setting score_2 to the corresponding score_1 value in the same row. In other words your PHP code would have been equivalent to (Assuming the value of $_POST['score_1'] is 7)

UPDATE tavlen SET score_2=7;

Open in new window


After executing - all values in score_2 would be 7 - not the desired result.

The solution that worked used a query that said to the DB

For every row in the database Do
   Find the score_2 value in the current row and set it to whatever is in the score_1 value IN THE SAME ROW
End For

Open in new window


I hope that sheds some light.
0
 
Mike KristensenIT administratorAuthor Commented:
Thx all.
It was some great help.

#JulianH
This was what i thought aswell. Very much thx for clearifying :)

I just signed up for some PHP classes again. :) So i can get to understand the basics, which obvisuly is pretty damn important :)
0
 
Julian HansenCommented:
You are welcome - good luck with your project.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.