PHP Code to Edit MySQL Database

M

megan1989

Baseband Member
Messages
29
Location
USA
I've been working on some code to update a MySQL and am running into some problems involving the actual updating and deleting of said data. From everything that I have seen and looked up on this, it should be working but it is not. Through my troubleshooting of it, the update/delete works if I manually put in the ID of the row of data i.e. mysql_query("DELETE FROM Words WHERE ID='$86'"); that I want to delete in the database and it also will work if I input another column name in this i.e. mysql_query("DELETE FROM Words WHERE Title='$Title'"); Of course if I attempt the latter it won't be able to update the title if someone would want to do that.

Here is the Edit Code:
<?php
include("../dbconfig.php");
include("../../inc/menucode.php");
$ID = $_GET["ID"];
$order = "SELECT * FROM Words WHERE ID = '$ID'";
$result = mysql_query($order);
$row = mysql_fetch_array($result);
?>

<script language="javaScript" src="http://www.site.com/inc/subscribevalidate.js" type="text/javascript"></script>
<script src="../../inc/AC_RunActiveContent.js" type="text/javascript"></script>

<SCRIPT TYPE="text/javascript" LANGUAGE="JavaScript">
<!--
function GetForm(myForm) {
if (myForm.category.options[myForm.category.selectedIndex].value == "") {
alert("Please choose a Category from the list!");
myForm.category.focus();
return false;
}
return true;
} // function GetForm
// -->
</SCRIPT>

</head>

<body>

<?php
include("../../inc/tooltipcode.inc");
include("../../inc/googleanalyticscode.php");
?>

<div id="container">
<table width="1000" border="0" cellspacing="0" cellpadding="0">
<tr>
<td id="top">

<?php
include ("../../inc/subscribeboxes.inc");
include ("../../inc/navbar_menu.inc");
?>

</td>
</tr>
<tr>
<td>
<table width="980" border="0" cellspacing="0" cellpadding="0">
<tr>
<td id="main">


<h1>Content Management System - Edit Word</h1>

<form method="post" action="words_update.php">
<fieldset>
<legend>Edit A Article</legend>
<ol>
<li><label>Title:</label>
<input type="text" name="Title" size="50" value="<?php echo htmlspecialchars($row['Title']) ?>" /></li>
<li><label>Author:</label>
<input type="text" name="Author" size="50" value="<?php echo htmlspecialchars($row['Author']) ?>" /></li>
<li><label>Email Address:</label>
<input type="text" name="Email" size="50" value="<?php echo htmlspecialchars($row['Email']) ?>" /></li>
<li><label>"From" Name</label>
<input type="text" name="From" size="50" value="<?php echo htmlspecialchars($row['Source']) ?>" /></li>
<li><label>"From" URL</label>
<input type="text" name="URL" size="50" value="<?php echo htmlspecialchars($row['URL']) ?>" /></li>
<li><label>FB Image URL:</label>
<input type="text" name="image" size="50" value="<?php echo htmlspecialchars($row['image']) ?>" /> <a target="_blank" href="<?php echo htmlspecialchars($row['image']) ?>">Open Photo URL</a></li>


<li><label>Category</label>
<select name="category">
<option value="<?php echo htmlspecialchars($row[category]) ?>"><?php echo htmlspecialchars($row[category]) ?></option>
<option value="word">Word</option>
<option value="conference">Conference</option>
<option value="advertizing">Advertizing</option>
<option value="gmw">God's Mysterious Ways</option>
</select>
</li>

<li><label>Display Date</label>
<input type="text" name="Display_Date" value="<?php echo htmlspecialchars($row['Display_Date']) ?>" /></li>
<li><label>Listing Date</label>
<input type="text" name="Date_Created" value="<?php echo htmlspecialchars($row['Date_Created']) ?>" /></li>
<li><label>Active?</label>
<input type="checkbox" value="on" name="active" <?php if($row['active']) {echo "checked";} ?> /></li>
<li><label>Word</label>
<textarea name="Word" rows="15" cols="55"><?php echo $row['Word']?></textarea></li>
</ol>
</fieldset>
<fieldset class="submit">
<input type="submit" value="Submit" />
<input type="submit" name="cancel" value="Cancel" />
<!--<form method="post"><input type="submit" name="delete" value="Delete"/>
<input type="hidden" name="ID" value="'.$row['ID'].'"/>-->

<input type="submit" value="Delete" name="delete" />
</fieldset>
</form>

Here is the Update Code:
<?php
include("../dbconfig.php");
include("../../inc/menucode.php");
$result = mysql_query("SELECT * FROM Words WHERE ID = '$ID'");
$row = mysql_fetch_array($result);

?>

<script language="javaScript" src="http://www.site.com/inc/subscribevalidate.js" type="text/javascript"></script>
<script src="../../inc/AC_RunActiveContent.js" type="text/javascript"></script>

</head>

<body>

<?php
include("../../inc/googleanalyticscode.php");
?>

<div id="container">
<table width="1000" border="0" cellspacing="0" cellpadding="0">
<tr>
<td id="top">

<?php
include ("../../inc/subscribeboxes.inc");
include ("../../inc/navbar_menu.inc");
?>


</td>
</tr>
<tr>
<td>
<table width="1000" border="0" cellspacing="0" cellpadding="0">
<tr>
<td id="main">

<div align="center" style="font-family: verdana; margin: 20px;">
<?php
$ID = ($_POST['ID']);
$Title = mysql_escape_string($_POST['Title']);
$Author = mysql_escape_string($_POST['Author']);
$Display_Date = mysql_escape_string($_POST['Display_Date']);
$Word = mysql_escape_string($_POST['Word']);
$Date_Created = mysql_escape_string($_POST['Date_Created']);
$Email = mysql_escape_string($_POST['Email']);
$From = mysql_escape_string($_POST['From']);
$image = mysql_escape_string($_POST['image']);
$URL = mysql_escape_string($_POST['URL']);
$category = mysql_escape_string($_POST['category']);

