SQL error when trying to update content with PHP

Question

I have been trying to edit content with a form from my database with PHP and I keep getting the error, "Query Failed. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax." I can't find where this error is coming from anywhere. Any advice would be greatly appreciated.

Here is my code:

<?php
  if(isset($_GET['p_id'])) {
      $the_post_id = $_GET['p_id'];

  }

  $query = "SELECT * FROM posts WHERE post_id = $the_post_id";
  $select_posts_by_id = mysqli_query($connection,$query);

  while($row = mysqli_fetch_assoc($select_posts_by_id)) {
    $post_id = $row['post_id'];
    $post_author = $row['post_author'];
    $post_title = $row['post_title'];
    $post_category_id = $row['post_category_id'];
    $post_status = $row['post_status'];
    $post_image = $row['post_image'];
    $post_content = $row['post_content'];
    $post_tags = $row['post_tags'];
    $post_comment_count = $row['post_comment_count'];
    $post_date = $row['post_date'];
  }

  // update post data

  if(isset($_POST['update_post'])) {

    $post_author = $_POST['post_author'];
    $post_title = $_POST['post_title'];
    $post_category_id = $_POST['post_category'];
    $post_status = $_POST['post_status'];
    $post_image = $_FILES['image']['name'];
    $post_image_temp = $_FILES['image']['tmp_name'];
    $post_content = $_POST['post_content'];
    $post_tags = $_POST['post_tags'];

    move_uploaded_file($post_image_temp, "../images/$post_image");

    // update query
    $query = "UPDATE posts SET ";
    $query .= "post_title = '{$post_title}', ";
    $query .= "post_category_id = '{$post_category_id}', ";
    $query .= "post_date    = now(), ";
    $query .= "post_author  = '{$post_author}', ";
    $query .= "post_status  = '{$post_status}', ";    
    $query .= "post_tags    = '{$post_tags}', ";
    $query .= "post_content = '{$post_content}', ";
    $query .= "post_image   = '{$post_image}' ";
    $query .= "WHERE post_id = {$the_post_id} ";

    $update_post = mysqli_query($connection,$query);

    confirmQuery($update_post); 
  }

?>

    <form action="" method="post" enctype="multipart/form-data">

        <div class="form-group">
            <label for="title">Post Title</label>
            <br>
            <input value="<?php echo $post_title; ?>" type="text" class="form-control" name="post_title">
        </div>

        <div class="form-group">
            <select name="post_category" id="">

                <?php 
                  // select categories as dropdown

                  $query = "SELECT * FROM categories";
                  $select_categories = mysqli_query($connection,$query);    

                  confirmQuery($select_categories);

                  while($row = mysqli_fetch_assoc($select_categories )) {
                    $cat_id = $row['cat_id'];
                    $cat_title = $row['cat_title'];

                    echo "<option value=''>{$cat_title}</option>";
                  }

                ?>

            </select>

        </div>

        <div class="form-group">
            <label for="title">Post Author</label>
            <br>
            <input value="<?php echo $post_author; ?>" type="text" class="form-control" name="post_author">
        </div>

        <div class="form-group">
            <label for="post_status">Post Status</label>
            <br>
            <input value="<?php echo $post_status; ?>" type="text" class="form-control" name="post_status">
        </div>

        <div class="form-group">
            <img width="100" src="../images/<?php echo $post_image; ?>" alt="" />
            <input type="file" name="image">
        </div>

        <div class="form-group">
            <label for="post_tags">Post Tags</label>
            <br>
            <input value="<?php echo $post_tags; ?>" type="text" class="form-control" name="post_tags">
        </div>

        <div class="form-group">
            <label for="post_content">Post Content</label>
            <br>
            <textarea class="form-control" name="post_content" id="" cols="30" rows="10">
                <?php echo $post_content; ?>

            </textarea>
        </div>

        <div class="form-group">
            <input class="btn btn-primary" type="submit" name="update_post" value="Update Post">
        </div>

    </form>

Thank you!

You are wide open to [**SQL injection**](https://www.owasp.org/index.php/SQL_Injection). You need to use prepared statements, rather than concatenating variables into your query. See [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1). — elixenide, Mar 30 '18 at 04:27
Thanks Mahidul. This is more of a learning project than anything I would use in a real project, since I fairly new to developing with PHP. I am trying to branch out from front end design, but my question did not seem very well taken and has been down voted. The problem ended up being the settings in my database. Do you have any tips for someone with a real passion to excel with PHP to ask questions that don't get negative reactions? — JayG.Dev, Apr 08 '18 at 16:04

score 0 · Answer 1 · answered Mar 30 '18 at 03:30

Based on the snippet you've posted here, this is likely an issue to do with variable scope. Since you haven't declared $the_post_id outside of your if {} block, the variable itself won't be accessible. The generated query would then be:

SELECT * FROM posts WHERE post_id =

which is not correct SQL syntax.

Somehow, you'll need to make the declaration in order to utilize it properly:

<?php
$the_post_id = "";
if(isset($_GET['p_id'])) {
    $the_post_id = $_GET['p_id'];
}
// rest of code...

As a side note, you really should consider using prepared statements as your code (as it stands now) is extremely vulnerable to SQL injection.

This is *not* a scope problem. `if` blocks are not scopes in PHP. It is, however, a problem of failing to define the variable in all scenarios before it is used. — elixenide, Mar 30 '18 at 04:29

score 0 · Answer 2 · answered Mar 30 '18 at 03:49

0

You cannot retrieve a record from posts table when you're not passing $_GET['p_id'] with any value. It will throw that error if that variable is empty.

Your UPDATE query will also fail without that variable having a value.

answered Mar 30 '18 at 03:49

Karlo Kokkak

3,674
4
18
33

Jonny · Answer 3 · 2018-03-30T14:55:48.513

0

Capitalize NOW(). The NOW() function is case sensitive.

    $query .= "post_date    = now(), ";

Should be

    $query .= "post_date    = NOW(), ";

edited Mar 30 '18 at 14:55

answered Mar 30 '18 at 05:25

Jonny

1,319
1
14
26

SQL error when trying to update content with PHP

3 Answers3