CODEWITHSUNDEEP
phpmysqlsql
user1060187
user1060187

Reputation: 977

php mysql data doesn't appear in table

At the moment, I am submitting two 'WS_ID' numbers in a text box with a space in between them. The SQL command that I have is able to update two results in the sql database but when there is only one 'WS_ID' submitted in the text box, nothing is updated. any help would be briliant. Many thanks.

         if(isset($_GET["Update"]))
        {   $DEP_TIME = $_GET["DEP_TIME"];
            $ARR_TIME = $_GET["ARR_TIME"];
            $TRAVEL_TIME = $_GET["TRAVEL_TIME"];
            $HRS_WORKED = $_GET["HRS_WORKED"];
            $INC_DEP_TIME = $_GET["INC_DEP_TIME"];
            $INC_LAB_DAY = $_GET["INC_LAB_DAY"];
            $LAB_DATE = $_GET["LAB_DATE"];
            $WS_ID = $_GET["WS_ID"];
            $WS_ID2 = explode(" ", $WS_ID);
            $SQL = "UPDATE `elecsys`.`worksheet_labour` SET DEP_TIME = $DEP_TIME, ARR_TIME = $ARR_TIME, TRAVEL_TIME = $TRAVEL_TIME, HRS_WORKED = $HRS_WORKED WHERE WS_ID = $WS_ID2[0] OR WS_ID = $WS_ID2[1]"; 
            $resultset2 = mysql_query($SQL);                
        }

Upvotes: 0

Views: 124

Answers (1)

Travis Pessetto
Travis Pessetto

Reputation: 3298

In your sql you probably need quotes around the value as it currently is. However, at this current time you are subject to SQL injection. You should use PDO or MYSQLI to avoid this.

When $WS_ID2[1] is blank you get WHERE WS_ID=value OR WS_ID = ; I don't believe that MYSQL knows that is suppose to be blank without quotes.

Code using MYSQLI, to prevent SQL injection and your code failing after the deprecated code as been removed from PHP:

$con = new mysqli($databasehost,$dbuser,$dbpassword);
if ($con->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " .  $mysqli->connect_error;
}
//Set all your variables here, omitted from code...
$stmt = $con->prepare("UPDATE `elecsys`.`worksheet_labour` SET DEP_TIME = ?, ARR_TIME = ?, TRAVEL_TIME = ?, HOURS_WORKED = ?, WHERE WS_ID = ? OR WS_ID = ?");
//bind using data according to http://www.php.net/manual/en/mysqli-stmt.bind-param.php
$stmt->bind_param('ssddii', $DEP_TIME, $ARR_TIME, $TRAVEL_TIME, $HRS_WORKED, $WS_ID2[0], $WS_ID2[1]);
$stmt->execute();
$stmt->close();
$con->close();

Also, my other thoughts is that if you only have one value for $WS_ID2, does PHP not give an out of bounds error for $WS_ID[1]?

Upvotes: 2

Related Questions