CODEWITHSUNDEEP
phpsql
Syahmi Roslan
Syahmi Roslan

Reputation: 110

How to do checking in sql?

        $sql = "SELECT *
                FROM users
                WHERE username = '$username' AND s_id = '$s_id'";
        $result = mysql_query($sql);
        if (($result) == 1) {
        $errorMessage = 'Username or Student ID already taken. Choose another one'; 
} else {            
        $sql   = "INSERT INTO users VALUES ('$username','$password','$f_name','$l_name','$s_id','$email')";
        mysql_query($sql);
        $errorMessage = 'Registration is Successful. You can <a href="userpage.php">Login</a> Now.';
        }

Hello, i just want to ask why the sql statement didn't read the first statement which is if there have the same username and s_id the user should change it. But it will print successful inserted into database but the data didn't have in the database because the username or s_id already have in database. Is there have any syntax error? Please help me

Upvotes: 1

Views: 75

Answers (5)

SIDU
SIDU

Reputation: 2278

Your student table ID should be primary key -- thus no need to check name

$pdo = 'mysql:host=' . $host . ';dbname=' . $db;
$dbL = new PDO($pdo, $user, $pass);
$sql = 'SELECT 1 FROM users WHERE s_id = '. ceil($s_id);
$sth = $dbL->prepare($sql);
$sth->execute();
$row = $sth->fetch(PDO::FETCH_NUM);
if ($row[0]) {
  //already exists
} else {
  //new record and save it
}

Upvotes: 0

dev.null
dev.null

Reputation: 538

And you can increase performance when you use SELECT count(*) FROM ... and check if the result (with mysql_result function) is greater than 0: 

$sql = "SELECT count(*)
    FROM users
    WHERE username = '$username'
    AND s_id = '$s_id'";
$result = mysql_query($sql);
if (mysql_result($result, 0, 1) > 0) {
    $errorMessage = 'Username or Student ID already taken. Choose another one'; 
} else {            
    $sql = "INSERT INTO users VALUES ('$username','$password','$f_name','$l_name','$s_id','$email')";
    if (mysql_query($sql)) {
        $errorMessage = 'Registration is Successful. You can <a href="userpage.php">Login</a> Now.';
    } else {
        $errorMessage = 'An error occurred.';
    }
}

Upvotes: 0

Ahasanul Haque
Ahasanul Haque

Reputation: 11134

Very nice question, people always get confused by this problem. Firstly, skip the code. Question yourself, what does a SELECT statement return? It returns a table if it succeeds.

Now, tell me can u compare a table with the value 1? The answer is no, u don't. What you have to do is to first compute the number of row of the table. And as your userId is unique, you will find one row(if matches) or zero row(if doesn't match) always.

So check if that number of row is one or not. How to do it? Change your code like that,

      $sql = "SELECT *
            FROM users
            WHERE username = '$username' AND s_id = '$s_id'";
    $result = mysql_query($sql);
    if (mysql_num_rows($result) == 1) {
    $errorMessage = 'Username or Student ID already taken. Choose another one'; 
    } else {            
    $sql   = "INSERT INTO users VALUES ('$username','$password','$f_name','$l_name','$s_id','$email')";
    mysql_query($sql);
    $errorMessage = 'Registration is Successful. You can <a href="userpage.php">Login</a> Now.';
    }

As included in previous answer,

MYSQL extension is deprecated as of PHP 5.5.0, and has been removed as of PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used.

Upvotes: 1

Vao Tsun
Vao Tsun

Reputation: 51456

Because mysql_query() returns TRUE on success or FALSE on error. use mysql_num_rows instead

Upvotes: 0

Hassaan
Hassaan

Reputation: 7662

Use mysql_num_rows to count/compare rows. ($result) > 0 is more better then ($result) == 1

Change from

if (($result) == 1) {

To

if(mysql_num_rows($result) > 0) {

Warning: MYSQL extension is deprecated as of PHP 5.5.0, and has been removed as of PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used.

Upvotes: 3

Related Questions