CODEWITHSUNDEEP
phpmysqli
Tim M
Tim M

Reputation: 306

Error when deleting - Only variables can be passed by reference PHP/MYSQLI

I'm submitting form data and am deleting before inserting instead of checking to see if an update needs to be made. However, I get the following error:

Fatal error: Uncaught Error: Only variables can be passed by reference in /public_html/fitbit2/admin/admin_assignments.php:98 Stack trace: #0 {main} thrown in /public_html/fitbit2/admin/admin_assignments.php on line 98

This is line 98: 

$stmtdel->bind_param( "ss", $Assignment, $Section[$i], $Semester, $Year );

Here is more of the code:

if ( $_POST ) {

    //assign date variables
    $PreStartDate = $_POST[ 'PreStartDate' ];
    $PreEndDate = $_POST[ 'PreEndDate' ];

    $PostStartDate = $_POST[ 'PostStartDate' ];
    $PostEndDate = $_POST[ 'PostEndDate' ];

    $semesterarr = array('Fall','Spring','Summer1','Summer2','Winter','May');
if (in_array($_POST['Semester'], $semesterarr)) {
    $Semester = clean($_POST['Semester']);
}


if (filter_var($_POST['Year'], FILTER_VALIDATE_INT) !== FALSE) {
    $Year = clean((int)$_POST['Year']);
}

    //convert time format to prepare for mysql insert
    $PreStartTime = clean( $_POST[ 'PreStartTime' ] );
    $PreStartTime = date( "H:i:s", strtotime( "$PreStartTime" ) );

    $PreEndTime = clean( $_POST[ 'PreEndTime' ] );
    $PreEndTime = date( "H:i:s", strtotime( "$PreEndTime" ) );

    $PostStartTime = clean( $_POST[ 'PostStartTime' ] );
    $PostStartTime = date( "H:i:s", strtotime( "$PostStartTime" ) );

    $PostEndTime = clean( $_POST[ 'PostEndTime' ] );
    $PostEndTime = date( "H:i:s", strtotime( "$PostEndTime" ) );

    //create datetime for mysql insertion 
    $PreStartDate = $PreStartDate . ' ' . $PreStartTime;
    $PreEndDate = $PreEndDate . ' ' . $PreEndTime;

    $PostStartDate = $PostStartDate . ' ' . $PostStartTime;
    $PostEndDate = $PostEndDate . ' ' . $PostEndTime;

    $PointsPossible = filter_var( $_POST[ 'PointsPossible' ], FILTER_SANITIZE_NUMBER_INT );
    $Assignment = filter_var( $_POST[ 'Assignment' ], FILTER_SANITIZE_STRING );


    $Section = $_POST[ 'SectionNumber' ];

    if ( empty( $Section ) ) {
        echo( "You didn't select any sections. Please click the back button in your browser to restart the process." );
        exit;
    } else {

    //DELETE before inserting
        //$stmtsel = $connection->prepare( "SELECT AssignmentID FROM Assignments WHERE Assignment=? AND SectionNumber=?" );

        $stmtdel = $connection->prepare( "DELETE FROM Assignments WHERE Assignment=? AND SectionNumber=? AND Semester=? AND Year=?" );

        $stmt = $connection->prepare( "INSERT INTO Assignments SET PreStartDate=?, PreEndDate=?, PostStartDate=?, PostEndDate=?, Assignment=?, PointsPossible=?, SectionNumber=?, Semester=?, Year=?" );

        $N = count( $Section );
        $success = '';

        for ( $i = 0; $i < $N; $i++ ) {

$stmtdel = $connection->prepare("DELETE FROM Assignments WHERE Assignment=? AND SectionNumber=? AND Semester=? AND Year=?");
// 4 parameters, $sectionNumber does not need to be defined at this stage
$stmtdel->bind_param('ssss', $Assignment, $sectionNumber, $Semester, $Year);

$stmt = $connection->prepare("INSERT INTO Assignments SET PreStartDate=?, PreEndDate=?, PostStartDate=?, PostEndDate=?, Assignment=?, PointsPossible=?, SectionNumber=?, Semester=?, Year=?");
// 9 parameters
$stmt->bind_param('sssssssss', $PreStartDate, $PreEndDate, $PostStartDate, $PostEndDate, $Assignment, $PointsPossible, $sectionNumber, $Semester, $Year);

foreach ($Section as $sectionNumber) {
    // $sectionNumber becomes defined here
    $stmtdel->execute();
    $stmt->execute();
}

            $result = $stmt->get_result();
            if ( $result->num_rows === 0 ) {
                BodyHeader( "No Data", '', '' );
                ?>
                <div class="alert alert-danger" role="alert">
                    There was a problem. None of the sections were updated.
                </div>
                <?php
                BodyFooter();
                exit;
            }
            $success .=   $Section[$i] . " ";

        }
    }
}

?>


<?php //show sync message at top of page
if(isset($success)){?>
<div class="alert alert-success alert-dismissable fade show" id="flash-msg" role="alert">
    You successfully updated the following sections: <?=$success; ?>
    <button type="button" class="close" data-dismiss="alert" aria-label="Close">
    <span aria-hidden="true">&times;</span>
  </button>
</div>
<?php } ?>

          <h1 class="h2">Setup Assignments</h1>
