Mysql Query Problem

[JayC]

Spoiler: Query

dbquery("INSERT INTO groups (name,desc,badge,ownerid,created,roomid,locked,privacy) VALUES ('".$name."','".$desc."','".$badge."','".USER_ID."','".$x."','".$rid."','".$Priv."','".$Stat."')");

Spoiler: Variables

$rid = filter($_POST['room_id']);
$name = filter($_POST['name']);
$desc = filter($_POST['desc']);
$Priv = filter($_POST['priv']);
$Stat = filter($_POST['stat']);
$badge = filter($_POST['img']);
$x = date('j F Y h:i');

Error

Spoiler: Page Error

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 'desc,badge,ownerid,created,roomid,locked,privacy) VALUES ('Test','Test','ES079',' at line 1

 
@FalseKing please help

 

[Weasel]

What does it say when you add or die(mysql_error()); after the mysql_query()?

 

[JayC]

same thing 
Wrote it out in navicat and this happened:

[SQL]INSERT INTO groups (name,desc,badge,ownerid,created,roomid,locked,privacy) VALUES ('test','test','ES079','1','2014-10-16','9','open','open');
[Err] 1064 - 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 'desc,badge,ownerid,created,roomid,locked,privacy) VALUES ('test','test','ES079',' at line 1

 

[GarettM]

Try this sql code into the mysql server

INSERT INTO `groups` (`name`,  `desc`, `badge`,  `ownerid`, `created`,  `roomid`, `locked`, `privacy`)  VALUES ('test1', 'test1example', 'test1badgeno', '1', NOW(), '1', 'open', 'open');

If this runs fine the reason for the error could be not using ``

Sorry if santax error on phone and its kinda hard to code on one

 

[Weasel]

What does your table structure look like?

 

[JayC]

My Table Structure:
id (int) auto increment
name (varchar)
desc (varchar)
badge (varchar)
ownerid (int)
created (varchar)
roomid (int)
locked (enum) (open/closed/locked)
privacy (enum) (open/blocked)

 

[GarettM]

Here is what i did to make this work.

Created a table called JayCustom.groups;

CREATE DATABASE JayCustom;

