[RESOLVED] MySQL row not updating

robertjm

Hi all,

Cannot figure out what the problem is and hope it's something that will be readily obvious here.

I have a form that pulls an individual record from a table, based upon one field. That info is loaded into a form, which a user can then edit most of the fields on. After making changes the user clicks on a button, which is supposed to update the database, and returns the user to the default page.

I tried using both field="$variable" and field='$variable' However, neither update the field. I added an echo of one of the variables below the update statement, and the variable's data IS properly printing on the webpage.



<table>
<tr>
<td>
<?PHP $update = 'UPDATE `episodes` SET `guid`="$guid", `id`="$id", `title`="$title", `subtitle`="$subtitle", `keywords`="$keywords", `explicit`="$explicit", `isClosedCaptioned`="$isClosedCaptioned", `author`="$author", `description`="$description", `pubdate`="$pubdate", `pubZoneOffset`="$pubZoneOffset", `blockEpisode`="$blockEpisode", `size`="$size", `duration`="$duration", `folder`="$folder", `filename`="$filename" WHERE guid = "$guid"';
$update_result = mysql_query($update);
mysql_close();
?>

[<a href="index.php" OnClick="JavaScript:return confirm('Are you sure you want to MODIFY this episode? There is no undo step.');">Edit</a>]

</td>
</tr>


</table>

</form>

</html>

dagon

from a form the field values are either $POST['guid'] or $GET['guid']

also they should be sanitised if coming directly from users

you should at some point echo $update, and use mysql_error() to get the error the db is returning

robertjm

As of right now, I'm the only one who is actually accessing the database, so it's not a publicly viewed website. But, what do you mean by "sanitized"??

dagon;10998206 wrote:
from a form the field values are either $POST['guid'] or $GET['guid']

also they should be sanitised if coming directly from users

you should at some point echo $update, and use mysql_error() to get the error the db is returning

dagon

robertjm;10998207 wrote:
As of right now, I'm the only one who is actually accessing the database, so it's not a publicly viewed website. But, what do you mean by "sanitized"??

user can compromise your db and whole site security if you don't sanitise the input they provide, such as in forms. its called: SQL injection

Remember, mom knows best

robertjm

Thanks for the caution about sanitizing the database. Once I can get this to actually work, I'll take a step back to do that.

I'm still having issues getting it to update the database though. I made the changes you menationed, using $REQUEST since I'm using both $GET and $_POST on the page. Below is what I have, code-wise. Why isn't it updating?!?!?!?


<html>
<?PHP
// Report all PHP errors
 ini_set('error_reporting', E_ALL);

// Set the display_errors directive to On
 ini_set('display_errors', 1);

$con = mysql_connect("localhost", "user", "password");
$db_selected = mysql_select_db('podadmin', $con);

$value = $_GET['guid']; 

$result = mysql_query("SELECT * from episodes WHERE guid='$value'") or dieWithError("mysql query error: " . mysql_error());

$_REQUEST['guid']=mysql_result($result, 0, "guid");
$_REQUEST['id']=mysql_result($result,0,"id");
$_REQUEST['title']=mysql_result($result, 0,"title");
$_REQUEST['subtitle']=mysql_result($result,0,"subtitle");
$_REQUEST['keywords']=mysql_result($result,0,"keywords");
$_REQUEST['explicit']=mysql_result($result,0,"explicit");
$_REQUEST['isClosedCaptioned']=mysql_result($result,0,"isClosedCaptioned");
$_REQUEST['author']=mysql_result($result,0,"author");
$_REQUEST['description']=mysql_result($result,0,"description");
$_REQUEST['pubdate']=mysql_result($result,0,"pubdate");
$_REQUEST['pubZoneOffset']=mysql_result($result,0,"pubZoneOffset");
$_REQUEST['blockEpisode']=mysql_result($result,0,"blockEpisode");
$_REQUEST['size']=mysql_result($result,0,"size");
$_REQUEST['duration']=mysql_result($result,0,"duration");
$_REQUEST['folder']=mysql_result($result,0,"folder");
$_REQUEST['filename']=mysql_result($result,0,"filename");

?>



<!-- <DIV ID="calendar_div" STYLE="position:absolute;visibility:hidden;background-color:white;layer-background-color:white;"></DIV> -->

<form name="detailform" action="index.php?action=upload2" method="POST">

