PHP MySQL (UPDATE)

Question

I am trying to build a simple web application where students can book a driving lesson (for college project) I done the insert and view... now I need to add the update and delete... I am having problems with the update.... It does not seem to update the rows I want... I want the row to update where the Active column is equal to true/1 The information is passed through apart from the Firstname... The last Firstname in the list is added no other.... Simple problem I can imagine, can anyone help?

    <!DOCTYPE html> 

    <head>
        <title>Edit Students</title>
    </head>

<?php

            $user = 'root';     //Database username ("Root for xampp")
            $pass = '';             //Database password ("empty for exampp")
            $db = 'dragondrivingschooldb';      //Name of database

            $con = new mysqli('localhost', $user, $pass, $db) or die("Unable to connect");  //Create new data connection ('name of host/server', user, password, database name)

            $sql = mysqli_query($con, "SELECT * FROM booking");

    echo "<table border='1'> //Creating table to store data
    <tr>                     //Table headers
        <th>Active</th>
        <th>Booking ID</th>
        <th>First Name</th>
        <th>Last Name</th>
        <th>Age</th>
        <th>Driving Experience</th>
        <th>Street</th>
        <th>PostCode</th>
        <th>City</th>
        <th>County</th>
        <th>Mobile Number</th>
        <th>House Number</th>
        <th>Email</th>
        <th>Course</th>
        <th>Package Type</th>
        <th>Length Of Lesson</th>
        <th>Driving Instrutor</th>
        <th>Date</th>
        <th>Time</th>
        <th>Name On Bank Card</th>
        <th>Card Holder Address</th>
        <th>Card Holder Postcode</th>
        <th>Card Number</th>
        <th>Three Digit Card Number</th>
    </tr>";

    //Show Edit Form///////////////////////////////////////////////////////////////////////////////////////////////////

    while($row = mysqli_fetch_array($sql)) {    //Run sql code till there are no more rows to import 


    echo "<form action=\"UpdateStudents.php\" method=\"post\">";  //create form

    echo "<tr>";
        echo "<td> <input type=\"radio\"  \" name=\"radActive\"> </td>";          //when this equals true, then the row will be updated
        echo "<td> <input value=" . $row['BookingID'] . " name=\"txtid\"> </td>";
        echo "<td> <input value=" . $row['FirstName'] . " name=\"txtfirstname\"> </td>";
        echo "<td> <input value=" . $row['LastName'] . "> </td>";
        echo "<td> <input value=" . $row['Age'] . "> </td>";
        echo "<td> <input value=" . $row['DrivingExpereince'] . "> </td>";
        echo "<td> <input value=" . $row['Street'] . "> </td>";
        echo "<td> <input value=" . $row['PostCode'] . "> </td>";
        echo "<td> <input value=" . $row['City'] . "> </td>";
        echo "<td> <input value=" . $row['County'] . "> </td>";
        echo "<td> <input value=" . $row['MobileNumber'] . "> </td>";
        echo "<td> <input value=" . $row['HouseNumber'] . "> </td>";
        echo "<td> <input value=" . $row['EMail'] . "> </td>";
        echo "<td> <input value=" . $row['Course'] . "> </td>";
        echo "<td> <input value=" . $row['PackageType'] . "> </td>";
        echo "<td> <input value=" . $row['LengthOfLesson'] . "> </td>";
        echo "<td> <input value=" . $row['DrivingInstrutor'] . "> </td>";
        echo "<td> <input value=" . $row['Date'] . "> </td>";
        echo "<td> <input value=" . $row['Time'] . "> </td>";
        echo "<td> <input value=" . $row['BankName'] . "> </td>";
        echo "<td> <input value=" . $row['BankAddress'] . "> </td>";
        echo "<td> <input value=" . $row['BankPostCode'] . "> </td>";
        echo "<td> <input value=" . $row['BankCardNo'] . "> </td>";
        echo "<td> <input value=" . $row['BankSecurityNo'] . "> </td>";
    echo "</tr>";
    }

    echo "</table>";     //close table

    echo "<input name=\"btnUpdate \" type=\"submit\" value=\"Update\" />";   //Create buton to update

    echo "</form>";

    mysqli_close($con);   //close database connection

    ?>


