Solved

$_POST processing PHP page and update MySQL

Posted on 2011-09-11
9
346 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 110

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 110

Expert Comment

by:Ray Paseur
ID: 36519547
Can you please show us the <form> part of things?  Thanks.
0
Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

 

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 110

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

Webinar: Choosing a MySQL HA Solution

Join Percona’s Principal Technical Services Engineer, Marcos Albe as he presents Choosing a MySQL High Availability Solution on Thursday, June 29, 2017 at 10:00 am PDT / 2:00 pm EDT (UTC-7).

Question has a verified solution.

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

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

691 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