<table>
<tr>
<td align="right" width="120">MP3 File:</td>
<td>
<input type="hidden" name="userfile" value="<?php print $_REQUEST['filename'] ?>">
<?php echo $_REQUEST['filename'] ?>
</td>
</tr>
<tr>
<td align="right" width="120">GUID:</td>
<td>
<input type="hidden" name="userfile" value="<?php print $_REQUEST['guid'] ?>">
<?php print $_REQUEST['guid'] ?>
</td>
</tr>
<tr>
<td align="right" width="120">FOLDER:</td>
<td>
<input class="textinput" size="30" type="text" name="podcast_folder" value="<?php print $_REQUEST['folder'] ?>">
</td>
</tr>
<tr>
<td align="right" width="120">Size:</td>
<td>
<input type="hidden" name="size" value="<?php print $_REQUEST['size'] ?>">
<?php print $_REQUEST['size'] ?> Bytes
</td>
</tr>

<tr>
<td align="right" width="120">Duration:</td>
<td>
<input type="hidden" name="userfile" value="<?php print $_REQUEST['duration'] ?>">
<?php echo $_REQUEST['duration'] ?>
</td>
</tr>

<tr>
<td align="right">Title:</td>
<td><input class="textinput" size="60" type="text" name="title" value="<?php echo $_REQUEST['title'] ?>" maxlength="50"></td>
</tr>

<tr>
<td align="right" valign="top">Description:</td>
<td>
<textarea class="textinput" name="description" style="width: 500px; height: 100px;"><?php echo $_REQUEST['description'] ?></textarea>
</td>
</tr>

<tr>
<td align="right">Publish Date:</td>
<td>
<input class="textinput" size="25" type="text" name="pubdate" value="<?php echo $_REQUEST['pubdate'] ?>" maxlength="30">
</td>

<tr>
<td align="right">Time Zone Differential:</td>
<td>
<input class="textinput" size="7" type="text" name="pubZoneOffset" value="<?php echo $_REQUEST['pubZoneOffset'] ?>" maxlength="5">
</td>
</tr>
	<tr>
	<td align="right">Block Episode:</td>
	<td><input class="textinput" size="3" type="text" name="keywords" value="<?php echo $_REQUEST['blockEpisode'] ?>" maxlength="3"> (Yes, No or blank ONLY)</td>
	</tr>

</table>
ITUNES STUFF:</br>
<table>
	<tr>
	<td align="right">Subtitle:</td>
	<td><input class="textinput" size="60" type="text" name="subtitle" value="<?php echo $_REQUEST['subtitle'] ?>" maxlength="255"></td>
	</tr>
	<tr>
	<td align="right">Author:</td>
	<td><input class="textinput" size="60" type="text" name="author" value="<?php echo $_REQUEST['author'] ?>" maxlength="255"></td>
	</tr>

<tr>
<td align="right">Keywords:</td>
<td><input class="textinput" size="60" type="text" name="keywords" value="<?php echo $_REQUEST['keywords'] ?>" maxlength="255"></td>
</tr>
<tr>
<td align="right">Explicit Y, N or Clean?:</td>
<td><input class="textinput" size="5" type="text" name="keywords" value="<?php echo $_REQUEST['explicit'] ?>" maxlength="5"> (Yes, No, Clean or blank ONLY)</td>
</tr>

<tr>

<tr>
<td align="right">Closed Captioned Y or N?:</td>
<td><input class="textinput" size="3" type="text" name="keywords" value="<?php echo $_REQUEST['isClosedCaptioned'] ?>" maxlength="5"> (Yes, No or blank ONLY)</td>
</tr>

</table>

<table>
<tr>
<td>

<?PHP 
$guid=$_REQUEST['guid'];
$id=$_REQUEST['id'];
$title=$_REQUEST['title'];
$subtitle=$_REQUEST['subtitle'];
$keywords=$_REQUEST['keywords'];
$explicit=$_REQUEST['explicit'];
$isClosedCaptioned=$_REQUEST['isClosedCaptioned'];
$author=$_REQUEST['author'];
$description=$_REQUEST['description'];
$pubdate=$_REQUEST['pubdate'];
$pubZoneOffset=$_REQUEST['pubZoneOffset'];
$blockEpisode=$_REQUEST['blockEpisode'];
$size=$_REQUEST['size'];
$duration=$_REQUEST['duration'];
$folder=$_REQUEST['folder'];
$filename=$_REQUEST['filename'];

