MySQL and PHP: Loop through table find value if it exists update if not insert new value

Question

the tables in my Database look like this:

http://abload.de/img/bildschirmfoto2014-07osj75.png

I need a function in php which loops through the table and finds the committed value in the "Translations" Column, if the value already exists it should update the Count value +1, if not insert a new row.

I hope someone can help me, because my PHP skills are not the best.

This is what I tried so far:

The Javascript Code:

var insertNewTranslation = function(word, translation) { 
    console.log("DBController, " +word+ " " + translation);

        $.ajax({
        type: 'GET',
        url: 'themes/tagging-theme/php/insertTranslation.php',
        data: {
                aktuelleswort: translation,
                uebersetzung: word
                },
        dataType: 'json',
        success : function(data){
            var tableNamen = data;
            console.log(tableNamen)



        },
        error : function(XMLHttpRequest, textStatus, errorThrown) {
            console.log("XMLHttpRequest", XMLHttpRequest);
            console.log("textStatus", textStatus);
            console.log("errorThrown", errorThrown);    
        }
    });

};

The php File looks like this:

<?php  
if( isset($_POST['aktuelleswort']) ) {
     $table=$_POST['aktuelleswort'];
}
else {
     $table="default";
}
if( isset($_POST['uebersetzung']) ) {
     $translation=$_POST['uebersetzung'];
}
else {
     $translation="default";
}


$con = mysql_connect('localhost', 'root', 'root');
if (!$con)
{
  die('Could not connect: ' . mysql_error());
}

mysql_select_db("bayerisches_lexikon", $con);

$sql = "SELECT _id, Translations, Count FROM $table";

$result = mysql_query($sql) OR die(mysql_error());
while ($row = mysql_fetch_assoc($result))
    $uebersetzungen[] = $row;

// mysql_query("INSERT INTO `bayerisches_lexikon`.`$table` (`Translations`, `Count`) 
//   VALUES ('" . $translation . "', '" . $count . "');");

mysql_close($con);

?>

Please share with us what you have tried. Set up a SQL fiddle, you may not need PHP to do what you need to do, you could possibly do it all in a MySQL stored procedure. — Jay Blanchard, Jul 01 '14 at 12:39
http://stackoverflow.com/questions/1218905/how-do-i-update-if-exists-insert-if-not-aka-upsert-or-merge-in-mysql — xQbert, Jul 01 '14 at 12:41

score 0 · Accepted Answer · answered Jul 01 '14 at 12:43

0

You need to set a unique key on the "Translation" column. Then use

insert into mytable (translations, count) values ('gewurztraminer',1) on duplicate key update count=count+1

Not php at all.

answered Jul 01 '14 at 12:43

ffflabs

17,166
5
51
77

score 0 · Answer 2 · answered Jul 01 '14 at 12:45

0

mysqli_num_rows()

You'll use the above function; if it returns 0 you'll do an INSERT; if it doesn't you'll perform an UPDATE.

answered Jul 01 '14 at 12:45

user3741598

297
1
12

MySQL and PHP: Loop through table find value if it exists update if not insert new value

2 Answers2