-1

I managed to get Ajax to dynamically update MySQL row results by clicking on the entry I want to update and this works. However, if I use GROUP CONCAT I can still click on the entry I want to update but the changes don't get saved. Is this normal? Or should this still work with GROUP CONCAT?

Current code that works when the mysql query doesn't use group concat:

This is the function:

function edit_data(id, text, column_name)  
{  
$.ajax({  
url:"food_edit.php",  
method:"POST",  
data:{id:id, text:text, column_name:column_name},  
dataType:"text",  
success:function(data){  
//alert(data);
}  
});  
}

And this is food_edit.php

<?php
include ("expenses.inc");
session_start(); 

$con = mysqli_connect("$server","$user","$pw","$db");   

$id = $_POST["id"];  
$text = $_POST["text"];  
$column_name = $_POST["column_name"];

$query = "UPDATE food SET ".$column_name."='".$text."'    WHERE id='".$id."'";
if(mysqli_query($con, $query))  
{  
echo 'Data Updated';  
}                                         
?>

I tried updating the code as below to compensate for group concat but it did not work:

function edit_data(ID, FOOD_TYPE, STORE_NAME, LOCATION, DATUM, COST)  
{  
$.ajax({  
url:"food_edit.php",  
method:"POST",  
data:{ID:ID, FOOD_TYPE:FOOD_TYPE, STORE_NAME:STORE_NAME, LOCATION:LOCATION, DATUM:DATUM, COST:COST},  
dataType:"text",  
success:function(data){  
//alert(data);
}  
});  
}

and food_edit.php: 

<?php

include ("expenses.inc");

session_start(); 

$con = mysqli_connect("$server","$user","$pw","$db");   

$id = $_POST["id"];  

$FOOD_TYPE = $_POST["FOOD_TYPE"];
$STORE_NAME = $_POST["STORE_NAME"];
$LOCATION = $_POST["LOCATION"];
$DATUM = $_POST["DATUM"];
$COST = $_POST["COST"];

$query = "UPDATE food SET
".$FOOD_TYPE."='".$_POST["FOOD_TYPE"]."',
".$STORE_NAME."='".$_POST["STORE_NAME"]."',
".$LOCATION."='".$_POST["LOCATION"]."',
".$DATUM."='".$_POST["DATUM"]."',
".$COST."='".$_POST["COST"]."'

WHERE ID='".$_POST["ID"]."'";  

if(mysqli_query($con, $query))  
{  
echo 'Data Updated';  
}                                     
?>

Hope this clarifies.

I added the mysql query with the group concat

$query = "
Select

GROUP_CONCAT(ID,' <br>' ORDER BY DATUM SEPARATOR ' ') AS ID,  

GROUP_CONCAT(FOOD_TYPE,' <br>' ORDER BY DATUM SEPARATOR ' ') AS FOOD_TYPE,  

GROUP_CONCAT(STORE_NAME,' <br>' ORDER BY DATUM SEPARATOR ' ') AS STORE_NAME,     

GROUP_CONCAT(LOCATION,' <br>' ORDER BY DATUM SEPARATOR ' ') AS LOCATION,

GROUP_CONCAT(DATUM,' <br>' ORDER BY DATUM SEPARATOR ' ') AS DATUM,   

GROUP_CONCAT(COST,' <br>' ORDER BY DATUM SEPARATOR ' ') AS COST 

FROM
(
SELECT food.ID, food.FOOD_TYPE, food.STORE_NAME, food.LOCATION, food.DATUM, food.COST FROM food
) TEST
group by DATUM
ORDER BY DATUM DESC";
PAPADOC
  • 45
  • 1
  • 6
  • 1
    Can you include the relevant parts of your existing code? – Panda Dec 17 '18 at 04:32
  • Does it shows any error message? – Hasta Dhana Dec 17 '18 at 04:50
  • No errors are reported. – PAPADOC Dec 17 '18 at 05:01
  • We have no idea where you are calling this `edit_data` function, resp. what parameters you are feeding to it. The GROUP_CONCAT is part of the query that selects the data only (it would not make sense in an UPDATE statement in the first place), so the only thing we can guess is that the parameter values you are calling your function with don’t make sense or are the wrong ones, but with what you have shown us so far, impossible for us to tell. – misorude Dec 17 '18 at 08:49
  • edit_data is referred to in the main index.php file along with food_select.php. food_select.php is the script that displays the data and where I am trying to use GROUP_CONCAT. I'm sure it has to do with the parameters, because the function that does work when GROUP_CONCAT is not used referred only to ID, text and column_name. I tried specifying all my columns when using GROUP_CONCAT but for sure I'm doing something wrong. Or maybe this just won't work seeing that the results are grouped? – PAPADOC Dec 18 '18 at 08:10

1 Answers1

0

It would help to see an example of what exactly you are calling on your back end to make changes, but my guess is that the API Endpoint you are using is expecting a single item ID and now you are submitting multiple. In general Updates can not contain any grouping as they have to refer to specific rows, Grouping can be accomplished via sub-selects.

Eugene Lorman
  • 66
  • 3