Keeps making new rows when it shouldn't

SeanKann

Running this in a batch file on windows to send a update to my server with the new ip info from the vpn.

It's putting in the right info but it keeps making new rows all the time when the IP address is the same. If I change the IP address in the csv file it won't make a new row

Thoughts?


<?php

$hostname = file_get_contents("hostname.csv");
$vpn_ip = file_get_contents("hostip.csv");

$dbhost = "####";
$dbuser = "####";
$dbpass = "####";
$dbname = "####";
$table = "computers";

mysql_connect($dbhost,$dbuser,$dbpass) or die("Unable to connect to database");
@mysql_select_db("$dbname") or die("Unable to select database $dbname");

$query = mysql_query("UPDATE $table SET vpn_ip = '$vpn_ip' WHERE hostname = '$hostname';");
if (mysql_affected_rows()== 0) {
	$query = mysql_query("INSERT INTO $table VALUES ('', '$hostname', '$vpn_ip');");
}

mysql_close();
?>

Just to go over. Some computer names will be the same. So I just want their IP address to update only. If that computer name doesn't exist. Make a new row with the computer name and the current IP

But if their computer name hasn't changed I just want it to update the IP address.

TIA

johanafm

SeanKann;10997632 wrote:

@mysql_select_db("$dbname") or die("Unable to select database $dbname");

Why the error supression? If you can't select a database, you won't be able to execute your queries either.

Also, you should insert checks that the connection to the db was successful, followed by check if selecting schema was successful, followed by checks if each separate query was successful.

If you update a row with the same information as before (i.e. host and ip hasn't changed) you will affect 0 rows, thus proceeding to insert. Since you are using MySQL, you could use REPLACE INTO, assuming you have a unique index for (host), or INSERT INTO ... ON DUPLICATE KEY ...

SeanKann

johanafm;10997639 wrote:
Why the error supression? If you can't select a database, you won't be able to execute your queries either.

Also, you should insert checks that the connection to the db was successful, followed by check if selecting schema was successful, followed by checks if each separate query was successful.

If you update a row with the same information as before (i.e. host and ip hasn't changed) you will affect 0 rows, thus proceeding to insert. Since you are using MySQL, you could use REPLACE INTO, assuming you have a unique index for (host), or INSERT INTO ... ON DUPLICATE KEY ...

You lost me on the REPLACE INTO and INSERT INTO ON DUPLICATE KEY.

That is something new that I haven't done. From the examples that I could find in a google search, it doesn't make a lot of sense to me right now. I will have to do some more reading on it to understand it.

Derokorian

If hostname is set to be a unique key (commonly a primary key works best) then you can do:

REPLACE $table (hostname,vpn_ip) VALUES ('$hostname','$vpn_ip')

And if the unique key hostname already exists then it will update that row, if it doesn't exist then it will insert a new row.

Weedpacket

SeanKann wrote:
From the examples that I could find in a google search,

The first place to look would have been 12.2.7. [MySQL] REPLACE Syntax and 12.2.5.3. [MySQL] INSERT ... ON DUPLICATE KEY UPDATE Syntax before slogging through Google hits of often-dubious provenance.

NogDog

Weedpacket;10997679 wrote:
The first place to look would have been 12.2.7. [MySQL] REPLACE Syntax and 12.2.5.3. [MySQL] INSERT ... ON DUPLICATE KEY UPDATE Syntax before slogging through Google hits of often-dubious provenance.

+1 just for the use of the phrase "often-dubious provenance." 🙂