• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

$_POST processing PHP page and update MySQL

I have an Array that comes from a Post form that is auto generated by a php script I need to add these into a mysql database but I can't figure out how to loop through the info and store it in a database.

I want a page named process.php that takes the array data and in the end updates the database with a MySQL statement like the one below. It has to use variables so I don't have to hard code everything.

UPDATE  `URL` SET `Column`=$Column#  WHERE  `Group Category` LIKE  '$colGroupCat#'

I probably will add more columns.
Array
(
    [colGroupCat1] => Bathroom
    [Column1] => 3
    [colGroupCat2] => Carpet
    [Column2] => 3
    [colGroupCat3] => Organic Pesticides
    [Column3] => 3
    [colGroupCat4] => Organizing
    [Column4] => 3
)

Open in new window

0
phat_code
Asked:
phat_code
  • 5
  • 3
1 Solution
 
Ray PaseurCommented:
So it looks like you want to insert data into four columns of the data base?  Please post the CREATE TABLE statement, thanks.
0
 
phat_codeAuthor Commented:
no I want only one column updated. The where clause is just specifying what to update.
0
 
Ray PaseurCommented:
Can you please show us the <form> part of things?  Thanks.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
phat_codeAuthor Commented:
<?php

echo '<form name="stack" method="POST" action="stackprocess.php">';
echo '<table bordercolor="#9966FF" align="center" width="734">'; 
echo '<tr>';
echo '<td>Group Category</td>';
echo '<td>Column</td>';
echo $sql;
echo '</tr>'; 
while($row = mysql_fetch_array($result)){  
$i++;
echo '<tr>';
echo "<td><input type='text' value='".$row[$colGroupCat]."' name='colGroupCat".$i."' /></td>";
echo "<td><input type='text' value='".$row[$colNumber]."' name='Column".$i."' /></td>";
echo "</tr>";

}  
echo '<td colspan="2" align="center">'; 
echo '<input type="submit"/>';
echo '<a href="doubledropdown.php">Bookmarks</a>'; 
echo '</td></tr></table></form>'; 

?>  

Open in new window

0
 
Ray PaseurCommented:
I must be missing something.  It appears that $sql and $result is undefined in that script.

Please post the CREATE TABLE statement, thanks.
0
 
phat_codeAuthor Commented:
no you are not missing anything i define them above. I just posted the form.
0
 
phat_codeAuthor Commented:
CREATE TABLE IF NOT EXISTS `URL` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Top Level Category` varchar(18) DEFAULT NULL,
  `Page Category` varchar(41) DEFAULT NULL,
  `Group Category` varchar(30) DEFAULT NULL,
  `Title` varchar(148) DEFAULT NULL,
  `URL` varchar(2083) DEFAULT NULL,
  `Password Needed` varchar(1) DEFAULT NULL,
  `Column` int(1) NOT NULL,
  `Row` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2554 ;

Open in new window

0
 
DubouxCommented:
How about this:

// Just created for testing
$YourArray = array(
	"colGroupCat1" => "Bathroom",
	"Column1" => 3,
	"colGroupCat2" => "Carpet",
	"Column2" => 3,
	"colGroupCat3" => "Organic Pesticides",
	"Column3" => 3,
	"colGroupCat4" => "Organizing",
	"Column4" => 3
);


	// Make SQL
	$IN = array();
	$Sql = "
			UPDATE `URL`
			SET
			`Column` = CASE `Group Category`
	";
	for ($i = 1; $i <= (count($YourArray) / 2); $i++) {
		echo $i;
		$Sql .= "WHEN '".$YourArray["colGroupCat".$i]."' THEN '".$YourArray["Column".$i]."'\n";
		// Prep the IN part
		$IN[] = $YourArray["colGroupCat".$i];
	}
	$Sql .= "
			END
			WHERE `Group Category` IN ('".implode("', '", $IN)."')
	";

	echo nl2br($Sql);
	exit;
?>


If you want single queries per item, then:


<?php
	for ($i = 1; $i <= (count($YourArray) / 2); $i++) {
		$Sql[] = "UPDATE  `URL` SET `Column` = '".$YourArray["Column".$i]."'  WHERE  `Group Category` LIKE '".$YourArray["colGroupCat".$i]."'";
		// $Result = mysql_query($Sql).....
    }
?>

Open in new window


Have fun !
0
 
phat_codeAuthor Commented:
Thank you. I didn't know I could do a SQL statement like that. Great job!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now