$update = "UPDATE episodes SET guid='$guid', id='$id', title='$title', subtitle='$subtitle', keywords='$keywords', explicit='$explicit', isClosedCaptioned='$isClosedCaptioned', author='$author', description='$description', pubdate='$pubdate', pubZoneOffset='$pubZoneOffset', blockEpisode='$blockEpisode', size='$size', duration='$duration', folder='$folder', filename='$filename' WHERE guid = '$guid'";

$update_result = mysql_query($update);

mysql_close();
?>

[<a href="index.php" OnClick="JavaScript:return confirm('Are you sure you want to MODIFY this episode? There is no undo step.');">Edit</a>]

</td>
</tr>


</table>

</form>

</html>

bradgrafelman

Why are you doing this:

$result = mysql_query("SELECT * from episodes WHERE guid='$value'") or dieWithError("mysql query error: " . mysql_error()); 

$_REQUEST['guid']=mysql_result($result, 0, "guid"); 
$_REQUEST['id']=mysql_result($result,0,"id"); 
$_REQUEST['title']=mysql_result($result, 0,"title"); 
$_REQUEST['subtitle']=mysql_result($result,0,"subtitle"); 
$_REQUEST['keywords']=mysql_result($result,0,"keywords"); 
$_REQUEST['explicit']=mysql_result($result,0,"explicit"); 
$_REQUEST['isClosedCaptioned']=mysql_result($result,0,"isClosedCaptioned"); 
$_REQUEST['author']=mysql_result($result,0,"author"); 
$_REQUEST['description']=mysql_result($result,0,"description"); 
$_REQUEST['pubdate']=mysql_result($result,0,"pubdate"); 
$_REQUEST['pubZoneOffset']=mysql_result($result,0,"pubZoneOffset"); 
$_REQUEST['blockEpisode']=mysql_result($result,0,"blockEpisode"); 
$_REQUEST['size']=mysql_result($result,0,"size"); 
$_REQUEST['duration']=mysql_result($result,0,"duration"); 
$_REQUEST['folder']=mysql_result($result,0,"folder"); 
$_REQUEST['filename']=mysql_result($result,0,"filename");

? You're overwriting the values of any external variables (assuming they existed) with the data from the DB.

Then, later, you do this:

<?PHP 
$guid=$_REQUEST['guid']; 
$id=$_REQUEST['id']; 
$title=$_REQUEST['title']; 
$subtitle=$_REQUEST['subtitle']; 
$keywords=$_REQUEST['keywords']; 
$explicit=$_REQUEST['explicit']; 
$isClosedCaptioned=$_REQUEST['isClosedCaptioned']; 
$author=$_REQUEST['author']; 
$description=$_REQUEST['description']; 
$pubdate=$_REQUEST['pubdate']; 
$pubZoneOffset=$_REQUEST['pubZoneOffset']; 
$blockEpisode=$_REQUEST['blockEpisode']; 
$size=$_REQUEST['size']; 
$duration=$_REQUEST['duration']; 
$folder=$_REQUEST['folder']; 
$filename=$_REQUEST['filename']; 

$update = "UPDATE episodes SET guid='$guid', id='$id', title='$title', subtitle='$subtitle', keywords='$keywords', explicit='$explicit', isClosedCaptioned='$isClosedCaptioned', author='$author', description='$description', pubdate='$pubdate', pubZoneOffset='$pubZoneOffset', blockEpisode='$blockEpisode', size='$size', duration='$duration', folder='$folder', filename='$filename' WHERE guid = '$guid'";

even though you just retrieved that data from the DB.

In other words, you're UPDATE'ing the DB with the same data you just retrieved from the DB.

robertjm

The first group of variables does pull the information in from the MySQL table. It populates an editable form. The second group of variables are supposed to represent any changes made within those fields, which I am wanting to update the database when I click the Edit link/button at the bottom of the page.

Does the variable not get redefined by the data keyed into the user editable field this way? Or do I need to use two different sets of variables; one for pulling info from the current table, and then one to update the field with the new details??

Robert

bradgrafelman

It's not that you need "different sets of variables" but instead need to deal with two totally separate page requests altogether. All of the PHP code is being executed by the server; when the user submits the form, that POST request will be a completely different page request.

