Jquery / AJAX sort function can not store data with dash in mysql

Question

I have a page that lists a set of products to sort by drag & drop. Everything here works fine.

I have an AJAX post to a sort-on-the-fly PHP file that saves the sort order in a Mysql table whenever the sort order is altered. This almost works fine.

The code looks like this:

The product sort file:

<script>
$(document).ready(function () {
    $('ul').sortable({
        scroll:true,
        helper: "clone",
        axis: 'y',
        stop: function (event, ui) {
            var data = $(this).sortable('serialize');
            $('#span').text(data);

            $.ajax({
            data: data,
            type: 'POST',
            url: '/admin/pages/sortonthefly.php'

        });
    }
    });
});
  </script>
<?
while($hent_produkter_over = mysql_fetch_array($hent_produkter)){
            $show = mysql_query("SELECT * FROM tblProdukt WHERE fldProduktNrID = 
            '$hent_produkter_over[prodref]' AND land = '$country' AND aktiv = 1");
            $hent_produkter_data = mysql_fetch_array($show);
            if($hent_produkter_data[fldID] != ""){  ?>

                <? $itemcontainer = $hent_produkter_data[fldProduktNrID]."mzm".$_GET[id]."mzm".$_GET[subid]."mzm".$_GET[country];?>             
                <li id="item-mzm<?=$itemcontainer?>" class="list-group-item hand">
                    <div class="row">
                        <div class="col-md-2">
                            <img src="/images/<?=$hent_produkter_data[fldProduktImg]?>" class="img-responsive">
                        </div>
                        <div class="col-md-10">
                            <?=$hent_produkter_data[fldProduktNavn]?><br/>
                            Varenummer: <?=$hent_produkter_data[fldProduktNrID]?>
                        </div> 
                    </div>
                </li>
            <?
                                                }
}
?>

The sort-on-the-fly file looks like this:

foreach ($_POST['item'] as $value) {

    $explode = explode("mzm", $value);
mysql_query("UPDATE b_kat_prod SET sortering = $i WHERE prodref = '$explode[1]' AND katref = '$explode[2]' AND country = '$explode[4]' ");

}

The updater works but when the $explode[1] contains a dash (-) the updater fails. I simply can't seem to get it to update when the $explode[1] has a - in it.

The data parsed in the product sort file could look like this:

<li id="item-mzm6820mzm2mzmNaNmzmdk" class="list-group-item hand">
                    <div class="row">
                        <div class="col-md-2">
                            <img src="/images/6820.jpg" class="img-responsive">
                        </div>
                        <div class="col-md-10">
                         Alu værksteds donkraft 2,5 T.<br/>
                            Varenummer: 6820                        </div> 
                    </div>
                </li>


                <li id="item-mzm6815mzm2mzmNaNmzmdk" class="list-group-item hand">
                    <div class="row">
                        <div class="col-md-2">
                            <img src="/images/6815.jpg" class="img-responsive">
                        </div>
                        <div class="col-md-10">
                         Alu værksteds donkraft 1,25 T.<br/>
                            Varenummer: 6815                        </div> 
                    </div>
                </li>


                <li id="item-mzmR6815-21mzm2mzmNaNmzmdk" class="list-group-item hand">
                    <div class="row">
                        <div class="col-md-2">
                            <img src="/images/1463467375.jpg" class="img-responsive">
                        </div>
                        <div class="col-md-10">
                         Kardanled for 1,25 ton donkraft. <br/>
                            Varenummer: R6815-21                        </div> 
                    </div>
                </li>


                <li id="item-mzmR6820-UJmzm2mzmNaNmzmdk" class="list-group-item hand">
                    <div class="row">
                        <div class="col-md-2">
                            <img src="/images/1463467386.jpg" class="img-responsive">
                        </div>
                        <div class="col-md-10">
                         Kardanled for 2,5 ton donkraft. <br/>
                            Varenummer: R6820-UJ                        </div> 
                    </div>
                </li>


                <li id="item-mzmR6815-pakmzm2mzmNaNmzmdk" class="list-group-item hand">
                    <div class="row">
                        <div class="col-md-2">
                            <img src="/images/1463466442.jpg" class="img-responsive">
                        </div>
                        <div class="col-md-10">
                         Olie pakning kit sæt for 1,25 ton donkraft.<br/>
                            Varenummer: R6815-pak                       </div> 
                    </div>
                </li>


                <li id="item-mzmR6820-pakmzm2mzmNaNmzmdk" class="list-group-item hand">
                    <div class="row">
                        <div class="col-md-2">
                            <img src="/images/1463466450.jpg" class="img-responsive">
                        </div>
                        <div class="col-md-10">
                         Olie pakning kit sæt for 2,5 ton donkraft.<br/>
                            Varenummer: R6820-pak                       </div> 
                    </div>
                </li>


                <li id="item-mzmR6815-1mzm2mzmNaNmzmdk" class="list-group-item hand">
                    <div class="row">
                        <div class="col-md-2">
                            <img src="/images/1463467321.jpg" class="img-responsive">
                        </div>
                        <div class="col-md-10">
                         Gummiplade for 1,25 ton donkraft.<br/>
                            Varenummer: R6815-1                     </div> 
                    </div>
                </li>


                <li id="item-mzmR6820-1mzm2mzmNaNmzmdk" class="list-group-item hand">
                    <div class="row">
                        <div class="col-md-2">
                            <img src="/images/1463467331.jpg" class="img-responsive">
                        </div>
                        <div class="col-md-10">
                         Gummiplade for 2,5 ton donkraft.<br/>
                            Varenummer: R6820-1                     </div> 
                    </div>
                </li>

I hope someone can help me out here - I do believe it has to do with the containing a dash already, but removing it does not solve the problem.

Also I am aware of the deprecated mysql statements. I am in the process of converting to mysqli.

When it fails, what is the actual runtime query being executed? How specifically is it failing? If it's a problem stemming from the SQL injection vulnerabilities you have then using prepared statements and query parameters could render the problem moot. (And, as an added bonus, fix your SQL injection problems.) — David, Oct 25 '16 at 18:35
Hi @David, It fails in that way that if the first 5 loops are without a dash, these complete and updates in the Mysql. If the sixth loop contains a dash, the update stops. I am unable to see any errors as the code is run in the sortonthefly.php file. — MazeyMazey, Oct 25 '16 at 18:42
You're going to have to do some actual debugging here. At the moment you're basically saying "it doesn't work" which isn't very helpful. Focus on that one iteration of the loop which fails and debug it. Determine what the actual runtime values are, specifically that SQL query. Determine *how* it fails. If the query isn't succeeding then actually check for errors from the database (`mysql_error()`). Chances are the database is telling you why it's failing, but you're just not checking. — David, Oct 25 '16 at 18:44
I agree with you and I am sorry for not being more detailed. I just do not know how to debug a file, that is being fired by AJAX. Maybe you can lead me in the right direction. Usually I debug using developer tools built in the browser, but this does not state any errors. — MazeyMazey, Oct 25 '16 at 18:48
Debugging the server-side code really doesn't have anything to do with whether or not it was invoked via AJAX. I'm not familiar with any modern debugging tooling for PHP, but at the very least you can write runtime values to a log or to output (the latter of which would be visible in your browser tools as the response from the HTTP request). The main thing you should *really* be doing is checking the database query for errors. Currently this code just assumes that the query succeeds and never checks for errors. You should *always* check for errors. — David, Oct 25 '16 at 18:51
I am using phpMyadmin and I simply can not find any logfile in there... I have tried looking under Status and can't seem to find an error log. Sorry for all the missing information :-/ — MazeyMazey, Oct 25 '16 at 19:08

score 0 · Accepted Answer · edited May 23 '17 at 12:33

I found out that the dash I suspected was indeed the problem - as I found here:

jQuery UI Sortable, then write order into a database

When you use the serialize option, it will create a POST query string like this: item[]=1&item[]=2 etc. So if you make use - for example - your database IDs in the id attribute, you can then simply iterate through the POSTed array and update the elements' positions accordingly.

Because my product ID's could contain dashes, this was problematical (and it seemed as a bad solution to use str_replace to solve it) so I went with another solution together with json, where I was able to decode the json and insert the values inside a foreach loop like the original question had.

The working result is here:

The sort file:

<script>
$(document).ready(function () {
    $('ul').sortable({
        scroll:true,
        helper: "clone",
        axis: 'y',
        stop: function (event, ui) {
            var data = {category: '<?=$_GET['id']?>', subcategory: '<?=$_GET['subid']?>', lang: '<?=$_GET['country']?>', dataset:$("#sortable").sortable('toArray')};
            var json = JSON.stringify(data);
            $('#span').text(data);

            $.ajax({
            data: json,
            type: 'POST',
            contentType: "application/json",
            url: '/admin/pages/sortonthefly.php'
        });
    }
    });
});
  </script>

The sortonthefly file:

$i = 1;


$data = json_decode(file_get_contents('php://input'));

$productId = $data->dataset;
$category = $data->category;
$subcategory = $data->subcategory;
$country = $data->lang;


foreach ($productId as $value) {

    mysql_query("UPDATE b_kat_prod SET sortering = $i WHERE prodref = '$value' AND katref = $category AND subref = $subcategory AND country = '$country'") or die(mysql_error());

        $i++;
    }

Jquery / AJAX sort function can not store data with dash in mysql

1 Answers1