Solved

Saving position of jquery sortable portlet to database

Posted on 2016-10-10
11
47 Views
Last Modified: 2016-10-10
Hi,  I have a page that saves the jquery sortable portlet position to a text file, however I need it to save to a database.  If anyone can tell me where i'm going wrong would much appreciate it.

<?php
include('connection.php');


// for some reason, the portlet toArray 'adds' an empty element to the start of each array

if(isset($_POST['data']))
{
    foreach($_POST['data'] as $arr)
    {
        //$arr[0] is the column id - COL_1, COL_2 (these are the status number 1-6 = New - To Be Invoiced)
        //$arr[1] is an array of the ids that are in the column - ID_1, ID_5
        
        // get the status (column) number
        list($not_used,$status) = explode('_',$arr[0]);
        
        // get the id's in each status/column
        $arr[1] = array_filter($arr[1]); // remove empty elements
        if(empty($arr[1]))
        {
            // an empty status/column
            $str = "Status: $status, empty";
            file_put_contents('log.txt',print_r($str,true)."\n",FILE_APPEND);
        }
        else
        {
            // non-empty status/column
            foreach($arr[1] as $element)
            {
                // get the id number
                list($not_used,$id) = explode('_',$element);
                $str = "Status: $status, Id: $id";
                file_put_contents('log.txt',print_r($str,true)."\n",FILE_APPEND);
                $query = mysql_query("UPDATE jobs SET status = " . $status . " WHERE id = " .$id );
            }
        }
    }
}
?>

Open in new window

0
Comment
Question by:Karen Liddy
  • 6
  • 4
11 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
There may be some errors in the script that are not visualized, so you would never know they are happening.  You want to add error_reporting(E_ALL) to the top of your PHP scripts to help catch these "silent errors."  

This article shows the correct way to use a MySQL database, including how to visualize an errors.  It also explains why and how to get off the MySQL extension, something you want to do as soon as possible.
https://www.experts-exchange.com/articles/11177/PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

You may also find that var_dump() is your friend when you do not know exactly what a function returns or what a data element contains!  For example, you might want to create the query string in its own variable and print it with var_dump() so you can see exactly what is getting sent to the SQL engine.
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
Can we see the data sent in the $_POST.
0
 

Author Comment

by:Karen Liddy
Comment Utility
I'm sorry my mistake all my other calls are actually in MySQLi.

This is the update statement :
$sql = "UPDATE jobs SET status = " . $status . " WHERE id = " .$id ;

I have error reporting all on in my php.ini but nothing shows, not sure if its because the save_order.php page is an ajax call though

I have attached log.txt which every time you move a block it makes a note of all the boxes and their locations.  I need to get this into the database instead of the txt file.

Here is the address of the script atm.  http://adlantic.co/backend/jobs/jobs

How can i show you the data sent in $_POST?
log.txt
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Just a thought... MySQL column names are case-sensitive, as are PHP variables and array indexes.

not sure if its because the save_order.php page is an ajax call though
This is a common issue.  It's usually solved by using error_log() and looking at the error_log file after the fact.

You can capture the contents of $_POST like this, and it will be written to the error_log file.
ob_start();
echo 'POST: ';
echo PHP_EOL;
var_dump($_POST);
echo PHP_EOL;
$post = ob_get_clean;
error_log($post);
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Here's a quick look at $_POST['data'].  Looks like each element consists of an array with a name like "COL_xxx" at position zero, and an array at position one.  This interior array has a collection of "ID_xxx" data elements, with position zero being empty in every case.  Is this what you expect from the AJAX request?
Array
(
    [0] => COL_1
    [1] => Array
        (
            [0] => 
            [1] => ID_21
            [2] => ID_23
            [3] => ID_15
            [4] => ID_17
            [5] => ID_20
            [6] => ID_14
        )

)
Array
(
    [0] => COL_2
    [1] => Array
        (
            [0] => 
            [1] => ID_2
        )

)
Array
(
    [0] => COL_3
    [1] => Array
        (
            [0] => 
            [1] => ID_3
            [2] => ID_1
            [3] => ID_18
        )

)
Array
(
    [0] => COL_4
    [1] => Array
        (
            [0] => 
            [1] => ID_4
        )

)
Array
(
    [0] => COL_5
    [1] => Array
        (
            [0] => 
            [1] => ID_11
            [2] => ID_5
        )

)
Array
(
    [0] => COL_6
    [1] => Array
        (
            [0] => 
            [1] => ID_16
        )

)

Open in new window

0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
And looking at the generated query statements, they look like this...  This seems probably OK to me, except that a LIMIT 1 clause would probably make sense.  
UPDATE jobs SET status = 1 WHERE id = 21
UPDATE jobs SET status = 1 WHERE id = 23
UPDATE jobs SET status = 1 WHERE id = 15

Open in new window

So, couple of thoughts.  

1. Please add a test for query success or failure (MySQL and MySQLi both have return values from the _query() method) and visualize the error messages if any.  Please error_log() those messages and post them here.

2. Be sure that you're running the correct scripts during the test - you can add some identifying information to the error_log() statements.

3. Show us the CREATE TABLE statements for the "jobs" table.

4. Show us the code in this: include('connection.php'); -- you can obscure any passwords, etc., but we need to see if the DB connection is being made correctly in the AJAX script.
0
 

Author Comment

by:Karen Liddy
Comment Utility
I will post the information you requested shortly (not home or work atm).  However the log.txt gets the information correctly so the information is being sent to the save_order page and the function seems to be working, just my update statement.  Connection page makes connection ok as prevous page jobs can list all the information.

Will post info soon as get home
0
 

Author Comment

by:Karen Liddy
Comment Utility
<?php

$con=mysqli_connect("192.186.231.2","username","password","adlantic_jobs");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  } 

?>

Open in new window

adlantic_jobs.sql
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
That all looks OK.  Do you use the $con variable in the mysqli_query() function call?  Its arguments are in a different order in the obsolete MySQL extension.

Also, this may or may not matter, but the `status` column definition is varchar, suggesting that it might make sense to put quotes around the variable in the SQL statement.  Something like this...
$query  = "UPDATE jobs SET status = '$status' WHERE id = '$id' LIMIT 1";
$result = mysqli_query($con, $query);
if (!$result)
{
    $msg = "FAIL: $query " . mysqli_error($con);
    trigger_error($msg, E_USER_WARNING);
}

Open in new window

0
 

Author Comment

by:Karen Liddy
Comment Utility
It was the single quotes around the variables, thats done it.  Thank you so much for your help.  Now I can move on to the next part of the application.
Again Thanks.
Mskazza
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
:-)

Best of luck with your project!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

How to build a simple, quick and effective accordion menu using just 15 lines of jQuery and 2 css classes
Introduction JSON is an acronym for JavaScript Object Notation.  It is a text-string data transport mechanism, capable of representing simple or complex data structures in a consistent and easy-to-read manner.  Similar in concept to XML, but more e…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now