Update and/or Insert an entry

[acctman]

 

i'm using this code in php to insert IP info into a table

sql_query("INSERT INTO $logstable VALUES('$line[m_id]','$line['m_user']',".time().",'$ip')");

 

If I was to use UPDATE would it insert into the table even if there is no entry to update? If yes, how do I change that INSERT query to work with an UPDATE

[fanfavorite]

No because UPDATE requires WHERE values.  You need to run a check:

 

$q = mysql_query("SELECT * FROM $logstable WHERE ip = '$ip'")

if (mysql_num_rows($q) > 0) {

mysql_query("UPDATE $logstable VALUES('$line[m_id]','$line['m_user']',".time().",'$ip') WHERE ip = '$ip'");

} else {

mysql_query("INSERT INTO $logstable VALUES('$line[m_id]','$line['m_user']',".time().",'$ip')");

}

[acctman]

No because UPDATE requires WHERE values.  You need to run a check:

 

$q = mysql_query("SELECT * FROM $logstable WHERE ip = '$ip'")

if (mysql_num_rows($q) > 0) {

mysql_query("UPDATE $logstable VALUES('$line[m_id]','$line['m_user']',".time().",'$ip') WHERE ip = '$ip'");

} else {

mysql_query("INSERT INTO $logstable VALUES('$line[m_id]','$line['m_user']',".time().",'$ip')");

}

 

have you ever used INSERT ON DUPLICATE KEY UPDATE, is that better for performance?

[acctman]

what is the actual DUPLICATE KEY? is that the PRIMARY KEY (field) in the table?

 

/* old insert replacing this one */
sql_query("INSERT INTO $logstable VALUES('$line[m_id]','$line['m_user']',".time().",'$ip')")


/* new insert with update */
sql_query("INSERT INTO $logstable VALUES('$line[m_id]','$line['m_user']',".time().",'$ip') ON DUPLICATE KEY UPDATE log_user='$line['m_user']',log_time='".time()."',log_ip='$ip'");

[acctman]

i'm receiving this error:

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING

[samshel]

i think fanfavorite is correct you will have to fire a query to check if it exists.

 

if IP was your primary key you could have used REPLACE INTO..