can't add or update new user or page

[Lande-91]

Error i get for user:

User Could not be updated becouse: 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 '' at line 1

Query: UPDATE users SET first = '', last = '', email = 'john@doe.com', password = SHA1('test'), status = 0 WHERE id =

Error i get for pages:

Page Could not be updated becouse: 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 '' at line 1

Query: UPDATE pages SET user = 2, slug = 'test', title = 'test', label = 'test', header = 'test', body = '

"UPDATE pages SET user = $_POST[user], slug = '$_POST[slug]', title = '$title', label = '$label', header = '$header', body = '$body' WHERE id = $_GET[id]";

"INSERT INTO pages (user, slug, title, label, header, body) VALUES ($_POST[user], '$_POST[slug]', '$title', '$label', '$header', '$body')";

"UPDATE users SET first = '$first', last = '$last', email = '$_POST[email]', $password status = $_POST[status] WHERE id = $_GET[id]";

"INSERT INTO users (first, last, email, password, status) VALUES ('$first', '$last', '$_POST[email]', SHA1('$_POST[password]'), '$_POST[status]')";

[Dragotic]

We will need to have a look on your whole code because it seems that the queries are fine. But since Query Syntx error it is probably on the forms.

So please paste your whole code.

[thedigicraft]

Agree with Dragotic

[Lande-91]

    switch ($page) {

        case 'dashboard':

        break;

        case 'pages':

            if(isset($_POST['submitted']) == 1) {

                $title = mysqli_real_escape_string($dbc, $_POST['title']);
                $label = mysqli_real_escape_string($dbc, $_POST['label']);
                $header = mysqli_real_escape_string($dbc, $_POST['header']);
                $body = mysqli_real_escape_string($dbc, $_POST['body']);

                if(isset($_POST['id'])!== '') {
                    $action = 'updated';
                    $q = "UPDATE pages SET user = $_POST[user], slug = '$_POST[slug]', title = '$title', label = '$label', header = '$header', body = '$body' WHERE id = $_GET[id]";

                } else {
                     $action = 'added';
                     $q ="INSERT INTO pages (user, slug, title, label, header, body) VALUES ($_POST[user], '$_POST[slug]', '$title', '$label', '$header', '$body')";
                    }

                $r = mysqli_query($dbc, $q);

                if($r){

                    $message = '<p class="alert alert-success">Page was '.$action.'!</p>';

                } else {

                    $message = '<p class="alert alert-danger">Page Could not be '.$action.' becouse: '.mysqli_error($dbc);
                    $message.= '<p class="alert alert-warning">Query: '.$q. '</p>';

                }

            }

            if(isset($_GET['id'])) { $opened = data_page($dbc, $_GET['id']); }

        break;

        case 'users':

            if(isset($_POST['submitted']) == 1) {

                $first_n = mysqli_real_escape_string($dbc, $_POST['first_n']);
                $last_n = mysqli_real_escape_string($dbc, $_POST['last_n']);

                if($_POST['password'] != '') {

                    if($_POST['password'] == $_POST['passwordv']) {

                        $password = " password = SHA1('$_POST[password]'),";
                        $verify = true;

                    } else {

                        $verify = false;

                    }

                } else {

                    $verify = false;

                }

                if(isset($_POST['id'])!== '') {
                    $action = 'updated';
                    $q = "UPDATE users SET first = '$first', last = '$last', email = '$_POST[email]', $password status = $_POST[status] WHERE id = $_GET[id]";
                    $r = mysqli_query($dbc, $q);

                } else {
                    $action = 'added';

                        $q ="INSERT INTO users (first, last, email, password, status) VALUES ('$first', '$last', '$_POST[email]', SHA1('$_POST[password]'), '$_POST[status]')";

                if($veify == true) {
                    $r = mysqli_query($dbc, $q);
                }

            }

                if($r){

                    $message = '<p class="alert alert-success">User was '.$action.'!</p>';

                } else {

                    $message = '<p class="alert alert-danger">User Could not be '.$action.' becouse: '.mysqli_error($dbc);

                    if($verify == false) {
                        $message.= '<p class="alert alert-danger"> Password fields empy and/or do not match. </p>';
                    }

                    $message.= '<p class="alert alert-warning">Query: '.$q. '</p>';

                }

            }

            if(isset($_GET['id'])) { $opened = data_user($dbc, $_GET['id']); }

        break;

        case 'settings':

        break;

        default:

        break;
    }

