Change value in Mysql database via PHP
Hi everyone, I want to change the value in a table (in mysql database) via PHP code. I have a row in a table called "approved" and I have two options, "0" (not approved) and "1" (approved). I am creating a script that will change the person approved from "0" to "1".
For example, there is another value called "position", and "approved" sets "position" as approved or not approved (if it is set to 1 or 0). If it's not phrased correctly, I'll try to make it clearer.
I guess my question is, can I make an individual "position" value so that its "validated" data toggles from 0 to 1 and vice versa.
Thanks!
EDIT / UPDATE:
here's the information from the dump for that particular 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 change this code here to change it not approved for approval (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 that helps, amazing, but sorry if this is more confusing haha.
EDIT: This is what I have but I get 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>
<? } ?>
a source to share
I'm not really sure what you are trying to do because it looks like an approved column, not a row, in a table. If you want 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 doing mysql queries if you don't see this link . Also make sure you use table names and field names.
a source to share
Yes. you would do something like:
UPDATE table_name
SET approved=value
WHERE position=some_value
A good place to learn SQL and PHP is w3schools: http://www.w3schools.com/sql/default.asp
a source to share
Figured out what I was doing wrong! Here's my code, it was actually something on the previous page that I forgot to fix (silly 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>
<? } ?>
a source to share