Having problems with a simple mysql query. Please help.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
blurredvision
Forum Newbie
Posts: 9
Joined: Fri Apr 04, 2008 5:34 am

Having problems with a simple mysql query. Please help.

Post by blurredvision »

In my ongoing struggles as a beginning PHP coder, I've ran into another problem. Although not all is bad, I think I did pretty good up until this point, it's just that last little hump that I can't seem to get over to get this thing working properly.

For a short description of what I'm doing, I have a table on my website that is filled with a while loop, and it's listing a song, the artist, year, person who submitted it, and a description, all coming from a mysql table. I'm trying to add the ability for each user to modify their entry should they catch a typo afterwards.

Without further ado, here is my code:

Code: Select all

<?php
if(!mysql_connect("localhost","____","____"))
    {
        echo "<h2>".$TEXT['cds-error']."</h2>";
        die();
    }
    mysql_select_db("____");
 
if(isset($_POST['submit'])) {
    $song = mysql_escape_string($_POST['song']);
    $artist = mysql_escape_string($_POST['artist']);
    $year = mysql_escape_string($_POST['year']);
    $description = mysql_escape_string($_POST['description']);
    $id = $_POST['id'];
    
    $query1 = "UPDATE singles SET song = '$song', artist = '$artist', year = '$year', description = '$description' WHERE id = '$id'";
    mysql_query($query) or die('Error, update failed.');
     
    echo "Your song has been updated.  You'll be redirected to the music page in 3 seconds, be patient!";
    echo "<meta http-equiv=Refresh content=3;url=music.php>";
} elseif (
    isset($_GET['id'])) {
        $result = mysql_query("SELECT * FROM singles WHERE id='$_GET[id]' ");
        while($myrow = mysql_fetch_assoc($result)) {
            $song = $myrow["song"];
            $artist = $myrow["artist"];
            $year= $myrow["year"];
            $description = $myrow["description"];
        }
}
?>
 
<form method="post" action="<?php echo $PHP_SELF; ?>">
<input type=hidden name=id value="<? $id2=$_GET[id]; echo $id2;?>">
<table border=0 cellpadding=0 cellspacing=0>
<tr><td><?=$TEXT['cds-attrib2']?>:&nbsp;&nbsp;</td><td><input type=text size=30 name=song value="<? echo $song; ?>" /></td></tr>
<tr><td><?=$TEXT['cds-attrib1']?>:&nbsp;&nbsp;</td><td><input type=text size=30 name=artist value="<? echo $artist; ?>" /></td></tr>
<tr><td><?=$TEXT['cds-attrib3']?>:&nbsp;&nbsp;</td><td><input type=text size=5 name=year value="<? echo $year; ?>" /></td></tr>
<tr><td valign=top><?=$TEXT['cds-attrib6']?>:*&nbsp;&nbsp;</td><td><textarea name=description rows=3 cols=24><? echo $description; ?></textarea></td></tr>
<tr><td></td><td><input type=submit name=submit border=0 value="<?=$TEXT['cds-button3']?>"></td></tr>
</table>
</form>
Here's a rundown of what's happening. The code first connects to the database and selects the proper table, which works fine. Upon first visit, nothing has been submitted at line 9, so we go to the elseif on line 21. Upon visiting this page, the id for the table is brought along, and the code properly sees that the $_GET['id'] is set, so it properly goes through with the SELECT query, and properly assigns each column I've called to a variable. These variables are so that when a person clicks on the edit button and is taken to the edit page, each field is already filled in for them to easily edit the song they've chosen. This part of the code works 100% at this point.

At the bottom, you see all the form data. The first input is a hidden field which grabs the id of the row in the database that we are editing. I've changed this hidden field to a text momentarily to visually verify it's grabbing the right id for the row we want to edit, and this works 100%, it's getting the right value. The rest of the fields are printed, and each field is properly filled with the data gathered from lines 22 through 28 depending on which row we are editing.

Once the submit button is pressed to edit, it's pointed back at the same file, at which point the php code is ran again from the top. The database connects fine once again, and at line 9, the code properly sees the submit button has been pressed. I have it so that the query is stored in a variable at line 16, then the variable is accessed through the mysql_query command at line 17. However, it's at this point that instead of the query going through, I'm presented with the "or die()" message that I've set. If I take away the "or die()" message, it will echo the string on line 19, and properly redirect me back to my music page after 3 seconds without any changes being made.

I've been racking my brain over this the past couple of hours, and I simply cannot figure out what is going wrong. Why will the UPDATE query not work? Is it something wrong with my syntax? I'm going to die when somebody points out something very simple. From my run-through with the code, it seems like it should work. However, my problem has to be line 16.

I appreciate any and all advice.
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: Having problems with a simple mysql query. Please help.

Post by EverLearning »

Code: Select all

$query1 = "UPDATE singles SET song = '$song', artist = '$artist', year = '$year', description = '$description' WHERE id = '$id'";
mysql_query($query) or die('Error, update failed.');
your sql statement is in $query1 var, and you're passing $query to mysql_query().
blurredvision
Forum Newbie
Posts: 9
Joined: Fri Apr 04, 2008 5:34 am

Re: Having problems with a simple mysql query. Please help.

Post by blurredvision »

BWAHAHAHAHA!!! Brilliant! :banghead:

I originally had those 2 lines combined in one, and decided to split them off about halfway through. I changed my query's variable name, and never synced it up with the line below it.

:oops: Thanks everlearning!
Post Reply