Insert and / or update

[liquid79]

The problem: An example:

 

I have a form that lists names and has a box to input a price such as:

 

Bob £

Mike £

John £

Fred £

Barney £

Tod £

 

If a user only fills out 2 of the 6 prices, say he puts BOb £10 and Mike £4 this then will only insert the ones he filled out to save inserting loads of blank price rows.

 

Now i want to update and / or insert new rows.

 

so the user goes back and updates Bob to £12 and adds Tod £2.

 

So when he clicks submit it checks to see if the row is already in the database and then it can say UPDATE blah blah.

 

Then i need to carry on and say if you cant find just Tod but the rest you could, do an insert statement just for Tod.

 

 

Table looks like:

 

username  Name    Price

 

liquid79  Bob      £12

 

Some example code:

 

<?php

$username =  get_username ( $_SESSION['user_id'] );

$name=$_POST['name'];
$price=$_POST['price'];


$names = mysql_query("SELECT * from fruits WHERE username = '$username'"); 
while ($row = mysql_fetch_object($names)) {

if ( $username == $row->username) {


		$updates = "UPDATE `fruits` SET `name`='".$name."', `price`='".$price."'  WHERE `username` ='".$username."' AND `name`='".$name."'";
		mysql_query($updates) or die(mysql_error());
}

else {

 $insert="Insert into fruits (username, name, price values('$username','$name','$price')";
mysql_query($insert) or die(mysql_error());
}

} 

?>

 

I could be missing something simple here as i couldnt fine the problem in the search, please note the code is not exactly as above but demonstrates roughly what i have. Thanks

 

[revraz]

You don't state what the actual problem or error is.

 

And if you don't post the Exact code, there is no way for us to help you even if you did say what the the problem was.

[liquid79]

Ah sorry i did notice that but it just said i couldnt edit it! .. the problem is it will only do one of things either update or insert but i want it to do both things.

 

 

ie. if it finds liquid79, Bob already in the database update that row with the new price if the price has changed.

 

Next

 

Find liquid79 Tod and if it cant find that row INSERT the row.

 

Hope this helps. Thanks

[trq]

You might want to look into using mysql's REPLACE instead of going about it as you are.

[liquid79]

Yes thanks thorpe, so i need to use REPLACE, which i have just looked at and says you need a unique key. Currently i don’t have any unique key so i guess ill have to make a new column to store something else that will be unique for each user and row.

 

But if there is no easier way of getting around the problem i will have to go for that! Thanks for your help!

[revraz]

You don't have a ID field or your username field isn't unique?

[tinker]

similar to Thorpe's suggestion is insert-on-duplicate, but you do still need a unique field, i'd of thought the name was though?

[redarrow]

trie that

<?php session_start(); //<<< must always have session_start()
//at the top off any used session page...

$username =  get_username ( $_SESSION['user_id'] );

$name=$_POST['name'];
$price=$_POST['price'];


$names = mysql_query("SELECT * from fruits WHERE username = '$username'"); 
while ($row = mysql_fetch_object($names)) {

if ( $username == $row->username) {


		$updates = "UPDATE fruits SET name='$name' , price='$price'  WHERE username ='$username' AND name='$name'";
		mysql_query($updates) or die(mysql_error());
}

else {

 $insert="Insert into fruits (username, name, price values('$username','$name','$price')";
mysql_query($insert) or die(mysql_error());
}

} 

?>

 

 

[liquid79]

The name is unique until another user comes along and logs in and then adds his prices for Bob and Tod.

 

Then you will get

 

id  username  name  Price

 

1  Liquid79    Bob      £12

2  Liquid79    Tod      £10

3  Larry88      Bob      £12

4  Larry88      Tod      £10

 

but yes you are right there is an id column also, which is the primary key could i do a REPLACE on a primary key?

Something along the lines as:

 

$sql = "REPLACE INTO `fruits` SET `username`='".$username"', `name`='".$name."', `price`='".price."' WHERE  id = $id";

 

 

Thanks

[revraz]

That's how you should do it, it wouldn't be reliable by name right since there is more than one instance of a name?

[revraz]

Do you want to change All the Bobs regardless of who entered it?

 

So if you have 3 Bobs, do you want to change all 3 prices?

[liquid79]

Do you want to change All the Bobs regardless of who entered it?

 

So if you have 3 Bobs, do you want to change all 3 prices?

 

No just change it depending on the username (ie. the person who is logged in), I will look into replace more as this seems the pond i need to be fishing in and not the pond im in!

[trq]

All database tables should have at least an auto incrementing field as a primary key, they are very usefull.