[Dragotic]

I just had a quick look because im away from my pc and i found that you had a typo... you used !== instead of !=

[Lande-91]

        <h1>Page Manager</h1>

<div class="row">

    <div class="col-md-3">

        <div class="list-group">

            <a class="list-group-item" href="?page=pages">
                <i class="fa fa-plus"></i>New Page
            </a>    

        <?php

            $q = "SELECT * FROM pages ORDER BY title ASC";
            $r = mysqli_query($dbc, $q);

            while($list = mysqli_fetch_assoc($r)) { 

                $blurb = substr(strip_tags($list['body']), 0, 160);

        ?>
            <a class="list-group-item <?php selected($list['id'], $opened['id'], 'active'); ?>" href="index.php?page=pages&id=<?php echo $list['id']; ?>">
                <h4 class="list-group-item-heading"><?php echo $list['title'];?></h4>
                <p class="list-group-item-text"><?php echo $blurb; ?></p>

            </a>                                    

        <?php } ?>

        </div>                          

    </div>

        <div class="col-md-9">

            <?php if(isset($message)) { echo $message;} ?>

            <form action="index.php?page=pages&id=<?php echo $opened['id']; ?>" method="post" role="form">

                <div class="form-group">

                    <label for="title">*Title for the page:</label>
                    <input class="form-control" type="text" name="title" id="title" value="<?php echo $opened['title'] ?>" placeholder="Page Title">

                </div>

                <div class="form-group">

                    <label for="user">*Select a user for this page</label>
                    <select class="form-control" name="user" id="user">

                        <option value="0">No user</option>

                        <?php 

                            $q = "SELECT id FROM users ORDER BY first_n ASC";
                            $r = mysqli_query($dbc, $q);

                            while($user_list = mysqli_fetch_assoc($r)) { 

                                $user_data = data_user($dbc, $user_list['id']);

                        ?>

                                <option value="<?php echo $user_data['id']; ?>"
                                    <?php 
                                        if(isset($_GET['id'])) {
                                            selected($user_data['id'], $opened['user'], 'selected');
                                        } else {
                                            selected($user_data['id'], $user['id'], 'selected');
                                        }

                                    ?>><?php echo $user_data['fullname']; ?></option>

                        <?php } ?>

                    </select>

                </div>

                <div class="form-group">

                    <label for="slug">*Slug:replaces ?id=number</label>
                    <input class="form-control" type="text" name="slug" id="slug" value="<?php echo $opened['slug'] ?>" placeholder="Page slug">

                </div>                                                              

                <div class="form-group">

                    <label for="label">*Label:</label>
                    <input class="form-control" type="text" name="label" id="label" value="<?php echo $opened['label'] ?>" placeholder="Page label">

                </div>

                <div class="form-group">

                    <label for="header">*Header:</label>
                    <input class="form-control" type="text" name="header" id="header" value="<?php echo $opened['header'] ?>" placeholder="Page header">

                </div>

                <div class="form-group">

                    <label for="body">*Body:</label>
                    <textarea class="form-control editor" name="body" id="body" rows="8" placeholder="Page body"><?php echo $opened['body'] ?></textarea>

                </div>

                <button type="submit" class="btn btn-default">Save</button>
                <input type="hidden" name="submitted" value="1">
                <?php if(isset($opened['id'])) { ?>
                    <input type="hidden" name="id" value="<?php echo $opened['id'] ?>">
                <?php } ?>

            </form>

            </div>

    </div>

<h1>User Manager</h1>