If you want to do this all in a single .php file, then you'll need to check if the $_POST array was empty; if so, execute the code to display the form... otherwise, process the POST'ed data and update the DB.

robertjm

So I solved this, but it's pretty ugly.

Ended up using $_GET to pass all the data in a row to the second page, which has text boxes containing the values I might want to edit. Once edited, that sends the data through POST to a processing page, which updates the MySQL table.

Obviously, this is still ripe for sql injection, not to mention that's one ugly URL that's being passed, so I will eventually want to improve on it.

Right now my immediate question is, is there a way to count the number of characters in the URL I will be sending to the next page to make sure I don't go over a certain number? I doubt I'll ever go anywhere near 2048 (the limit before IE8 goes wonky). However, I'd like to be able to test for that, and then shorten as necessary (probably by shortening a very long description).

Thanks again for your help everyone. I realize I didn't necessarily apply it, but I'm still in a learning stage with this and the resolution I applied got me immediately beyond a sticking point I had.

Robert

Weedpacket

robertjm wrote:
Ended up using $_GET to pass all the data in a row to the second page,

Why not just have the second page retrieve the contents of that row from the database?

Have a look at [man]sessions[/man]. Then you won't have to trust the browser or its user not to mangle the data.

robertjm

THANKS!! I'll take a look at that this evening.

Robert

Weedpacket;10998440 wrote:
Why not just have the second page retrieve the contents of that row from the database?

Have a look at [man]sessions[/man]. Then you won't have to trust the browser or its user not to mangle the data.

robertjm

So I've been trying to implement sessions today, but am having some problems. I can fill the second page with session variables carried from the first page. However, no matter which podcast edit button I click on it always fills the form with field data from the very first row in the table.

There is only one line of code that is different on the page with the edit buttons:

WORKING, with $_GET passing variables within the URL

[<a href="index.php?action=edit&filename=<?php print $filename ?>&guid=<?php print $guid ?>&id=<?php print $id ?>&title=<?php print $title ?>&subtitle=<?php print $subtitle ?>&keywords=<?php print $keywords ?>&explicit=<?php print $explicit ?>&isClosedCaptioned=<?php print $isClosedCaptioned ?>&author=<?php print $author ?>&description=<?php print $description ?>&pubdate=<?php print $pubdate ?>&pubZoneOffset=<?php print $pubZoneOffset ?>&blockEpisode=<?php print $blockEpisode ?>&size=<?php print $size ?>&duration=<?php print $duration ?>&folder=<?php print $folder ?>" OnClick="JavaScript:return confirm('Are you sure you want to MODIFY this episode? There is no undo step.');">Edit</a>]

NOT WORKING, using $_SESSION variables (within the include file):

[<a href="index.php?action=edit<?php include('session_update_episode.php'); ?>&guid=<?php print $guid; ?>" OnClick="JavaScript:return confirm('Are you sure you want to MODIFY this episode? There is no undo step.');">Edit</a>]

The include file is:


<?PHP
session_start();

$_SESSION['filename'] = $filename;
$_SESSION['guid'] = $guid;
$_SESSION['id'] = $id;
$_SESSION['title'] = $title;
$_SESSION['subtitle'] = $subtitle;
$_SESSION['keywords'] = $keywords;
$_SESSION['explicit'] = $explicit;
$_SESSION['isClosedCaptioned'] = $isClosedCaptioned;
$_SESSION['author'] = $author;
$_SESSION['description'] = $description;
$_SESSION['pubdate'] = $pubdate;
$_SESSION['pubZoneOffset'] = $pubZoneOffset;
$_SESSION['blockEpisode'] = $blockEpisode;
$_SESSION['size'] = $size;
$_SESSION['duration'] = $duration;
$_SESSION['folder'] = $folder;
?>

Then, on the next page, I'm using either $SESSION or $GET, depending on which line I'm using on the page which lists the episodes.

Do I need to pass a unique identifier (guid in my case) with $GET on the first page and then query the database again to get the podcast specific variables into a session on the second page based upon what I passed with $GET?

Robert

Weedpacket;10998440 wrote:
Why not just have the second page retrieve the contents of that row from the database?

Have a look at [man]sessions[/man]. Then you won't have to trust the browser or its user not to mangle the data.