if(isset($_REQUEST["delete"])) {
mysql_query("DELETE FROM Words WHERE ID='$ID'");
echo mysql_error();
echo 'The Word has been successfully removed from the database.<br><br>';
echo '<a href=index.html>Return to the Words Page</a><P>';
echo '<a href=words_list.php>Return to the Words List</a>';
exit;
}

if($_REQUEST["Title"] == "")
{
$EString = 'You must include a Title.';
}

if($_REQUEST["Author"] == "")
{
$EString .= '<br>You must include an Author.';
}

if($_REQUEST["Word"] == "")
{
$EString .= '<Br>You must include a Word';
}

if(isset($EString))
{
echo $EString;
echo '<br><br>Please use your browsers back button to correct the above errors.';
exit;
}

$result = mysql_query("SELECT * FROM Words where ID = '$ID'");
$row = mysql_fetch_array($result);

$qry_string = "UPDATE Words SET";
if($row['Title'] != $Title)
{
$end_string .= " Title = '$Title'";
}

if($row['Author'] != $Author)
{
if(isset($end_string))
{
$end_string .= ", Author = '$Author'";
}
else
{
$end_string = " Author = '$Author'";
}
}

if($row['Display_Date'] != $Display_Date)
{
if(isset($end_string))
{
$end_string .= ", Display_date = '$Display_Date'";
}
else
{
$end_string = " Display_date = '$Display_Date'";
}
}

if($row['Word'] != $Word)
{
echo 'Alter Word<br>';
if(isset($end_string))
{
$end_string .= ", Word = '$Word'";
}
else
{
$end_string = " Word = '$Word'";
}
}

if($row['Date_Created'] != $Date_Created)
{
if(isset($end_string))
{
$end_string .= ", Date_Created = '$Date_Created'";
}
else
{
$end_string = " Date_Created = '$Date_Created'";
}
}

if($row['Email'] != $Email)
{
if(isset($end_string))
{
$end_string .= ", Email = '$Email'";
}
else
{
$end_string = " Email = '$Email'";
}
}

if($row['Source'] != $From)
{
if(isset($end_string))
{
$end_string .= ", Source = '$From'";
}
else
{
$end_string = " Source = '$From'";
}
}

if($row['URL'] != $URL)
{
if(isset($end_string))
{
$end_string .= ", URL = '$URL'";
}
else
{
$end_string = " URL = '$URL'";
}
}

if($row['image'] != $image)
{
if(isset($end_string))
{
$end_string .= ", image = '$image'";
}
else
{
$end_string = " image = '$image'";
}
}


if($row['category'] != $category)
{
if(isset($end_string))
{
$end_string .= ", category = '$category'";
}
else
{
$end_string = " category = '$category'";
}
}

$_REQUEST["active"] == "on"?$active=1:$active=0;

if($row["active"] != $active)
{
if(isset($end_string)) $end_string .= ", ";
$end_string .= "active = " . $active;
}

if(!isset($end_string))
{
echo 'There was no information to be updated.<br><br>';
echo '<a href=index.html>Return to the Words Page';
exit;
}

$qry_string = $qry_string.$end_string." WHERE ID = '$ID'";

if(mysql_query($qry_string) == FALSE)
echo mysql_error();
{
echo 'There was an error attempting to update the database.<br>';
echo 'Please contact the system administrator with the following information:';
echo "<br><br>Query String -> $qry_string";
echo "<br/><br/>mysql_error();";
exit;
}

echo 'The database has been successfully updated<br><br>';
echo '<a href=index.html>Return to the Words Page</a><P>';
echo '<a href=words_list.php>Return to the Words List</a>';


?>


I am hoping someone can see something that I am missing with this, any help would be greatly appreciated! Thank you
 
I think that you might need the escape yourquotes.
eg.
where title=\'$title\'
 
From what I tried with adding the escape yourquotes where title=\'$title\', it did not look like it accomplished anything different. But in a echo statement of the query this is what it spit out to me:

Query String -> UPDATE Words SET Title = 'Testing', Author = 'Jane Doe', Display_date = 'Aug 30, 2015', Word = 'Testing Testing Testing Testing Testing Testing', Date_Created = '2015-08-30', Email = 'someone@yahoo.com', Source = 'Testing', URL = 'Testing', image = 'Testing', category = 'word' WHERE ID = ''

And just by looking at this it appears that the ID is empty as WHERE ID='', as when I run it for instance using Title i.e. WHERE Title = '$Title'" it would return a value for that. For instance WHERE Title = 'Title of Article'. Why it is empty I have no idea, do you have any thoughts on this?
 
if you just have echo '$ID' what do you get?

(having looked properly)
I suspect that this is related to <form method=post> and $ID=$_GET['ID'];

(i.e you should be using $_POST['ID']; to retrieve posted variables.)

(though you see to call the ID variable again later in the script using the _POST variables, I can't quite understand if this is a different file that is working.)
 
You must log in or register to reply here.

Similar threads

danhodge
PHP Slim Post Request
Replies
6
Views
3K
carnageX
carnageX
M
Help With PHP Calender Code
2
Replies
18
Views
6K
crghsam
C
S
Basic Linux Commands ..
Replies
4
Views
1K
Yami
Yami
L
radio button link
Replies
9
Views
2K
carnageX
carnageX
fallenapples
Help organizing my php and html
2
Replies
11
Views
3K
carnageX
carnageX
Back
Top Bottom