CREATE TABLE JayCustom.groups
(
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` TINYTEXT DEFAULT NULL,
    `desc` MEDIUMTEXT DEFAULT NULL,
    `badge` TINYTEXT DEFAULT NULL,
    `ownerid` MEDIUMINT NOT NULL,
    `created` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `roomid` INT DEFAULT NULL,
    `locked` ENUM('open', 'closed', 'locked') DEFAULT 'open',
    `privacy` ENUM('open', 'blocked') DEFAULT 'open',
    PRIMARY KEY (id)
) Engine=InnoDB;

used mysqli query and ran this query.

INSERT INTO groups (`name`, `desc`, `badge`, `ownerid`, `created`, `roomid`, `locked`, `privacy`) VALUES ('Staff', 'Official Staff Group!', 'staff', '1', '2014-10-20 07:09:29', '1', 'open', 'open');

Here are the variables i used.

 # Variables
 $name            =    'Staff';
 $description    =    'Official Staff Group!';
 $badge            =    'staff'; // sorry don't know the staff badge code.
 $owner            =    1; // USER_ID
 // Date must be formated YYYY-MM-DD HH:MM:SS
 $created        =    date('Y-m-d h:i:s', time()); // should be 2014-10-20 10:59:23 - My local Time. Location: Gresham, OR, United States of America.
 $room            =    1;
 $locked        =    'open';
 $privacy        =    'open';

 # Run Query
 $query = dbquery(
    sprintf(
        "INSERT INTO `groups` (`name`, `desc`, `badge`, `ownerid`, `created`, `roomid`, `locked`, `privacy`) VALUES ('%s', '%s', '%s', '%d', '%s', '%d', '%s', '%s');",
        $name,
        $description,
        $badge,
        $owner,
        $created,
        $room,
        $locked,
        $privacy
    )
 );

 if($query == true) {
    echo 'The Group was created successfully <br />';
 } else {
    echo 'The Group was not created successfully <br />';
 }

Here is the full script,

<?php
 # Time zone
 date_default_timezone_set('UTC');

 # Simple Database Query
 $database = new mysqli('localhost', 'root', 'devbest', 'JayCustom'); // i don't care if my development servers password is on devbest

 if($database->connect_errno) {
    echo sprintf('Script Error: The database could not connect because "%s"', $database-connect_error);
    exit;
 }

 function dbquery($query) {
    global $database;

    if($result = $database->query($query)) {
        return true;
    }
    return false;
 }

 # Variables
 $name = 'Staff';
 $description = 'Official Staff Group!';
 $badge  = 'staff'; // sorry don't know the staff badge code.
 $owner = 1; // USER_ID
 // Date must be formated YYYY-MM-DD HH:MM:SS
 $created = date('Y-m-d h:i:s', time()); // should be 2014-10-20 10:59:23 - My local Time. Location: Gresham, OR, United States of America.
 $room  = 1;
 $locked = 'open';
 $privacy =  'open';

 # Run Query
 $query = dbquery(
    sprintf(
        "INSERT INTO `groups` (`name`, `desc`, `badge`, `ownerid`, `created`, `roomid`, `locked`, `privacy`) VALUES ('%s', '%s', '%s', '%d', '%s', '%d', '%s', '%s');",
        $name,
        $description,
        $badge,
        $owner,
        $created,
        $room,
        $locked,
        $privacy
    )
 );

# Results
 if($query == true) {
    echo 'The Group was created successfully <br />';
 } else {
    echo 'The Group was not created successfully <br />';
 }

IMPORTANT NOTE!!!
Programmers don't recommend using $_POST, $_GET or $_SERVER variables directly

<?php
# standards don't recommend using $_POST, $_$SERVER or $_GET directly.
 $rid    = (int) filter_input(INPUT_POST, 'room_id');
 $name    = filter_input(INPUT_POST, 'name', FILTER_SANITIZE_SPECIAL_CHARS);
 $desc    = filter_input(INPUT_POST, 'desc', FILTER_SANITIZE_SPECIAL_CHARS);
 $priv    = filter_input(INPUT_POST, 'priv', FILTER_SANITIZE_SPECIAL_CHARS);
 $stat    = filter_input(INPUT_POST, 'stat', FILTER_SANITIZE_SPECIAL_CHARS);
 $badge = filter_input(INPUT_POST, 'img',  FILTER_SANITIZE_SPECIAL_CHARS);
 $date  = date('Y-m-d h:i:s', time());

 

[JayC]

You lost me lol , how does this help with my code?

 

[GarettM]

Okay first run the sql code below into your database. You don't use varchar for dates.

ALTER TABLE groups DROP `created`; ALTER TABLE groups ADD `created` DATETIME;

Second change how you get your variables

$rid = (int) filter_input(INPUT_POST, 'room_id');
$name = filter_input(INPUT_POST, 'name', FILTER_SANITIZE_SPECIAL_CHARS);
$desc = filter_input(INPUT_POST, 'desc', FILTER_SANITIZE_SPECIAL_CHARS);
$priv = filter_input(INPUT_POST, 'priv', FILTER_SANITIZE_SPECIAL_CHARS);
$stat = filter_input(INPUT_POST, 'stat', FILTER_SANITIZE_SPECIAL_CHARS);
$badge = filter_input(INPUT_POST, 'img', FILTER_SANITIZE_SPECIAL_CHARS);
$date = date('Y-m-d h:i:s', time());

Third try this and see if you still get an error.

dbquery(sprintf("INSERT INTO groups (name, desc, badge, ownerid, created, roomid, locked, privacy) VALUES ('%s', '%s', '%s', '%d', '%s', '%d', '%s', '%s');",$name, $desc, $badge, (int)USER_ID, $date, $rid, $priv, $stat));

i believe the error is spacing :3

Also another note add more filtering. like strip non alpha characters

 

[JayC]

I actually think I know what the problem is im going to try my idea first really quick 
Yup, my problem was it wasn't reading the tables so i put `name`,`desc`,`badge` and so on.. Solved , thanks