Insert function that takes into account form data AND passed values

[thenorman138]

short(est) version: I created a simple CMS that allows the user to choose from 4 templates. Each template has different panel types. For this example I'm showing a full page template which only has one full page panel.

Basically, the panel offers up an instance of TinyMCE to allow the user to create text/image content for the panel.

The flow is:
Content ->(assigned to)->Panel->(assigned to)->Page->(assigned to)->display

So in this case, the user selects a link which passes a value through the URL, in this case the value is 1. This tells templates.php to include fullWidth.php

So templates.php has a save form with a submit button, and the loaded fullWidth.php file has the panel template which gives us the number for panel type as well as the content in a textarea.

My problem is, I need to fully realize a function that saves a page (which by proxy saves records for content and panels as well)

So with the code below, I would be inserting into 3 different tables basically, but the ```panels``` table is inserting foreign keys; the IDs of the newly created ```pages``` entry as well as the newly inserted ```content``` entry.

I know the majority of this is just inserting some form values into the database but I have 2 main hurdles here:

1. How to insert values that aren't in the form such as the content and panel_type ID from fullwidth.php as well as the passed in $value in templates.php
2. How to create a function that does the main inserting with the ability to grab 2 different IDs used as foreign keys for panels.

For this example with the code I'm posting (assuming the user is selecting the hard coded ```<options>``` I'm using below), I would insert:

saveContentPage.php

    //This is complete pseudo code, I'm not sure how the syntax would change for the different types
    $title= $_POST['$title'];
    $pageType= $_POST['$value'];
    $displayId = $_POST['#areaSelect'];
    $start_time = now();
    $end_time = $_POST['#datePicker'];
    $slide_order = $_POST['#orderSet'];
    $duration = $_POST['#durationSet'];

    $sql="INSERT INTO pages(title, page_type_id, display_id, start_time, end_time, slide_order, duration) 
            VALUES ('$title','$pageType','$displayId','$start_time','$end_time','$slide_order','$duration')";

    //Here I need to pass the content from the included fullwidth.php to get the textarea content and the panel_type_id
    $content = $_POST['textArea']

    $sql = "INSERT INTO content(content)
            Values('$content')";

    if(#id = FullPage){
    $panel_type = 1;
    }

    $sql = INSERT INTO panels (panel_type_id, page_id, cont_id)
            VALUES ('$panel_type', /*ID that was created from 'pages' insert*/, /*ID that was created from 'content' INSERT*/)

How can I create a function that performs the necessary insert(s)?

Code:

