Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,602

  • Joined

  • Last visited

  • Days Won

    830

Everything posted by Barand

  1. Barand
    Not tested. Backup data before trying. Assumes the dates are stored correctly in yyyy-mm-dd format (other formats will not work for range comparisons) DELETE booking_slots, booking_reservation FROM booking_slots INNER JOIN booking_reservation USING (slot_id) WHERE booking_slots.slot_date BETWEEN '2012-01-01' AND '2013-01-01'
  2. Barand
    Is it possible to do what? You haven't said what it is you you are trying to achieve, other than, whatever it is, you want to use a single query to do it.
  3. Barand
    The relevant bit is
  4. Barand
    If you allocate 3x as many columns to your HTML table then you can arrange them by outputting empty cells. In this example there are 3 rooms, and the room number sets the left, centre or right position mysql> SELECT * FROM timetable; +--------------+-----------+------+----------+----------+ | timetable_id | subject | room | timefrom | timeto | +--------------+-----------+------+----------+----------+ | 1 | English | 1 | 09:00:00 | 10:00:00 | | 2 | Maths | 2 | 10:00:00 | 11:00:00 | | 3 | Biology | 3 | 11:00:00 | 12:00:00 | | 4 | Geography | 2 | 13:00:00 | 14:00:00 | | 5 | History | 3 | 14:00:00 | 15:00:00 | +--------------+-----------+------+----------+----------+ the sample code $mysqli = new mysqli(HOST,USERNAME,PASSWORD,'test'); $sql = "SELECT subject , room , timefrom , timeto FROM timetable ORDER BY timefrom"; $res = $mysqli->query($sql); $ttdata = ''; while (list($sub,$room,$from,$to) = $res->fetch_row()) { $from = date('g:i', strtotime($from)); $to = date('g:i', strtotime($to)); $ttdata .= '<tr>'; switch ($room) { case 1: $ttdata .= "<td>$sub</td><td>$from</td><td>$to</td><td colspan='6' class='empty'></td></tr>"; break; case 2: $ttdata .= "<td colspan='3' class='empty'></td><td>$sub</td><td>$from</td><td>$to</td><td colspan='3' class='empty'></td></tr>"; break; case 3: $ttdata .= "<td colspan='6' class='empty'></td><td>$sub</td><td>$from</td><td>$to</td></tr>"; break; } } ?> <html> <head> <title>Sample</title> <style type="text/css"> table {border-collapse: collapse;} th {background-color: blue; color: white;} td {padding: 2px 4px;} td.empty {background-color: #eee;} </style> </head> <body> <table border="1" cellspacing="3"> <tr><th colspan='3'>Room 1</th><th colspan='3'>Room 2</th><th colspan='3'>Room 3</th></tr> <?=$ttdata?> </table> </body> </html> Output
  5. Barand
    echo $ban; post the results from that so we can see if the query looks right
  6. Barand
    Suppose we have mysql> select * from appointment; +---------------+----------+-----------+------------+-----------+----------+ | idappointment | idclient | idstylist | app_date | app_start | app_end | +---------------+----------+-----------+------------+-----------+----------+ | 1 | 1 | 1 | 2014-12-18 | 10:00:00 | 10:45:00 | | 3 | 2 | 1 | 2014-12-18 | 12:00:00 | 12:45:00 | | 4 | 3 | 2 | 2014-12-18 | 10:00:00 | 10:45:00 | | 5 | 4 | 1 | 2014-12-18 | 13:00:00 | 13:45:00 | | 6 | 5 | 2 | 2014-12-18 | 16:00:00 | 16:45:00 | +---------------+----------+-----------+------------+-----------+----------+ and client wants to book 2014-12-18 from 12:45 to 13:30 SELECT s.idstylist , s.name , COUNT(idappointment) as clashes FROM stylist s LEFT JOIN appointment a ON s.idstylist = a.idstylist AND app_date = '2014-12-18' AND app_start < '13:30' AND app_end > '12:45' GROUP BY idstylist; which gives this +-----------+------+---------+ | idstylist | name | clashes | +-----------+------+---------+ | 1 | John | 1 | | 2 | Jane | 0 | +-----------+------+---------+ showing that Jane is free for that booking
  7. Barand
    Which field (A) provides the values for the column headings? Which field (B) provides the values for the row headings? Which field © provides the values for the totals? You would need something like SELECT A, B, SUM(C) as C FROM ... GROUP BY A, B then a while() loop to process the results. Output a row of totals for B when A changes
  8. Barand
    This may help. New booking is from start to end. Existing bookings are from s to e new booking | | start end ------------------------------------------------------------------- | | s--------e | clashing | s----e | | s-----------e e > start s---------------e && | | s < end | | -------------------------------------------------------------------- | | s------------e | | OK | | s----------e OK | |
  9. Barand
    If you have a new booking (time_start to time_end) then any other bookings where (bookingend > time_start) && (bookingstart < time_end) will clash with the new one
  10. Barand
    That's because you fetch a row at the top of your script then ignore it. Remove $row_consulta = mysql_fetch_assoc($consulta);
  11. Barand
    If you are working with ip6 then you need INET6_ATON() . This function requires MySQL 5.6+
  12. Barand
    1 ) fetch_assoc() is a mysqli_result method, so you need to use $row = $banres->fetch_assoc(); 2 ) Also, to access $row['active'] you need to select 'active' in your query. You only select the id. 3 ) Use a prepared query or escape the GET value - don't use it directly in your query
  13. Barand
    How about SELECT a.yr , a.tot as yr_tot , b.tot as prev_yr_tot , IF(a.tot>=b.tot, 'more','') as more , IF(a.tot< b.tot, 'less','') as less FROM ( SELECT YEAR(vrijeme) as yr , SUM(nabavna_cijena) as tot FROM kalkulacija_stavke GROUP BY YEAR(vrijeme) ) as a LEFT JOIN ( SELECT YEAR(vrijeme) as yr , SUM(nabavna_cijena) as tot FROM kalkulacija_stavke GROUP BY YEAR(vrijeme) ) as b ON a.yr = b.yr + 1;
  14. Barand
    Do you mean SELECT YEAR(vrijeme), SUM(nabavna_cijena) as total FROM kalkulacija_stavke WHERE YEAR(vrijeme) > YEAR(CURDATE()) -2 GROUP BY YEAR(vrijeme)
  15. Barand
    mysql> SELECT INET_ATON('199.119.180.52') as ip_num; +------------+ | ip_num | +------------+ | 3346510900 | +------------+ Therefore you can $ip_add = '199.119.180.52'; $sql = "SELECT * FROM ip2location_db11 WHERE INET_ATON('$ip_add') BETWEEN ip_from AND ip_to";
  16. Barand
    You may find it useful to look at MySQLs INET_* and INET6_* functions http://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_inet-aton
  17. Barand
    Perhaps you should read the replies there.
  18. Barand
    With the exception of some expected "deprecated mysql" warnings, your code ran fine when I tried it. Of course I had to change the database connection credentials.
  19. Barand
    What determines that a particular client should move from position 5 to position 3? If it is a particular attribute that could be stored or calculated then you could use that in an ORDER BY clause and save yourself the effort of moving them manually.
  20. Barand
    Following on from what QuickOldCar said, here's some code I put together many months ago. It should get you started. dbAdmin.php dbAdmin_ajax_c.php dbAdmin_ajax_p.php dbAdmin_ajax_t.php
  21. Barand
    Why are you naming the options?
  22. Barand
    The point of using a timestamp column is so you do not have to enter a datetime value - it is automatically entered. For example CREATE TABLE student ( student_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30), created TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); INSERT INTO student (name) VALUES ('Barand'); Then mysql> SELECT * FROM student; +------------+--------+---------------------+ | student_id | name | created | +------------+--------+---------------------+ | 1 | Barand | 2014-12-12 14:54:09 | +------------+--------+---------------------+
  23. Barand
    A couple of functions you should look at to get you started are file() explode()
  24. Barand
    Note that when an image input is clicked, the x,y coordinates of the click in the image are sent. So if you have <form> <input type='image' name='imageButton' src='myimage.jpg'> </form> then this is sent back to your page Array ( [imageButton_x] => 4 [imageButton_y] => 11 )
  25. Barand
    My mistake, the highest value is 2147483647 (2^31 - 1)
×
×
  • 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.