Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,612

  • Joined

  • Last visited

  • Days Won

    834

 Content Type 

Everything posted by Barand

  1. Barand
    PHP concatenation operator is "." and not "+"
  2. 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
  3. Barand
    The simple answer is "No". If you want to select all, don't specify the IN() condition
  4. 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
  5. Barand
    you could use something like this SELECT a.user , group_concat(b.user) as guests , count(b.guestof) as total FROM userlist a LEFT JOIN userlist b ON b.guestof = a.user GROUP BY a.user ; +-------+-----------+-------+ | user | guests | total | +-------+-----------+-------+ | Bill | Jason | 1 | | Frank | NULL | 0 | | Gwen | NULL | 0 | | Jack | Gwen,Matt | 2 | | Jason | Jill,Jack | 2 | | Jill | Frank,Stu | 2 | | Matt | NULL | 0 | | Stu | NULL | 0 | +-------+-----------+-------+
  6. Barand
    In addition, your SQL query syntax is wrong. String values need to be in single quotes INSERT INTO student_register_test10 (name, address, topic1) values ('$name', '$address', '$topic1' )
  7. Barand
    How does that work? There doesn't appear to be anything to say books have been returned, or not
  8. Barand
    Was I correct in my dates assumption. So if they are not yet returned then the endDate is NULL?
  9. 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.
  10. Barand
    Your understanding is correct. However, that refers to the MySql TIMESTAMP type which has the same format as DATETIME type (but a smaller date range). In my comment above I stated "unix timestamp" which is an INT (eg 1447148711).
  11. Barand
    Do it all in the query SELECT ..., COUNT(*) as total FROM ... WHERE status='Done' GROUP BY student_email
  12. Barand
    I'd make use of data attributes to get the data in one place when clicked. Here's an example using a couple of your rows <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title>Primes & Factors</title> <script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script> <script type="text/javascript"> $().ready(function() { $(".old-price, .orange-price").click(function() { var vol = $(this).data('vol'); var price = $(this).html(); $("#vol").val(vol); $("#price").val(price); }) }) </script> <style type="text/css"> .old-price, .orange-price { cursor: pointer; } caption { font-size: 14pt; font-weight: 600; } </style> </head> <body> <table id="myTable" class="demo"> <caption> Sisteme de cosuri fum TONA <span class="orange-price">- 15% REDUCERE</span> </caption> <thead> <tr> <th> Inaltime / Dimensiune </th> <th>Ø 14</th> <th>Ø 16</th> <th>Ø 18</th> <th>Ø 20</th> <th>Ø 25</th> </tr> </thead> <tbody> <tr> <td>5 ml <br> </td> <td><span data-vol='5' class="old-price">583 EUR</span> <br> <span data-vol='5' class="orange-price">496 EUR</span> </td> <td><span data-vol='5' class="old-price">630 EUR</span> <br> <span data-vol='5' class="orange-price">536 EUR</span> </td> <td><span data-vol='5' class="old-price">661 EUR</span> <br> <span data-vol='5' class="orange-price">562 EUR</span> </td> <td><span data-vol='5' class="old-price">704 EUR</span> <br> <span data-vol='5' class="orange-price">598 EUR</span> </td> <td><span data-vol='5' class="old-price">1125 EUR</span> <br> <span data-vol='5' class="orange-price">956 EUR</span> </td> </tr> <tr> <td>5.5 ml</td> <td><span data-vol='5.5' class="old-price">608 EUR</span> <br> <span data-vol='5.5' class="orange-price">517 EUR</span> </td> <td><span data-vol='5.5' class="old-price">659 EUR</span> <br> <span data-vol='5.5' class="orange-price">560 EUR</span> </td> <td><span data-vol='5.5' class="old-price">692 EUR</span> <br> <span data-vol='5.5' class="orange-price">588 EUR</span> </td> <td><span data-vol='5.5' class="old-price">737 EUR</span> <br> <span data-vol='5.5' class="orange-price">626 EUR</span> </td> <td><span data-vol='5.5' class="old-price">1187 EUR</span> <br> <span data-vol='5.5' class="orange-price">1009 EUR</span> </td> </tr> </tbody> </table> <br> Chosen volume <input type="text" name="vol" id="vol" size="5"> Chosen price <input type="text" name="price" id="price" size="8"> </body> </html>
  13. Barand
    I'm using workbench (6.3.3) and the only time it threw an error for me was when there really was an error. I just now entered another ENUM column and it let me tab quite happily to the next column name. Only had to "apply" after all changes were done.
  14. Barand
    Yes. Notepad is using non-standard single quotes. This should work ENUM('Ford','GMC','Chevrolet','Chrysler','Dodge','Buick','Toyota','Volvo','International','Kenworth','Peterbilt','Mack','Freightliner','Isuzu')
  15. Barand
    IMHO a unix timestamp is not a good option. If when you need to browse your data in the table it makes it almost impossible to know what the actual date/time value is and needs conversion to DATE/TIME type before you can use the majority of datetime SQL functions.
  16. Barand
    You define it only when $_POST['submit'] is set - ie when data has been posted to the form. You try to use whether data is posted or not
  17. Barand
    If you have "syntax error, unexpected xxx" then the error is just before the xxx. That is what makes it "unexpected"
  18. Barand
    Correction: I forgot you had separate date and time fields. The ORDER BY clauses in my query need to include the date too.
  19. Barand
    As Benanemen said, put the makes in a separate table, each with a unique id and store the appropriate id in the car table carMake +--------+-------------------+ | makeId | make | +--------+-------------------+ | 1 | Ford | | 2 | GMC | | 3 | Chevorlet | | 4 | Chrysler | | 5 | Dodge | | 6 | Buick | | 7 | Toyota | | 8 | Volvo | | 9 | International | | 10 | Kenworth | | 11 | Peterbilt | | 12 | Mack | | 13 | Freightliner | | 14 | Isuzu | +--------+-------------------+ | | +-----------------+ | car | +-------+--------+----------+------+--------+ | carId | makeId | plate | year | colour | +-------+--------+----------+------+--------+ | 1 | 2 | AB10XYZ | 2010 | Red | | 2 | 14 | M55DEF | 2005 | Blue | | 3 | 8 | DT15AAA | 2015 | Silver | +-------+--------+----------+------+--------+ To retrieve the make use a query with a JOIN between the two tables, matching on the makeId.
  20. Barand
    I that case you have a problem as views don't like subqueries or @variables. Basically, don't store derived values, run the query when you want the cumulatives
  21. Barand
    You would join the table to itself on data_id/parent_id Example SELECT p.item_id as parent_item , p.alias as parent_alias , p.type as parent_type , c.alias as child_alias , c.type as child_type , c.name as child_name FROM item p LEFT JOIN item c ON p.data_id = c.parent_id WHERE p.parent_id = 0 ORDER BY parent_item; When you process the results, check for a change in the parent_item value. When it changes, close off the previous table and start a new one for the new item. Example $db = new mysqli(HOST, USERNAME, PASSWORD, 'darius'); $sql = "SELECT p.item_id as parent_item , p.alias as parent_alias , p.type as parent_type , c.alias as child_alias , c.type as child_type , c.name as child_name FROM item p LEFT JOIN item c ON p.data_id = c.parent_id WHERE p.parent_id = 0 ORDER BY parent_item"; $currItem = 0; $res = $db->query($sql); while (list($parent_item,$parent_alias,$parent_type,$child_alias,$child_type,$child_name) = $res->fetch_row()) { if ($parent_item != $currItem) { // has value changed? if ($currItem) { echo "</table>\n"; // close table (if we have one) } echo "<table border='1'><caption>$parent_alias</caption>\n"; $currItem = $parent_item; // reset current item value } echo "<tr><td>$child_alias</td><td>$child_type</td><td>$child_name</td></tr>\n"; } echo "</table>\n"; // close final table
  22. Barand
    You would have a single Emp table but connect to it twice in the same query giving each instance a different table alias. In which case you would group by form and select SUM(scoring)
  23. Barand
    I added "match_id" from your mecze table to your current "cumulative_profit" view then ran my query against that view instead of the mecze table. You have already done the hard work to calculate the profit in that view. SELECT country ,Competition ,match_date ,ko_time ,home ,away ,Result ,Profit ,cum_profit FROM cumulative_profit INNER JOIN ( SELECT match_id , @cum:=@cum+profit as cum_profit FROM cumulative_profit JOIN (SELECT @cum:=0) init ORDER BY ko_time, match_id ) cum USING (match_id) ORDER BY ko_time DESC, match_id DESC; results +---------+----------------+------------+---------+-------------------+--------------+--------+--------+------------+ | country | competition | match_date | ko_time | home | away | Result | Profit | cum_profit | +---------+----------------+------------+---------+-------------------+--------------+--------+--------+------------+ | England | Premier League | 2015-11-07 | 15:00 | Leicester | Watford | W | 8.70 | -20.28 | | England | Premier League | 2015-11-07 | 15:00 | West Ham | Everton | L | -10 | -28.98 | | England | Championship | 2015-11-02 | 15:00 | Bolton | BristolCity | L | -10 | -18.98 | | England | Championship | 2015-11-05 | 15:00 | Blackburn | Brentford | W | 8.82 | -8.98 | | England | Premier League | 2015-11-07 | 15:00 | Manchester United | West Brom | L | -10 | -17.8 | | England | Premier League | 2015-11-07 | 15:00 | Norwich | Swansea | L | -10 | -7.8 | | England | Premier League | 2015-11-07 | 15:00 | Sunderland | Southampton | L | -10 | 2.2 | | England | Championship | 2015-11-06 | 12:30 | Huddersfield | Leeds | W | 12.20 | 12.2 | +---------+----------------+------------+---------+-------------------+--------------+--------+--------+------------+
  24. Barand
    What if you select "childrenID" instead of "signupChildrenID"? Or SELECT * FROM activities LEFT JOIN signupActivity ON activityID = SignupActivityID AND signupActivitychildID = 33 LEFT JOIN children ON signupActivitychildID = childrenID WHERE activitySection = 3
  25. Barand
    I suspect the real question is "How can I bombard sites with spam?"
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.