Checking for record in SQL databse, what is wrong here?

Question

Possible Duplicate:
Best way to stop SQL Injection in PHP
supplied argument is not a valid MySQL result resource
php/mysql account activation

Can't figure this out for the life of me...

Basically I just want to check if a record exists and if it doesn't, do something and if it does, do something else. Can't get it to work with this code I've written.

First of all, the error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in claimreview.php on line 7

Here is my db connection (which is working fine as it doesn't give any errors)

dbconn.php
<?

// e.g. dbconn('localhost','your_database','your_login','your_pass');

$db = dbconn('localhost','db','login','pass');

// No need to edit below this line.

function dbconn($server,$database,$user,$pass){
// Connect and select database.
$db = mysql_connect($server,$user,$pass);
$db_select = mysql_select_db($database,$db);
return $db;
}

?>

And here is my script which takes in an email (which I am echoing to make sure it is receiving the email, and it is)

<?php
include('functions/dbconn.php');
$email = $_POST["email"];
$sql = "SELECT * FROM reviewers WHERE email = '$email'";
echo $sql;
$result = mysql_query($sql);
$num = mysql_num_rows($result); //LINE 7
if ($num > 0) {
echo "Found record";
}
else
{
echo "Didn't find record </br>";
}
echo $num;
echo $email;
    ?>

It is echoing the SQL also which looks like this:

SELECT * FROM reviewers WHERE email = 'email-from-form-here'

try this: $sql = "SELECT * FROM reviewers WHERE email = '".$email."'"; — Thomas, Dec 13 '11 at 08:19
On an unrelated note, if this is for a production website, you are vulnerable to SQL injection attacks, because you're inserting the email address straight into the query. See http://en.wikipedia.org/wiki/SQL_injection — Gnat, Dec 13 '11 at 08:20
@Thomas - I'm not seeing the improvement in what you've suggested. — Jared Farrish, Dec 13 '11 at 08:25
Thanks guys, but the problem laid entirely within that the username I was using wasn't added to the db. All the code works as posted. — Brenden Clerget, Dec 13 '11 at 08:32
Also, hopefully this is better: $email_s = mysql_real_escape_string($email); $sql = "SELECT * FROM reviewers WHERE email = '$email_s'"; — Brenden Clerget, Dec 13 '11 at 08:34

score 0 · Answer 1 · answered Dec 13 '11 at 08:18

0

Test your sql query:

$result = mysql_query($sql) or die(mysql_error());

answered Dec 13 '11 at 08:18

Oyeme

11,088
4
42
65

lol@myself. Thank you, solved it once it told me no database was selected I realized I never added the db user to the database I was connecting to so it wasn't connecting. – Brenden Clerget Dec 13 '11 at 08:31

VolkerK · Answer 2 · 2011-12-13T08:30:17.887

You need to add some error handling to find out what is going wrong. Each of the mysql_... functions may fail and will then return false, mysql_error() can tell you more about the error.
Let's start with your dbconn function.

<?php
function dbconn($server,$database,$user,$pass) {
    // Connect and select database.
    // 1. give the calling script at least a chance to detect connect/db-select errors
    $db = mysql_connect($server,$user,$pass);
    if ( $db ) {
        $db_select = mysql_select_db($database, $db);
        if ( !$db_select ) {
            $db = false;
        }
    }
    return $db;
}

and then the main script

<?php
require 'functions/dbconn.php';
// 2. check if the database connection has been established
if ( !$db ) {
    die(mysql_error());
}

// 3. prevent sql injections
$email = mysql_real_escape_string($_POST["email"], $db);

// 4. If you don't need the data itself use Count(*) instead of mysql_num_rows()
$sql = "SELECT Count(*) FROM reviewers WHERE email = '$email'";
echo $sql;
$result = mysql_query($sql, $db);
// 5. check for errors 
if ( !$result ) {
    die(mysql_error($db));
}
// 6. SELECT Count(*) returns the result as a record, fetch it
$row = mysql_fetch_row($result);
// 7. ...it _should_ return a record, test it
if ( !$row ) {
    die('error: no record');
}

if ( '0'==$row[0] ) {
    echo "Didn't find record </br>";

}
else
{
    echo "Found record";
}

Checking for record in SQL databse, what is wrong here?

2 Answers2