gmc1103

No, it works when i put instead of $user->delitem($delItem) i put a name file who gonna call that function, example updates.php who includes db_config where is the class error_reporting(E_ALL); ini_set('display_errors', 1); header('Content-type: application/json'); include_once 'dbconfig.php'; $id_itens = filter_input(INPUT_POST, 'item'); $delItem = filter_input(INPUT_POST, 'delItem'); if ($delItem != null) { $ret = $user->delItem($id_itens); echo json_encode($ret); }

Hi Jacques The ajax code iv'e posted works with one fucntion file, the problem is that my main class has several functions and i never did that way. Thanks

Thanks for your reply I just have to call the file ? But i have several functions in that file, how to know wich one is gonna be called? Thanks

Hi I would like to use my php class to be called from ajax but i don't get it to work propery This is my main class (part) class USER { private $db; function __construct($DB_con) { $this->db = $DB_con; } public function delItem($id_itens){ try { $sql = 'DELETE FROM `esmaior_biblioteca`.`item` WHERE `id_itens` = :me'; $stmt = $this->db->prepare($sql); $stmt->bindValue(':me', $id_itens, PDO::PARAM_INT); if (!$stmt->execute()) { print_r($stmt->errorInfo()); return array('status' => 'error', 'message' => 'Problema ao remover este item...'); } else { return array('status' => 'success', 'message' => 'O registo foi removido com sucesso...'); } } catch (PDOException $e) { echo $e->getMessage(); } } And in my main file <?php error_reporting(E_ALL | E_NOTICE); ini_set('display_errors', '1'); include_once 'dbconfig.php'; $user_role = $_SESSION['user_role']; if (!$user->is_loggedin()) { $user->redirect('index.php'); } //include 'menu_admin.php'; $user_id = $_SESSION['user_session']; ?> <script> $(function () { $('#myFormSubmit').click(function (e) { e.preventDefault(); var item = $('#id_itens').val(); var delItem = "delItem"; $.ajax({ url: '<?php echo $user->delitem($delItem); ?>', type: 'POST', data: {'delItem': delItem, 'item': item}, success: function (response) { if (response.status === 'success') { console.log("success"); $("#myModal").modal('hide'); $('#respostas .modal-title').html('Sucesso'); $('#respostas .modal-body').html('Informação: ' + response.message); $('#respostas').modal('show'); $('#respostas').on('hidden.bs.modal', function () { window.location.reload(true); }); } } }); }); }); </script> And i can't get this working properly, do i miss something? Thanks

Hi Barand From the beginning posts "your separate date and time columns should be one DATETIME data type column." So i have datetime in my database. You can't say that i didn't follow yours suggestions.

Hi I'm having another problem with the query So, the begining is SELECT i.`quantidade` - IFNULL(SUM(r.`quant`),0) AS total FROM `item` AS i LEFT JOIN `requisicoes` AS r ON (r.`id_item` = i.`id_itens`) AND r.`startDate` = "2015-11-16 12:00" AND r.`endDate` = "2015-11-17 12:00" WHERE i.`id_itens` = 8 My total books are 30 and it gives me how many books i have to borrow at this time and this hour (18) but if i change to "2015-11-16 13:00" it says i have 30, so this is wrong since i have already borrowed 12 from "2015-11-16 12:00" til 2015-11-17 12:00, the correct answer should be 18 I have tried this query and it don't seems to work but i would like your opinion if it is correct or not SELECT i.`quantidade` - IFNULL(SUM(r.`quant`),0) AS total FROM `item` AS i LEFT JOIN `requisicoes` AS r ON (r.`id_item` = i.`id_itens`) AND (((r.`startDate` = "2015-11-16 15:00") AND (r.`endDate` = "2015-11-17 12:00")) OR (r.`startDate` <= "2015-11-16 15:00" AND r.`endDate` >= "2015-11-17 12:00")) WHERE i.`id_itens` = 8

I'm so stup.... :happy-04: , i was thinking in java. How to thing about that approach??, first getting the books available, then insert Thank you for your help Barand.

