SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2003
    Location
    Ontario, Canada
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    "UPDATE" SQL error

    I've been having problems creating an update script for the reviews in my database. I want to be able to have my staff edit their own reviews. Now I know how to list the anime that they've reviewed, but I've run into some trouble when it comes to editing the review. The following is the php/html for the first staff member to update their reviews:

    PHP Code:
    <?php
    if (!$submit)
    {
        
    $query "SELECT * FROM animereview WHERE id = '$id'";
        
    $result mysql_query($query) or die ("Error in query: $query. " mysql_error());
        
        if (
    mysql_num_rows($result) > 0)
        {
            
    $row mysql_fetch_object($result);

            if (
    $row->staffid 1)
            {
    ?>
    <table width="100%" cellpading="1" cellspacing="0" border="0" id="con">
    <form action="<? echo $PHP_SELF?>" method="POST">
    <input type="hidden" name="id"  value="<?php echo $id?>">
    <input type="hidden" name="staffid"  value="1">
    <input type="hidden" name="animeid"  value="<?php echo $row->animeid?>">
    <input type="hidden" name="date"  value="<?php echo $row->date?>">
    <tr>
    <td width="25%" valign="top"><b>Review:</b></td>
    <td width="75%"><textarea cols="50" rows="10" name="review"><? echo $row->review?></textarea></td>
    </tr>
    <tr>
    <td width="25%"><b>Animation:</b></td>
    <td width="75%"><input size="4" type="text" name="animation" value="<? echo $row->animation?>"></td>
    </tr>
    <tr>
    <td width="25%"><b>Plot:</b></td>
    <td width="75%"><input size="4" type="text" name="plot" value="<? echo $row->plot?>"></td>
    </tr>
    <tr>
    <td width="25%"><b>Character:</b></td>
    <td width="75%"><input size="4" type="text" name="character" value="<? echo $row->character?>"></td>
    </tr>
    <tr>
    <td width="25%"><b>Music:</b></td>
    <td width="75%"><input size="4" type="text" name="music" value="<? echo $row->music?>"></td>
    </tr>
    <tr>
    <td width="25%"><b>Value:</b></td>
    <td width="75%"><input size="4" type="text" name="value" value="<? echo $row->value?>"></td>
    </tr>
    <tr>
    <td width="25%"><b>Enjoyment:</b></td>
    <td width="75%"><input size="4" type="text" name="enjoyment" value="<? echo $row->enjoyment?>"></td>
    </tr>
    <tr>
    <td width="25%"></td>
    <td width="75%"><input type="Submit" name="submit" value="Edit Review"></td>
    </tr>
    </form>
    </table>
    <?
            
    }
            else
            {
                echo 
    "You do not have permission to edit this review";
            }
        }
        else
        {
            echo 
    "That review does not exist";
        }
    }
    else
    {
        
    $query "UPDATE animereview SET animeid='$animeid', staffid='$staffid', review='$review', animation='$animation', plot='$plot', character='$character', music='$music', value='$value', enjoyment='$enjoyment', date='$date' WHERE id=$id";
        
    $result mysql_query($query) or die ("Error in query: $query. " mysql_error());
        echo 
    "Review successfully edited";
    }
    ?>
    Now whenever I click submit the following error is displayed:
    Error in query: UPDATE animereview SET animeid='1', staffid='1', review='test', animation='10', plot='10', character='10', music='10', value='10', enjoyment='10', date='2003-06-02' WHERE id=1. You have an error in your SQL syntax near 'character='10', music='10', value='10', enjoyment='10', date='2003-06-02' WHERE ' at line 1
    My table information is as follows:
    Table: animereview

    field: id
    type: int(11)
    extra: auto_increment

    field:animeid
    type: int(11)

    field:staffid
    type: int(11)

    field: review
    type: text

    field: animation
    type: int(11)

    field: plot
    type: int(11)

    field: character
    type: int(11)

    field: music
    type: int(11)

    field: value
    type: int(11)

    field: enjoyment
    type: int(11)

    field: date
    type: date


    Any help to resolve this issue would be much appreciated.

  2. #2
    SitePoint Enthusiast int3rface's Avatar
    Join Date
    Jun 2003
    Location
    Hull
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    right i'm not expert but looking at your script and having similar trouble this week and finally getting there i would do the folllowing:

    after the PHP start tag you need to get the ID:
    PHP Code:
    $id $_GET['id']; 
    otherwise mySQL won't know which row to update,

    also do you need to select everything in the database (*) as this is a waste of resources if not select each field in turn, i would change it to following:

    PHP Code:
    if (!$submit)
    {
        
    $result mysql_query ("SELECT id, animeid, staffid, review, animation, plot, character, music, value, enjoyment, date");
    if (!
    $result) {
      die (
    '<p>Error retrieving data from database<br />'.
        
    'Error:' mysql_error() . '</p>');
    }
    while (
    $row mysql_fetch_array($result)) {
      
    $id $row['id'];
      
    $animeid $row['animeid'];
      
    $staffid $row['staffid']; 
    i've not done all of them for the while statement but I presume you get the drift, also if any field is likely to have html characters in them change it to this:
    PHP Code:
    $animeid htmlspecialchars($row['animeid']); 
    obviously a field that as an ID won't need htmlspecialcharacters but your text ones could potentially and you want to make it idiot proof!

    change your hidden input to this:
    PHP Code:
    <input type="hidden" name="ID" value="<?=$id?>" />
    and in the form this for textareas:

    <td width="75%"><textarea cols="50" rows="10" name="review"><?=$review ?></textarea></td>

    Finally for input boxes this:

    PHP Code:
    <td width="75%"><input size="4" type="text" name="enjoyment" value="$enjoyment ?>"></td
    That how i've done my news, reports, fixtures section and it works for me. Hope that helps, any trouble just post.

    Darren

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2003
    Location
    Ontario, Canada
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Whenever I attempt to use
    PHP Code:
    result mysql_query ("SELECT id, animeid, staffid, review, animation, plot, character, music, value, enjoyment, date" ); 
    or even

    PHP Code:
    result mysql_query ("SELECT animeid, staffid, review, animation, plot, character, music, value, enjoyment, date FROM animreview WHERE id=$id); 
    Neither work.. Thus I am assuming this problem is linked to the error I receive using UPDATE, as the SELECT only works for me when I use *

  4. #4
    SitePoint Enthusiast int3rface's Avatar
    Join Date
    Jun 2003
    Location
    Hull
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try this:

    PHP Code:
    $result mysql_query ("SELECT id, animeid, staffid, review, animation, plot, character, music, value, enjoyment, date FROM animreview WHERE id='$id' " ); 
    adding single quotes around $id, sorry I missed the where bit out before.
    I could always send you a file I've been working on, it simarly is trying to update a table. I'll be up for a while so post back if that starts working, you could always echo result to see if it is retrieving anything.

    Darren

  5. #5
    SitePoint Enthusiast
    Join Date
    Apr 2003
    Location
    Ontario, Canada
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It still doesn't work, although my problem is not with the SELECT, I can get it to display all the info I need using *, and since I need all the fields anyway it does not waste any resources. In addition my problem lies with the
    PHP Code:
    query "UPDATE animereview SET animeid='$animeid', staffid='$staffid', review='$review', animation='$animation', plot='$plot', character='$character', music='$music', value='$value', enjoyment='$enjoyment', date='$date' WHERE id=$id"
        
    $result mysql_query($query) or die ("Error in query: $query. " mysql_error()); 
        echo 
    "Review successfully edited"
    Which returns with an error, rather than updating the row in the database.

  6. #6
    SitePoint Enthusiast int3rface's Avatar
    Join Date
    Jun 2003
    Location
    Hull
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try this:

    PHP Code:
    $query "UPDATE animereview SET animeid='$animeid', staffid='$staffid', review='$review', animation='$animation', plot='$plot', character='$character', music='$music', value='$value', enjoyment='$enjoyment', date='$date' WHERE id='$id' "
    again adding the single quotes around $id, also notice i've added the dollar sign, not sure if you've just missed that on the copy and paste, this is what I use in mine:

    PHP Code:
      $sql "UPDATE fixtures SET
              reporttext='
    $reporttext',
              hscore='
    $hscore',
              ascore='
    $ascore',
              manofthematch='
    $manofthematch'
              WHERE ID='
    $id'";
      if (@
    mysql_query($sql)) {
        echo(
    '<p>Match report updated.</p>');
      } else {
        echo(
    '<p>Error updating match report: ' .
             
    mysql_error() . '</p>');
      } 
    hope that helps!

  7. #7
    SitePoint Enthusiast
    Join Date
    Apr 2003
    Location
    Ontario, Canada
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok I changed my script around a bit it's now as follows:
    PHP Code:
    $sql "UPDATE animereview SET
        animeid='
    $animeid',
        staffid='
    $staffid',
        review='
    $review',
        animation='
    $animation',
        plot='
    $plot',
        character='
    $character',
        music='
    $music',
        value='
    $value',
        enjoyment='
    $enjoyment',
        date='
    $date'
        WHERE id='
    $id'";
        if (@
    mysql_query($sql))
        { 
            echo(
    'Review succesfully Edited'); 
        }
        else
        { 
            echo(
    '<p>Error editing review: ' 
            
    mysql_error() . '</p>'); 
        } 
    But I get the following error:
    Error editing review: You have an error in your SQL syntax near 'character='1', music='1', value='1', enjoyment='1', date='2003-06-02' WHERE' at line 7

  8. #8
    SitePoint Enthusiast int3rface's Avatar
    Join Date
    Jun 2003
    Location
    Hull
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    looks completely correct to me, as I was told on a similar post:

    1) Double check you have types/cases/variable names matching exactly
    2) Use mysql_error() to tell you what the problem is
    3) Check each variable (particularly $ID) is getting set by echoing them.
    4) Check your if works. Do a simple echo "********!"; to check the update statement is even being accessed.
    5) If all else fails, go to the pub
    sorry I can't be of better use but thats baffled me!

  9. #9
    SitePoint Enthusiast
    Join Date
    Apr 2003
    Location
    Ontario, Canada
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I threw in an echo "test!"; before and after the $sql as such:

    PHP Code:
        echo "test!";
        
    $sql "UPDATE animereview SET
        animeid='
    $animeid',
        staffid='
    $staffid',
        review='
    $review',
        animation='
    $animation',
        plot='
    $plot',
        character='
    $character',
        music='
    $music',
        value='
    $value',
        enjoyment='
    $enjoyment',
        date='
    $date'
        WHERE id='
    $id'";
        if (@
    mysql_query($sql))
        { 
            echo(
    'Review succesfully edited'); 
        }
        else
        { 
            echo(
    '<p>Error editing review: ' 
            
    mysql_error() . '</p>'); 
        } 
        echo 
    "test!"
    And I receive the following:
    test!
    Error editing review: You have an error in your SQL syntax near 'character='1', music='1', value='1', enjoyment='1', date='2003-06-02' WHERE' at line 7

    test!
    So it's obviously going to the UPDATE statement. With regards to the other issues, the case and variables are obviously right, so I do not understand what is wrong.

    Also line 7 is:
    PHP Code:
        character='$character'
    So perhaps something is wrong with that, or the $character variable

  10. #10
    + platinum's Avatar
    Join Date
    Jun 2001
    Location
    Adelaide, Australia
    Posts
    6,441
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    is character a reserverd mysql word? try putting backticks on it `character` just to check...

    edit: yep is is... http://www.mysql.com/doc/en/Reserved_words.html

  11. #11
    SitePoint Enthusiast
    Join Date
    Apr 2003
    Location
    Ontario, Canada
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    lol and that would explain why the SELECT would not work when I would use character. I will change it in my database to something else, maybe character_rating or something like that. I will let you know if it works.

  12. #12
    SitePoint Enthusiast
    Join Date
    Apr 2003
    Location
    Ontario, Canada
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's working now.. I am extremely thankful for all your help (platinum and int3rface), I don't know what I would have done had I not had your help. Thank you so much ^_^

  13. #13
    SitePoint Enthusiast int3rface's Avatar
    Join Date
    Jun 2003
    Location
    Hull
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    see what happens when an expert comes in, would have never known character was a reserved mysql word, only a newbie myself but kevs book as wettered the appetite. Good luck xarnet and your welcome.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •