How can i get one id result and add it to another in a mysql table?

Question

How can I record all data in a table together?

i have values in individual rows that i wish to add up to one. and get the value into a variable.

what can i use instead of this code below to get all id in the table instead of one?

  if(isset($_GET['ide'])){
 $coode = $_GET['ide'];

so that once i get all id, i can do the query below...

$products = $db->query("SELECT e1,e2 FROM eyfstb WHERE specialnum='$coode'");
 while($row = $products->fetch_assoc()){
 $e1view = $row["e1"]; $e2view = $row["e2"];
}

and once the query is done, i want to be able to store them in a variable like below

$final = (e1,e2 of id1) + (e1,e2 of id2) + (e1,e2 of id3) + (e1,e2 of id4);

fine is 5 good is 4 fair is 3 my e1 is fine which is equal 5 my e2 is good which is equal 4

making 9 when i added it. but i want to get for all record rows in the table

currently i'm able to get the details for only one student from the url $coode but i want to get for all the student from a table and be able to add the resulting data.

Table Structure

id  |   e1    |   e2   |
---------------------------
1   |  fine   |  good  |
2   |  good   |  good  |
3   |  fair   |  fine  |

Does this answer your question? [How to add column values in mysql](https://stackoverflow.com/questions/12387061/how-to-add-column-values-in-mysql) — deoomen, Jul 01 '20 at 10:40
it gave an insight but not really, in my database, e1 is fine, and fine =5 e2 is good, and good is 4. — flochristos, Jul 01 '20 at 10:44
For reference, most useful table structure from mysql's CLI : `show create table yourTableName`. With that you will improve the pertinence and quality of answers received. — YvesLeBorg, Jul 01 '20 at 10:57

score 0 · Accepted Answer · answered Jul 01 '20 at 11:09

Better way is to store in database parameters like this as integer. It generates less problems with math operations or comparison.

But if you can't refactor it you should map each value to their numerical equivalent.

function mapDbValue(string $value): int {
  switch ($value) {
    case 'fine': return 5;
    case 'good': return 4;
    case 'fair': return 3;
  } 
}

And now in your while loop map values and add it.

$final = 0;
while (...) {
  $final += mapDbValue($row['e1']) + mapDbValue($row['e2']);
}

yeah, this is great, but i want to be able to get this record for all the members of the table and add their final score together. — flochristos, Jul 01 '20 at 11:29

How can i get one id result and add it to another in a mysql table?

1 Answers1