Insert data in table (diary) and update the data in the (category) table mysql

Question

I am trying to insert the image name in the table (diary) and at the same time I am trying to update the image name in the table (category). but the value is not updating the in the category. I tried all the available solutions on the internet but nothing worked can someone help me?

I want to update the (category) table "cat_img" whenever the user adds a new image to the table (diary) diary_thumbnail_image.

Please help me it will be appreciated.

here is my code add_diary.php

<?php include 'header.php';
if ($admin != 1 && $admin != 2) {
    header("location:index.php");
}
if (isset($_SESSION['user_data'])) {
    // This will fetch the author id that is stored in index['0'].
    $author_id = $_SESSION['user_data']['0'];
}
    $sql = "SELECT * FROM categories";
    $query = mysqli_query($config, $sql);

    $sql_school = "SELECT * FROM school";
    $query_school = mysqli_query($config, $sql_school);
?>
                     <form method="POST" action="" enctype="multipart/form- 
                     data"
                            this diary:</strong></label>
                              <select class="form-control" 
                                 name="show_in_school">
                                 <option value="" 
                                 name="show_in_school">Select School 
                                 Name</option>
                                 <!-- $cats will fetch all the data that 
                                 is stored in the categories. -->
                                 <?php while ($school_result = 
                                 mysqli_fetch_assoc($query_school)) { ?>
                                 <option value="<?= 
                                 $school_result['school_title'] ?>">
                                       <?= $school_result['school_title'] ?>
                                    </option>
                                <?php } ?>
                                }
                              </select>
                        </div>
                        <div class="mb-3">
                            <input type="submit" name="add_diary" 
                            value="Add" class="btn btn-primary">
                            <a class="btn btn-secondary" 
                            href="diary_information.php">Back</a>
                        </div>  
                     </form>         
<?php
              if(isset(add_diary)){
                $select_cat = "SELECT * FROM categories";
                $query_cat = mysqli_query($config, $select_cat);
                $cat_result = mysqli_fetch_array($query_cat);
                $get_cat_id = $cat_result['cat_id'];

                // FACING THE ISSUE HERE
                $cat_up = "UPDATE categories SET cat_name='$category' 
                cat_img='$filename' WHERE
                cat_id='$get_cat_id'";
                $query_up = mysqli_query($config, $cat_up);

                if ($query_up) {
                    $msg = ["Post Publish Successfully", "alert-success"];
                    $_SESSION['msg'] = $msg;
                    // If the post publish successfully then redirect to 
                    same page with a success message.
                    header("location:add_diary.php");
                } else {
                    $msg = ["Failed, Please try again", "alert-danger"];
                    $_SESSION['msg'] = $msg;
                    // If the post is not able to post then redirect to the 
                    same 
                    page with an error message.
                    header("location:add_diary.php");
                }
              }
?>

I tried to insert using the id but didn't work and checked all the ways to insert and update that were available on the internet but didn't work

// This worked but it stays with the old image and doesn't update the new image.
$sql_up_cat = "UPDATE categories SET cat_img = (SELECT diary_thumbnail_image FROM diary WHERE cat_id = blog_id) ";
$query_up = mysqli_query($config, $sql_up_cat);

You never check if the update query was successful and if not what error is returned. — Shadow, Jan 17 '23 at 20:28
In your UPDATE query, you are missing a `,` between the cat_name and cat_img, so you are getting a sintax error. Besides that, you should use prepared statements to avoid sql injection — nacho, Jan 17 '23 at 20:32
**Warning:** Your code is vulnerable to SQL Injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / mysqli. **Never** insert unparameterised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data. mysqli_real_escape_string is obsolete and doesn't guard against everything. — ADyson, Jan 17 '23 at 20:43
https://phpdelusions.net/mysqli also contains good examples of writing safe SQL using mysqli. See also the [mysqli documentation](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) and this: [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) . Parameterising your queries will also greatly reduce the risk of accidental syntax errors as a result of un-escaped or incorrectly quoted input values. If you learnt your current technique from a tutorial or book, please don't use that resource again. — ADyson, Jan 17 '23 at 20:43
As for "does not work"...that's a meaningless description, and doesn't help us to help you. Luckily, someone spotted a syntax error, but it won't always be that obvious. Since the problem occurs in a query, and you apparently haven't noticed, please read [How to report errors in mysqli](https://phpdelusions.net/mysqli/error_reporting) and improve how you handle and report errors. — ADyson, Jan 17 '23 at 20:45
@Shadow Thank you very much for the help really appreciated it. I was sending the integer in the value of cat_name that's why it was not finding the category name. now it is working thank you so much. — Jenil Shah, Jan 17 '23 at 21:21
@ADyson sorry and thank you so much for the information of errors I'll look into it. — Jenil Shah, Jan 17 '23 at 21:24
Please trim your code to make it easier to find your problem. Follow these guidelines to create a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example). — Community, Jan 18 '23 at 10:25

score -1 · Accepted Answer · answered Jan 17 '23 at 21:33

Actually, I was sending the value of the cat_name in integer to the database that's why it was not able to check the category name and was not updating the image.

I than changed it's value to the String.

                        <div class="mb-3">
                            <label><strong>Category:</strong></label>
                            <select class="form-control" name="category">
                                <option value="" name="category">Select 
                                Category</option>
                                <!-- $cats will fetch all all the data that is store 
                                in the categories. -->
                                <?php while ($cats = mysqli_fetch_assoc($query)) { ? 
                                >
                                    <option value="<?= $cats['cat_name'] ?>">
                                        <?= $cats['cat_name'] ?>
                                    </option>

                                <?php } ?>
                                }
                            </select>
                        </div>

Updated the query to this and then it worked

$cat_up = "UPDATE categories SET cat_img='$filename' WHERE cat_name='$category'";
$query_up = mysqli_query($config, $cat_up);

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Jan 20 '23 at 07:53

Insert data in table (diary) and update the data in the (category) table mysql

1 Answers1