[SOLVED] passing POST data to database

[soddengecko]

Hi All

 

I have a jquery script that allows me to drag and drop multiple widgets across multiple columns. The code is below.

 

What I am trying to achieve is to update a database to remember the positions of the widgets on the stage and their open/closed state.

 

$(function(){
$('.dragbox')
.each(function(){
	$(this).hover(function(){
		$(this).find('h2').addClass('collapse');
	}, function(){
		$(this).find('h2').removeClass('collapse');
	})
	.find('h2').hover(function(){
		$(this).find('.configure').css('visibility', 'visible');
	}, function(){
		$(this).find('.configure').css('visibility', 'hidden');
	})
	.click(function(){
		$(this).siblings('.dragbox-content').toggle();
	})
	.end()
	.find('.configure').css('visibility', 'hidden');
});

$(document).ready(function(){ 	
  function slideout(){
  setTimeout(function(){
  $("#response").slideUp("slow", function () {
      });
    
  }, 5500);}

$('.column').sortable({
	connectWith: '.column',
	handle: 'h2',
	cursor: 'move',
	placeholder: 'placeholder',
	forcePlaceholderSize: true,
	opacity: 0.4,
	stop: function(event, ui){
		$(ui.item).find('h2').click();
		var sortorder='';
		$('.column').each(function(){
			var item_order=$(this).sortable('toArray');
			var columnId=$(this).attr('id');
			sortorder+=columnId+'='+item_order.toString()+'&';
		});
		/*alert(sortorder);*/
		/*Pass sortorder variable to server using ajax to save state*/

      
      console.log(sortorder);
      $.get('updateDesktop.php', sortorder, function(Response){
      $("#response").html(Response);
		$("#response").slideDown('slow');
      slideout(); 
      }); 
  
	}
})
.disableSelection();
});
});

 

 

The output I get from the post data is like so

 

column1=2,1&column2=&column3=3&column4=&

 

What I want to know, is anyone able to point me in the right direction to update the database. Does a change of JS need to be done to output the variables differently or can I get the data sorted correctly on the server side?

 

TIA

Mark

[ldb358]

Ive never worked with jquery but if your sending that exact string(or same format)

that could all be handled server side for example:

$test = explode('&', $_POST['your_post_value']);

then just use the explode(divider,string) to keep separating till its in the format you want

[soddengecko]

Hi

 

Thank you for the info.

 

I use the following on the server side to get the POST data

 

$_POST['column1']

$_POST['column2']

$_POST['column3']

 

so the values are like so

 

1,3

2

4,5

 

for each column respectively.

 

After following what you have suggested I know have the following array data

 

Array ( [0] => 1,2 ) Array ( [0] => 3,4 ) Array ( [0] => 5,6 ) Array ( [0] => 7,8 ) Array ( [0] => 9,10 )

 

I think the array numbers are wrong, they cannot all be 0 surely?

[kratsg]

There might be a nicer way using regular expressions...

 

**Haven't tested, but should work

$pattern = '/column([0-9])=([0-9],?)*[^\&]/';
$matches = array();
preg_match_all($pattern,$input,$matches,PREG_SET_ORDER);
//$matches[#][0] contains the column number, 1,2,3,etc...
//$matches[#][1] contains the comma-separated list of numbers (2,3) for example

 

From there, using a foreach() will make it astoundingly easy to loop through all data.

 

Edit: if you want the "column" part as well, use this pattern (IE: column1, column2, but I like numbers :-o)

$pattern = '/(column[0-9])=([0-9],?)*[^\&]/';

 

