library query help

[gmc1103]

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?

[mac_gyver]

the example data record you have shown should have a negative quantity,  -11, since those books are checked out/not available. the SUM(quant) in the query should take the original record with 30 books, plus the -11 books checked out by that user_id, giving 19 available books.

 

your separate date and time columns should be one DATETIME data type column.

[gmc1103]

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?

[Barand]

Somewhere you need a table which would tell the query that 30 books were available for item_id 8 if you want to know how many you can loan

[mac_gyver]

the OP should have that already in this table, as this is pretty much just a continuation of another thread - http://forums.phpfreaks.com/topic/298896-pdo-with-array/

[gmc1103]

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.. 

[mac_gyver]

doing an exact, equal, comparison with dates doesn't make any sense. would the query you tried match any other row(s) in your database table for that id_item? it sounds like you don't know why you added the start and end dates/times, but whatever reason you have, you would need to make use of <, <=, >, or >= comparisons, not just = comparisons.

[gmc1103]

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

[Barand]

Is "startDate" the date it was borrowed and "endDate" the date of return?

 

What happens if, say, they borrow 2 but only return 1? You have only one quantity column.

 

From what you have shown us so far I am not convinced your data structure can support your requirements.

[gmc1103]

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

[Barand]

Was I correct in my dates assumption. So if they are not yet returned then the endDate is NULL?

[gmc1103]

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.

[Barand]

 

if the user don't return the book in the end date period, the admin receives in dashboard an alert.

 

How does that work? There doesn't appear to be anything to say books have been returned, or not

[mac_gyver]

you are actually doing two things - 1) recording who, what book_id/title they have borrowed/returned, how many they have borrowed/returned, when they borrowed/returned them (not just when they promised to return them.), and 2) being able to track actual inventory of books.

 

before you continue to try and write any code or queries, or do things that other people tell you, which may not take into account all the requirements that only you know about, you need to sit down and do some 'thinking through the problem' and figure out what data you need to record and how you will query to get the results you need.

 

the following is my thinking through this problem, and assumes that you are not going to serially number each book (which you stated in the previous thread that you weren't doing), which would make this like a discrete resource reservation system, i.e. a room booking system. without a serial number for each book, this is like a shopping cart/order system, except that you expect all the items to be returned (or accounted for if not returned.)

 

so, someone visits the library, picks out some number of one or more different books, and goes to the check out desk. this should create a record for this transaction (order) that assigns a unique id, that will be used to refer to everything that's part of this transaction, who the person is, and any other unique information about each transaction.

 

then, for each book_id/title that's part of a transaction, you would enterer a record in a second (order details) table that assigns a unique id (for reference purposes), the transaction_id, the book_id, quantity (i would store a negative quantity when a book is borrowed, a positive quantity when a book is returned, so that you can directly SUM() quantities to find inventory levels), date (or datetime if you are actually using the time part) that the quantity of book(s) was borrowed/returned, a scheduled/promised return by date (or datetime if you are actually using the time part) - this could be different for each book_id/title and could be extended by any amount upon request, and any other information you need for each book_id/title that's part of a transaction, such as status/memo fields.

 

when book(s) are returned by a person, you would enter more records in this details table, using the same transaction id, book_id, a positive quantity, so that they offset the number of books that were borrowed, the date (or datatime) they were returned (the scheduled/promised return date is not used in this case.)

 

the only use of a date (or datetime) in a query would be to find out if any books are over due or to determine when books should be available. to find out what books a person (still) has checked out, you would just query for the data for his transaction(s), group by the book_id and sum up the +/- quantities. to find out the available quantity of any book(s), you would write a query that groups by the book_id, regardless of the transaction, and sums up all the +/- quantiles for each book_id.

 

to record initial books on hand, books added, books lost/damaged/sold, books found by others, ... you would enter records in this same details table, with + or - quantity. the status/memo field(s) would be used to mark the record with this type of extra information about a transaction.

 

if you have any requirements that are not covered by this suggested method, you need to sit down and think through what data you need to store or calculate and how you will query to get the results you need.

[gmc1103]

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

[gmc1103]

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?

Edited November 11, 2015 by gmc1103

[Barand]

One or two things wrong with that query.

 

1. Your calculation - If you had two loans, each of 10 books, then you would expect a result of 10 remaining. Yours would give (30-10) + (30-10) = 40. You need

i.`quantidade` - SUM(r.`quant`) AS total

2. Your join - INNER JOIN retrieves records only when there is a matching record in both tables. If one table has no record then no results. You need

FROM `item` AS i LEFT JOIN  `requisicoes` AS r ON (r.`id_item` = i.`id_itens`)

so that you select the item record even if there are no req records.

 

Having changed to a LEFT join you need to change the WHERE clause so it doesn't  contain conditions on the req data. Those need to go in the join condition.

 

So the query now becomes

SELECT i.`quantidade` - SUM(r.`quant`) AS total 
FROM `item` AS i 
    LEFT JOIN  `requisicoes` AS r ON (r.`id_item` = i.`id_itens`)
        AND  r.`startDate` = "2015-11-22 11:30" AND r.`endDate` = "2015-11-23 11:20
WHERE i.`id_itens` = 8

[gmc1103]

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

[Barand]

Sorry, arithmetic on a NULL value produces null result

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-22 11:30" AND r.`endDate` = "2015-11-23 11:20
WHERE i.`id_itens` = 8

[gmc1103]

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}

Edited November 11, 2015 by gmc1103

[Barand]

PHP concatenation operator is "." and not "+"

[gmc1103]

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.

[gmc1103]

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

Edited November 12, 2015 by gmc1103

[Barand]

We have told you how you should be tackling the problem and you have ignored those suggestions and decided to take this approach. You can go on forever trying to adjust the query to work for each particular situation as it arises.

 

The correct approach is a proper data structure and queries that work for all eventualities.

[gmc1103]

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.