Foreach with explode

[gmc1103]

Hi

 

I have a small issue with the following code

Someone pick time period, can be more than one

Then i have a function to check if the time period is already picked

$sala = $this->getDisponibilidadeSala($data, $id_bloco, $id_sala); 

If the return is 1 it is, but it's only pick me the first time period,, so i need a loop to check for every time period

the code is the following

 public function reqSalas($data, $atividade, $id_utilizador, $id_bloco, $id_sala)
    {
        $sala = $this->getDisponibilidadeSala($data, $id_bloco, $id_sala);
        $id_ano = $this->getAnoEscolar();
        $id_equipamento = NULL;
        $sobreposicao = $this->getDisponibilidadeDocente($data, $id_bloco, $id_utilizador);
        if ($sala > 0 ) {
            return array('status' => 'error', 'message' => 'Esta sala já está reservada nesse dia e bloco ...');
        }
        if($sobreposicao >0){
            return array('status' => 'error', 'message' => 'Ja tem uma reserva para este dia e bloco...');
        }
        else {
            try {
                $stmt = $this->db->prepare("INSERT INTO requisicoes (datas,atividade,id_ano) 
                                        VALUES(:datas,:atividade,:id_ano);");
                $stmt->bindparam(":datas", $data);
                $stmt->bindparam(":atividade", $atividade);
                $stmt->bindparam(":id_ano", $id_ano);
                $id_blocos = explode(',', $id_bloco);
                foreach ($id_blocos as $id) {
                    $result = $stmt->execute();
                    $id_requisicoes = $this->db->lastInsertId();
                    $this->insertRequisicaoByLastId($id_requisicoes, $id_utilizador, $id_equipamento, $id, $id_sala);
                }
                if (!$result) {
                    print_r($stmt->errorInfo());
                    return array('status' => 'error', 'message' => 'Problema ao gravar esta nova reserva para esta sala ...');
                } else {
                    return array('status' => 'success', 'message' => 'O registo foi criado com sucesso...');

                }
            } catch (Exception $ex) {
                echo $ex->getMessage();
            }
        }
    }

The function called is

 public function getDisponibilidadeSala($data, $id_bloco, $id_sala){
        try {
            $stmt = $this->db->prepare("SELECT COUNT(*) AS total FROM rel_requisicoes_detalhes AS t1 
                                        INNER JOIN requisicoes AS t2 ON (t1.id_requisicoes = t2.id_requisicoes)
                                        WHERE t1.id_bloco = :id_bloco AND t1.id_sala = :id_sala AND t2.datas = :datas;");
            $stmt->bindparam(":datas", $data, PDO::PARAM_STR);
            $stmt->bindparam(":id_bloco", $id_bloco, PDO::PARAM_INT);
            $stmt->bindparam(":id_sala", $id_sala, PDO::PARAM_INT);
            if (!$stmt->execute()) {
                print_r($stmt->errorInfo());
                return false;
            } else {
                $result = $stmt->fetchColumn();
                return $result;
            }
        } catch (Exception $ex) {
            echo $ex->getMessage();
        }
    }

This works only if the fisrt time period is empty...otherwise, no

[Barand]

It is often better to describe what the problem is rather try to describe your solution to that problem.

[gmc1103]

Ok

Let me explain better with my strange english 

The diagram i was working it is to make a new database but until is not ready i have this problem

An user want to make a reservation of a classroom for instance all morning

From 8:15 until 13:20

So until 13:20 i have 5 time period

If i choose only one time period...the function i put in my first post works, but if i put 2,3,4 don't.

So the fisrt fucntion

 public function reqSalas($data, $atividade, $id_utilizador, $id_bloco, $id_sala)

call in the first line 

$sala = $this->getDisponibilidadeSala($data, $id_bloco, $id_sala); 

 public function getDisponibilidadeSala($data, $id_bloco, $id_sala){
        try {
            $stmt = $this->db->prepare("SELECT COUNT(*) AS total FROM rel_requisicoes_detalhes AS t1 
                                        INNER JOIN requisicoes AS t2 ON (t1.id_requisicoes = t2.id_requisicoes)
                                        WHERE t1.id_bloco = :id_bloco AND t1.id_sala = :id_sala AND t2.datas = :datas;");
            $stmt->bindparam(":datas", $data, PDO::PARAM_STR);
            $stmt->bindparam(":id_bloco", $id_bloco, PDO::PARAM_INT);
            $stmt->bindparam(":id_sala", $id_sala, PDO::PARAM_INT);
            if (!$stmt->execute()) {
                print_r($stmt->errorInfo());
                return false;
            } else {
                $result = $stmt->fetchColumn();
                return $result;
            }
        } catch (Exception $ex) {
            echo $ex->getMessage();
        }
    }

This function check if the $id_bloco is already in a reservation as long the $data and $id_sala(classroom)

With one time period, it works well, with more...don't

Check the picture and follow what i gonna write

 

 

So look the number one in red, this user made a reservation in 12/12/2016 at 10:00

Then

number 2,3,4 made a reservation from 9 AM until 10:45 (this makes 3 time periods), so since the fisrt time period was clear, the function add to the database different users in same classroom and same time period. 1 and 3 

 

Better explained?

Edited January 19, 2017 by gmc1103

[mac_gyver]

this should (greatly) reduce the amount of code the OP has, to a single query, executed in a loop, which will make it easier to see where any problems are at, i.e. being able to see the forest for the trees.

 

in general, you should NOT try to select data to test if it already exists, to decide if you should insert it. just try to insert the data, and with the appropriate composite unique index defined for your table, detect if the insert query returned a unique index error. to detect the unique index error (the error number is 1062) you would have a try/catch block around just the execution of the insert query and check the error number in the catch block. since you can insert multiple rows, you should set up an array to hold the returned status from all the queries that get executed inside the loop. your current code is checking the last result after the end of the loop and returning just that one piece of status information.

 

also, i am wondering why you must explode a comma separated list back to an array of data, when your form should be submitting an array of data in the first place. shouldn't you just pass the submitted array of data to the function?

Edited January 20, 2017 by mac_gyver

[Barand]

You may find my resource booking tutorial is of some help.

[gmc1103]

Hi Mac and Barand

 

Mac, you are problably right, the problem is i'm getting an array, and then i want to use explode to separate the items and then execute the loop.

The way you say it's better but i never use that approach

detect if the insert query returned a unique index error. How??

 

 

Barand, i have read the website you gave me, you are talking about the The Booking Process, right?

 

I have managed to make it working but with a lot of if statements, and this is not the best way.

 

best regards

[mac_gyver]

Mac, you are problably right, the problem is i'm getting an array, and then i want to use explode to separate the items and then execute the loop.

 

 

the code you posted is expecting a comma delimited list (which could be a single value) in the main $id_bloco parameter. explode() breaks a delimited list into an array.

 

however, your other uses of $id_bloco, when you call ->getDisponibilidadeSala($data, $id_bloco, $id_sala) and ->getDisponibilidadeDocente(($data, $id_bloco, $id_utilizador), appear like they expect only a single value. your current program logic, of first testing if the data exists before deciding to insert new data, would need to call these two methods inside the foreach(){} loop, not call them once before the start of the loop. another problem with the current foreach(){} loop is you are repeatedly executing the prepared insert query, which doesn't use the $id value, inside the loop and also running whatever insert query the ->insertRequisicaoByLastId() call executes. i'm pretty sure this is not what you intended.

 

so, your program logic is confused about what is in $id_bloco and won't work when it is anything more than a single value.

 

you need to make a list what input data you have available, with the data type or format (is the main $id_bloco parameter just one value, a comma delimited list, or an array), then define what processing you are going to do based on the input data. this will help make sure that the code you write is using the data properly and only contains code that you need.

 

somewhat related to first defining what the input data is, your function should not get the school year ($id_ano) internally. all the data that your function needs should be supplied as call-time parameters. this will make your function general purpose and it will work for any set of data values. the current code will only work with the school year value that ->getAnoEscolar() returns.

 

as to detecting if the insert query returned a unique index error, i gave an outline of the method, that you would need to use. however, it turns out that the overall code will need to be more complicated then what you have now, since you have two insert queries, one for the requisicoes (requisitions) table, and whatever insert query the ->insertRequisicaoByLastId() call executes. you would first need to determine when and if you need to execute the insert query for the requisicoes (requisitions) table or get the id for an existing row in the requisicoes (requisitions) table (actually you may be able to use an INSERT on DUPLICATE KEY UPDATE query, where the UPDATE part actually sets up the correct id value for the ->lastInsertId() method to access), to use in the rest of the code.

Edited January 20, 2017 by mac_gyver

[gmc1103]

Hi mac

I will try to implement the way you say, any problem i will be back

 

Thanks to you and Barand

[mac_gyver]

have been looking at the code more, and here is a point that will simplify the posted code even more. inserting a row in the  requisicoes (requisitions) table, with the datas, atividade, id_ano (date, activity, school year), is an administrative operation. this must exist before a user can even try to pick a room and time for a date/activity/school year. therefore, the insert query for the requisicoes (requisitions) table doesn't belong in this code. the form that the user picks the room and time on would also submit the correct id for the row in the requisicoes (requisitions) table that he/she is trying to sign up for.