从数组解析值到数据库PHP

Question

I have this sortable list which makes it possible for the user to sort things by dragging them in the particular place they want it to be. The place the item is dragged to, is then put into a database so that it remembers the exact location the next time the user logs in. For that I have this:

$item_number = 0;
$rowsize = 12;
$itemArray = array();
$finalArray = array();
$results = 0;

for ($i = 0; $i < $rowsize; $i++) {
    $stmt = $mysqli->stmt_init();
    $stmt->prepare('SELECT z, name FROM house_room1 INNER JOIN objects ON house_room1.object_id=objects.object_id WHERE house_room1.ref_id = ?');
    $stmt->bind_param('i', $i);
    if ($stmt->execute()) {
        $stmt->bind_result($z, $name);
        while ($stmt->fetch()) {
            $results = 1;
            $itemArray['number'] = $item_number;
            $itemArray['name'] = $name;
            $itemArray['ref_id'] = $z;
            array_push($finalArray, $itemArray);
        }
    }
    else {
        echo 'Something went terribly wrong' . $mysqli->error;
    }

    $stmt->close();
    $item_number++;
}

if ($results == 1) {
    aasort($finalArray, "ref_id");
    foreach($finalArray as $arr) {
        echo '<li id="item-' . $arr['number'] . '" class="ui-state-default"><span class="ui-icon ui-icon-arrowthick-2-n-s"></span>' . $arr['name'] . ' 
                                        <img class="rotate" src="images/house/other/settings.jpg" onclick="rotateObject()"></li>';
    }
}

// create a function for sorting

function aasort(&$array, $key)
{
    $sorter = array();
    $ret = array();
    reset($array);
    foreach($array as $ii => $va) {
        $sorter[$ii] = $va[$key];
    }

    asort($sorter);
    foreach($sorter as $ii => $va) {
        $ret[$ii] = $array[$ii];
    }

    $array = $ret;
}

As you can see, there is also a button that you can press on, which has to send some data to the database by the use of ajax. The function is named rotateObject() and that ajax call is here:

function rotateObject()
{
var xmlhttp;
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("item").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("POST","database/update_settings_rotate.php",true);
xmlhttp.send();
}

And after that call has been made, the database has to insert the arr['item_number'] value from the code inside a database:

require ('../includes/db_connect.php');

/* Register a prepared statement */

if ($stmt = $mysqli->prepare('UPDATE house_room1 SET rotation = ? WHERE ref_id = ?')) {
    /* Bind parametres */
    $stmt->bind_param('ii', $i, $item_number);
    $i = 5;
    $item_number = 3;
    /* Execute the query */
    $stmt->execute();
    /* Close statement */
    $stmt->close();
}
else {
    /* Something went wrong */
    echo 'Something went terribly wrong' . $mysqli->error;
}

Right now, the item_numer is set equal to 3, for some testing. It works as it should, but this variable should be equal to the element the user presses on, on the sorted list. Which is arr['number']. The problem is that this array doesn't seem to hold any values any longer. I don't know how I can parse that value so the correct element gets changed in the database. Uhm hope I made everything clear, feel free to ask for more information, thanks in advance.

Answer 1

From what I understand, you need to send the item_number of the element clicked.
For that, we need to send the item_number as a parameter in the AJAX call.
The below modification to your <img> element allows you to get the item_number in the rotateObject() function.

<img class="rotate" id="img_'.$arr['number'].'" src="images/house/other/settings.jpg" onclick="rotateObject(this)">
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^ Modified here                                               ^^^^ and here

Now the rotateObject() function can access the item_number and can send it to the server.
Here I have sent the item_number as a value to the key "item_id"-

function rotateObject(e)
{
    //e is handler which contains info about the item clicked. From that we can obtain the image id.
    //since the id are of the form img_123(some number), we need to extract only the number.
    var img_id = e.id.split("_")[1];
    var xmlhttp;
    if (window.XMLHttpRequest)
    {// code for IE7+, Firefox, Chrome, Opera, Safari
        xmlhttp=new XMLHttpRequest();
    }
    else
    {// code for IE6, IE5
        xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
    }
    xmlhttp.onreadystatechange=function(){
        if (xmlhttp.readyState==4 && xmlhttp.status==200)
        {
            document.getElementById("item").innerHTML=xmlhttp.responseText;
        }
    }
    xmlhttp.open("POST","database/update_settings_rotate.php",true);
    xmlhttp.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded');
    xmlhttp.send("item_id="+encodeURIComponent(img_id));
}

So in your update_settings_rotate.php, item_number is obtained -

if(isset($_POST['item_id'])){
    $item_number = $_POST['item_id'];
    //Rest of your code...
}

Hopefully, this helped.