SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    Apr 2010
    Posts
    390
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Error, modify details failed : You have an error in your SQL syntax; check the manual

    Hi i am currently using xampp localhost i have this script to modify data from the database but i keep getting this error
    Code:
    Error, modify details failed : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= 'wed', news_story = 'k', news_image_caption = 'm', ' at line 2
    the same script work with a different form and table but on the script that gives the error i have 15 columns on the table so i was wondering if perhaps gives me the above error because i have too many columns on this particular table. and xampp localhost doesnt allow that many columns??

    this is my table i dont see anything wrong with i dnt think is the update script because it works fine with a table with less fields any idea what causes this error.
    here my table
    Code:
    CREATE TABLE IF NOT EXISTS `news` (
      `news_id` int(11) NOT NULL AUTO_INCREMENT,
      `news_title` varchar(180) DEFAULT NULL,
      `news_story` text NOT NULL,
      `news_image` varchar(252) DEFAULT NULL,
      `news_image_caption` varchar(92) NOT NULL DEFAULT '',
      `news_image_link` varchar(180) NOT NULL DEFAULT 'http://www.',
      `news_date_day` varchar(4) DEFAULT '1',
      `news_date_month` varchar(4) DEFAULT '1',
      `news_date_year` varchar(4) DEFAULT '2009',
      `news_status` varchar(11) DEFAULT 'off',
      `news_website` varchar(32) DEFAULT 'home',
      `news_date_created` varchar(14) DEFAULT '000000000000',
      `news_date_modified` varchar(14) DEFAULT '000000000000',
      `news_client_id` int(11) DEFAULT NULL,
      `news_client` varchar(12) DEFAULT 'HOMEONFILM',
      PRIMARY KEY (`news_id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=572 ;

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,068
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Can you please post the query concerned?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SitePoint Addict
    Join Date
    Apr 2010
    Posts
    390
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    Can you please post the query concerned?
    this is the modify form
    PHP Code:
    <?php 
    mysql_connect
    ("localhost""root""") or die(mysql_error()) ;
    mysql_select_db("upload") or die(mysql_error()) ;

    // query to get records 
    $news_id $_GET['news_id'] ;

    // create query to delete record 

    $query "SELECT news_id, news_title, news_story, news_image, news_image_caption, news_image_link, news_date_day, news_date_month, news_date_year, news_status, news_website FROM news WHERE news_id = '$news_id' ";
    //Run the query

    $result mysql_query($query);

    //see if any rows were returned 
    if (mysql_num_rows($result) > 0) {  // yes - Display Form
      
    $row mysql_fetch_array($result); //Fetch the row

      
      //Display the form with original values 
    ?>        

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
              "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" 
                                               lang="en">
    <head>
       <title>My xhtml page styled with CSS</title>
       <meta http-equiv="Content-Type"
             content="text/html; charset=utf-8" />
       <link href="style/style.css" rel="stylesheet" type="text/css" />
    <style type="text/css">
    <!--
    body {
        background-color: #FFFFFF;
    }
    .style1 {font-size: 12px}


    -->


    </style>
    <h1 align="center"><a href="list.php">list</a>  Admin Center   <a href="form.php">upload form</a></h1>

    <body  marginheight="0" marginwidth="0" leftmargin="0" topmargin="0">
    <center>


     <br />
    <form action = 'update.php' method="POST" enctype="multipart/form-data">
    <table width="780" border="0" cellspacing="0" cellpadding="4" bgcolor="#eeeeee">
     <tr>
       <td colspan="2" align="left" valign="top"><input type="hidden"  readonly="yes" name="news_id" class='text_box' value="<?php print $row["news_id"?>"></td>
       </tr>            
      <tr>
       <td align="left" valign="top" width="140"><span class="hofblack12bold">News Date:</span></td>
        <td width="625"><table width="180" border="0" cellspacing="0" cellpadding="0">                                

     <tr>                                    

     <td align="right"><span class="hofblack10">Day</span></td>                                    

     <td align="right"><span class="hofblack10">Month</span></td>                                    

     <td align="right"><span class="hofblack10">Year</span></td>                                

     </tr>                                

     <tr>                                    

     <td align="right"><select name="news_date_day" id="valdaynum" size="1">                                            

     <option  <?php if($row['news_date_day'] == "") { print("selected"); } ?> value="">Select...</option>

     <?php                                    

     
    for ( $loopday 01$loopday <= 31$loopday += 1

     {                                    

     if(
    $loopday <10) {                                        

     
    $loopday="0".$loopday;                                    

     }                                        

     echo 
    "<option ";                                            

     if(
    $row['news_date_day'] == "$loopday"

     {                                                

     echo 
    "selected ";                                            

     }                                        

     echo 
    "value='$loopday'>$loopday</option>";                                

     }                            

     
    ?>                                        

     </select></td>                                    

     <td align="right"><select name="news_date_month" id="valmonth" size="1">                                            

     <option  <?php if($row['news_date_month'] == "") { print("selected"); } ?> value="">Select...</option>

     <?php                                    

     
    for ( $loopmonth 01$loopmonth <= 12$loopmonth += 1

     {                                    

     if(
    $loopmonth <10

     {                                        

     
    $loopmonth="0".$loopmonth;                                    

     }                                        

     echo 
    "<option ";                                            

     if(
    $row['news_date_month'] == "$loopmonth"

     {                                                

     echo 
    "selected ";                                            

     }                                        

     echo 
    "value='$loopmonth'>$loopmonth</option>";                                

     }                            

     
    ?>                                        

     </select></td>                                    

     <td align="right">
     <select name="news_date_year" id="valyear" size="1">                                            

     <option value="2009">2009</option>                                            

     <option value="2010">2010</option>    

     <option value="2011">2011</option>                                        

     </select></td>                                

     </tr>                            

     </table></td>
        
      </tr>
      <tr>
       <td align="left" valign="top" width="140"><span class="hofblack12bold">Title:</span></td>
        <td><input type="text" name="news_title" class='text_box' value="<?php print $row["news_title"?>" size="80"></td>
      </tr>
      <tr>
       <td align="left" valign="top" width="140"><span class="hofblack12bold">Image:</span></td>
        <td><input type="file" name="news_image"  value="" class='text_box'><?php echo '<img class="picture" src="images/' $row['news_image'] . ' " width="120"  height="120 " />'?></td>
      </tr>
      <tr>
       <td align="left" valign="top" width="140"><span class="hofblack12bold">URL Image Link:</span></td>
        <td><input type="text"  name="news_image_link" class='text_box' value="<?php print $row["news_image_link"?>" size="88" ></td>
      </tr>
      <tr>
       <td align="left" valign="top" width="140"><span class="hofblack12bold">Image Caption:</span></td>
        <td><input type="text"  name="news_image_caption" class='text_box' value="<?php print $row["news_image_caption"?>" size="80" /></td>
      </tr>
      <tr>
        <td align="left" valign="top" width="140"><span class="hofblack12bold">Story:</span></td>
        <td><textarea name="news_story" rows=20 cols=75 ><?php print $row["news_story"?> </textarea>
    </td>
      </tr>
      <tr>
        <td align="left" valign="top" width="140"><span class="hofblack12bold">Publish To:</span></td>
        <td><select name="news_website" size="1">                                

     <option  <?php if($row['news_website'] == "") { print("selected"); } ?> value="">Select...</option>                                <option  <?php if($row['news_website'] == "hof") { print("selected"); } ?> value="hof">HOF</option>                                <option  <?php if($row['news_website'] == "eaof") { print("selected"); } ?> value="eaof">EAOF</option>                                <option  <?php if($row['news_website'] == "all") { print("selected"); } ?> value="all">ALL</option>                            

     </select></td>
      </tr>
      <tr>
        <td><span class="hofblack12bold">Status:</span></td>
        <td><select name="news_status" size="1">                        
     <option  <?php if($row['news_status'] == "")
      {
       print(
    "selected");
      }
     
    ?> value="">Select...</option>
     <option  <?php if($row['news_status'] == "on")
      {
       print(
    "selected");
      }
     
    ?> value="on">On</option>                                
     <option  <?php if($row['news_status'] == "off")
      {
       print(
    "selected");
      }
     
    ?> value="off">Off</option>
     <option  <?php if($row['news_status'] == "delete")
      {
       print(
    "selected");
      } 
    ?> value="deleted">Delete</option>
    </select></td>
      </tr>
      <tr>
       <td align="left" valign="top" bgcolor="white" width="140"></td>
       <td align="left" valign="top" bgcolor="white"><input type="submit" value="Submit" name="Submit" ></td>
      </tr>
    </table>

      </form>        
    <?php         
     
    //End if rows returned
       //No rows returned
       
    else  print "No records were found";
    ?> 

    </center>
    </body> 
    </html>
    and this the action script
    PHP Code:
    <?php
    mysql_connect
    ("localhost""root""") or die(mysql_error()) ;
    mysql_select_db("upload") or die(mysql_error()) ;

    // my file the name of the input area on the form type is the extension of the file
    //echo $_FILES["myfile"]["type"];

    //myfile is the name of the input area on the form 

    $name $_FILES["news_image"]["name"]; // name of the file
    $type $_FILES["news_image"]["type"]; //type of the file
    $size $_FILES["news_image"]["size"]; //the size of the file
    $temp $_FILES["news_image"]["tmp_name"];//temporary file location when click upload it temporary stores on the computer and gives it a temporary name
    $error =array(); // this an empty array where you can then call on all of the error messages
    $allowed_exts = array('jpg''jpeg''png''gif'); // array with the following extension name values
    $image_type = array('image/jpg''image/jpeg''image/png''image/gif'); // array with the following image type values
    $location 'images/'//location of the file or directory where the file will be stored
    $appendic_name "news".$name;//this append the word [news] before the name so the image would be news[nameofimage].gif

    // substr counts the number of carachters and then you the specify how how many you letters you want to cut off from the beginning of the word example drivers.jpg it would cut off dri, and would display vers.jpg
    //echo $extension = substr($name, 3);

    //using both substr and strpos, strpos it will delete anything before the dot in this case it finds the dot on the $name file deletes and + 1 says read after the last letter you delete because you want to display the letters after the dot. if remove the +1 it will display .gif which what we want is just gif
    $extension strtolower(substr($namestrpos ($name'.') +1));//strlower turn the extension non capital in case extension is capital example JPG will strtolower will make jpg
    // another way of doing is with explode
    // $image_ext strtolower(end(explode('.',$name))); will explode from where you want in this case from the dot adn end will display from the end after the explode

    $news_id $_POST["news_id"];
    $news_title $_POST["news_title"];
    $news_story $_POST["news_story"];
    $news_image $_FILES['news_image'];
    $news_image_caption $_POST["news_image_caption"];
    $news_image_link $_POST["news_image_link"];
    $news_date_day $_POST["news_date_day"];
    $news_date_month $_POST["news_date_month"];
    $news_date_year $_POST["news_date_year"];
    $news_status $_POST["news_status"];
    $news_website $_POST["news_website"];


     
    $timestamp date('Ymdhis');                    

    if(
    $name==""// if name is empty just update these fields on the database
    {    
         
    $query "UPDATE news 
                   news_title           = '
    $news_title',
                   news_story            = '
    $news_story',
                   news_image_caption   = '
    $news_image_caption',
                   news_image_link      = '
    $news_image_link',
                   news_date_day        = '
    $news_date_day',
                   news_date_month      = '
    $news_date_month',
                   news_date_year       = '
    $news_date_year',
                   news_status          = '
    $news_status',
                   news_website          = '
    $news_website',
                   WHERE news_id='
    $news_id'";                    

       
    mysql_query($query
           or die(
    'Error, modify details failed : ' mysql_error());
              
    header("Location: list.php");
        
    }
    else 
    // if image has name execute these lines
    {
                      
            
    // if extension is not equal to any of the variables in the array $allowed_exts error appears
            
    if(in_array($extension$allowed_exts) === false )
           {
             
    $error[] = 'Extension not allowed! gif, jpg, jpeg, png only<br />'// if no errror read next if line
             
           
    }
            
    // if file type is not equal to any of the variables in array $image_type error appears
            
    if(in_array($type$image_type) === false)
           {
              
    $error[] = 'Type of file not allowed! only images allowed<br />';     
           }
           
            
    // if file bigger than the number bellow error message
            
    if($size 2097152)
           {
              
    $error[] = 'File size must be under 2MB!';     
           }
           
           
    // check if  folder exist in the server
            
    if(!file_exists ($location))
           {
              
    $error[] = 'No directory ' $location' on the server Please create a folder ' .$location;     
           }            
         
    // if no error found do the move upload function
           
    if (empty($error)){
               
    move_uploaded_file($temp$location .$appendic_name);
               
                 
    // update data into database first are the field name teh values are the variables you want to insert into those fields appendic is the new name of the image
                 
    $sql "SELECT news_image
                  FROM news
                  WHERE news_id = '
    $news_id'";

          
    $result mysql_query($sql
                    or die(
    'Error, get details info failed. ' 
                            
    mysql_error());
          
    $row mysql_fetch_assoc($result);
          
    unlink($location $row['news_image']);//remove the image from the folder remmebr $location is 
         
    $query "UPDATE news 
                   news_title           = '
    $news_title',
                   news_story            = '
    $news_story',
                   news_image           = '
    $appendic_name'
                   news_image_caption   = '
    $news_image_caption',
                   news_image_link      = '
    $news_image_link',
                   news_date_day        = '
    $news_date_day',
                   news_date_month      = '
    $news_date_month',
                   news_date_year       = '
    $news_date_year',
                   news_status          = '
    $news_status',
                   news_website          = '
    $news_website',       
                   WHERE news_id='
    $news_id'";

       
    mysql_query($query
           or die(
    'Error, modify details failed : ' mysql_error());
         
    header("Location: list.php");
               }
           
           else
              {
            foreach (
    $error as $error)
               {
                   echo 
    $error;
               }
              }
    }

    //echo $type;
    ?>

  4. #4
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    echo $query on to the page.

    Look at it carefully. does it have errors? Can you prove it has no errors?

    paste the generated sql statement into your database directly, does it work?

    If it contains errors then chase them back, is it PHPs fault? or did the story title contain apostrophes or something like that?

    You need to protect your database from bad (or malevolent) data by escaping your query.

    examples:
    PHP Code:
    // turn an string id into a integer
    $news_id = (int) $_POST["news_id"]; 

    // escape a string ready for inclusion into your database
    $news_title mysql_real_escape_string($_POST["news_title"]); 
    I cannot see where you are doing this kind of escaping.

  5. #5
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,807
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    You are missing a word and that word is SET

    PHP Code:
    $query "UPDATE news
                   SET 
                   news_title           = '
    $news_title', 
                   news_story            = '
    $news_story', 
                   news_image_caption   = '
    $news_image_caption', 
                   news_image_link      = '
    $news_image_link', 
                   news_date_day        = '
    $news_date_day', 
                   news_date_month      = '
    $news_date_month', 
                   news_date_year       = '
    $news_date_year', 
                   news_status          = '
    $news_status', 
                   news_website          = '
    $news_website', 
                   WHERE news_id='
    $news_id'"
    Same for the other update queries too.

    BTW, heed what Cups says otherwise or it could come back to bite you in the future!
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  6. #6
    SitePoint Addict
    Join Date
    Apr 2010
    Posts
    390
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much yes that fix the problem

  7. #7
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    absence of SET, doh...


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
  •