[SOLVED] passing POST data to database

soddengecko · September 9, 2009

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 · September 9, 2009

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 · September 9, 2009

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 · September 9, 2009

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 · September 10, 2009

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 · September 10, 2009

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 · September 10, 2009

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 · September 10, 2009

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 · September 10, 2009

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 · September 10, 2009

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 · September 10, 2009

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 · September 10, 2009

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 · September 10, 2009

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 · September 10, 2009

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 · September 11, 2009

<?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 · September 11, 2009

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 · September 11, 2009

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

foreach($_GET as $ind){

soddengecko · September 11, 2009

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 · September 11, 2009

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

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

soddengecko · September 11, 2009

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 · September 11, 2009

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 · September 11, 2009

Post the current code you have.

soddengecko · September 11, 2009

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 · September 11, 2009

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 · September 11, 2009

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.

Sign In

[SOLVED] passing POST data to database

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information