gmc1103

Hi Ok, to avoid any mistake this is the original code i'm trying to handle. <?php class DB_Functions { private $conn; function __construct() { require_once 'DB_Connect.php'; // connecting to database $db = new Db_Connect(); $this->conn = $db->connect(); } function __destruct() { } public function storeUser($name, $email, $password) { $uuid = uniqid('', true); $hash = $this->hashSSHA($password); $encrypted_password = $hash["encrypted"]; // encrypted password $salt = $hash["salt"]; // salt $stmt = $this->conn->prepare("INSERT INTO users(unique_id, name, email, encrypted_password, salt, created_at) VALUES(?, ?, ?, ?, ?, NOW())"); $stmt->bind_param("sssss", $uuid, $name, $email, $encrypted_password, $salt); $result = $stmt->execute(); $stmt->close(); if ($result) { $stmt = $this->conn->prepare("SELECT * FROM users WHERE email = ?"); $stmt->bind_param("s", $email); $stmt->execute(); $user = $stmt->get_result()->fetch_assoc(); $stmt->close(); return $user; } else { return false; } } public function getUserByEmailAndPassword($email, $password) { $stmt = $this->conn->prepare("SELECT * FROM users WHERE email = ?"); $stmt->bind_param("s", $email); if ($stmt->execute()) { $user = $stmt->get_result()->fetch_assoc(); $stmt->close(); return $user; } else { return NULL; } } } The first function "storeUser($name, $email, $password)" works since all the data is inserted into my DB The second function "getUserByEmailAndPassword($email, $password)" i'm getting the following error. Fatal error: Call to undefined method mysqli_stmt::get_result() in /home/xxxxx/public_html/mobile/include/DB_Functions.php on line 48 The line 48 is $user = $stmt->get_result()->fetch_assoc(); Yes, Barand, i'm puzzled too..and if you check the first function i have get_result() there. So...i'm lost

Hi Barand Thank's for your reply I'm having issues Call to undefined method mysqli_stmt::get_result(); But the strange is i have this method in another function and i don't have this error. This is the function and this one works public function storeUser($name, $email, $password) { $uuid = uniqid('', true); $hash = $this->hashSSHA($password); $encrypted_password = $hash["encrypted"]; // encrypted password $salt = $hash["salt"]; // salt $stmt = $this->conn->prepare("INSERT INTO users(unique_id, name, email, encrypted_password, salt, created_at) VALUES(?, ?, ?, ?, ?, NOW())"); $stmt->bind_param("sssss", $uuid, $name, $email, $encrypted_password, $salt); $result = $stmt->execute(); $stmt->close(); // check for successful store if ($result) { $stmt = $this->conn->prepare("SELECT * FROM users WHERE email = ?"); $stmt->bind_param("s", $email); $stmt->execute(); $user = $stmt->get_result()->fetch_assoc(); $stmt->close(); return $user; } else { return false; } } Strange....

Hi I'm having problems with this function, it doesn't bind_result() any variables. So my "While" doesn't work either public function getUserByEmailAndPassword($email, $password) { $stmt = $this->conn->prepare("SELECT unique_id,name,email,created_at,updated_at, encrypted_password , salt FROM users WHERE email = ?"); $stmt->bind_param("s", $email); if ($stmt->execute()) { $stmt->store_result(); $num_of_rows = $stmt->num_rows; $stmt->bind_result($aid, $aname, $aemail, $acreated_at, $aupdated_at , $aencrypted_password , $asalt); echo $aemail; //no echo while ($user=$stmt->fetch()) { echo "inside while"; // no echo $user[0] = $aid; $user[1] = $aname; $user[2] = $aemail; $user[3] = $acreated_at; $user[4] = $aupdated_at; $user[5] = $aencrypted_password; $user[6]= $asalt; $user[7] = $password; } $stmt->free_result(); $stmt->close(); return $user; } else { echo "no $stmt->execute()"; //no echo return NULL; } } Any error in this code? Thanks

Hi Ch0cu3r Thanks for helping me. I have modified this and now it is working. Thank you

Hi hansford Thank you for your apport. I have tested your code but nothing happen This is my ajax <script> $(function () { $('#myFormSubmit').click(function (e) { e.preventDefault(); var item = $('#id_itens').val(); $.ajax({ url: 'class.user.php', type: 'POST', data: {action: '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 this is my class.user.php function public function delItem(){ $id_itens = filter_input(INPUT_POST, 'item'); 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 i don't have any response....

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"