Solved

$_POST processing PHP page and update MySQL

Posted on 2011-09-11
9
344 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
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…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

821 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