CODEWITHSUNDEEP
phpmysql
Patrick C
Patrick C

Reputation: 789

Change value in Mysql database via PHP

Hey all, I want to change a value in a table (in a mysql database) via PHP code. I have a row in the table called 'approved' and there are two options it can be set to, "0" (not approved) and "1" (approved). I am creating a script that will change an individual approved from "0" to "1".

For example, there is a different value called 'position' and 'approved' sets the 'position' as approved or not approved (where approved is set to 1 or 0). If that is worded wrong, I will try to make it more clear.

I guess my question is can I make an individual 'position' value have its 'approved' data be switched from 0 to 1 and vice versa.

Thanks!

EDIT/UPDATE:

here's the info from the dump for this specific table:

 CREATE TABLE `positions` (
  `posID` int(10) unsigned NOT NULL auto_increment,
  `postitle` varchar(500) NOT NULL default '',
  `addtitletext` varchar(35) default NULL,
  `description` text NOT NULL,
  `print_website` enum('1','2') NOT NULL default '1',
  `userID` tinyint(4) unsigned NOT NULL default '0',
  `submitted_on` datetime NOT NULL default '0000-00-00 00:00:00',
  `approved_date` date NOT NULL default '0000-00-00',
  `approved` enum('0','1') NOT NULL default '0',
  PRIMARY KEY  (`posID`)
) ENGINE=MyISAM AUTO_INCREMENT=464 DEFAULT CHARSET=latin1;

LOCK TABLES `positions` WRITE;
/*!40000 ALTER TABLE `positions` DISABLE KEYS */;
INSERT INTO `positions` (`posID`,`postitle`,`addtitletext`,`description`,`print_website`,`userID`,`submitted_on`,`approved_date`,`approved`)
VALUES

and I was trying to edit this code here to make it change from not approved to approved (or vice versa)

    <? 
include('secure.php');
include('config.php'); 

if (isset($_POST['submitted'])) { 
foreach($_POST AS $key => $value) { $_POST[$key] = mysql_real_escape_string($value); } 
$sql = "UPDATE `positions` SET `approved` =  '{$_POST['approved']}'"; 
mysql_query($sql) or die(mysql_error()); 
echo (mysql_affected_rows()) ? "Edited row.<br />" : "Nothing changed. <br />"; 
echo "<a href='list.php'>Back To Listing</a>"; 
} 
$row = mysql_fetch_array ( mysql_query("SELECT * FROM `positions` WHERE `posID` = '$posID' ")); 




?>

<form action='' method='POST'> 
<p><b>Approved:</b><br /><input type='text' name='approved' value='<?= stripslashes($row['approved']) ?>' /> 
<p><input type='submit' value='Edit Row' /><input type='hidden' value='1' name='submitted' /> 
</form>

If it helps, awesome, but sorry if it is more confusing haha.

EDIT: Here's what I have, but getting a blank page (errors I know)

<? 
include('secure.php');
include('config.php'); 
if (isset($_GET['posID']) ) { 
$posID = (int) $_GET['posID']; 
if (isset($_POST['submitted'])) { 
foreach($_POST AS $key => $value) { $_POST[$key] = mysql_real_escape_string($value); } 
$sql = "UPDATE `positions` SET `approved` =  '{$_POST['approved']}'  WHERE `posID` = '$posID'"; 
mysql_query($sql) or die(mysql_error()); 
echo (mysql_affected_rows()) ? "Edited row.<br />" : "Nothing changed. <br />"; 
echo "<a href='list.php'>Back To Listing</a>"; 
} 

<form action='' method='POST'> 
<p><b>Approved:</b><br /><input type='text' name='approved' value='<?= stripslashes($row['approved']) ?>' /> 
<p><input type='submit' value='Edit Row' /><input type='hidden' value='1' name='submitted' /> 
</form> 
<? } ?>

Upvotes: 0

Views: 16058

Answers (3)

Patrick C
Patrick C

Reputation: 789

Figured out what I was doing wrong! Here's my code, it was actually something in a previous page that I neglected to fix (stupid little link error). 

<? 
include('secure.php');
include('config.php'); 

if (isset($_GET['posID']) ) { 
$posID = (int) $_GET['posID'];
if (isset($_POST['submitted'])) { 
$sql = "UPDATE `positions` SET  `approved` =  '{$_POST['approved']}'   WHERE `posID` = '$posID'"; 
mysql_query($sql) or die(mysql_error()); 
echo (mysql_affected_rows()) ? "Edited row.<br />" : "Nothing changed. <br />"; 
echo "<a href='list.php'>Back To Listing</a>"; 
} 
$row = mysql_fetch_array ( mysql_query("SELECT `approve` FROM `positions` WHERE `posID` = '$posID' ")); 
//echo "<p><b>Department</p></b>";
//$query="SELECT deptname,deptID FROM depts";

//$result = mysql_query ($query);
//echo "<select name=depts value=''>Department</option>";

//while($nt=mysql_fetch_array($result)){
//echo "<option value=$nt[deptID]>$nt[deptname]</option>";
/* Option values are added by looping through the array */
//}
//echo "</select>";
//`department` =  '{$_POST['department']}'

?>

<form action='' method='POST'> 
<p><b>Approved:</b><br /><input type='text' name='approved' value='<?= stripslashes($row['approved']) ?>' /> 
<p><input type='submit' value='Edit Row' /><input type='hidden' value='1' name='submitted' /> 
</form> 

<? } ?>

Upvotes: 0

Gavin H
Gavin H

Reputation: 10482

I'm not entirely sure what you are trying to do, because it sounds like approved is a column, not a row, in the table. If you wanted to do something like the following:

function toggle_approved($position_id) {
$query = "UPDATE positions SET approved = !approved WHERE posID = '$position_id'";
// execute the query here with your mysql_query() call
}

I am assuming you have some method of executing mysql queries, if not see this link. Also make sure you use your table name and position field names.

Upvotes: 4

Josh Curren
Josh Curren

Reputation: 10226

yes. you would do something like:

UPDATE table_name
SET approved=value
WHERE position=some_value

A good place for learning SQL and PHP is w3schools: http://www.w3schools.com/sql/default.asp

Upvotes: 1

Related Questions