add new or over right row in mysql

[daverichris]

Hello,

 

When a user updates their profile, I want the mysql action to check for a matching '$user' in the database, if there is none add a new row, if there is one simply update the existing row. This is the code i'm using but it wont work, it just adds another row regardless of a matching '$user', any ideas whats wrong with it?

 

<?php

session_start();

include 'english.php';

 

if (!isset($_SESSION['user']))

die("<br /><br />You need to login to view this page");

$user = $_SESSION['user'];

 

echo "<h3>Edit your housing search</h3>";

 

        //opend database

$connect = mysql_connect("$dbhost","$dbuser","$dbpass");

mysql_select_db("$dbname"); //select database

 

    $write = mysql_query("INSERT INTO housing VALUES ('','$user','$rentorbuy','$min','$max','$townone','$working','$mintaxband','$maxtaxband')");

 

    $update = mysql_query("UPDATE housing SET ('$rentorbuy','$min','$max','$townone','$working','$mintaxband','$maxtaxband' WHERE user='$user')");

 

    echo "<br><a href='/index.php?page=housing'> check housing</a>";

 

?>

 

thanks for any help

[Pikachu2000]

You can put a UNIQUE index on `user` if it doesn't already have it, and use INSERT INTO . . . ON DUPLICATE KEY UPDATE query syntax.

[gizmola]

What is wrong with it?

 

Well you do an insert followed by an update on every execution. 

 

What you need here, is to query for the existence of the user.  You can either use mysql_num_rows() or do a SELECT count(*) as countof WHERE user... query.

 

Based on that result, you either insert the new row, or do your update.

 

Mysql does have a syntax -- http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html  that might work for you in one query.  What you will need to try it out is to create a unique index on the user column in the table.

 

You'll need to implement one or the other approaches.