Strange outcomes with $stmt->fetchColumn()

[David-London]

Hi,

I have two scripts using almost identical code.
One of them works and finds the field (email) - the PRIMARY KEY (email)
and the other doesn't (user_key) - not indexed

Is this because PHP 7 needs to use only indexes in the WHERE part of SQL Query?

Here are the two scripts - first one works

$sql = 'SELECT access,fname FROM clients WHERE email=?';

	    $stmt = $connect->prepare($sql);

	    $stmt->execute([$email]); 

	    $data_exists = ($stmt->fetchColumn() > 0) ? true : false;

	    if ($data_exists) { //   account found.

	        $row = $stmt->fetch();

	        $access = $row['access'];

	        $fname = $row['fname'];

	        }

	    else {

	        $err_msg = 'Invalid Email Address and/or Password.';

	        $email = '';

	        $pass = '';

	        require_once ("login_fm.php");

	        exit;

	        }

	[/PHP]

This second one drops through to the ERROR page.

 

$sql = 'SELECT email,fname,lname,confirm FROM clients WHERE user_key=?';
    $stmt = $connect->prepare($sql);
    $stmt->execute([$the_key]); 
    $data_exists = ($stmt->fetchColumn() > 0) ? true : false;
    if ($data_exists) { //   Account found.
        $row = $stmt->fetch();
        $email        = $row['email'];
        $name         = $row['fname'].' '.$row['lname'];
        $confirm     = $row['confirm'];
        
        $_SESSION['auth'] = "yes";
        $_SESSION['email'] = $email;
        $_SESSION['name'] = $name;
        
        $sql = 'UPDATE clients SET confirm = ?,log_count = log_count+1,last_date=? WHERE email=?';
        $stmt= $connect->prepare($sql);
        $stmt->execute(['y',$today_time,$email]);
     
        require_once("index.php");   //  SUCCESSFUL LOGIN: THIS LOADS THE START PAGE  
        exit;    
        }  // end if
    else {
        $err_page_message = "ERROR - Account not found $the_key : $name";
        require_once("err.php"); 
        exit;
        }  // end else  

 

May be it's a different reason - like I'm too tied !

On the second one. I could rewrite it to just try the update and fail-over to the ERROR page,

But it would be nice to know why it isn't working.

 

It's quite simple, I just want to check to see if a row exists with the condition given, that's all.

I never had this problem the old mysql but the PDO seems a bit tricky or I'm just using the wrong code lol.

 

Thanks

Edited June 15, 2019 by David-London

[requinix]

fetchColumn() returns an array or false. It does not return a number.

[ginerjm]

I have not seen this done before so here's my question.

If you do a fetchcolumn the manual says you can't retrieve any other column from that row again.  You are attempting to do a fetch (of a whole row) after having done the fetchcolumn.  I wonder if you would be retrieving the 2nd row of the results when you do the fetch (row) instead of the first row.  If you only have one row returned what will the fetch call return to you - a False value?

Why do you do the fetchcolumn anyway?  Can't you just do a rowcount call instead?

[Barand]

I agree with @ginerjm. If your first code is "working" it's because you have more than one record with the found email value.

The best use I have found for fetchColumn() is when getting results from single-value queries EG

$result = $db->query("SELECT COUNT(*) FROM user");
$user_count = $result->fetchColumn();

 

[David-London]

Thanks for he replies.
I am converting from the old mysql_query to prepared statements with PDO so sorry for the basic questions !
I usually try to research and find the answer and then post the problem.
Sometimes that leads to a debate based around the (incorrectly) chosen method/function that I posted. 

Which I think happened here 

Maybe using  a couple of simple examples will help me understand this.

Examples:
So. I have a table named cars.
The column "plate" is the Primary Index.
Other columns are not indexed

See image attached.

Environment:
CENTOS 7.6 kvm [server] v80.0.15 
Server Version: Apache/2.4.39
Server version: 5.7.26 - MySQL Community Server (GPL)
Server charset: UTF-8 Unicode (utf8)
PHP version: 7.2.7


1) Assume there is a form which allows the user
to enter a color name. So from that POST we get
a variable $color.

I want to ask the db if there is a row with color equal to $color in the table cars

Answer should be yes/no 

What should I use ?

2) Secondly, a different script.
I get the $color from the form and this time I want to ask ...~
"how many rows have color equal to $color"

I'd like the answer as 0 or 1 or 2 etc.

Do I have to ask if there is a row first, (as in question 1) and then do a count on them if there is data or not ?
What should be the code for this?

3) Now I have another different form and it lets me enter the plate number (which is indexed)

I get $plate variable.

I just want to ask same as question (1) Is there a row with plate == $plate in table cars
yes or no.

I want to know if the code in (3) differs from (1) because of the index.

Rather, than write the code I think it should be (like I did before), may I ask what you think it should be
so that I can understand clearly? 


Hopefully I will "get" it.

Many thanks

[Barand]

I would use virtually tthe same query for each of the above. When checking for existence, get the count.

The advantage is there will always be a row returned, even if the count is zero. This avoids checking for reults prior to checking the value.

$color = 'red';                           // posted input

$res = $db->prepare("SELECT COUNT(*) 
                     FROM cars
                     WHERE color = ?
                    ");
$res->execute([$color]);

then if you just want a yes/no answer

echo  $res->fetchColumn() > 0 ? 'Yes' : 'No';

but if you want to know how many

echo $res->fetchColumn() . " cars found";

The plate search will be exactly the same. The difference is that a key search will be faster as the index can be used instead of the query processor having to read every record.

All those scenarios are single value queries. The difference would  come when you you need more info, such as "What make and color is Peter's car?" or "What make and color is Mary's car?" (doesn't exist). Assuming each driver has only one car...

$name = 'Mary';                           // posted input

$res = $db->prepare("SELECT color
                          , make 
                     FROM cars
                     WHERE driver = ?
                    ");
$res->execute([$name]);

$row = $res->fetch();

if (!$row) {
    echo "$name has no car";
}
else {
    echo "$name has a {$row['color']} {$row['make']}";
}

 

[David-London]

Many thanks ?