php mysql data doesn't appear in table

Question

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);                
        }

even so, php isn't updating mysql_query anymore, need to think about long term — lusketeer
– lusketeer, Commented Aug 2, 2012 at 13:21
Please don't use mysql_* functions in new code. They were removed from PHP 7.0.0 in 2015. Instead, use prepared statements via PDO or MySQLi. See Why shouldn't I use mysql_* functions in PHP? for more information. — PeeHaa
– PeeHaa, Commented Aug 2, 2012 at 13:25

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

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]?

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 2, 2012 at 13:18

Travis Pessetto

3,2984 gold badges29 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user1060187 Over a year ago

No, the quotes aren't required here. I am able to update two WS_ID's but not 1 so that can't be the issue

Travis Pessetto Over a year ago

When you have two there is no one with a blank one. When you have one It looks like you are trying to assigning it to the other thing assigned. So you need quotes to show that that value is indeed blank.

user1060187 Over a year ago

Where should the quotes be around?

Travis Pessetto Over a year ago

around ``` $WS_ID2[1]``` but just a second for me to research mysqli and I will give you a better solution resistant to sql injection.

user1060187 Over a year ago

I put the quotes around the $WS_ID[1] but still not change

|

Collectives™ on Stack Overflow

php mysql data doesn't appear in table

1 Answer 1

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

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

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

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related