Page 1 of 3 123 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Posts
    80
    Rep Power
    7

    Insert Array into MySQL -> not inserting all options


    Thanks in advance for checking this out. I've been staring at it for hours. Can't figure it out.

    In a nutshell, i am working on an events calendar, and i am trying to work with events that occur every week.

    What i HAVE been able to do is get my ONE event to be put into an array, and loop through to create the recurring events. I can verify that the array is then complete, showing all the events i need.

    What i CANNOT get this to do, is then insert each piece of the array as a separate row. It will only insert the first option. Here is my code:

    PHP Code:
    $res = array(venue_id => $venueIdorg_id => $orgIddate => $daterecurrence => $recurrencef_date => $f_dateend_date => $end_datedatenote => $datenotetitle => $titlenews => $newsjazz => $jazzblues => $bluesacoustic => $acousticclassical => $classicalvisualarts => $visualartscommunity => $communityother_general => $other_generalsponsored => $sponsoredstatus => $statusdisplaybegindates => $displaybegindatedisplayenddate => $displayenddateposition => $positionfarmers => $farmersworld => $worldticket_price_range => $ticket_price_rangeticket_contact => $ticket_contactticket_note => $ticket_notelogo => $logoballroom => $ballroomholiday => $holiday);     
    $events = array();              
    if (
    $recurrence == '') {             
    $recurrence "1 day";         
    }              
    // Calculate the event's recurrence.         
    while ($date <= $end_date) {             
    $events[date($date)][] = $res;             
    // Add the recurrence to the previous occurrence's time.             
    $date strtotime("+$recurrence"$date);             
    // Go back to beginning of this loop and check it again.         
    }      
    echo 
    '<pre>';     
    print_r ($events);     
    echo 
    '</pre>';          
    foreach(
    $events AS $name => $value) {         
    foreach(
    $value AS $e) {             
    $EventAdd "INSERT INTO events_recurring (venue_id, presenting_org_id, date, f_date, end_date, date_note, title, news, jazz, blues, acoustic, classical, visual_arts, community, other_general, sponsored, time, status, DisplayBeginDate, DisplayEndDate, Position, farmers, world, ticket_price_range, ticket_contact, ticket_note, logo, ballroom, holiday) VALUES ('".$e['venue_id']."', '".$e['org_id']."', '".$name."', '".$e['f_date']."', '".$e['end_date']."', '".addslashes($e['datenote'])."', '".addslashes($e['title'])."', '".addslashes($e['news'])."', '".$e['jazz']."', '".$e['blues']."', '".$e['acoustic']."', '".$e['classical']."', '".$e['visualarts']."', '".$e['community']."', '".$e['other_general']."', '".$e['sponsored']."', '".date("U")."', '".addslashes($e['status'])."', '".$e['displaybegindate']."', '".$e['displayenddate']."', '".$e['position']."', '".$e['farmers']."', '".$e['world']."', '".addslashes($e['ticket_price_range'])."', '".addslashes($e['ticket_contact'])."', '".addslashes($ticket_note)."', '".addslashes($e['logo'])."', '".$e['ballroom']."', '".$e['holiday']."')";             
    mysql_query($EventAdd);             
    $feedback "Event has been added.";             
    return 
    $feedback;         
    }     


  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    you have a "return" in your loop, so that's exactly what the code will do: It will return after the first iteration. I guess you wanted an "echo".

    Apart from that, flooding the database with duplicate events isn't exactly a smart solution. It means a lot of unnecessary data, and whenever you wanna change the recurring pattern, you need to actually go through all events and update/delete them one by one. Why not simply store one event together with the recurrence data? Something like this:

    Code:
    recurrences
    
    -event_id
    -repeat_every (an integer)
    -unit (day, week, month, year -- stored in a separate table)
    -repeat_end (a date)
    So you could mark an event as, for example, recurring every weak days until a certain date.

    What's with all the "jazz" and "blues" and whatnot? Looks like you really need to normalize your table. And the mysql_ functions are a bit antique given that it's the 21. century. Yes, the 90s are over.

    Comments on this post

    • zubes1001 agrees
    Last edited by Jacques1; May 15th, 2013 at 07:17 PM.
    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".
  4. #3
  5. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7170
    Why not simply store one event together with the recurrence data?
    Ultimately it depends on how the data is going to be used. It's a lot more difficult to query against recurring event data that isn't duplicated.

    For example, if one of the purposes of this data is to show whether a particular room in a building is occupied at any given time, it's a lot easier to determine that in a JOIN if the data is duplicated.

    But yes, duplicating the data does make inserts and updates much more expensive. So it comes down to whether read or write performance is more important for the particular application.

    Comments on this post

    • dmittner agrees
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Posts
    80
    Rep Power
    7
    first off, thanks for taking the time to respond. That return was definitely the culprit, can't believe i missed that. works great now thanks.

    onto the other things you had mentioned...

    i have gone back and forth after reading numerous forum posts on the best solution for how to handle recurring events. the big hold up for me to use one event to store ALL the recurring data is due to the facts of: what if a particular week gets canceled due to a holiday? what if that particular week has a different artist at the venue? i'd have to go in and edit the event to stop recurring on the week before the change, enter in a new event for that one instance, and then recreate the recurring series once it starts up again. granted how many times does this really happen though... but still. Plus, add in the fact that we don't have THAT many events that are ongoing, so I would THINK this is minimal.

    as for the "jazz" "blues" fields in the table... those are the different categories for the calendar. Some go across multiple, some just one. If you have a different idea on how to handle calendars with categories, i'd love to hear it as i also agree its not the best.

    and as far as mysql goes... yes, i am aware of its limitations, and that it will soon be disappearing. Ive been working on PDO recently, and learning the differences and slowly changing things as i learn more. just havent done it here yet.

    Thanks again for the input.
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by zubes1001
    as for the "jazz" "blues" fields in the table... those are the different categories for the calendar. Some go across multiple, some just one. If you have a different idea on how to handle calendars with categories, i'd love to hear it as i also agree its not the best.
    Make a "categories" table storing a category ID and the category name ("jazz", "blues", ...). Then make an "event_to_category" table assigning event IDs to category IDs. If, for example, the event with the ID 5 has the categories 12 and 32, this would be two rows in this table: (5, 12) and (5, 32).

    I strongly suggest that you read up on normaliziation, because that's one of the basics of proper database design. Without this knowledge, you'll run into the same problems again and again, and there's a huge risk of ending up with broken data and a mess of queries.
    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".
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Posts
    80
    Rep Power
    7
    Good advice, thank you.

    will do some additional reading. Thanks for the tips.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Posts
    80
    Rep Power
    7
    Originally Posted by Jacques1
    Make a "categories" table storing a category ID and the category name ("jazz", "blues", ...). Then make an "event_to_category" table assigning event IDs to category IDs. If, for example, the event with the ID 5 has the categories 12 and 32, this would be two rows in this table: (5, 12) and (5, 32).
    One thought that occurred to me just now... when its done this way... what happens if say a category was checked by mistake? I assume the best way to go is to run an IF statement to see if the record exists for that ID number, and if there is, delete it from the table, or otherwise, do nothing.
  14. #8
  15. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Not exactly sure what you mean. If you want to uncheck a category, you simple delete the corresponding assignment:

    sql Code:
    DELETE
    FROM
    	event_to_category
    WHERE
    	event_id = ...
    	AND category_id = ...
    ;

    No if, no checks, just a plain delete.
    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".
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Posts
    80
    Rep Power
    7
    Originally Posted by Jacques1
    Not exactly sure what you mean. If you want to uncheck a category, you simple delete the corresponding assignment:

    sql Code:
    DELETE
    FROM
    	event_to_category
    WHERE
    	event_id = ...
    	AND category_id = ...
    ;

    No if, no checks, just a plain delete.
    Well thats what I figured... and you are right, i did not explain it well enough.

    so let's say i am editing an event. it has 3 categories selected. then its realized that 2 of the categories selected are incorrect. So on the edit form, they are unchecked. upon submission of the form, would you just delete ALL of the category entries first, and THEN on the insert string it adds back the only one that is correct/checked?
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Posts
    80
    Rep Power
    7
    i will also say... that the categories are simply stored with a 1 or 0. 1 meaning yes, 0 meaning no. isn't this roughly the same as creating 2 different tables to do the same thing since each event is unique?
  20. #11
  21. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by zubes1001
    isn't this roughly the same as creating 2 different tables to do the same thing since each event is unique?
    No. Storing a list of data in a long group of columns is conceptually wrong in the relational model and leads to all kinds of problems. For example, you cannot write your queries in a generic way so that they're independent from the concrete category names. You need to hard-code the names into your application. Every time you wanna add or delete a category, you have to change your code and also fumble with the database structure (add or delete columns). Simple tasks like counting the categories of an event is almost impossible. You cannot store additional data for the categories. There's also a huge risk of the columns "falling out of sync". What if some columns are NOT NULL, while others may contain NULL? And so on.

    As long as you only work with small scripts and unimportant data, you may not experience those problems. But as soon as your code and data get more complex, you'll realize that proper database design does matter.

    As to the previous question: Yeah, deleting all associations and then rewriting them is the easiest approach. However, do not insert the rows one by one, because this is very inefficient. Make a single query for all selected categories:

    sql Code:
    INSERT INTO
    	event_to_category (event_id, category_id)
    SELECT
    	<the event id>
    	, category_id
    FROM
    	categories
    WHERE
    	category_id IN (<a list OF ALL selected IDs>)
    ;

    Don't forget to escape the input! Not with that addslashes() stuff, but 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".
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Posts
    80
    Rep Power
    7
    Will give it a shot. Thanks.

    Nice to know there are people out there willing to help out so that new programmers learn better practices.
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Posts
    80
    Rep Power
    7
    Well I am doing pretty good with my work here in getting my table structure working. But now i've run into a little bit of a snag on the administrative side of my website, and could use some more help.

    I have my tables all set up accordingly:

    Users
    - id
    - username
    ...

    user_Categories
    - id
    - category
    ...

    user_permissions
    - user_id
    - category_id

    The trouble that I am having is when i am editing a user. I cannot seem to figure how to list all of the permissions that a user COULD have, and have the checkbox "checked" if they have that permission.

    This query is for selecting the user:
    PHP Code:
                                SELECT u.idu.usernameu.emailGROUP_CONCAT(uc.id SEPARATOR ', ') AS catidGROUP_CONCAT(uc.category SEPARATOR ', ') AS categoryGROUP_CONCAT(up.category_id SEPARATOR ', ') AS upcatidup.user_id 
    FROM  users u 
    JOIN users_permissions 
    AS up ON u.id up.user_id  
    LEFT JOIN users_categories 
    AS uc ON uc.id up.category_id
    WHERE u
    .id='$_GET[id]' GROUP BY u.id 
    I also have a second query that is currently pulling ALL of the possible permissions.

    How do I get the two to interact? Or is that even possible?
  26. #14
  27. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    inserting $_GET['id'] is a massive security risk as it allows for SQL injections. That's what you need to fix first. If there's any possibility for you to update your code and use one of the modern database extensions (PDO or MySQLi), do that! You've been lucky this time, because we've pointed out the security hole before you've put the site online (at least that's what I hope). But you may very well have less luck next time, and that means you might get your whole server "hacked".

    Secondly, get rid of the GROUP_CONCAT(). You want checkboxes, so you need the user categories. A string won't help you (don't even think about using explode() to extract the elements).

    Combining related tables is done with a join -- the thing you already use in your query. However, the logic is slightly different in this case.

    First you wanna combine each user with each user category. This is done with a cross join. Then you wanna join those combined tables with the user_permissions to check for each combination if it's registered in this table. If not, you just want NULL. So you need a left join.

    sql Code:
    SELECT
    	users.id AS user_id
    	, users.username
    	, users.email
    	, users_categories.id AS category_id
    	, users_categories.category
    	, users_permissions.user_id IS NOT NULL AS has_permission
    FROM
    	users
    	CROSS JOIN
    		users_categories
    	LEFT JOIN
    		users_permissions
    		ON
    			users_permissions.user_id = users.id AND users_permissions.category_id = users_categories.id
    WHERE
    	users.id = 1
    ORDER BY
    	users_categories.id ASC
    ;

    There's still some work to do. You need to rename some tables and columns, set up foreign keys etc. But that comes later.
    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".
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Posts
    80
    Rep Power
    7
    Originally Posted by Jacques1
    inserting $_GET['id'] is a massive security risk as it allows for SQL injections.
    I have just started getting into PDO after learning that mysql_ commands are being deprecated soon. the $_GET option being bad was something i was kind of aware of, but hadn't learned yet the proper method. I read your article on it and believe that will not be an issue going forward.

    Originally Posted by Jacques1
    Secondly, get rid of the GROUP_CONCAT(). You want checkboxes, so you need the user categories. A string won't help you (don't even think about using explode() to extract the elements).
    I have used GROUP_CONCAT() on other pages, along with explode(). SO if its NOT good to use explode(), does CROSS JOIN fix using that... or is it only in this instance where i cant use it?

    The rest I am hoping can figure out. Thanks again for all your help.
Page 1 of 3 123 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo