Num Rows Problem

[ckarlss0n]

Hello!
This is my first post here, hehe. Seems to be a great site.
Anyways, I'm quite new in PHP, but it's fun and I think I'm learning quite fast :)
There's appeared a problem for me now though, and I can't see what the problem is...?
I have made a form, which posts values to my php-page. This page inserts the values to the database, and that works just fine. But now I wanted to add the function to check if a value is already in use; in this case the value you entered in the email field.
So I found out about the num_rows function and as far as I know, it should do the thing.
I've made the part about num_rows bold.

So what I want to do is; check if the value I enter in the email field, in my form, already exists in my database/table. If numrows > 0, then it does exist, and my command is interrupted. Else it inserts the values from the form into my table and database. Thanks on advance!

<?php
include 'connect.php';

$fname = $_POST["fname"];
$lname = $_POST["lname"];
$pass = $_POST["pass"];
$email = $_POST["email"];
$age = $_POST["age"];

** $sql_select="SELECT info FROM users WHERE fname='$email'";
$result = mysql_query($sql_select);

if(mysql_num_rows($result) > 0 ) {
 echo "The email you entered is already in use, please try again.";
} else {**

$sql = "INSERT INTO `users`.`info` (`fname`, `lname`, `pass`,`email`,`age`)
VALUES ('$fname', '$lname', '$pass', '$email', '$age')";

mysql_query($sql);

}
mysql_close($link);
?>

[maori]

hiya
i havent treid this but something like this should help

$checkemail = mysql_query("SELECT email from submissions WHERE email = $email");

if (!$checkemail) {
    add your insert stuff here 
}

if (mysql_num_rows($checkemail) > 0) {
    echo "email exists already.";
    $email = mysql_fetch_array($checkemail);
    print_r($email); // the data returned from the query
}

[Atli]

A better approach would be to use the SQL COUNT() function to have the SQL server return the number of rows, rather than return the rows and count them in PHP. It's less wasteful.

$sql = "SELECT COUNT(*) FROM `users` WHERE fname = %s";
$sql = sprintf($sql, mysql_real_escape_string($_POST["email"]));

$count_result = mysql_query($sql);
if (!$count_result) {
    trigger_error("Query failed: " . mysql_error(), E_USER_ERROR);
}

$row = mysql_fetch_row($count_result);
$count = (int)$row[0];

if ($count == 0) {
    // Insert the row
}
else {
    // Email already exists.
}

There is also one very important thing to note here: Under no circumstances should user input ever enter a SQL query unescaped! Otherwise you are an easy target for SQL Injection attacks

When using the old MySQL API extension, you should always use the mysql_real_escape_string function on any user input that is meant to go into the query.

A much better way is to abandond the old, deprecated MySQL API extension and use PDO or MySQLi instead. They offer prepared statements, which eliminates SQL Injection altogether.