So when looping to put in a database... $matches[#][0] contains the "column1" for easier reference perhaps.

[soddengecko]

sorry, that array data actually looks like this

 

   

Array ( [0] => 1,2 ) Array ( [0] => 3,4 ) Array ( [0] => 5,6 ) Array ( [0] => 7,8 ) Array ( [0] => 9,10 )

[kratsg]

Oh wait, we misread it :-P So you're using AJAX to post that? IE: using that returned object as parameters?

 

Then just use the following:

 

function prep_data($input,$column){

$string = '';
$numbers = explode(",",$input);
foreach($numbers as (int)$number){
if(!empty($string)){$string .= ",";}
$string .= "($number)";
}
return "($column) VALUES".$string;
}

 

Then call it like so:

 

$insertions = array();//set it null
$insertions[] = prep_data($_POST['column1'],'column1');
//and so on..
//for all the post data...

foreach($insertions as $inserts){
mysql_query("INSERT INTO `tablename` $inserts");
}

[soddengecko]

Hi

 

Yes, I am using ajax to send the data. each column is the variable and the widgets in the column are the variable data.

 

I will try what you have suggested and get back you

[soddengecko]

Hi kratsg

 

I am not sure I fully understand your code. i have five variables passed from the ajax string like so

 

?column1=1,2&column2=........ and so on.

 

I can grab those variables like so

 

$col1 = explode('&', $_GET['column1']);
$col2 = explode('&', $_GET['column2']);
$col3 = explode('&', $_GET['column3']);
$col4 = explode('&', $_GET['column4']);
$col5 = explode('&', $_GET['column5']);

print_r($col1);
print_r($col2);
print_r($col3);
print_r($col4);
print_r($col5);

 

this prints the array as my last post states.

 

I have also changed the code to a GET instead of POST

 

Could you help break down your code for me. I am sorry if I seem a little dumb on this one but it is more complex than I am used to.

 

TIA

Mark

[kratsg]

The function

function prep_data($input,$column){//1

$string = '';
$numbers = explode(",",$input);
foreach($numbers as (int)$number){//5
if(!empty($string)){$string .= ",";}
$string .= "($number)";
}
return "($column) VALUES".$string;
}//10

Line by line:

(1)define the function and input parameters

(2) it's blank

(3) initialize $string

(4) $input would be $_POST['column1'] which can be either "1" or "1,2", this makes it into an array([0]=>1) or  array([0]=>1,[1]=>2)

(5) loop through this array

(6) add a comma to the end of the string if it isn't empty (IE: just started the foreach)

(7) concatenate (add on to the end) each number.

( end the foreach

(9) returns "(column1) VALUES (1)" or "(column1) VALUES (1),(2)"

(10) end the function

 

$insertions = array();//1
$insertions[] = prep_data($_POST['column1'],'column1');
foreach($insertions as $inserts){
mysql_query("INSERT INTO `tablename` $inserts");
}//5

(1) initialize $insertions

(2) similar to string concatenation, but with adding new values for the array

(3) loop through

(4) execute query to insert the values into a new row with the corresponding column

(5) end the loop

 

[soddengecko]

Hi Kratsg

 

Thank you for the explanation, I really appreciate your help and patience.

 

I have a slight problem though, I have put the code on the page and when run I get a parse error that I am not sure how to fix. this is the error

 

Parse error: syntax error, unexpected T_INT_CAST, expecting '&' or T_STRING or T_VARIABLE or '$' in /var/www/updateDesktop.php on line 20

 

This is the code I have on the page.

 

<?php
include "includes/config.php";

$col1 = explode('&', $_GET['column1']);
$col2 = explode('&', $_GET['column2']);
$col3 = explode('&', $_GET['column3']);
$col4 = explode('&', $_GET['column4']);
$col5 = explode('&', $_GET['column5']);

//print_r($col1);
//print_r($col2);
//print_r($col3);
//print_r($col4);
//print_r($col5);

function prep_data($input,$col1){//1

$string = '';
$numbers = explode(",",$input);                   
foreach($numbers as (int)$number){//5        <---------------- THIS IS LINE 20
if(!empty($string)){$string .= ",";}
$string .= "($number)";
}
return "($col1) VALUES".$string;
}//10


$insertions = array();//1
$insertions[] = prep_data($_GET['column1'],'column1');
foreach($insertions as $inserts){
//mysql_query("INSERT INTO `tablename` $inserts");
echo $inserts;
}//5
?>

 

As I said, this piece of code is more complex than I have used so am having a little trouble working it out.

 

If it would be easier and a help I can explain in greater detail what I am trying to build?

 

TIA

Mark

[kratsg]

Ok, replace:

 

foreach($numbers as (int)$number){//5        <---------------- THIS IS LINE 20
if(!empty($string)){$string .= ",";}
$string .= "($number)";
}

 

With:

 

foreach($numbers as $number){//5        <---------------- THIS IS LINE 20
$num = (int)$number;
if(!empty($string)){$string .= ",";}
$string .= "($num)";
}

[soddengecko]

Hi kratsg

 

Absolutely beautiful. That works well. I am now using the following script (please correct me if there is a better way to iterate through all available columns without me having to code each one)

 

function prep_data($input,$column){//1

$string = '';
$numbers = explode(",",$input);
foreach($numbers as $number){//5        <---------------- THIS IS LINE 20
$num = (int)$number;
if(!empty($string)){$string .= ",";}
$string .= "($num)";
}
return "($column) VALUES".$string;
}//10

$insertions = array();//1
$insertions[] = prep_data($_GET['column1'],'1');
foreach($insertions as $inserts){
//mysql_query("INSERT INTO `tablename` $inserts");
echo $inserts . "<br />";
}//5

$insertions = array();//1
$insertions[] = prep_data($_GET['column2'],'2');
foreach($insertions as $inserts){
//mysql_query("INSERT INTO `tablename` $inserts");
echo $inserts . "<br />";
}

$insertions = array();//1
$insertions[] = prep_data($_GET['column3'],'3');
foreach($insertions as $inserts){
//mysql_query("INSERT INTO `tablename` $inserts");
echo $inserts . "<br />";
}

$insertions = array();//1
$insertions[] = prep_data($_GET['column4'],'4');
foreach($insertions as $inserts){
//mysql_query("INSERT INTO `tablename` $inserts");
echo $inserts . "<br />";
}

$insertions = array();//1
$insertions[] = prep_data($_GET['column5'],'5');
foreach($insertions as $inserts){
//mysql_query("INSERT INTO `tablename` $inserts");
echo $inserts . "<br />";
}

 

This then returns the following info (depending which widget is in which column)

 

(1) VALUES(3),(9),(1),(2),(6)
(2) VALUES(0)
(3) VALUES(
(4) VALUES(4),(5)
(5) VALUES(7),(10)

 

I believe (with your kind help) that i am now making progress. One last hurdle is how to tell the database what to update and where.

 

Thank you

Mark

[kratsg]

Ok, let's try this... depending on how you're getting the variables, (GET or POST)

 

Do the following at the top of the page so we can see what is being posted/getted:

 

var_dump($_GET);
//OR
var_dump($_POST);

 

Post results. If the only thing being posted/getted are just the columns, then it will be easy... if there's other stuff, we'll filter it out first :-)

 

On the query question, how do you want it inserted in the database? IE: how is it being recorded?

[soddengecko]

the result of the dump is

 

array(5) { ["column1"]=> string(3) "1,2" ["column2"]=> string(1) "6" ["column3"]=> string(3) "8,3" ["column4"]=> string(5) "4,5,9" ["column5"]=> string(4) "7,10" }

 

i am storing the data in a table called sg_desktop_widgets and the table has the following rows

 

id (auto increment/primary key)

name

column_id

widget_order

 

an example would be

 

id | name | column_id | widget_order

------------------------------------

1  | RSS Feeds | 1 | 0

2  | Bookmarks | 1 | 1

3  | Misc | 2 | 0

 

I get the widgets from the DB by order of column, then sort the widgets in each column by widget_order. Hope that makes sense to you

[kratsg]

<?php
include "includes/config.php";

function prep_data($input,$col1){

$string = '';
$numbers = explode(",",$input);                   
foreach($numbers as $number){
$num = (int)$number;
if(!empty($string)){$string .= " AND";}
$string .= " `id` = ($number)";
}
return "UPDATE sg_desktop_widgets SET `column_id` = $col1 WHERE".$string;
}

$insertions = null;
foreach($_GET as $ind->$val){
$ind = preg_replace ('/[^\d\s]/', '',$ind);//strip non-numeric characters
$ind = (int)$ind;//make it an integer
$insertions = prep_data($ind,$val);
echo $insertions;
}
?>

 

See if this code does the job. Loops through your $_GET or $_POST (change $_GET to $_POST if you want to use $_POST instead). It takes the 'column#' and strips it to '#' then changes to # (an int), passes the value ("2,3,8") with the # into the function. It'll output (hopefully) the correct queries.

 

I assumed: the ("2,3,8") are the widget ids listed in the `id` column; # is the `column_id`.

[soddengecko]

Hi

 

I get a "Cannot access empty property in" for this line

foreach($_GET as $ind->$val){

 

the ("2,3,8") is the sort order rather than the ID

[mikesta707]

im not sure why he was using an object there, but try changing it to

foreach($_GET as $ind){

[soddengecko]

Hi Mikesta

 

The script is from another forum member trying to help me out with a problem I am having. Your amendment has helped and I know get this as the output:

 

UPDATE sg_desktop_widgets SET 'column_id' = WHERE 'id' = (2)UPDATE sg_desktop_widgets SET 'column_id' = WHERE 'id' = (61)UPDATE sg_desktop_widgets SET 'column_id' = WHERE 'id' = (893)UPDATE sg_desktop_widgets SET 'column_id' = WHERE 'id' = (45)UPDATE sg_desktop_widgets SET 'column_id' = WHERE 'id' = (710)

 

As you can see, I am not getting the column ID's at all and the numbers are now bunched together ie.

(45)

 

That should be a 4 and a 5 not 45. Can you see where it is going wrong?

[mikesta707]

oh my bad I see what he was trying to do. change to

foreach($_GET as $ind=>$val){

[soddengecko]

fantastic. That fixed it.

 

my update statement is now

 

UPDATE sg_desktop_widgets SET 'column_id' = 1,2 WHERE 'id' = (1)
UPDATE sg_desktop_widgets SET 'column_id' = 6 WHERE 'id' = (2)
UPDATE sg_desktop_widgets SET 'column_id' = 8,9,3 WHERE 'id' = (3)
UPDATE sg_desktop_widgets SET 'column_id' = 4,5 WHERE 'id' = (4)
UPDATE sg_desktop_widgets SET 'column_id' = 7,10 WHERE 'id' = (5)

 

It is not quite correct, the id's are the wrong way round but its close and I will try make the changes myself. I will get back to you shortly. Thank you for all of your help. I am sure I will have more questions on this particular problem. back in 10

[soddengecko]

ok, so I made it this far

 

UPDATE sg_desktop_widgets SET column_id = 1 WHERE id = 1,3,10,2UPDATE sg_desktop_widgets SET column_id = 2 WHERE id = 6,9UPDATE sg_desktop_widgets SET column_id = 3 WHERE id = 8UPDATE sg_desktop_widgets SET column_id = 4 WHERE id = 4,5UPDATE sg_desktop_widgets SET column_id = 5 WHERE id = 7

 

how do I break each update statement out of that long string and split the ID's up so I can add this all to the database?

 

TIA

Mark

[kratsg]

Post the current code you have.

[soddengecko]

This is what I have currently

 

<?php
include "includes/config.php";

function prep_data($input,$widget_column_id){

$string = '';
$columns = explode(",",$input);                   
foreach($columns as $number){
$num = (int)$number;
if(!empty($string)){$string .= " AND";}
$string .= "$number";
}
return "UPDATE sg_desktop_widgets SET column_id = '$string' WHERE id = '$widget_column_id'";
}

$insertions = null;
foreach($_GET as $ind=>$val){
$ind = preg_replace ('/[^\d\s]/', '',$ind); //strip non-numeric characters
$ind = (int)$ind;//make it an integer
$insertions = prep_data($ind,$val);

echo $insertions; 

} 
?>

 

The output of $insertions is

 

UPDATE sg_desktop_widgets SET column_id = '1' WHERE id = '1,4,2,9,5'UPDATE sg_desktop_widgets SET column_id = '2' WHERE id = '6,10,3'UPDATE sg_desktop_widgets SET column_id = '3' WHERE id = '8'UPDATE sg_desktop_widgets SET column_id = '4' WHERE id = ''UPDATE sg_desktop_widgets SET column_id = '5' WHERE id = '7'

 

It is getting very close.

[kratsg]

Replace the function with this:

 

function prep_data($input,$widget_column_id){
$widget_column_id = str_replace(",","','",$widget_column_id);
$string = (int)$input;
return "UPDATE sg_desktop_widgets SET column_id = '$string' WHERE id IN ('$widget_column_id')";
}

 

The reason it's one line is cause you're echoing it out... if you run the query instead of echoing it out, it'll be fine.

[soddengecko]

WOW. that's it. I have tested it and it all appears to be working. I cannot thank you enough for your help on this.

 

This is the whole code that is now being used

 

function prep_data($input,$widget_column_id){
$widget_column_id = str_replace(",","','",$widget_column_id);
$string = (int)$input;
return "UPDATE sg_desktop_widgets SET column_id = '$string' WHERE id IN ('$widget_column_id')";
}

$insertions = null;
foreach($_GET as $ind=>$val){
$ind = preg_replace ('/[^\d\s]/', '',$ind); //strip non-numeric characters
$ind = (int)$ind;//make it an integer
$insertions = prep_data($ind,$val);

mysql_query("$insertions") or die('<div class="error ui-corner-all">Error, insert query failed</div>');

 

Thank you again for all the help you guys have provided.