Last_Insert_ID() within a function

Question

I have a webform where users enter names and select a "program" name from a drop down. I have a javascript function that allows the user to add boxes when they want to enter multiple names into the form. When the program is selected, and inserted into the table, a Primary Key(LINE_ID) is generated. The names are inserted into another table with then name and LINE_ID in their respective columns.

    <div>
<label>Name:</label>
<tr>
<td><input type ="Text" name="yourname"/></td>
<td><input onclick="addBox(this);" type="button" value="+ Add Another Name"/></td>
</tr>
</div>

<script type="text/javascript">
        var BoxNum = 0;
        var NameCount = [];
    function addBox(input) {
    console.log(input);
        BoxNum ++;
            var row ='<input name = "BoxNum'+BoxNum+'">';
        $(input).before(row);
        NameCount.push(BoxNum);
        $('#nameCount').val(NameCount);
        };
    </script>

The first name that appears will be entered useing the pre-existing HTML input box (1), the additional boxes will be generated by the user clicking a button (2).

    <?php

    $name = $_POST['yourname'];
    $program = $_POST['PROGRAM_NAME'];
        mysql_query("INSERT INTO prod_activity (PROGRAM_ID) (SELECT PROGRAM_ID FROM tc_program WHERE PROGRAM_NAME='$program' AND ZONE_ID=4)");
        $lineid = mysql_insert_id();
        // THIS WORKS:
mysql_query("INSERT INTO tc_user (USER_NAME, LINE_ID) VALUES ('$name','$lineid')");
        $ElementNo = 0;
    function POSTALLNAMES(){
        //$ElementNo = 0;
        $ElementNo ++;
        $nameCount = $_POST['nameCount'];
        $nameelement = explode(",", $nameCount);
        $NameCountLgth = sizeof($nameelement);
        foreach ($nameelement as $value){
        $RealName = $_POST["BoxNum${value}"];
// THIS DOES NOT WORK:
        mysql_query("INSERT INTO tc_user (USER_NAME, LINE_ID) VALUES ('$RealName','$lineid')");}}
            POSTALLNAMES();

The first time I try and insert the $lineid variable with the HTML (1) box, everything is inserted into tc_user correctly. The second time I try and do it with the javascript generated input boxes (2) it does not. When I echo the variable $lineid after POSTALLNAMES(); runs, it gives the correct value for $lineid which tells me that the variable is not being reset at any point.

When I take out the LINE_ID/$lineid from the Insert statement in (2) and leave it in (1), everything inserts as you would expect (the first name with the ID and the additional names without).

If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) [statements](http://php.net/manual/en/pdo.prepared-statements.php) instead, and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Jul 14 '15 at 16:35
[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Jay Blanchard, Jul 14 '15 at 16:35
My guess, because we have not seen the modified HTML, is that you're duplicating id's in your markup. That will cause you no end of grief as id's in HTML must be unique. — Jay Blanchard, Jul 14 '15 at 16:39

score 0 · Answer 1 · answered Jul 14 '15 at 17:41

There a number of areas in which your code could be improved, as noted above. I strongly recommend looking into how to use PDO and prepared statements, plus how to properly perform input validation on user-submitted data.
Always remember: Never trust the client! Your site will be attacked at some point, it's just a matter of when. No matter how small or uninteresting you may think it is.

I also noticed that there weren't any coherent coding style used, which makes it a lot more difficult for others (and yourself) to read your code. This is something which will only come back to haunt you, and make it less likely that someone will take the time to actually help you. That's why it's recommended to pick one of the most common coding styles, and stick to it religiously. PSR-2 is the recommended one at the time of writing.

Lastly, to your code and issue at hand:
The issue itself was quite simple. You haven't passed the variable you were trying to use in the function, thus it didn't exist there giving you a null value (and a notice) instead. Which would get MySQL to complain about a missing value. I've fixed this issue, and cleaned up your code a bit. Also added a few comments further highlighting what I've done and why, as well as what still needs to be done.
Enjoy! :)

<?php

// CF: Validate input, to make sure you actually get something that could be a valid name.
$name = filter_input (INPUT_POST, 'yourname', FILTER_VALIDATE_REGEXP, "/^[a-z\\pL][a-z\\d\\pL '-]+\\z/iu");
$program = filter_input (INPUT_POST, 'program_name', FILTER_VALIDATE_REGEXP, '/^[a-z\\d][a-z\\d_-]+');

// CF: You could validate the input of $_POST['boxes'] here too, instead of inside the function.
$userBoxes = array ();
if (is_array ($_POST['boxes'])) {
    $userBoxes =  $_POST['boxes'];
}

// CF: If any of the above validations failed, show an error here, re-display the
// form with the previous values already filled in, and abort further processing of
// the POSTed data.

// Fill out the necessary details here. The PHP manual will help you on that.
$db = new PDO ();

$stmt = $db->prepare ("INSERT INTO prod_activity (PROGRAM_ID) (
        SELECT PROGRAM_ID FROM tc_program WHERE PROGRAM_NAME=? AND ZONE_ID=4)");
$stmt->exec (array ($program));
$lineId = $db->llastInsertId();

// THIS WORKS:
$stmt = $db->prepare ("INSERT INTO tc_user (USER_NAME, LINE_ID) VALUES (:name, :line_id)");
$stmt->exec (array (':name' => $name, ':line_id' => $lineId));

// CF: What's this for?
$ElementNo = 0;

if (!save_names ($db, $lineId, $userBoxes)) {
    // CF: Something failed, I presume. Handle it.
}

/**
 * Runs through the array of boxes, and saves them with the lineID.
 * 
 * @param PDO $db A connection to the database.
 * @param int $lineId {A useful description}
 * @param array $boxes {A useful description}
 * @return void (for now)
 */
function save_names (PDO $db, $lineId, $boxes)
{
    // CF: What's this for?
    // $ElementNo = 0;
    $ElementNo ++;

    $nameelement = explode(",", );

    // CF: This is not used. Should be removed if not used in the actual code as well.
    $NameCountLgth = sizeof ($nameelement);

    // CF: Prepare the statement outside of the loop, so that we can re-use it.
    $stmt = $db->prepare ("INSERT INTO tc_user (USER_NAME, LINE_ID) VALUES (:name, :id)")
    foreach ($boxes as $realName) {
        // CF: Add validation here, as I did above. If not added at the start of the script.
        if (!validate ($realName)) {
            // CF: Handle the error somehow here. Either by skipping the record, or
            // returning from the function with an error.
        }

        $stmt->exec (array (':name' => $RealName, ':id' =>$lineId));
    }
}

Thank you for taking the time to respond. As a beginner your comments are very helpful. — Zachary, Jul 14 '15 at 18:35

score -1 · Answer 2 · answered Jul 14 '15 at 16:35

-1

Use $lineid as a global variable.

Within the function call lineid as global $lineid

function POSTALLNAMES(){
   global $lineid; //this should take the correct value of $lineid
   //rest of the code here ......
}

I think that should work correctly :)

answered Jul 14 '15 at 16:35

Ketan Malhotra

1,255
3
16
44

Please do not follow this advice, using globals are bad! A web search will tell you lots of reason why. I'm working on an answer for you, just wanted to warn you about this in the meanwhile. – ChristianF Jul 14 '15 at 17:07

Last_Insert_ID() within a function

2 Answers2