<div class="row">

    <div class="col-md-3">

        <div class="list-group">

            <a class="list-group-item" href="?page=users">
                <i class="fa fa-plus"></i>New User
            </a>    

        <?php

            $q = "SELECT * FROM users ORDER BY last_n ASC";
            $r = mysqli_query($dbc, $q);

            while($list = mysqli_fetch_assoc($r)) { 

                $list = data_user($dbc, $list['id']);

                //$blurb = substr(strip_tags($list['body']), 0, 160);

        ?>
            <a class="list-group-item <?php selected($list['id'], $opened['id'], 'active'); ?>" href="index.php?page=users&id=<?php echo $list['id']; ?>">
                <h4 class="list-group-item-heading"><?php echo $list['fullname_reverse'];?></h4>
                <!--<p class="list-group-item-text"><?php //echo $blurb; ?></p>-->

            </a>                                    

        <?php } ?>

        </div>                          

    </div>

        <div class="col-md-9">

            <?php if(isset($message)) { echo $message;} ?>

            <form action="index.php?page=users&id=<?php echo $opened['id']; ?>" method="post" role="form">

                <div class="form-group">

                    <label for="first_n">First Name:</label>
                    <input class="form-control" type="text" name="first_n" id="first_n" value="<?php echo $opened['first_n'] ?>" placeholder="First Name" autocomplete="off">

                </div>

                <div class="form-group">

                    <label for="last_n">Last Name:</label>
                    <input class="form-control" type="text" name="last_n" id="last_n" value="<?php echo $opened['last_n'] ?>" placeholder="Last Name" autocomplete="off">

                </div>

                <div class="form-group">

                    <label for="email">Email adress:</label>
                    <input class="form-control" type="text" name="email" id="email" value="<?php echo $opened['email'] ?>" placeholder="@" autocomplete="off">

                </div>

                <div class="form-group">

                    <label for="status">Status:</label>
                    <select class="form-control" name="status" id="status">

                        <option value="0" <?php if(isset($_GET['id'])) {selected(['0'], $opened['status'], 'selected');} ?> >Inactive</option>
                        <option value="1" <?php if(isset($_GET['id'])) {selected(['1'], $opened['status'], 'selected');} ?> >Active</option>

                    </select>

                </div>

                <div class="form-group">

                    <label for="password">Password:</label>
                    <input class="form-control" type="password" name="password" id="password" value="" placeholder="Password" autocomplete="off">

                </div>

                <div class="form-group">

                    <label for="passwordv">Verify password:</label>
                    <input class="form-control" type="password" name="passwordv" id="passwordv" value="" placeholder="Old Password" autocomplete="off">

                </div>

                <button type="submit" class="btn btn-default">Save</button>
                <input type="hidden" name="submitted" value="1">
                <?php if(isset($opened['id'])) { ?>
                    <input type="hidden" name="id" value="<?php echo $opened['id'] ?>">
                <?php } ?>

            </form>

            </div>

    </div>

[Lande-91]

got new error after that Fix:

" User Could not be added becouse:

Query: INSERT INTO users (first, last, email, password, status) VALUES ('John', 'Doe', 'john@doe.com', SHA1('test'), '0') "

[Dragotic]

Hello again Lande-91,

I am really sorry I am not responding that fast but today i didn't have much time with my pc.

So, back to the point I found another error that probably is the cause.

in your real escape string function for the users case you use:

 $first_n = mysqli_real_escape_string($dbc, $_POST['first_n']);
 $last_n = mysqli_real_escape_string($dbc, $_POST['last_n']);

while in your queries you use:

if(isset($_POST['id'])!== '') {
                    $action = 'updated';
                    $q = "UPDATE users SET first = '$first', last = '$last', email = '$_POST[email]', $password status = $_POST[status] WHERE id = $_GET[id]";
                    $r = mysqli_query($dbc, $q);

                } else {
                    $action = 'added';

                        $q ="INSERT INTO users (first, last, email, password, status) VALUES ('$first', '$last', '$_POST[email]', SHA1('$_POST[password]'), '$_POST[status]')";

                if($veify == true) {
                    $r = mysqli_query($dbc, $q);
                }

you have use $first_n, $last_n while in your queries you use $first, first, $last, last and on your user manager form you have the name values equal to first_n and last_n and so you confuse the script which thing to add where. I suggest you keep it the same. $first or $first_n, for example, whatever you choose in every single time you use those variables. There is also $veify and $verify as well and the script won't run properly.

It seems that your code have many mispells. You should check your code thoroughly and maintain the same code manner so you won't have any problem in the future.

I hope all these "guidelines" I provided you will help you solve these problems you are dealing with!

[Lande-91]

Adding User's now works:) , Thanks. you are awsome:)

[Lande-91]

And adding Pages

[Dragotic]

Hahaha I'm happy to hear that :) Keep it up my friend !! :)

[thedigicraft]

Thanks again Dragontic! I have been a bit busy this week so you have been a life saver!

[Dragotic]

man, I feel like I'm still your work, you know? :P

[Dragotic]

I meant stealing, not still...lol :P

[thedigicraft]

Haha... I am quit happy to share debugging responsibilities! :-)