I have an issue when I try to calculate the number of rows inside my table, using a multiple separated ifs condition inside a trigger to test the field if it's different from empty.
I have tried to put the if out from select function and it work but I can't do it for all the cases that I have, it will be not optimized.
BEGIN
UPDATE max_tare
SET max_row = (
SELECT COUNT(*)
FROM history
IF(NEW.client != '') THEN
WHERE nom LIKE CONCAT('%', NEW.client, '%') END IF;
IF(NEW.commune != '') THEN
WHERE commune LIKE CONCAT('%', NEW.commune, '%') END IF;
IF(NEW.type != '') THEN
WHERE type LIKE CONCAT('%', NEW.type, '%') END IF;
IF(NEW.matricule != '') THEN
WHERE mat LIKE CONCAT('%', NEW.matricule, '%') END IF;
IF(NEW.tare != '') THEN
WHERE tare LIKE CONCAT('%', NEW.tare, '%') END IF;
WHERE cancled = 0),
max_tare =
(SELECT SUM(tare)
FROM history
WHERE cancled = 0) WHERE id = 1;
END
in php query I got the row number easy, but when I tried the trigger I got an error saying:
MySQL replied: # 1064 - Syntax error near '
IF' (new.client! = '') THEN WHERE name LIKE CONCAT ('%', NEW.client, '%')END IF;
I will lend the php code used under codeigniter framework this works perfectly,
$this->db->select("*");
$this->db->from('history');
if ($query[1] != '') {
$this->db->like('nom', $query[1]);
}
if ($query[2] != '') {
$this->db->like('commune', $query[2]);
}
if ($query[3] != '') {
$this->db->like('type', $query[3]);
}
if ($query[4] != '') {
$this->db->like('mat', $query[4]);
}
if ($query[10] != '') {
$this->db->like('rfid', $query[10]);
}
if ($query[5] != '') {
$this->db->like('tare', $query[5]);
}
if ($query[6] != '') {
$this->db->where('date >', $query[6]);
}
if ($query[7] != '') {
$this->db->where('date <', $query[7]);
}
if ($query[11] != '') {
$this->db->where('time_plode >', $query[11]);
}
if ($query[12] != '') {
$this->db->where('time_plode <', $query[12]);
}
$this->db->where('cancled', 0);
return $this->db->count_all_results();
this code will return the count number of rows and it works fine under php, but I want to use it in trigger rather than php query.
hope with this I am more clear
I will try to make an example:
+----+--------+---------+------+--------------+------+
| temp_fetch |
+----+--------+---------+------+--------------+------+
| id | client | commune | type | matricule | tare |
+----+--------+---------+------+--------------+------+
| 1 | EPIC | | | | |
+----+--------+---------+------+--------------+------+
this is my triggered table that will activate when I update it, let's just assume it's have been updated to this case here and now I will try to filter my result to count them
+----+-------------+---------+--------+--------------+-------+---------+---------+
| history |
+----+-------------+---------+--------+--------------+-------+---------+---------+
| id | nom | commune | type | mat | tare | rfid | cancled |
+----+-------------+---------+--------+--------------+-------+---------+---------+
| 1 | EPIC paris | france | white | 01248-816-16 | 7600 | ABCF44C | 0 |
+----+-------------+---------+--------+--------------+-------+---------+---------+
| 2 | EPIC london | UK | white | 06854-315-16 | 5233 | A8CG27C | 1 |
+----+-------------+---------+--------+--------------+-------+---------+---------+
| 3 | NET barça | ESP | red | 03254-615-16 | 8900 | HBC54AC | 1 |
+----+-------------+---------+--------+--------------+-------+---------+---------+
| 4 | NET Dubai | arab | blue | 35251-117-16 | 11200 | HDK7BV5 | 0 |
+----+-------------+---------+--------+--------------+-------+---------+---------+
| 5 | EPIC roma | ita | red | 36524-618-16 | 7300 | NBL53DC | 0 |
+----+-------------+---------+--------+--------------+-------+---------+---------+
| 6 | SNC beta | alpha | green | 69358-117-16 | 5400 | JDLF8ND | 1 |
+----+-------------+---------+--------+--------------+-------+---------+---------+
| 7 | EPIC tokyo | japan | yellow | 46258-712-16 | 8700 | K5ND55D | 1 |
+----+-------------+---------+--------+--------------+-------+---------+---------+
| 8 | SARL Fit | body | black | 69531-614-16 | 9600 | AIES5HJ | 0 |
+----+-------------+---------+--------+--------------+-------+---------+---------+
So this is my table that I want to count rows inside that match with my array table (1st one) So my desired result inside my table max_tare :
+----------------------------+
| max_tare |
+----------------------------+
| id | max_row | max_tare |
+----+------------+----------+
| 1 | 2 | 14900 |
+----+------------+----------+
that counted two row rows they have nom like epic and where cancled = 0
hopefully now its clear as I added simple data
when I try Danblack solution which is
UPDATE max_tare
SET max_row =
(SELECT COUNT(*)
FROM history
WHERE nom LIKE CONCAT('%', NEW.client, '%')
AND commune LIKE CONCAT('%', NEW.commune, '%')
AND TYPE LIKE CONCAT('%', NEW.type, '%')
AND mat LIKE CONCAT('%', NEW.matricule, '%')
AND tare LIKE CONCAT('%', NEW.tare, '%')
AND cancled = 0),
max_tare =
(SELECT SUM(tare)
FROM history
WHERE cancled = 0)
WHERE id = 1;
I don't get :
+----------------------------+
| max_tare |
+----------------------------+
| id | max_row | max_tare |
+----+------------+----------+
| 1 | 0 | 14900 |
+----+------------+----------+
and this is not what I want, the problem with this solution is that it search in my table any data that can be empty.
thanks in advance for the help
First, the there is an IF function and an IF statement. They look different and are relevant in different parts of SQL syntax.
Could this be simpler by not considering the empty string in an if statement and just using:
UPDATE max_tare
SET max_row =
(SELECT COUNT(*)
FROM history
WHERE nom LIKE CONCAT('%', NEW.client, '%')
AND commune LIKE CONCAT('%', NEW.commune, '%')
AND TYPE LIKE CONCAT('%', NEW.type, '%')
AND mat LIKE CONCAT('%', NEW.matricule, '%')
AND tare LIKE CONCAT('%', NEW.tare, '%')
AND cancled = 0),
max_tare =
(SELECT SUM(tare)
FROM history
WHERE cancled = 0)
WHERE id = 1;
After all '%%' as a pattern matches all.