Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    22
    Rep Power
    0

    Multiple inserts by date


    I have a database to control classes in a school with a Php admin page . I would like to be able to insert multiple classes at the same time.

    For example 1 class of the same type for every day of term that is to say 1 class for every day between 2 dates. Or 1 class for every (for example) Tuesday

    Currently to insert 1 class on one day I have the following code

    $query = "INSERT INTO classes (date,time, subject, teacher)
    VALUES ('$dt', $ti,'$sj', '$tc')";
    $result = @mysql_query ($query);


    How could I do this?

    Thanks in advance
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    676
    Rep Power
    7
    Well, first off, you are going to hear it here for sure, so never touch mysql_ again.... Switch to MySQLi or PDO.

    As far as your question goes, make your submission page offer 2 date boxes for you to make a day count from the date range of the 2 values. Then, you will have a number to define how many times you will loop your INSERT, and add to the day being submitted.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    22
    Rep Power
    0
    Thanks for the reply

    Well I'm kind of stuck with mysql for the moment but will take your advice in the future.

    My php is quite limited, so I put in the other date box ok. Could you tell me how to do the "Insert Loop"?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    676
    Rep Power
    7
    Well, first step... Lets get a count of how many times to run this loop. Can you create such from your dates?
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    22
    Rep Power
    0
    No, I've got the 2 dates.

    $dt and $dt2

    But I don't know how to calculate the difference
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    22
    Rep Power
    0
    OK I can calculate the difference in days this way


    <?php
    function date_diff($date1, $date2) {
    $current = $date1;
    $datetime2 = date_create($date2);
    $count = 0;
    while(date_create($current) < $datetime2){
    $current = gmdate("Y-m-d", strtotime("+1 day", strtotime($current)));
    $count++;
    }
    return $count;
    }

    echo (date_diff($dt, $dt2)." days <br \>");
    ?>
  12. #7
  13. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Hi,

    apart from the fact that you're trying to overwrite a native PHP function (date_diff() already exists), that your date calculations are pretty messy and that you send the kids to school on Sundays: Isn't that loop exactly what you need for the inserts? Fix the errors, throw out the counter stuff and put your query into the loop, and you're pretty much done.

    PHP Code:
    <?php 

    // every day between and including $start and $end
    $start '2013-6-5';
    $end '2013-6-16';

    $current_date = new DateTime($start);
    $end_date = new DateTime($end);

    while (
    $current_date <= $end_date) {
        echo 
    'The current date is ' $current_date->format('Y-m-d') . '<br />';
        
    $current_date->modify('+1 day');
    }
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    22
    Rep Power
    0
    Hi

    Thanks for your help.

    Sorry, I don't understand how to combine the loop with the original insert query

    $query = "INSERT INTO clases (fecha, hora, teacher, duracion, grupo ) VALUES ('$dt', $ti, '$tch', '$drt' , '$grp' )";
  16. #9
  17. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    You have a query which inserts a single row for a particular date. And you have a loop to enumerate all days between two dates. Right?

    All you need to to now is put the query into your loop:

    PHP Code:
    while ($current_date $end_date) {
        
    $date_string $current_date->format('Y-m-d');
        
    // put your query here, using $date_string as the value for the date column
        
    $current_date->modify('+1 day');

    That's it. This will insert a row for every day between the two dates.

    I hope that $dt, $ti etc. are all escaped properly with mysql_real_escape_string()?
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    676
    Rep Power
    7
    And as far as your 1 class every Tuesday type thing, it will be the same loop, but just contain an if/then to check if the day of week is equal to something. The following will say "July 1, 2000 is on a 6" In the example shown, the day of week is called in numerical format. Sunday being 0, and Saturday being 6. So, your if/then for a Tuesday with this example would be like if $var == 2 then...

    echo "July 1, 2000 is on a " . date("w", mktime(0, 0, 0, 7, 1, 2000));

    date() function

    Hope this helps.
    Last edited by Triple_Nothing; June 16th, 2013 at 04:57 PM.
  20. #11
  21. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Why does everybody love those low-level time functions so much?

    Unless you're using some ancient PHP 4 setup, there's really no reason to keep fumbling with this Unix timestamp stuff. We have the DateTime class now.

    PHP Code:
    <?php 

    // every Tuesday between and including $start and $end
    $start '2013-6-1';
    $end '2013-6-17';

    $current_date = new DateTime($start);
    $current_date->modify('this Tuesday');
    $end_date = new DateTime($end);

    while (
    $current_date $end_date) {
        
    $date_string $current_date->format('Y-m-d');
        echo 
    $date_string '<br />';
        
    $current_date->modify('+1 week');
    }

    Comments on this post

    • Triple_Nothing agrees : Us old farts just dun quite catch up as fast as we used to ;-)
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    22
    Rep Power
    0
    Many thanks! works really well!
  24. #13
  25. No Profile Picture
    Permanently Banned
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    23
    Rep Power
    0
    By using prepaired statement to insert multiple rows at a time in database.
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    22
    Rep Power
    0
    Ok so is there any way I could select say 2 days (for example Tuesday and Thursday) and insert a class for each of these days with one query.

    As it is I have to first introduce the Tuesdays and then the Thursdays.
  28. #15
  29. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    The above logic still works. The only difference is that you have to go through the days in every week:

    PHP Code:
    <?php

    $weekdays 
    = array('Tuesday''Thursday');
    $start '2013-6-1'
    $end '2013-6-17';

    $current_date = new DateTime($start); 
    $end_date = new DateTime($end); 

    while (
    $current_date $end_date) {
        foreach (
    $weekdays as $weekday) {
            
    $current_date->modify($weekday ' this week');
            echo 
    $current_date->format('Y-m-d'), '<br />';    
        }
        
    $current_date->modify('+1 week'); 
    }
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo