Log in

View Full Version : troubleshooting php code for updating mysql database


mags93:4
08-28-2015, 04:09 PM
I have two php files one for editing the second for updating, i am not sure what is missing with them as they display nothing when i click to edit a entry from the database.
This is the edit php file:

<?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.elijahlist.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></h1>
<form method="post" action="words_update.php?ID=<?php echo $ID?>" onSubmit="return GetForm(this)">
<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" />
<input type="submit" value="Delete" name="delete" />
</fieldset>
</form>

<div style="padding-left:42px;" align="center"><a target="_blank" href="http://www.site.com/words/display_word.php?ID=<?php echo htmlspecialchars($row[ID]) ?>">Open This Word in a New Tab</a></div>

</td>

</tr>
</table>

</td>
</tr>

</table>
</div>
</body>
</html>


This is the code for update php file:

<?php
if(isset($_REQUEST["cancel"])) {
header("Location:index.html");
exit;
}
?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title></title>
<?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>

<link href="/styles/layoutstyles.css" rel="stylesheet" type="text/css" />
<link href="/styles/styles.css" rel="stylesheet" type="text/css" />
<link href="../adminstyles.css" rel="stylesheet" type="text/css" />

</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
$Title = $_POST['Title'];
$Author = $_POST['Author'];
$Email = $_POST['Email'];
$Source = $_POST['From'];
$URL = $_POST['URL'];
$category = $_POST['category'];
$Display_Date = $_POST['Display_Date'];
$Date_Created = $_POST['Date_Created'];
$Active = $_POST['Active'];
$Word = $_POST['Word'];
if(isset($_REQUEST["delete"])) {
mysql_query("DELETE FROM Words WHERE ID=" . $_REQUEST["ID"]);
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.html>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 Words Page';
exit;
}

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

if(mysql_query($qry_string) == FALSE)
{
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 updated<br><br>';
echo '<a href=index.html>Return to the Words Page</a><P>';
echo '<a href=words_list.html>Return to the Words List</a>';
?>
</div>
</td>
</table>
</div>
</body>
</html>
The trigger that loads the edit page is is hyperlink:"<td><a href=words_edit.php?ID=$row[ID]>Edit</a></td></tr>";
I am not sure of the problem here, i am thinking it may have to do with how the edit page is initially loaded. There are no errors that i am receiving it is just not displaying the data that is coming from the database. Thanks
james438
08-28-2015, 07:38 PM
I would recommend upgrading to mysqli (http://php.net/mysqlinfo.api.choosing) as mysql has been deprecated for a while now.

Try

print_r($row);exit();

after

$row = mysql_fetch_array($result);

to see if you are really pulling results from your database.
mags93:4
08-30-2015, 05:11 AM
Hi,

So I did try the

print_r($row);exit(); which unfortunately did not work. However, I did find that inserting this bit of code allowed the displaying of data into the php web form $ID = $_GET["ID"]; below the include("../dbconfig.php"); line. So the connection to the database is working. About upgrading from mysqli to MySQL, I am a bit new to working with these types of functions, would the database itself need to be updated to mysqli or could I just change the mysql_query to mysqli_query for example?

My problem now is that it will not update new information entered from the web form to the database. I've troubleshooting this and found that the update would work with "... FROM Words WHERE Title='$Title'" and when I manually put in the ID ("... FROM Words WHERE ID='$86'");. But with the Title, this would fail when someone actually wanted to update the Title of something in the database. Also I have inserted: $ID = ($_POST['ID']); into the update code so I am thinking that the underlying problem may have to do with a conflict between the GET and POST?
james438
08-30-2015, 01:59 PM
What does print_r($row);exit(); output? If nothing displays then $row is empty.

To upgrade from MySQL to MySQLi you do not need to update your database, just your php queries. Here is the example from the php.net website:


<?php
// mysqli
$mysqli = new mysqli("example.com", "user", "password", "database");
$result = $mysqli->query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");
$row = $result->fetch_assoc();
echo htmlentities($row['_message']);
?>

I often use


$get_addresses = "SELECT ID FROM article WHERE ID = $ID";
$get_addresses_res = mysqli_query($conn,$get_addresses);
$add_info = mysqli_fetch_array($get_addresses_res);
$ID = $add_info['ID'];

GET and POST should not be interfering with each other. Can you post the query you are sending to the database that is not working? You may want to echo $query; exit(); to see what it looks like.
mags93:4
08-30-2015, 03:21 PM
Well the print_r($row);(exit); did a couple of things when I inserted the print_r($row);exit(); it failed to load the page at all, and I modified that to print_r($row); in which it did load the page but did not display any data from the database. So based on that it seems that you would be correct that $row is empty. Here is a sample query that I am sending to the database that is not working:
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 = ''

I can see that in the WHERE, ID is empty as well though which would definitely be a problem
james438
08-30-2015, 09:24 PM
It looks like getting $ID assigned the value you want is the main problem here. It depends on whether you want to get it from the url using $_GET['ID'] or from $_POST['ID']. If you use both in the same page you run the risk of unintentionally replacing one value for $ID with another. I think I may have misunderstood you earlier in regards to $_GET and $_POST conflicting with each other. $ID=$_GET['ID'] and $Title=$_POST['Title'] will not interfere with each other.

try

print_r($_POST);exit();

after

include("../../inc/menucode.php");

to see if all of your variables are being pulled. However, it sounds like you are pulling the ID from the url, so you want to use $ID=$_GET['ID']; in order to assign the needed variable to $ID.
mags93:4
08-30-2015, 10:18 PM
Okay so the

print_r($_POST);exit(); returned this code after attempting to submit changes:
Array ( [Title] => Bla [Author] => John Doe [Email] => Blah [From] => Blah [URL] => Blah [image] => Blah [category] => word [Display_Date] => Aug 30, 2015 [Date_Created] => 2015-08-30 [Word] => Testing Update Code)

However it still did nothing to update the database entry. But you are correct I am pulling the ID from the URL using the $ID=$_GET['ID']; in order to display the data on the edit page. So how would I go about using the $ID=$_GET['ID'] in order to assign the correct variable to $ID? Would I need to insert the GET code into the update php file, at this point I am not sure. Thank you
james438
08-30-2015, 10:39 PM
$_GET will only pull the result from the current url. I am unaware of a way to use $_GET to pull the value from an included page. I doubt there is one since that is what $_POST is for. If you want to use $ID=$_GET['ID'] I would place it after

include("../../inc/menucode.php");

I hope this helps.