Add and update row in database

Question

I am storing some data from jQuery UI onto a database....

$( ".MYELEMENT" ).draggable({  
    stop: function(event, ui) {
        var pos_x = ui.position.left;
        var pos_y = ui.position.top;
        var divid = ui.helper.attr("id");

        jQuery.ajax({
            type: "POST",
            url: window.ajaxurl,
            data: { "action": "myAjax", id: divid, x: pos_x, y: pos_y }})
            .done(function( msg ) {
        });
    }  
});

Notice those variables, I pass those to my script..

function myAjax() { 
    global $wpdb;

    // The data here comes from an ajax call
    $term_id = 100;
    $name = $_POST['id']; 
    $slug = $_POST['x'];
    $term_group = $_POST['y']; 

    // I'm adding into wp_terms a default table (for testing)... 
    $query = "INSERT INTO $wpdb->terms (term_id, name, slug, term_group)
              VALUES (%s, %s, %s, %s)"; 

    // Here I insert the ajax data into the columns 
    $wpdb->query($wpdb->prepare($query, $term_id, $name, $slug, $term_group));
    $wpdb->update("$wpdb->terms", 
                   array('slug' => $_POST['x'], 'term_group' => $_POST['y']),
                   array('name' => $_POST['id']));
    die();
}

The $_POST['id']; is the ID of this HTML element...

<div class="MYELEMENT draggable" id="element"></div>

this ID gets stored inside the $name variable above..

My table looks like

|term_id|   |name|      |slug|      |term_group|
|100|     |element| |X Value here| |Y value here|

you see in the update function I target slug and term_group and update them with the ajax value WHERE name is $_POST['id'] <--- this is element in my table.. All is well... BUT

What if I have a new $_POST['id']?

<div class="MYELEMENT draggable" id="element2"></div>// notice the ID changes

The ID changes and will pass into my ajax value, how do I create a new row and continue to update that rows X and Y values? and I will continue to keep adding new elements..

<div class="MYELEMENT draggable" id="element3"></div>
<div class="MYELEMENT draggable" id="element4"></div>

Each div should have its own row.. this works when I manually change $term_id = 100; for example when I drag <div class="MYELEMENT draggable" id="element"></div> this gets stored into the database like above

|term_id|   |name|      |slug|      |term_group|
|100|     |element| |X Value here| |Y value here|

BUT when I change $term_id = 100; to $term_id = 101; and drag <div class="MYELEMENT draggable" id="element2"></div> the row above sticks... and a new row is created

|term_id|   |name|      |slug|      |term_group|
|101|     |element2| |X Value here| |Y value here|

This is what I am trying to achieve but I don't want to manually change $term_id = 101;

Does this make sense?

OK SOLUTION I finally used a little logic and decided to give the ID's numbers..

     echo '<div class="MYELEMENT draggable" id="100"style="left: '.$x.'px; top: '.$y.'px;"></div>';

                echo '<div class="MYELEMENT draggable" id="102"style="left: '.$x.'px; top: '.$y.'px;"></div>';

                echo '<div class="MYELEMENT draggable" id="103"style="left: '.$x.'px; top: '.$y.'px;"></div>';

Then I run my update like this

    global $wpdb;

    //The data here comes from an ajax call
    $term_id = $_POST['id'];
    $name = $_POST['id']; 
    $slug = $_POST['x'];
    $term_group = $_POST['y']; 


    //Im adding into wp_terms a default table (for testing)... 
    $query = "INSERT INTO {$wpdb->terms} (term_id, name, slug, term_group) VALUES (%s, %s, %s, %s)"; 

    //Here I insert the ajax data into the columns 
    $wpdb->query($wpdb->prepare($query, $term_id, $name, $slug, $term_group));

    $wpdb->update("$wpdb->terms", array('slug' => $_POST['x'], 'term_group' => $_POST['y']), array('term_id' => $_POST['id']));



    die();

i suggest you to try to reduce the scope of your problem to find the solution, it's too confusing to understand the real problem as you are mixing ui and db issues — fmodos, Jul 17 '13 at 04:29
It's not a UI issue.. It's actually much much more simple than it looks im just trying to be as clear as possible.. summed up, I just want to add a new row for each draggable item, and update the x and y values of said row.. above I am just describing my layout, and showing that in fact I can update a row, I just cant add a new row if a new id exists.. — Mike, Jul 17 '13 at 04:41
Modifying my logic is okay, how would you add a new row for each element, and run an update on that row only changing the X and Y values.. — Mike, Jul 17 '13 at 04:45

score 0 · Answer 1 · answered Jul 17 '13 at 04:30

0

You have to use {} inside double quotes near $wpdb->terms of sql query to make it as variable_name.

$query = "INSERT INTO {$wpdb->terms} (term_id, name, slug, term_group) VALUES (%s, %s, %s, %s)";

answered Jul 17 '13 at 04:30

Yogesh Suthar

30,424
18
72
100

I did... but see, I dont see how a new row can be created when 100 is the $term_id = 100... that would have to increment and stick (incrementing only when there is a new $name) sticking when that names x and y values change... so what I am saying is the row term_id is 100... which ever is dragged first will take place of the name which is our $name – Mike Jul 17 '13 at 04:38
@Mike There is no `WHERE` condition in your `UPDATE`. – Yogesh Suthar Jul 17 '13 at 04:50
@Mike refer this link http://codex.wordpress.org/Class_Reference/wpdb#UPDATE_rows – Yogesh Suthar Jul 17 '13 at 05:28
Ok I tried applying it.. but I dont think I am thinking properly... let me fiddle with it some more... – Mike Jul 17 '13 at 05:30

Add and update row in database

1 Answers1

Linked