</html>

SECOND where the update takes place

<?php

$user = 'root';     //Database username ("Root for xampp")
$pass = '';             //Database password ("empty for exampp")
$db = 'dragondrivingschooldb';      //Name of database

$con = new mysqli('localhost', $user, $pass, $db) or die("Unable to connect");  //Create new data connection ('name of host/server', user, password, database name)

$sql = mysqli_query($con, "SELECT * FROM booking");   //Select all data from booking table

if (!mysqli_query($con,$sql)) {
    die('Error: ' . mysqli_error($con));    //if the sql does not run, then kill it
}    
//Create escape variables for security  

//Details

//$id = mysqli_real_escape_string($con, $_POST['txtid']); 

$Active = mysqli_real_escape_string($con, $_POST['radActive']);      //bring data in from prevoius page Active

$FirstName = mysqli_real_escape_string($con, $_POST['txtfirstname']);  //Bring data in from prevoius page Firstname

$sqlupdate=("UPDATE booking SET FirstName='$FirstName' Where Active = '$Active' ");  //Update row where Active = "true/1"


if (!mysqli_query($con,$sqlupdate)) {
    die('Error: ' . mysqli_error($con));   //if the query does not run, then kill it
}

echo " Information Updated"; 
echo " FirstName = $FirstName <br>";   //show the value of first name (for debugging purposes 
echo "$Active";   //show the value of active for debugging purposes

mysqli_close($con);  //Close Connection 

?>

A side note: your code is vulnerable to SQL injection, learn how to deal with it http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 — Bud Damyanov, May 15 '14 at 13:32
Not sure what you are trying to do here, looks to me like the form is using active as all the different columns on the db so depending what you choose it will be trying to update where Active = $row['HouseNumber'] which doesn't sound right, surely you want to update where an id = something — Anigel, May 15 '14 at 13:41
You can't expect to update the entire rows to be updated based on just the radio button. You need to name your other inputs and then assign variables to those also. MySQL doesn't know what you want to update, "you" need to tell it to. — Funk Forty Niner, May 15 '14 at 13:42
I did that to start with.... however when I did, all the FirstNames were updated to the last FirstName on the database — JiraiyaUK, May 15 '14 at 13:43
At the moment I am just trying to get the FirstName to update before I go though the tasks of added the other coloumns to the SQL statement — JiraiyaUK, May 15 '14 at 13:44

score 0 · Answer 1 · answered May 15 '14 at 13:42

0

$sqlupdate="UPDATE booking SET FirstName='".trim($FirstName)."' Where Active =  '".trim($Active)."'";

this should help please pay attention that all rows in a table will be updated with the same FirstName where Active =1

Thinks about other conditions to apply

answered May 15 '14 at 13:42

volkinc

2,143
1
15
19

I tried changing it to $sqlupdate="UPDATE booking SET FirstName='".trim($FirstName)."' Where BookingID = '".trim($StudentID)."'"; But it still seems to change all the names to the last Name in the list – JiraiyaUK May 15 '14 at 14:15
One query or update is independent of everything that has gone on before it. Do you guarantee that there is only **ONE** row where _Active_ is some value? It sounds like multiple rows match this criterion. You probably need the WHERE clause to include some other fields. – Phil Perry May 15 '14 at 14:21
Im using a Radio box, show only one field can show as True/1 :-/ but it is always showing the last name in the table, dispit me selecting the first name in the table – JiraiyaUK May 15 '14 at 14:24
$sqlupdate="UPDATE booking SET FirstName='".trim($FirstName)."' Where Active = '".trim($Active)."' AND txtid = '".trim( mysqli_real_escape_string($con, $_POST['txtid']))"; – volkinc May 15 '14 at 14:59
Keep on getting the error Parse error: syntax error, unexpected '"'in line 20 :-/ (The line the SQL statement is) – JiraiyaUK May 15 '14 at 15:14

PHP MySQL (UPDATE)

1 Answers1