templates.php      

  <!-- check GET 'value' -->
        <?php $value = isset($_GET['value']) ? $_GET['value'] : 1;?>

        <!-- If value is 1 then load the full page template class -->
        <?php if($value == 1){?>
        <?php include 'class/fullWidth.php'?>

        <!-- Submit button that links to modal with a 'save page' form inside -->
        <div class="modal fade" id="savePageModal" tabindex="-1" role="dialog" aria-labelledby="savePageLabel" aria-hidden="true">
            <div class="modal-dialog" role="document">
                <div class="modal-content">
                    <div class="modal-header">
                      <h5 class="modal-title" id="savePageLabel">Page Details:</h5>
                      <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                        <span aria-hidden="true">×</span>
                      </button> 
                    </div>
                    <div class="modal-body">
                        <div class="form-group">
                            <!-- this is the URL value i.e. templates.php?value=1 and should insert into pages.page_type_id -->
                            <label>Page Type: <?php echo $value;?></label>
                            </br>

                            <!-- This is page title, should map to pages.title -->
                            <label for="addTitle">Page Title:</label>
                            <input class="form-control" id="addTitle">
                            </input>

                            <!-- This shows the displays, will save the ID of the selected option to pages.display_id -->
                            <label for="areaSelect">Select An Area</label>
                            <select class="form-control" id="areaSelect">

                                <option>4</option>

                                
                            </select>

                            <!-- Input for seconds, will save to pages.duration -->
                            <label for="durationSet">Set A Duration (in seconds)</label>
                            <input class="form-control" id="durationSet">
                            </input>

                            <!-- User selects order of slide -->
                            <label for="orderSet">Set Slide Order</label>
                            <select class="form-control" id="orderSet">
                                <option>3</option>
                            </select>

                            <!-- This datepicker allows user to pick a date and time, will save as TIMESTAMP to pages.end_time -->
                            <label for="expirationSelect">Set Expiration Date/Time</label>
                            <div class="form-group">
                              <div class="datepick input-group date" id="datetimepicker" data-target-input="nearest">
                                <input type="text" class="form-control datetimepicker-input" data-target="#datetimepicker"/>
                                <span class="input-group-addon" data-target="#datetimepicker" data-toggle="datetimepicker">
                                <span class="fa fa-calendar"></span>
                                </span>
                              </div>
                            </div>


                        </div>
                    </div>
                      <div class="modal-footer">
                        <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
                        <button type="button" class="btn btn-primary">Save changes</button>
                      </div>
                    </div>
                </div>
            </div>
        </div>

fullwidth.php      

 <!-- This file is loaded based on page type ($value) from templates.php
            It loads an HTML template which has a main div houseing the panel_type and it has
            a textarea that houses the content -->

        <div class="row middle">
            <div class="col-lg-12 fullWidth">
                <!-- This Div ID is 1 which maps to the panel_type of this div, so upon saving It should insert to panels.panel_type_id a value of 1 -->
                <div class="fullContent" id="fullPage" style="background-color: white; height: 100%;">
                    <!-- THis is simply a modal that givees the user an interface to use TinyMCE in order to fill the content of mytextarea3 -->
                    <div class="modal fade bd-example-modal-lg" id="fullModal" tabindex="-1" role="dialog" aria-labelledby="fullLabel" aria-hidden="true">
                        <div class="modal-dialog modal-lg" role="document">
                              <div class="modal-content">
                                <div class="modal-header">
                                  <h5 class="modal-title" id="fullModal">Content Library:</h5>
                                  <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                                    <span aria-hidden="true">×</span>
                                  </button>
                                </div>
                                <div class="modal-body">
                                    <h3>Create your own content</h3>
                                    <form id="form-data3" method="post">
                                        <!-- This is the text area that should be saved to content.content -->
                                      <textarea id="mytextarea3"></textarea>
                                      <input type="submit" value="Get Data">
                                    </form>
                                </div>
                                <div class="modal-footer">
                                    <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
                                    <button type="button" class="btn btn-primary" data-dismiss="modal">Save changes</button>
                                </div>
                           </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>

 

[Barand]

You can get the id of the auto_incremented value of the last record added

PDO

$newId = $stmt->insert_id;

MYSQLI

$newId = $conn->insert_id;

 

[thenorman138]

Ok so, for mysqli $newID would be the variable and it will automatically get the id for each one?

I was thinking of running an insert for the page record, an insert for content, and then the panel last since it has to have those IDs, so would I have to set a $newID for each of the first 2 Inserts?

(sorry, I'm usually mostly front end and I'm so stuck on this)

[dalecosp]

He's just telling you how to get the ID of the last inserted row.  You wrote pseudo-code, so that's not the only thing you need help with, I'd expect?

Here's your pseudo-code with comments that I hope will help.  I'll post again with some direct answers to your two points, above.

<?php

# First off, your code is accepting $_POST data at face value.
# An attacker might be able to do something evil with that.
#
# Next, where's the SQL connection happening?  I'll do a mysqli ...

$db = mysqli_connect("localhost", "user", "pass", "db_name");

# NOTE: I don't do ANY error checking in this script.  You'll NEED
# to do that, because almost any invalid hit on the MySQL server will
# halt execution of the script.

$title       = $_POST['$title'];
$pageType    = $_POST['$value'];

# This code (and 3 others later) will make PHP expect a var named '#areaSelect'
# in the POST data. You may need to do a print_r(), var_dump(), or use other debug-type
# technique to ensure that you are indeed getting a var with that name from the browser.
# (In other words, I wonder if you're confusing JS's DOM element ID's with POST data).

$displayId   = $_POST['#areaSelect'];

# yields a UNIX_TIMESTAMP.  As it's only used in one SQL statement, you 
# might just use MySQL's UNIX_TIMESTAMP() function and omit this.

$start_time  = now(); 

# see note above
$end_time    = $_POST['#datePicker'];
$slide_order = $_POST['#orderSet'];
$duration    = $_POST['#durationSet'];

# depending on the data type, you might not want to quote all the values (e.g.
# MySQL may try to store integers as strings if they're quoted, and that might be
# "not what you want" or even cause your statement to fail).

$sql = "INSERT INTO pages(title, page_type_id, display_id, start_time, end_time, slide_order, duration) 
        VALUES 
	    ('$title','$pageType','$displayId','$start_time','$end_time','$slide_order','$duration')";

$db->query($sql);
$page_id = $db->insert_id;

//Here, pass the content from the included fullwidth.php extarea content and the panel_type_id

# you have a table named 'content', with a column named 'content', into which 
# you want to insert a variable named '$content' ... OK.  But can we discuss naming? ;-)
$content = $_POST['textArea']
$sql     = "INSERT INTO content(content) Values('$content')";
$db->query($sql);
$content_id = $db->insert_id;

# typo here?  $id is a PHP variable.
# Also bug:  I'm fairly sure you want ($id == "FullPage") ... not an assignment.
if(#id = FullPage){

    $panel_type = 1;
}

# This is where Barand's comment is helpful ... knowing how to get the Insert ID.
$sql = INSERT INTO panels (panel_type_id, page_id, cont_id)
            VALUES ('$panel_type', $page_id, $content_id);
$db->query($sql);

 

[dalecosp]

Quote

1. How to insert values that aren't in the form such as the content and panel_type ID from fullwidth.php as well as the passed in $value in templates.php

Well, can you put them in the form somehow?  Traditionally that might be done with <input type='hidden'>, if it's not possible to move the <form> tags to the appropriate locations.

If you can't do that, I'm not sure what to tell you, because that sounds like front-end type magic (AJAX).

 

Quote

2. How to create a function that does the main inserting with the ability to grab 2 different IDs used as foreign keys for panels.

Well, I kind of showed that in a more/less procedural style in my post above.  If you want you could wrap that in a function; keep in mind you'd need to either declare $db to be global or establish a connection (or connection object) INSIDE your function.  As Barand showed you, PDO or the newer MySQL(i) both assign the ID of the last inserted value to $db->insert_id; you can assign this to a variable and use it in your final SQL statement.

[thenorman138]

Wow, that's a lot of insight thank you! So, I could feasibly wrap the $value in a hidden form as well as the panel type Id from fullwidth.php rather than using a div ID. That sounds better to me, if I'm understanding you right

[thenorman138]

Also, I guess I can pass values from the <option> tags 

[thenorman138]

Thank you @dalecosp!

[thenorman138]

@dalecosp can I use php to loop hidden inputs? So if I have multiple instances of the page type that is being loaded there?

 

[dalecosp]

3 hours ago, thenorman138 said:

@dalecosp can I use php to loop hidden inputs? So if I have multiple instances of the page type that is being loaded there?

 

I'm not sure I understand 100% ... loop them for creation or reading?  

For reading, you'd need to know the element's name attributes (from the HTML) when processing the data in the handler script.

[thenorman138]

I actually created a new post a few hours ago that more specifically covers my problem if you care to take a look @dalecosp, I was able to get the insert working for one panel but trying to get it fixed for multiple panels and Content

Edited July 20, 2018 by thenorman138