Now it is working in sqlLyog, i have the number expected when i don't have any book borrow. Now, with PDO how i can get the result, i have tried this query but without success. public function checkItemTotal($reqItem, $startDate,$endDate){ try { $sql = 'SELECT i.`quantidade` - IFNULL(SUM(r.`quant`),0) AS total FROM `item` AS i LEFT JOIN `requisicoes` AS r ON (r.`id_item` = i.`id_itens`) AND r.`startDate` = :startDate AND r.`endDate` = :endDate WHERE i.`id_itens` = :id_item'; $stmt = $this->db->prepare($sql); $stmt->bindparam(':id_item', $reqItem, PDO::PARAM_INT); $stmt->bindparam(":startDate", $startDate, PDO::PARAM_STR); $stmt->bindparam(":endDate", $endDate, PDO::PARAM_STR); if (!$stmt->execute()) { print_r($stmt->errorInfo()); return array('status' => 'error', 'message' => 'Oppsss....Problema ao conseguir total disponível.'); } else { $total = $stmt->setFetchMode(PDO::FETCH_COLUMN, 0); return array('status' => 'success', 'message' => 'Valor total de livros disponíveis é de: ' + $total); } } catch (PDOException $e) { echo $e->getMessage(); } } So with this approach i want to know how many books i can borrow, so the $total variable seems to be empty each time. This is the messsage i get {"status":"success","message":1}

Hi Barand I still have null with the query you gave me. http://postimg.org/image/edgu3ttdj/ I think you were convinced that the result was 30 but it still null

I'm almost there, i have changed the all table like you guys said. But now i'm having problem with null values, my table doesn't any records and i use this query SELECT SUM(i.`quantidade`-r.`quant`) AS total FROM `requisicoes` AS r INNER JOIN `item` AS i ON (r.`id_item` = i.`id_itens`) WHERE r.`id_item` = 8 AND r.`startDate` = "2015-11-22 11:30" AND r.`endDate` = "2015-11-23 11:20" and the total is NULL, i was expected 30 because is the full quantity i have...any help?

