How to update more then one database column

Viewed 29

I was following one guy's tutorial on how to update database with ajax but I had some other intentions, he wanted to make a new row each time he updates his form, where I wanted to update an existing row. I Successfully updated the code to my needs but I want to update more then one column in that same row.

My form has 2 values, Show Announcement and Announcement Content where In text field I update Show Announcement to either 0 or 1 (true, false) and in Announcement Content to some text I want.

Now, the problem is that when trying to add && isset($_POST['anncontent']) && $_POST['anncontent']!='' in if statement in line 19 (marked where it is) and adding another SET option in $sql (SET announcement=('".addslashes($_POST['anncontent'])."')), it gives me status code 500 and neither of these 2 content updates in a database.

.submit.php

<?php
$host = "localhost";
$database = "";
$username = "";
$password = "";

try
{
    $conn = new PDO("mysql:host=$host;dbname=$database", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e)
{
    echo "Connection failed: " . $e->getMessage();
}

$response = array('success' => false);

LINE 19 ---> if(isset($_POST['name']) && $_POST['name']!='' && isset($_POST['anncontent']) && $_POST['anncontent']!='' )
{
    $sql = "UPDATE settings SET ann=('".addslashes($_POST['name'])."') AND SET announcement=('".addslashes($_POST['anncontent'])."')";
    //$sql = "UPDATE settings SET announcement=('".addslashes($_POST['anncontent'])."')";
    
    if($conn->query($sql))
    {
        $response['success'] = true;
    }
}

echo json_encode($response);
?>

index.php

    <form action="" method="post">

        <div> Show Announcement <input type="text" name="name" value="" /></div>
        <div> Announcement Content<input type="text" name="anncontent" value="" /></div>
        <div><button type="button"  onclick="submitForm();" name="save_announcement" value="Save"/>Update</button></div>
        </form>
    </body>
    <script type="text/javascript">
    function submitForm()
    {
        var name = $('input[name=name]').val();
        var anncontent = $('input[name=anncontent]').val();

        if(name != '')
        {       
        var formData = {name: name, anncontent: anncontent};
        $('#message').html('<span style="color: red">Updating...</span>');
        $.ajax({url: "https://localhost/admin/api/submit.php", type: 'POST', data: formData, success: function(response)
        {
            var res = JSON.parse(response);
            console.log(res);
            if(res.success == true)
                $('#message').html('<span style="color: green">Successfuly updated.</span>');
            else
                $('#message').html('<span style="color: red">Error while updating.</span>')
        }
        });
    }
    else
    {   
        $('#message').html('<span style="color: red">Please fill all the fields</span>');
    }
    
}
    </script>

Maybe the problem is in my sql call? I am not sure if that's the right way to update 2 columns in the same line.

I tried adding additional $sql call with only SET announcement but that didn't work. Same error code.

When I try to write something only in Show Announcement text field and press Update, I get #Error While updating# message (The one I set for if success = false) but when I try to set some content in another text field as well, I get a message "Updating..." and I get stuck on that. https://prnt.sc/_MexIxx6dSdJ

Please, let me know if I need to provide more information for this case for you to understand the problem.

1 Answers

I would advice using !empty() "not empty", which does both checks for you. Except that, you can bind 2 parameters doing something like:

if (!empty($_POST['name']) && !empty($_POST['announcement'])) {
$stmt= $pdo->prepare("UPDATE settings SET name = :name, announcement = :announcement");
$stmt->execute([
    'name' => $_POST['name'],
    'announcement' => $_POST['announcement']
]);}

Here is more about PDO

Related