num_rows not working pdo,mysqli, always return 0

Question

I have a interesting problem.

// in pdo with function --> not work

function UserIsExist($name)
{
    global $db;
    $stmt = $db->prepare("SELECT id FROM tarskereso_users WHERE email = '$name' LIMIT 1");
        $stmt->execute();
        if ($stmt->fetchColumn() == 1) return 1;
        else return 0;
}

// with MySQLi --> not working

function UserIsExist($name)
{
    global $db;
    $stmt = $db->prepare("SELECT id,email FROM tarskereso_users WHERE email = ? LIMIT 1");
        $stmt->bind_param('s', $name);
        $stmt->execute();
        $stmt->store_result();
        if ($stmt->num_rows == 1) 
            return 1;
        else 
            return 0;
        $stmt->close();
}

// In Register.php

 ... other ..
        if(UserIsExist($user) == 1)
            $error_msg = "Is Exist";
        else
        {
            $birthdate = $year.'.'.$month.'.'.$day;
            CreateUser($user,$pass,$birthdate,$sex);
                $error_msg = 'Success';
        }

So, with function not working, I try with:

$stmt = $db->prepare("SELECT id,email FROM tarskereso_users WHERE email = ? LIMIT 1");
        $stmt->bind_param('s', $name);
        $stmt->execute();
        $stmt->store_result();

        if ($stmt->num_rows > 0) 
            ... other ...
        else 
            echo 'isnt exist...';
        $stmt->close();

but not working, the num_rows always return 0. And the account in the database successfuly created

Looks like your query is looking for email but you are passing name to your function. Could this be the issue ? — Maximus2012, Apr 07 '15 at 18:20
Have you seen this? http://stackoverflow.com/questions/2700621/php-pdo-num-rows — Dallin, Apr 07 '15 at 18:20
Have you done a `var_dump()` of all variables involved, including the database connection variables? — jeroen, Apr 07 '15 at 18:21
my connection: $db = new PDO('mysql:host=127.0.0.1;dbname=asdelontest;charset=utf8', 'user', 'pw', array(PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)) var_dump($db); --> object(PDO)#1 (0) { } var_dump($name); --> string(17) "hdasda@bassda.com" — Donald, Apr 07 '15 at 18:33
I try with only text, I deleted the @ and the dot, and this work... But email = '$email' string is true — Donald, Apr 07 '15 at 19:00

score 0 · Accepted Answer · answered Apr 07 '15 at 18:22

0

in pdo, num_rows won't work. you have to use $sql->rowCount() method to get number of records in a table.

<?php
    $sql = $con->prepare("<YOUR SQL QUERY HERE>");
    $sql->execute();
    if($sql->rowCount() > 0){
        echo $sql->rowCount() ." rows found";
    }
?>

answered Apr 07 '15 at 18:22

Krishna Manoj Varanasi

88
7

In the SQL exist the row, but $sql->rowCount() == 0 – Donald Apr 07 '15 at 18:31
Check once again your SQL query properly. The query expression must match the attribute selection logic. if the sql query did not have proper algebraic logic, the query returns 0 rows. – Krishna Manoj Varanasi Apr 08 '15 at 15:00
Or you can use your query having a "count(*)" column which calculates the number of rows fetched depending on your WHERE clause. The while fetching the data, you can use the PDO statement as `code` $numRows = $sql->fetch(PDO::FETCH_ASSOC)['count']; echo $numRows; – Krishna Manoj Varanasi Sep 16 '15 at 11:47

num_rows not working pdo,mysqli, always return 0

1 Answers1