Hi Mac Nice explanation, i have convinced the school to use datetime explaining that way is better. So what i want to implement is the following, after getting the information about what they need. The library has several subjects books (ex: Maths grade 3, quantity 50) 1 - Usually the teacher ask a student to get x of that book 2- The student goes to the library and takes 25 at x day at x hours 3- The student return those books in same day at x hours Now, there is more than one math class at this hour, so another student goes to the library and want to borrow 30, he can't since there is only 25 left. When the books are returned i get 50 again because is quantity. I don't know if my explanation satisfied your doubts... So my table to the books are CREATE TABLE `item` ( `id_itens` int(11) NOT NULL AUTO_INCREMENT, `nome` varchar(350) COLLATE utf8_bin NOT NULL, `descricao` varchar(500) COLLATE utf8_bin NOT NULL, `data` date NOT NULL, `quantidade` int(5) NOT NULL, `id_tipo` int(11) DEFAULT NULL, `id_estado` int(11) DEFAULT NULL, PRIMARY KEY (`id_itens`), KEY `id_tipo` (`id_tipo`), KEY `id_estado` (`id_estado`), CONSTRAINT `item_ibfk_1` FOREIGN KEY (`id_tipo`) REFERENCES `tipo_item` (`id_tipo`), CONSTRAINT `item_ibfk_2` FOREIGN KEY (`id_estado`) REFERENCES `item_estado` (`id_estado`) ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COLLATE=utf8_bin and for borrow are Create Table CREATE TABLE `requisicoes` ( `id_req` int(11) NOT NULL AUTO_INCREMENT, `id_item` int(11) NOT NULL, `id_user` int(11) NOT NULL, `startDate` datetime NOT NULL, `endDate` datetime NOT NULL, `quant` int(3) NOT NULL, `created` date NOT NULL, PRIMARY KEY (`id_req`), KEY `id_item` (`id_item`), KEY `id_user` (`id_user`), CONSTRAINT `requisicoes_ibfk_1` FOREIGN KEY (`id_item`) REFERENCES `item` (`id_itens`), CONSTRAINT `requisicoes_ibfk_2` FOREIGN KEY (`id_user`) REFERENCES `users` (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8

Ok, nice point But when a school book is borrowed, the admin must fill this form <form id="eventForm" method="post"> <div class="form-group"> <input type="hidden" name="id" id="id"> </div> <div class="form-group"> <label for="recipient-name" class="control-label"></label> <input class="form-control" name="nome" id="nome" type="text" disabled="true"> </div> <div class="form-group"> <div class="input-group input-append date" id="startDatePicker"> <input type="text" class="form-control" id="startDate" name="startDate" placeholder="Data de levantamento"/> <span class="input-group-addon add-on"><span class="glyphicon glyphicon-calendar"></span></span> </div> </div> <div class="form-group"> <div class="input-group input-append timepicker-orient-left" id="startTimePicker"> <input type="text" class="form-control" id="startTime" name="startTime" placeholder="Hora - Formato HH:MM" /> <span class="input-group-addon add-on"><span class="glyphicon glyphicon-time"></span></span> </div> </div> <div class="form-group"> <div class="input-group input-append date" id="endDatePicker"> <input type="text" class="form-control" id="endDate" name="endDate" placeholder="Data de entrega"/> <span class="input-group-addon add-on"><span class="glyphicon glyphicon-calendar"></span></span> </div> </div> <div class="form-group"> <div class="input-group input-append timepicker-orient-left" id="timePicker"> <input type="text" class="form-control" id="endTime" name="endTime" placeholder="Hora - Formato HH:MM" /> <span class="input-group-addon add-on"><span class="glyphicon glyphicon-time"></span></span> </div> </div> <div class="form-group"> <div class="input-group input-append timepicker-orient-left" id="timePicker"> <input class="form-control" name="quantidade" id="quantidade" type="text" required="true" placeholder="Número de exemplares a levantar "> <span class="input-group-addon add-on"><span class="glyphicon glyphicon-check"></span></span> </div> </div> </form> So, no input can be null, i have always a endDate, if the user don't return the book in the end date period, the admin receives in dashboard an alert.

Hi Barand If someone borrow 2 he must return 2. If he returns only one the borrow must be extend changing the value 2 to 1. That's why someone (admin) is in charge of that process, it will update the information of that loan. Regards

Hi mac Thanks again for your contribution. Let me explain what they want. This is not an usaul library since the only thing they want is to borrow school books so every time students go there to borrow a book(s) they want the book_id, the quantity, the date of borrow, the time and the return date, quantity and time. So i was thinking like you suggest me to do a datetime but they don't want so this complicating my work This is an example of what i have (testing of course) in my db id_req id_item id_user startDate startTime endDate endTime quant created ------ ------- ------- ---------- --------- ---------- -------- ------ ------------ 9 10 1 2015-11-19 10:00:00 2015-11-19 11:50:00 23 2015-11-06 10 10 1 2015-11-19 10:00:00 2015-11-19 11:50:00 23 2015-11-06 As you can see i have the same book_id, startDate, time and quantity, since i have only 30 quantity this insert shouldn't have been done. So i'm looking to make a query to check if this book_id is already borrow and the quantity available to borrow. Hope i have explaining me better. Thanks again

Hi mac I have already tried to use that query in my database but i don't have the results i was expecting. The use of startTime and endTime is complicating my form but i need them, i must use that because they (company) want like this..

Hi mac So i should put datetime instead of one date and one time. Ok i will change it. And about the query after making those change?

Hi I need some help with sql query i need to make to check if in a date and time i how many books i can loan This is an example of my db loan id_req id_item id_user startDate startTime endDate endTime quant created ------ ------- ------- ---------- --------- ---------- -------- ------ ------------ 1 8 1 2015-11-12 12:00:00 2015-11-13 13:00:00 11 2015-11-06 So for instance (id_item has 30 books and only 11 are borrowed, so in this period (startDate, startTime, endDate, endTime) i can borrow 19) I want to have how many books i can borrow I have made like this but it is not enought since i have startTime and EndTime values SELECT SUM(quant) FROM `requisicoes` WHERE id_item = 8 AND `startDate` = "2015-11-12" AND `endDate` = "2015-11-13" GROUP BY id_item any help?

Yes, it is an object, i have resolved this issue using var jobstr=JSON.stringify(row); var semAspas=JSON.parse(jobstr); var itemN = semAspas.id_itens; // id_itens var nome = semAspas.nome; // name Now i receive just the number and not with double quotes. Thank all for pointing me to the rigth direction Bes regards

Hi mac_gyver The double quote comes from this part of code 'click .remove': function (e, value, row, index) { $('#myModal').modal('show').find('.modal-body #nome').val(JSON.stringify(row.nome)); $('#myModal').modal('show').find('.modal-body #id_itens').val(JSON.stringify(row.id_itens)); } And i have checked and i can see double quotes in input text area of modal Using var_dump the $id_itens retrives ""3"" What's wrong, it is the JSON?? Thanks

id_itens are defined in database int But i receive like this "7", that's why i put PDO::PARAM_STR"

Hi Barand i have removed this line print_r($this->db->query($sql)); and i don't have that error again but i have a strange situation From my sqlyog i use this DELETE FROM `esmaior_biblioteca`.`item` WHERE `id_itens` = "6"; and it works in the code i put a line to check if i receive the var and i have result (11) print_r($id_itens); 4a "11"{"status":"success","message":"O registo foi removido com sucesso..."} 0 And it say removed but when i check it still in my database..

Hi I'm having an error in the following code and i don't get why public function delItem($id_itens){ try { $sql = 'DELETE FROM `item` WHERE `id_itens`=:me'; $stmt = $this->db->prepare($sql); $stmt->bindValue(':me', $id_itens, PDO::PARAM_STR); if (!$stmt->execute()) { print_r($stmt->errorInfo()); return array('status' => 'error', 'message' => 'Problema ao remover este item...'); } else { print_r($this->db->query($sql)); return array('status' => 'success', 'message' => 'O registo foi removido com sucesso...'); } } catch (PDOException $e) { echo $e->getMessage(); } } and the error is SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':me' at line 1

Hi Barand Thank you Made a few change now no error but no data too $query = 'SELECT t1.id_plano , t2.cargo , t1.atividade , t1.descricao , t1.obj , t1.periodo , t1.previsao , t1.data_prevista , t1.orcamento , t1.destinatarios, t1.observacoes FROM new_pae AS t1 INNER JOIN pae_cargo AS t2 ON (t1.id_cargo=t2.idcargo) where t1.periodo = :me ORDER BY data_prevista ASC'; $stmt = $DB_con->prepare($query); if (!$stmt) { echo "\nPDO::errorInfo():\n"; print_r($DB_con->errorInfo()); } $stmt->bindparam(":me", $search,PDO::PARAM_STR); $stmt->execute(); $ixx = 0; while($result = $stmt->fetchAll(PDO::FETCH_ASSOC)){ $ixx = $ixx+1; $data_chegada[] = array_merge($result, array('num'=>$ixx)); } Since this is a pdf, how can i check if the $data_chegada array is not empty?

I used pdo like suggested but i'm stuck again try { $DB_con = new PDO("mysql:host={$DB_host};dbname={$DB_name}",$DB_user,$DB_pass, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")); $DB_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch(PDOException $e) { echo $e->getMessage(); } $query = $DB_con->query("SELECT t1.id_plano , t2.cargo , t1.atividade , t1.descricao , t1.obj , t1.periodo , t1.previsao , t1.data_prevista , t1.orcamento , t1.destinatarios, t1.observacoes FROM new_pae AS t1 INNER JOIN pae_cargo AS t2 ON (t1.id_cargo=t2.idcargo) t1.periodo =:me ORDER BY data_prevista ASC"); $query->$DB_con->prepare($sql); if (!$query) { echo "\nPDO::errorInfo():\n"; print_r($DB_con->errorInfo()); } $query->bindparam(":me", $search,PDO::PARAM_STR); $fetch = $query->fetchAll(); $query->bind_result($id_plano, $cargo, $atividade, $descricao, $obj, $periodo, $previsao, $data, $orcamento, $destinarios, $observacoes); $ixx = 0; foreach ($fetch as $plano) { $ixx = $ixx+1; $data_chegada[] = array_merge($plano, array('num'=>$ixx)); } Now the error is Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1.periodo =:me ORDER BY data_prevista ASC' at line 2' So my sql syntax have error but i can't see anyone

Hi mac_gyver Thank you, so if i undertstood what you told I used stmt->bind_param('s', $search); $stmt->execute(); $result = $stmt->get_result(); $stmt->bind_result($id_plano, $cargo, $atividade, $descricao, $obj, $periodo, $previsao, $data, $orcamento, $destinarios, $observacoes); while ($row = $result->fetch_array(MYSQLI_NUM)) { $ixx = 0; foreach ($row as $r) { $ixx = $ixx+1; $data_chegada[] = array_merge($r, array('num'=>$ixx)); } } and now i have Fatal error: Call to undefined method mysqli_stmt::get_result()