Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

$_POST processing PHP page and update MySQL

Posted on 2011-09-11
9
Medium Priority
?
352 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 111

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 111

Expert Comment

by:Ray Paseur
ID: 36519547
Can you please show us the <form> part of things?  Thanks.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 111

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 2000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

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. . .
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

596 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