Solved

$_POST processing PHP page and update MySQL

Posted on 2011-09-11
9
343 Views
Last Modified: 2012-05-12
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
Comment
Question by:phat_code
  • 5
  • 3
9 Comments
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 36519533
So it looks like you want to insert data into four columns of the data base?  Please post the CREATE TABLE statement, thanks.
0
 

Author Comment

by:phat_code
ID: 36519541
no I want only one column updated. The where clause is just specifying what to update.
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 36519547
Can you please show us the <form> part of things?  Thanks.
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:phat_code
ID: 36519596
<?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
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 36519692
I must be missing something.  It appears that $sql and $result is undefined in that script.

Please post the CREATE TABLE statement, thanks.
0
 

Author Comment

by:phat_code
ID: 36519803
no you are not missing anything i define them above. I just posted the form.
0
 

Author Comment

by:phat_code
ID: 36519807
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
 
LVL 3

Accepted Solution

by:
Duboux earned 500 total points
ID: 36521807
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
 

Author Comment

by:phat_code
ID: 36522540
Thank you. I didn't know I could do a SQL statement like that. Great job!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
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…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

803 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