Insert command failing

Question

So, I'm having trouble getting data into this table. I have a similar table setup for memberships. If I change the insert into query to membership from join everything works perfectly, but as soon as I change the table to join it stops working. The table seems to be properly setup since it's basically the same as my membership table, but for some reason data will not insert. I can't think of what could be causing my problem so I'm coming to the experts.

Note that this code all works perfectly when going into a different table. Thanks in advance.

if ( isset($_POST['btn-join']) ) {

    // clean user inputs to prevent sql injections
    $groupID = trim($_POST['groupID']);
    $groupID = strip_tags($groupID);
    $groupID = htmlspecialchars($groupID);      

    $teamname = trim($_POST['teamname']);
    $teamname = strip_tags($teamname);
    $teamname = htmlspecialchars($teamname);

    // Query groups to set group name
    $query2 = "SELECT groupName FROM groups WHERE groupID='$groupID'";
    $result2 = mysqli_query($con,$query2);
    $groupquery = mysqli_fetch_array($result2,MYSQLI_ASSOC);
    $groupname = $groupquery['groupName'];

    // groupID validation
    if (empty($groupID)) {
        $error = true;
        $groupIDError = "Please enter valid Group ID.";
    } else {
        // check email exist or not
        $query3 = "SELECT groupID FROM groups WHERE groupID='$groupID'";
        $result3 = mysqli_query($con,$query3);
        $count = mysqli_num_rows($result3);
        if($count!=1){
            $error = true;
            $groupIDError = "Provided Group does not exist.";
        }
    }

    // basic teamname validation
    if (empty($teamname)) {
        $error = true;
        $nameError = "Please enter your Team Name.";
    } else if (strlen($teamname) < 3) {
        $error = true;
        $nameError = "Team Name must have at least 3 characters.";
    }

    // if there's no error, continue to signup
    if( !$error ) {

        $query = "INSERT INTO join(groupID,userID,groupName,teamName) VALUES('$groupID','$userID','$groupname','$teamname')";
        $membership = mysqli_query($con,$query);

        if ($membership) {
            $errTyp = "success";
            $errMSG = "Account successfully updated";
            header("Location: dashboard.php");

        } else {
            $errTyp = "danger";
            $errMSG = "Something went wrong, try again later...";   
        }   
    }
}

SQL:

CREATE TABLE IF NOT EXISTS `join` (
  `jID` int(11) NOT NULL AUTO_INCREMENT,
  `groupID` varchar(32) NOT NULL,
  `userID` varchar(32) NOT NULL,
  `groupName` varchar(35) NOT NULL,
  `teamName` varchar(32) NOT NULL,
  `joinDate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`jID`),
  UNIQUE KEY `groupID` (`groupID`,`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

`mysqli_error($con)` on the query would have clearly shown you `right syntax to use near 'join` and most likely have avoided the question. — Funk Forty Niner, May 06 '17 at 20:51
It's mysql sorry I clicked that one by accident. Also thank you Jule. — Andrew, May 06 '17 at 20:51
You're also leaving yourself wide open to a serious SQL injection. What you have now won't help you; use a prepared statement. — Funk Forty Niner, May 06 '17 at 20:52
Yes I intend to use prepared statements once I've gotten all these little things worked out. The thing is I keep adding on things as I figure out my needs and this is the easiest way for me to test individual functions as a novice. I appreciate your tips. And I couldn't find a question like this on stack which is why its a duplicate. Probably just wasn't using the right search terms. — Andrew, May 06 '17 at 20:54
Also it didn't, and in the logs it wasn't showing that either. — Andrew, May 06 '17 at 20:54

score 4 · Accepted Answer · edited May 06 '17 at 20:53

4

join is a reserved word in SQL. To avoid these sorts of issues, use backticks around table and column names:

$query = "INSERT INTO `join`(`groupID`,`userID`,`groupName`,`teamName`) VALUES('$groupID','$userID','$groupname','$teamname')";
$membership = mysqli_query($con,$query);

As a side note, you should really rewrite this query to use a prepared statement and then bind the variables to it. This is a SQL injection waiting to happen.

edited May 06 '17 at 20:53

Rahul

76,197
13
71
125

answered May 06 '17 at 20:44

rickdenhaan

10,857
28
37

That fixed It, I have to wait 9 minutes to accept your answer. Also I do know about the prepared statements I intend to rewrite all of them in that way but I'm just working out this basic framework, also I'm a novice so doing it backwards is teaching me a lot about all the little things that come up. I appreciate your help. – Andrew May 06 '17 at 20:49
@Andrew check this post https://www.w3schools.com/php/php_mysql_connect.asp – Junius L May 06 '17 at 21:01
@julekgwa I actually used that post to convert my code to MySQLi. Are you suggesting I use PDO? – Andrew May 06 '17 at 21:20
@Andrew There's a prepared section on the link I gave you, or check this [PDO tutorial](http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers) – Junius L May 06 '17 at 21:23
@julekgwa Thanks, I'm actually in the process of converting everything over to prepared statements. Appreciate it. – Andrew May 11 '17 at 03:38

Insert command failing

1 Answers1