2

I am working on a user login feature and facing a strange problem "Invalid Number of Columns". I Google'd it and many people have the same issue and their question was bit different.

Here is my code:

//this is a function inside user class. And function receives $user_data array
try{

    $stmt = $this->db_connection->prepare("SELECT `id` FROM `aaa_users` WHERE (`user_email` = :user_email OR `user_name` = :user_email) AND `user_pass` = :user_pass");
    $stmt->bindparam(':user_email', $user_data['email']);
    $stmt->bindparam(':user_pass', $user_data['password']);
    $stmt->execute();

    $count = $stmt->rowCount();

} catch (PDOException $e){
    echo $e->getMessage();
}

The registration query works but this throws an exception. I wonder if there might be tiny mistake but I can't figure it out.

halfer
  • 19,824
  • 17
  • 99
  • 186
Alena
  • 1,134
  • 6
  • 19
  • 45
  • My only suggestion would be to try [`bindParam`](http://php.net/manual/en/pdostatement.bindparam.php) (w/ capital "P") instead of `bindparam`. Everything else looks about right to me. – DJ Sipe Jul 21 '17 at 21:03
  • Definitely pdo and not mysqli? – Doug Jul 21 '17 at 22:03
  • you have 3 placeholders, and 2 binds ... wonder where "invalid number of columns" comes from. – YvesLeBorg Jul 21 '17 at 22:25
  • @YvesLeBorg It's something else, but the OP seems to have left the question by not responding to other comments that were posted earlier. So, I don't think there's much sense for me to even mention the other most likely possibility. – Funk Forty Niner Jul 21 '17 at 22:28
  • @DJSipe Thanks for the tip and as mentioned in question. The registration query is working absolutely fine and the pattern is same for both queries. However, I have tried with capital P but no luck! :( – Alena Jul 22 '17 at 06:10
  • @Doug yes, it's PDO – Alena Jul 22 '17 at 06:11
  • @YvesLeBorg it is very strange problem. The problems seems with prepare statement line because above portion of the code executes well but when I try to do something after prepare statement. It throw same exception. – Alena Jul 22 '17 at 06:12
  • @Fred-ii- If someone doesn't respond within 24 hours then you can assume, OP left the question. Maybe someone else faced exactly same problem exactly according to my situation but I didn't copy/paste anyone's question. I am working on personal project and stuck in this problem and have no idea; what's wrong. I would be thankful if you mention other possibilities. – Alena Jul 22 '17 at 06:16
  • 1
    check your php system files to see if emulation is set. If it isn't you will neither to either turn it on or rename one of your duplicated placeholder names. That would be a reason why you're getting that error @Alena – Funk Forty Niner Jul 22 '17 at 12:07
  • @Fred-ii- thanks, learned something today. – YvesLeBorg Jul 22 '17 at 13:35
  • you're welcome @YvesLeBorg - I guess I/we need to wait and see what the OP has to say about my comment above. – Funk Forty Niner Jul 22 '17 at 13:37
  • @Fred-ii- i replicated the issue with my current code base. you nailed it! – YvesLeBorg Jul 22 '17 at 13:59
  • I figured as much, *"spidey tingle"* told me ;-) @YvesLeBorg thanks for testing my theory, *cheers* – Funk Forty Niner Jul 22 '17 at 14:00
  • @Fred-ii- maybe this is the problem but I don't completely understand. Can you tell me, how can I turn on emulation? And removing duplicate placeholder from same function OR from entire code/file/class? I have removed registration function completely from class and still got same error. And thanks for your answer! – Alena Jul 22 '17 at 20:52
  • @Alena I posted something for you below to look at. Please go through it in its entirety and let me know. I'll see what I can do after to further help if it still doesn't work. Keep me posted. – Funk Forty Niner Jul 22 '17 at 23:03

1 Answers1

1

As I mentioned in comments, PDO's emulation may not be enabled on your server and using the same named placeholder (may be) causing this to error out.

I've respectively renamed both :user_mail to :user_email_1 and :user_email_2.

$stmt = $this->db_connection->prepare("
                                       SELECT `id` FROM `aaa_users` 
                                       WHERE (`user_email` = :user_email_1 
                                       OR `user_name` = :user_email_2) 
                                       AND `user_pass` = :user_pass
                                    ");
$stmt->bindparam(':user_email_1', $user_data['email']);
$stmt->bindparam(':user_email_2', $user_data['email']);
$stmt->bindparam(':user_pass', $user_data['password']);
$stmt->execute();

You can read more about this in the following Q&A on Stack:

The accepted answer inside it, offers a good explanation.

Note: Make sure that both all values related to $user_data['X_values'] contain value. This suggests that it may be coming from a previous query and is unknown as to their origins / values.

Use error checking by following the below links, if any of them have not already been used during testing:

While making sure that all columns do in fact exist. There could be the slightest chance that your database/table stand to be case-sensitive, so check for that (letter case) also.

Another thing; it has happened in the past where people actually had to use bindParam in Camel Case instead of bindparam all in lower case; it's a possibility.

Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141