Search a database for user input and updating the row

[Nasher99]

I want search my database for the users server to make sure if it is already in the database if it is i want it to update the number of downtimes for that server and the time of the last downtime.

The code I have written is as follows:

$connect = mysqli_connect("localhost", "Username", "Password","Test_downtime");

if (!$connect)
{
die("Connection failed: " . mysqli_connect_error());
}else
{
echo "Connected successfully\n";
}

$servername = $_GET["server_name"];
$downtime = $_GET["downtime"];

$time_now = time();
$result = mysqli_query($connect, "SELECT COUNT(*) FROM `Test_downtime`.`Downtime` WHERE `Server_Name` = '$servername'");
$row = mysqli_fetch_array($result);

// If no downtime have been reported before
if ($row[0] == 0){
$sql = mysqli_query($connect, "INSERT INTO `Test_downtime`.`Downtime` (ID, Server_name, First_downtime, Last_downtime, Num_of_downtime,Total_downtime) VALUES (NULL, '$servername', '$time_now','$time_now',1,'$downtime'); ");
if ($sql==true) {
echo $servername . " has has its first downtime recorded\n";
}
}



//If users is already in the database
else{
$numdowntime = ($row["Num_of_downtime"] + 1);
$id = ($row["ID"]);
$sqlupdate = "UPDATE `Test_downtime`.`Downtime` SET `Num_of_downtime` = $numdowntime, `Last_downtime` = now() WHERE `Server_Name` = '$servername'";

if ($sqlupdate == TRUE) {
echo "Oh No! " . $servername . " has had ". $numdowntime ." of downtimes" ;
}
} 

 The code works if the server is not already in the database the problem is that it always gives the error is the server is already in the database. I believe the problem is to do with the fact that  $row is an array and not a number. How do I solve this?

[dkub]

$numdowntime = ($row["Num_of_downtime"] + 1);
$id = ($row["ID"]);

 

Where are these values coming from? You're not selecting them.

 

Honestly, though, there's so much wrong with that code I don't even know where to start.

[dkub]

if (!$connect)
{
    die("Connection failed: " . mysqli_connect_error());
}else
{
    echo "Connected successfully\n";
}

 

Don't do this. Don't display connection errors to the user; they shouldn't need to care and you don't want to be exposing internal errors to the outside world. Log errors, throw exceptions, handle them gracefully.

 

$servername = $_GET["server_name"];
$downtime = $_GET["downtime"];

$time_now = time();
$result = mysqli_query($connect, "SELECT COUNT(*) FROM `Test_downtime`.`Downtime` WHERE `Server_Name` = '$servername'");

 

Don't ever trust user input. You're leaving yourself wide open to SQL injection here. Use prepared statements. While you're at it, look at PDO instead of mysqli. It's far more pleasant to use.

 

$sql = mysqli_query($connect, "INSERT INTO `Test_downtime`.`Downtime` (ID, Server_name, First_downtime, Last_downtime, Num_of_downtime,Total_downtime) VALUES (NULL, '$servername', '$time_now','$time_now',1,'$downtime'); ");

 

More SQL injection and you're using GET requests for what should be a POST.

 

    $numdowntime = ($row["Num_of_downtime"] + 1);
    $id = ($row["ID"]);
    $sqlupdate = "UPDATE `Test_downtime`.`Downtime` SET `Num_of_downtime` = $numdowntime, `Last_downtime` = now() WHERE `Server_Name` = '$servername'";

 

Aside from the $row["Num_of_downtime"] not existing, you don't need PHP to increment this. SQL can do it just fine.

$sqlupdate = "UPDATE `Test_downtime`.`Downtime` SET `Num_of_downtime` = `Num_of_downtime` + 1, `Last_downtime` = NOW() WHERE `Server_Name` = ?";

[mac_gyver]

you should actually just INSERT a new row for each instance of downtime. this will give you a history/record for reporting purposes. to get a count of the down time, you would just use COUNT(*) in the query, with GROUP BY Server_name.

 

if you do have a need to INSERT/UPDATE a single row, such as if this is a programming assignment, you would use a single INSERT ... ON DUPLICATE KEY UPDATE ... query, with the Server_name column defined as a unique index. there's no need to first select data to decide if you should insert it or update it and in fact by first trying to select data, there's a race condition when multiple concurrent users try to manage the data, that will cause incorrect results/lost data.

[Nasher99]

Thank you very much Dkuh.

I just have one question:

If 

$sqlupdate = "UPDATE `Test_downtime`.`Downtime` SET `Num_of_downtime` = `Num_of_downtime` + 1, `Last_downtime` = NOW() WHERE `Server_Name` = ?";

Works instead of using the PHP would the same be possible for:

$sqlupdate = "UPDATE `Test_downtime`.`Downtime` SET `Total_downtime` = `Total_downtime` + $downtime WHERE `Server_Name` = ?";

[dkub]

you should actually just INSERT a new row for each instance of downtime. this will give you a history/record for reporting purposes. to get a count of the down time, you would just use COUNT(*) in the query, with GROUP BY Server_name.

 

if you do have a need to INSERT/UPDATE a single row, such as if this is a programming assignment, you would use a single INSERT ... ON DUPLICATE KEY UPDATE ... query, with the Server_name column defined as a unique index. there's no need to first select data to decide if you should insert it or update it and in fact by first trying to select data, there's a race condition when multiple concurrent users try to manage the data, that will cause incorrect results/lost data.

 

This is a much better solution.