Databases
StackOverflow2013
Postsdbo
POGetting an OOP PHP calendar app to update two tables at once
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POGetting an OOP PHP calendar app to update two tables at once
    primarykey
    data
    text
    <p>I'm building a PHP calendar app for a photography group. They need to be able to assign multiple dates/times to a single event.</p> <p>As of now, I have an Events table and a Slots table (for time slots). Events only has id, title, and description, while Slots has id, start time, end time, and event_id as a foreign key.</p> <p>I've created an Event class and a Slot class, and have a form for updating events that should be able to update both tables on submit by triggering an $event->save() method, and then cycling through each of the slots and triggering a $slot->save() method for each.</p> <p>I've included the code below. When I submit the form, I'm getting back an error on the $slot->save() section, and I can't figure out why.</p> <p>UPDATE: Adding code for some methods.</p> <pre><code> public static function find_by_id($id=0) { global $database; $result_array = self::find_by_sql("SELECT * FROM " . self::$table_name . " WHERE id={$id} LIMIT 1 "); return !empty($result_array) ? array_shift($result_array) : false; } public static function find_by_sql($sql="") { global $database; $result_set = $database-&gt;query($sql); $object_array = array(); while ($row = $database-&gt;fetch_array($result_set)) { $object_array[] = self::instantiate($row); } return $object_array; } private static function instantiate($record) { // Could check that $record exists and is an array // Simple, long-form approach: (if 100 cols, this would be huge) // PHP 5.3 LSB : $class_name = get_called_class(); $object = new self; // $object-&gt;id = $record['id']; // $object-&gt;username = $record['username']; // $object-&gt;password = $record['password']; // $object-&gt;first_name = $record['first_name']; // $object-&gt;last_name = $record['last_name']; // More dynamic, short-form approach: foreach($record as $attribute=&gt;$value) { if($object-&gt;has_attribute($attribute)) { $object-&gt;$attribute = $value; } } return $object; } public function save() { // A new record won't have an id yet. return isset($this-&gt;id) ? $this-&gt;update() : $this-&gt;create(); } protected function create() { // protected so you are forced to call save() method for safety (no duplicate creates) global $database; $attributes = $this-&gt;sanitized_attributes(); $sql = "INSERT INTO ". self::$table_name ." ("; $sql .= join(", ", array_keys($attributes)); $sql .= ") VALUES ('"; $sql .= join("', '", array_values($attributes)); $sql .= "')"; if($database-&gt;query($sql)) { $this-&gt;id = $database-&gt;insert_id(); return true; } else { return false; } } protected function update() { global $database; $attributes = $this-&gt;sanitized_attributes(); $attribute_pairs = array(); foreach ($attributes as $key =&gt; $value) { $attribute_pairs[] = "{$key} = '{$value}'"; } $sql = "UPDATE ". self::$table_name ." SET "; $sql .= join(", ", $attribute_pairs); $sql .= " WHERE id=". $database-&gt;escape_value($this-&gt;id); $database-&gt;query($sql); return ($database-&gt;affected_rows() == 1) ? true : false; } </code></pre> <p>And now onto the original code:</p> <pre><code>&lt;?php require_once('../../includes/init.php'); if (!$session-&gt;is_logged_in()) { redirect_to("login.php"); } $user = User::find_by_id($session-&gt;user_id); if (!isset($_GET['e'])) { redirect_to("calendar.php"); } else { $event_id = $_GET['e']; } $sql = "SELECT * FROM slots WHERE event_id = {$event_id}"; // find_by_sql() method also instantiates each row as a new Slot object // $slots is now an array() of objects $slots = Slot::find_by_sql($sql); // new Slot object used for adding a time slot to current event $blank_slot = new Slot(); // initialize $message var to "" $message=""; // Check to see if either of the submit buttons have been set in the $_POST if (isset($_POST['edit_event']) || isset($_POST['add_slot'])) { // Counts the number of slots associated with the current event id // Add one so that there is a blank field to add another time slot $count = count($slots) + 1; // Vars for checking if the last m/d/y is set in the form, before adding another slot field $check_month = "month" . $count; $check_year = "year" . $count; $check_day = "day" . $count; // initialize running errors var $errors = 0; // Don't add another slot field if the previous one has not been set if (isset($_POST['add_slot']) &amp;&amp; (empty($_POST[$check_month]) || empty($_POST[$check_year]) || empty($_POST[$check_day]))) { $message .= "Oops, you must enter a value for all months, dates, and years before adding a new date to this event.&lt;br /&gt;"; $errors++; } else { // create new Event object and set attrs from $_POST vars $event = new Event(); $event-&gt;id = $event_id; $event-&gt;title = $_POST['title']; $event-&gt;description = $_POST['description']; $event-&gt;category = $_POST['cat']; $new_event = $event-&gt;save(); if ($new_event = true) { $i = 1; // for each of the $slots objects... foreach ($slots as $slot) { // get the db_fields for the object $fields = Slot::get_db_fields(); // for each field... foreach ($fields as $field) { // create a $field_n var that appends $i, to match $_POST name $field_n = $field . $i; if ($field == 'id') { $slot_id_n = "slot_id" . $i; $slot-&gt;id = $_POST[$slot_id_n]; } elseif ($field == 'start_hour') { $start_ampm_n = "start_ampm" . $i; $start_hour = Slot::convert_to_military($_POST[$field_n], $_POST[$start_ampm_n]); $slot-&gt;start_hour = $start_hour; } elseif ($field == 'end_hour') { $end_ampm_n = "end_ampm" . $i; $end_hour = Slot::convert_to_military($_POST[$field_n], $_POST[$end_ampm_n]); $slot-&gt;end_hour = $end_hour; } elseif ($field == 'start_ampm' || $field == 'end_ampm') { } elseif ($field == 'event_id') { $slot-&gt;event_id = $event_id; } elseif ($field == 'status') { $slot-&gt;status = "confirmed"; } else { $slot-&gt;$field = $_POST[$field_n]; } } // save() method runs create() or update() method $save_slot = $slot-&gt;save(); // save m and y for redirecting back to calendar $last_month = $slot-&gt;month; $last_year = $slot-&gt;year; if ($save_slot == false) { // THIS IS THE ERROR I AM ALWAYS GETTING $message .= "(1) Problem saving time slot number {$i}.&lt;br /&gt;"; // rollback previous $slot and $event saves! $errors++; } $i++; } // add the new time slot, if it exists if (!empty($_POST[$check_month]) &amp;&amp; !empty($_POST[$check_year]) &amp;&amp; !empty($_POST[$check_day])) { $fields = Slot::get_db_fields(); foreach ($fields as $field) { $field_n = $field . $i; if ($field == 'id') { $slot_id_n = "slot_id" . $i; if(!empty($_POST[$slot_id_n])) { $blank_slot-&gt;id = $_POST[$slot_id_n]; } else { $blank_slot-&gt;id = NULL; } } elseif ($field == 'start_hour') { $start_ampm_n = "start_ampm" . $i; $start_hour = Slot::convert_to_military($_POST[$field_n], $_POST[$start_ampm_n]); $blank_slot-&gt;start_hour = $start_hour; } elseif ($field == 'end_hour') { $end_ampm_n = "end_ampm" . $i; $end_hour = Slot::convert_to_military($_POST[$field_n], $_POST[$end_ampm_n]); $blank_slot-&gt;end_hour = $end_hour; } elseif ($field == 'start_ampm' || $field == 'end_ampm') { } elseif ($field == 'event_id') { $blank_slot-&gt;event_id = $event_id; } elseif ($field == 'status') { $blank_slot-&gt;status = "confirmed"; } else { $blank_slot-&gt;$field = $_POST[$field_n]; } } $save_slot = $blank_slot-&gt;save(); $last_month = $blank_slot-&gt;month; $last_year = $blank_slot-&gt;year; if ($save_slot == false) { $message .= "Problem saving time slotb number {$i}.&lt;br /&gt;"; // rollback previous $slot and $event saves! $errors++; } } } else { $message .= "Error updating the event.&lt;br /&gt;"; $errors++; } if ($errors == 0) { $redirect = "calendar.php"; if (isset($last_month) &amp;&amp; isset($last_year)) { $redirect .= "?m={$last_month}&amp;y={$last_year}"; } if (isset($_POST['edit_event'])) { redirect_to($redirect); } } } } $event = Event::find_by_id($event_id); ?&gt; &lt;?php include_layout_template('header.php'); ?&gt; &lt;div id="main"&gt; &lt;h1&gt;Edit Event&lt;/h1&gt; &lt;?php if (!empty($message)) { echo "&lt;p class='message'&gt;" . $message . "&lt;/p&gt;"; } ?&gt; &lt;a href="calendar.php"&gt;Cancel (Go back to the Calendar)&lt;/a&gt;&lt;/p&gt;&lt;br /&gt;&lt;br /&gt; &lt;form style="width: 700px;" action="?e=&lt;?php echo $event_id; ?&gt;" method="post"&gt; &lt;?php echo $event-&gt;build_event_form(); ?&gt; &lt;?php $sql = "SELECT * FROM slots WHERE event_id = {$event_id}"; $slots = Slot::find_by_sql($sql); ?&gt; &lt;br /&gt;&lt;br /&gt; &lt;?php $num = 1; foreach ($slots as $slot) { echo $slot-&gt;build_slot_form($num); $num++; } echo $blank_slot-&gt;build_slot_form($num); ?&gt; &lt;br /&gt;&lt;br /&gt;&lt;br /&gt; &lt;input type="hidden" name="date_count" value="&lt;?php echo $num; ?&gt;"&gt; &lt;input type="submit" name="edit_event" value="Edit Event" /&gt; &lt;input type="submit" name="add_slot" value="Add Another Date"&gt; &lt;/form&gt; &lt;/div&gt; &lt;?php include_layout_template('footer.php'); ?&gt; </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USrhodesjason
    1. USrhodesjason
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POGetting an OOP PHP calendar app to update two tables at once
      1. USrhodesjason
      1. VTFavorite
    2. VO
      singulars
      1. POGetting an OOP PHP calendar app to update two tables at once
      1. USantihate
      1. VTFavorite
    1. COWhat is the error message? Is it PHP or MySQL error?
      singulars
      1. POGetting an OOP PHP calendar app to update two tables at once
      1. USLukasz Lysik
    2. COAnd could you post body of $slot->save() function?
      singulars
      1. POGetting an OOP PHP calendar app to update two tables at once
      1. USLukasz Lysik
    3. COI posted the object methods used in the example. The error I always get is in PHP, it's just spitting out the message I type for "if ($save_slot == false)" -- but it shouldn't be false if it saves.
      singulars
      1. POGetting an OOP PHP calendar app to update two tables at once
      1. USrhodesjason
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload