I have the following code, which is adding some data to a database via server side API. The field order_number should be created via $OrderNumberNext, which is calculated by a count(*)+1 as order_num variable.
However, checking the database after this is done shows that this only ever calculates as 0 (zero). Should I be using a different function call for this?
function addChartToSet($chartId, $setId){
$chartWithId = $this->db->get_var($this->db->prepare("select id from chords where chord_id=%s",$chartId));
$setWithId = $this->db->get_var($this->db->prepare("select id from sets where set_id=%s",$setId));
$orderNumberNext = $this->db->get_var($this->db->prepare("select count(*)+1 as `order_num` from `chords_inside_sets` where `set_id`=%s",$setId));
$this->db->query($this->db->prepare("update sets set `last_updated_time`=NOW() where `set_id`=%s",$setId));
$this->db->query($this->db->prepare("insert into `chords_inside_sets` set `chord_id`=%s , `set_id`=%s, `order_number`= %s",$chartWithId,$setWithId,$orderNumberNext));
return array("last_updated_time"=> $this->getMaxUpdatedTimeForSet($setId), "query"=> $this->db->last_query);
}
You are adding 1 inside the query that fetches the data. It cannot work. Get your count first through the SELECT query, then add one on the next line, like this: