PHP update else insert

  • seularts
  • Graduate
  • Graduate
  • User avatar
  • Posts: 153
  • Loc: Romania

Post 3+ Months Ago

Hi there,

I have a small issue with my code. I don't know how to edit the code to insert my values in the DB if they can't be updated (if they don't exist in the table of the DB).

Code: [ Select ]
    $SQL1 = "UPDATE tdp_base SET bid_value='$val1' WHERE bid_class='Bid_1'";
    $SQL2 = "UPDATE tdp_base SET bid_value='$val2' WHERE bid_class='Bid_2'";
    $SQL3 = "UPDATE tdp_base SET bid_value='$val3' WHERE bid_class='Bid_3'";
    $SQL4 = "UPDATE tdp_base SET bid_value='$val4' WHERE bid_class='Bid_4'";
    $SQL5 = "UPDATE tdp_base SET bid_value='$val5' WHERE bid_class='Bid_5'";
    $SQL6 = "UPDATE tdp_base SET bid_value='$val6' WHERE bid_class='Bid_6'";
    $SQL7 = "UPDATE tdp_base SET bid_value='$val7' WHERE bid_class='Bid_7'";

$result1 = mysql_query($SQL1);
$result2 = mysql_query($SQL2);
$result3 = mysql_query($SQL3);
$result4 = mysql_query($SQL4);
$result5 = mysql_query($SQL5);
$result6 = mysql_query($SQL6);
$result7 = mysql_query($SQL7);
  1.     $SQL1 = "UPDATE tdp_base SET bid_value='$val1' WHERE bid_class='Bid_1'";
  2.     $SQL2 = "UPDATE tdp_base SET bid_value='$val2' WHERE bid_class='Bid_2'";
  3.     $SQL3 = "UPDATE tdp_base SET bid_value='$val3' WHERE bid_class='Bid_3'";
  4.     $SQL4 = "UPDATE tdp_base SET bid_value='$val4' WHERE bid_class='Bid_4'";
  5.     $SQL5 = "UPDATE tdp_base SET bid_value='$val5' WHERE bid_class='Bid_5'";
  6.     $SQL6 = "UPDATE tdp_base SET bid_value='$val6' WHERE bid_class='Bid_6'";
  7.     $SQL7 = "UPDATE tdp_base SET bid_value='$val7' WHERE bid_class='Bid_7'";
  8. $result1 = mysql_query($SQL1);
  9. $result2 = mysql_query($SQL2);
  10. $result3 = mysql_query($SQL3);
  11. $result4 = mysql_query($SQL4);
  12. $result5 = mysql_query($SQL5);
  13. $result6 = mysql_query($SQL6);
  14. $result7 = mysql_query($SQL7);


So for example, if the attribute Bid_1 in bid_value, doesn't exist in the table I want to insert the whole row like this:

Code: [ Select ]
$SQL1_insert = "INSERT INTO tdp_base (bid_class,bid_value) VALUES ('Bid_1','$val1')";
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • Bigwebmaster
  • Site Admin
  • Site Admin
  • User avatar
  • Posts: 9090
  • Loc: Seattle, WA & Phoenix, AZ

Post 3+ Months Ago

First make sure you have you have an index setup as either primary or unique on whatever value you are wanting to be unique so that MySQL knows that there should only exist one entry per unique value. Once you make sure that is set, then you can typically do this (assuming your field1 is the unique key):

SQL Code: [ Select ]
INSERT INTO some_table (field1, field2) VALUES ('value1', 'value2')
ON DUPLICATE KEY UPDATE field2 = 'value2'
  1. INSERT INTO some_table (field1, field2) VALUES ('value1', 'value2')
  2. ON DUPLICATE KEY UPDATE field2 = 'value2'


So what happens here is it will insert the new values in your database, but if your unique key already exists, then instead of inserting it will instead update where that key already exists.
  • seularts
  • Graduate
  • Graduate
  • User avatar
  • Posts: 153
  • Loc: Romania

Post 3+ Months Ago

Ok, this works grate for one value, but the thing is that i will have multiple users inserting/updating their values in this table, and the primary key will just add up on the way. The thing is that I want to input 7 values into the DB all at once, but if they don't exist I should be able to create all 7 rows for each individual users that submits them.

Obviously the name of the user will be inserted into an index in the table for each row, of the inserted/updated value, to distinguish his 7 values from the other users.
  • Bigwebmaster
  • Site Admin
  • Site Admin
  • User avatar
  • Posts: 9090
  • Loc: Seattle, WA & Phoenix, AZ

Post 3+ Months Ago

I hope I am understanding correctly, basically you are saying you may want to insert multiple values at once and if it already exists then update it. Very similar to what I wrote above except with multiple values. Here is an example of how you would do that:

SQL Code: [ Select ]
INSERT INTO some_table (field1, field2)
VALUES ('value1', 'value2'), ('anothervalue1', 'anothervalue2'), ('morestuff1', 'morestuff2')
ON DUPLICATE KEY UPDATE field2 = VALUES(field2)
  1. INSERT INTO some_table (field1, field2)
  2. VALUES ('value1', 'value2'), ('anothervalue1', 'anothervalue2'), ('morestuff1', 'morestuff2')
  3. ON DUPLICATE KEY UPDATE field2 = VALUES(field2)


The areas I wrote field2, that would be with whatever the name of the second field is, with your examples above you would put exactly "bid_value" for field2. Make sense?
  • seularts
  • Graduate
  • Graduate
  • User avatar
  • Posts: 153
  • Loc: Romania

Post 3+ Months Ago

Well yes, kind of, but I found a simpler way. Because I couldn't define the user as a primary key, i made a filter like this:

Code: [ Select ]

        if($find['user']!=$the_name){
            $SQL1 = "INSERT INTO tdp_base (bid_class, bid_no, bid_value, prod_class, user) VALUES ('Bid_1', '1', '$val1', 'Carcasa', '$the_name')";
            $SQL2 = "INSERT INTO tdp_base (bid_class, bid_no, bid_value, prod_class, user) VALUES ('Bid_2', '2', '$val2', 'Carcasa', '$the_name')";
            $SQL3 = "INSERT INTO tdp_base (bid_class, bid_no, bid_value, prod_class, user) VALUES ('Bid_3', '3', '$val3', 'Carcasa', '$the_name')";
            $SQL4 = "INSERT INTO tdp_base (bid_class, bid_no, bid_value, prod_class, user) VALUES ('Bid_4', '4', '$val4', 'Carcasa', '$the_name')";
            $SQL5 = "INSERT INTO tdp_base (bid_class, bid_no, bid_value, prod_class, user) VALUES ('Bid_5', '5', '$val5', 'Carcasa', '$the_name')";
            $SQL6 = "INSERT INTO tdp_base (bid_class, bid_no, bid_value, prod_class, user) VALUES ('Bid_6', '6', '$val6', 'Carcasa', '$the_name')";
            $SQL7 = "INSERT INTO tdp_base (bid_class, bid_no, bid_value, prod_class, user) VALUES ('Bid_7', '7', '$val7', 'Carcasa', '$the_name')";
        }else{
            $SQL1 = "UPDATE tdp_base SET bid_value='$val1' WHERE prod_class='Carcasa' AND bid_class='Bid_1' AND user='$the_name'";
            $SQL2 = "UPDATE tdp_base SET bid_value='$val2' WHERE prod_class='Carcasa' AND bid_class='Bid_2' AND user='$the_name'";
            $SQL3 = "UPDATE tdp_base SET bid_value='$val3' WHERE prod_class='Carcasa' AND bid_class='Bid_3' AND user='$the_name'";
            $SQL4 = "UPDATE tdp_base SET bid_value='$val4' WHERE prod_class='Carcasa' AND bid_class='Bid_4' AND user='$the_name'";
            $SQL5 = "UPDATE tdp_base SET bid_value='$val5' WHERE prod_class='Carcasa' AND bid_class='Bid_5' AND user='$the_name'";
            $SQL6 = "UPDATE tdp_base SET bid_value='$val6' WHERE prod_class='Carcasa' AND bid_class='Bid_6' AND user='$the_name'";
            $SQL7 = "UPDATE tdp_base SET bid_value='$val7' WHERE prod_class='Carcasa' AND bid_class='Bid_7' AND user='$the_name'";
        }
        $result1 = mysql_query($SQL1);
        $result2 = mysql_query($SQL2);
        $result3 = mysql_query($SQL3);
        $result4 = mysql_query($SQL4);
        $result5 = mysql_query($SQL5);
        $result6 = mysql_query($SQL6);
        $result7 = mysql_query($SQL7);
  1.         if($find['user']!=$the_name){
  2.             $SQL1 = "INSERT INTO tdp_base (bid_class, bid_no, bid_value, prod_class, user) VALUES ('Bid_1', '1', '$val1', 'Carcasa', '$the_name')";
  3.             $SQL2 = "INSERT INTO tdp_base (bid_class, bid_no, bid_value, prod_class, user) VALUES ('Bid_2', '2', '$val2', 'Carcasa', '$the_name')";
  4.             $SQL3 = "INSERT INTO tdp_base (bid_class, bid_no, bid_value, prod_class, user) VALUES ('Bid_3', '3', '$val3', 'Carcasa', '$the_name')";
  5.             $SQL4 = "INSERT INTO tdp_base (bid_class, bid_no, bid_value, prod_class, user) VALUES ('Bid_4', '4', '$val4', 'Carcasa', '$the_name')";
  6.             $SQL5 = "INSERT INTO tdp_base (bid_class, bid_no, bid_value, prod_class, user) VALUES ('Bid_5', '5', '$val5', 'Carcasa', '$the_name')";
  7.             $SQL6 = "INSERT INTO tdp_base (bid_class, bid_no, bid_value, prod_class, user) VALUES ('Bid_6', '6', '$val6', 'Carcasa', '$the_name')";
  8.             $SQL7 = "INSERT INTO tdp_base (bid_class, bid_no, bid_value, prod_class, user) VALUES ('Bid_7', '7', '$val7', 'Carcasa', '$the_name')";
  9.         }else{
  10.             $SQL1 = "UPDATE tdp_base SET bid_value='$val1' WHERE prod_class='Carcasa' AND bid_class='Bid_1' AND user='$the_name'";
  11.             $SQL2 = "UPDATE tdp_base SET bid_value='$val2' WHERE prod_class='Carcasa' AND bid_class='Bid_2' AND user='$the_name'";
  12.             $SQL3 = "UPDATE tdp_base SET bid_value='$val3' WHERE prod_class='Carcasa' AND bid_class='Bid_3' AND user='$the_name'";
  13.             $SQL4 = "UPDATE tdp_base SET bid_value='$val4' WHERE prod_class='Carcasa' AND bid_class='Bid_4' AND user='$the_name'";
  14.             $SQL5 = "UPDATE tdp_base SET bid_value='$val5' WHERE prod_class='Carcasa' AND bid_class='Bid_5' AND user='$the_name'";
  15.             $SQL6 = "UPDATE tdp_base SET bid_value='$val6' WHERE prod_class='Carcasa' AND bid_class='Bid_6' AND user='$the_name'";
  16.             $SQL7 = "UPDATE tdp_base SET bid_value='$val7' WHERE prod_class='Carcasa' AND bid_class='Bid_7' AND user='$the_name'";
  17.         }
  18.         $result1 = mysql_query($SQL1);
  19.         $result2 = mysql_query($SQL2);
  20.         $result3 = mysql_query($SQL3);
  21.         $result4 = mysql_query($SQL4);
  22.         $result5 = mysql_query($SQL5);
  23.         $result6 = mysql_query($SQL6);
  24.         $result7 = mysql_query($SQL7);


So basically if the user is not found in the table, I create the rows, if it exists then it updates the values where the user is defined. It looks ugly, I know, but it works like a charm :)
  • Bigwebmaster
  • Site Admin
  • Site Admin
  • User avatar
  • Posts: 9090
  • Loc: Seattle, WA & Phoenix, AZ

Post 3+ Months Ago

Glad you found a solution that works for you. I am curious to why you cannot define the user field as a primary or unique key?
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8397
  • Loc: USA

Post 3+ Months Ago

PHP Code: [ Select ]
$sql = array('INSERT INTO...', 'INSERT INTO...', 'INSERT INTO...');
$result = array();
foreach($sql as $query)
{
    $result[] = mysql_query($query);
}
  1. $sql = array('INSERT INTO...', 'INSERT INTO...', 'INSERT INTO...');
  2. $result = array();
  3. foreach($sql as $query)
  4. {
  5.     $result[] = mysql_query($query);
  6. }

I hope that makes sense to what I'm suggesting.

Post Information

  • Total Posts in this topic: 7 posts
  • Users browsing this forum: No registered users and 90 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.