Jump to content

Search a database for user input and updating the row


Nasher99

Recommended Posts

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?

Link to comment
Share on other sites

$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.

Link to comment
Share on other sites

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` = ?";

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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` = ?";
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.