Data insert not success when using varchar as primary key?

Question

This is my table

CREATE TABLE room ( room_ID VARCHAR(8), room_name TEXT(50), room_capacity INT(3), room_building VARCHAR(20), room_type VARCHAR(20), room_desc TEXT(100), PRIMARY KEY (room_ID) );

and this is my insert code.

<?php
//Start session
session_start();

//Import connection
include('conn.php');

$room_ID = $_POST["room_ID"];
$room_type = $_POST["room_type"];
$room_name = $_POST["room_name"];
$room_capacity = $_POST["room_capacity"];
$room_building = $_POST["room_building"];
$room_desc = $_POST["room_desc"];

//echo $room_ID;
//echo $room_type;
//echo $room_name;
//echo $room_capacity;
//echo $room_building;
//echo $room_desc;

//Check for duplicate room ID
//if($room_ID != '') {
    $qry = "SELECT room_ID FROM room WHERE room_ID = '".$room_ID."'";
    $result = mysql_query($qry);
    if($result) {
        if(mysql_num_rows($result) > 0) { 
            header("location: duplicateID.php");
            exit();
        }
        @mysql_free_result($result);
    }
    else {
        die("Yang ini lah failed");
    }
}

//Create INSERT query
$qry = "INSERT INTO room (room_ID, room_name, room_capacity, room_building, room_type, room_desc)
        VALUES('$room_ID', '$room_name', '$room_capacity', '$room_building', '$room_type', '$room_desc')";
$result = @mysql_query($qry);

//Check whether the query was successful or not
if($result) {
    header("location: addroomsuccess.php");
    exit();
} else {
    die("Query failed");
}?>

But the problem is, the process stuck in the first if else. But when i delete those if else, the query still failed. Why did this happen? Is it because im using varchar as the data type for the primary key?

I don't think it is a good practice to have a VARCHAR as primary key since it can't be incremented automagically and indexing is less performant, etc — Alexandre Lavoie, May 28 '13 at 10:21
Please avoid using mysql_* the are no longer maintained . Switch to `PDO` or `mysqli` — alwaysLearn, May 28 '13 at 10:24
can you show also your form of post values your code working fine or try to check your values — Rakesh Sharma, May 28 '13 at 10:30
You are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) and should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). You are also **vulnerable to [SQL injection attacks](http://bobby-tables.com/)** that a modern API would make it easier to [defend](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) yourself from. — Quentin, May 28 '13 at 10:38

score 0 · Answer 1 · answered May 28 '13 at 10:37

So much wrong with this.

1) If you have a PK you will not get duplicates so your check is pointless.

2) Just insert it. If it fails, you have a duplicate or some other problem. Checking first just narrows the opportunity but it can still go wrong in a multiuser environment.

3) SQL injection - read up on it, understand it at implement SQL that avoids it (parameters).

But to answer your question, not it has nothing to do with VARCHAR as a PK - that's fine if it makes sense. @AlexandreLavoie's advice is not good or relevant (sorry!).

Data insert not success when using varchar as primary key?

1 Answers1