<p>&nbsp;</p>
<form action="admin_assignments.php" method="post">
    <p style="font-weight: bold">Which section(s) would you like to apply this assignment to? </p>
    <?php $stmt4 = $connection->prepare("SELECT DISTINCT SectionNumber FROM Courses WHERE SectionNumber != '' ORDER BY SectionNumber ASC
") or die($connection->error);
$stmt4->execute();
$result4 = $stmt4->get_result();

              while ($row4 = $result4->fetch_assoc()):
?>
                      <input type="checkbox" name="SectionNumber[]" value="<?=$row4['SectionNumber'];?>" id="SectionNumber[]"><?=$row4['SectionNumber'];?>

    <?php endwhile; ?>


    <p>&nbsp;</p>


        <div class="form-group has-float-label">

        <select name="Assignment" class="form-control" required autofocus>
<option value="" selected>Select Assignment</option>

    <?php $stmt4 = $connection->prepare("SELECT DISTINCT Assignment, AssignmentID FROM Assignments GROUP BY Assignment
") or die($connection->error);
$stmt4->execute();
$result4 = $stmt4->get_result();

              while ($row4 = $result4->fetch_assoc()):
?>

          <option value="<?=$row4['Assignment'];?>"><?=$row4['Assignment'];?></option>

    <?php endwhile; ?>
        </select>
    </div>


        <div class="form-group has-float-label">
      <input type="text" name="PointsPossible" id="PointsPossible" value="" class="form-control" placeholder="Points Possible" required autofocus>
          <label for="PointsPossible">Points Possible</label>
    </div>

     <div class="form-group has-float-label">

      <select name="semester" id="semester" class="form-control">
        <option selected="selected">--- Semester? ---</option>
        <option value="summer1">Summer 1</option>
        <option value="summer2">Summer 2</option>
        <option value="fall">Fall</option>
        <option value="spring">Spring</option>
        <option value="winter">Winter Minimester</option>
        <option value="may">May Minimester</option>
      </select>
    </div>

    <div class="form-group has-float-label">
  <select name="year" class="form-control">
        <?php 
   for($i=date('Y'); $i < date('Y')+2; $i++){
      echo "<option value=\"$i\">$i</option>";
   }
?>
  </select>
    </div>
        <p>&nbsp;</p>

  <div class="form-group row">
        <div class="col-sm-10">
            <strong>Pre-Test</strong>
            <p>Select the opening and closing dates for the Pre-Test:</p>
            <p id="predates" class="input-group">
                <input type="text" class="date start form-control" name="PreStartDate" placeholder="Start Date" required autofocus style="width: 150px" autocomplete="off"/>
                <input type="text" class="time start form-control" name="PreStartTime" placeholder="Start Time" required autofocus style="width: 150px" autocomplete="off"/> &nbsp;&nbsp; to &nbsp;&nbsp;
                <input type="text" class="date end form-control" name="PreEndDate" placeholder="End Date" required autofocus style="width: 150px" autocomplete="off"/>
                <input type="text" class="time start form-control" name="PreEndTime" placeholder="End Time" required autofocus style="width: 150px" autocomplete="off"/>
            </p>
        </div>
    </div>
        <div class="form-group row">
            <div class="col-sm-10">
                <strong>Post-Test</strong>

                <p>Select the opening and closing dates for the Post-Test:</p>
                <p id="postdates" class="input-group">
                    <input type="text" class="date start form-control" name="PostStartDate" placeholder="Start Date" required autofocus style="width: 150px" autocomplete="off"/>
                    <input type="text" class="time start form-control" name="PostStartTime" placeholder="Start Time" required autofocus style="width: 150px" autocomplete="off"/> &nbsp;&nbsp; to &nbsp;&nbsp;
                    <input type="text" class="date end form-control" name="PostEndDate" placeholder="End Date" required autofocus style="width: 150px" autocomplete="off"/>
                    <input type="text" class="time start form-control" name="PostEndTime" placeholder="End Time" required autofocus style="width: 150px" autocomplete="off"/>
                </p>
            </div>
    </div>
            <input class="btn btn-lg btn-primary btn-block" type="submit" value="Save Changes">

</form>

What do I need to do to correct that error?

Thanks,

Tim

Upvotes: 0

Views: 68

Answers (2)

Phil
Phil

Reputation: 164924

The problem here is that mysqli_stmt::bind_param accepts variable references. $Section[$i] does not qualify as it is a value retrieved from an array.

When executing statements in a loop, it is much more efficient to prepare and bind the statements first and only execute within the loop.

When binding, because variable references are used, the variables themselves do not need to be defined at this stage.

With that in mind, you should try this

$stmtdel = $connection->prepare("DELETE FROM Assignments WHERE Assignment=? AND SectionNumber=? AND Semester=? AND Year=?");
// 4 parameters, $sectionNumber does not need to be defined at this stage
$stmtdel->bind_param('ssss', $Assignment, $sectionNumber, $Semester, $Year);

$stmt = $connection->prepare("INSERT INTO Assignments SET PreStartDate=?, PreEndDate=?, PostStartDate=?, PostEndDate=?, Assignment=?, PointsPossible=?, SectionNumber=?, Semester=?, Year=?");
// 9 parameters
$stmt->bind_param('sssssssss', $PreStartDate, $PreEndDate, $PostStartDate, $PostEndDate, $Assignment, $PointsPossible, $sectionNumber, $Semester, $Year);

foreach ($Section as $sectionNumber) {
    // $sectionNumber becomes defined here
    $stmtdel->execute();
    $stmt->execute();
}

Upvotes: 1

Dat Pham
Dat Pham

Reputation: 1875

When you use the bind_param function, you could only pass variables as arguments and not value. Notice in your code this $Section[$i] is not a variable but a value from array access. To fix that you need to assign a variable $SectionValue = $Section[$i] and pass that variable to bind_param.

Upvotes: 1

Related Questions