CODEWITHSUNDEEP
phpmysqldatabasewhile-loopfetch
user11022857
user11022857

Reputation:

PHP Multiple DB Connections Failing

So I am creating a cronJob that will select ALL the users from my user table and then store the users full names in a variable. All that happens inside a while loop, inside the same loop I am selecting EVERYTHING from my customerLeads tables where the assignedTo column is equal to the users full name. Then inside this loop I want to record the customerName and store them all inside an array. So each user will have it's own array which has all the customersNames inside.

The purpose of this is to run this every morning so the users will get an email if they haven't updated a customerLead in over 2 days.

However I keep getting this error;

Fatal error: Uncaught Error: Call to a member function fetch() on boolean in /.../customerLeadReminder.php:18 Stack trace: #0 {main} thrown in /homepages/.../customerLeadReminder.php on line 18

I've had a look around online and everything says that it's the connection not working, but I've checked and the connection is running fine...

Question: Why does this error appear and what I am doing wrong?

<?php  
//Error Reporting
ini_set('display_startup_errors', 1);
ini_set('display_errors', 1);
error_reporting(-1);


require '../includes/conn.php';

  $userList = $salesConn->query("SELECT `email`, `firstname`, `lastname` FROM `users`");

  while ($uRow = $userList->fetch()) {

    $user_name = $uRow['firstname']." ".$uRow['lastname'];
    print_r($uRow);
    $customerList = $salesConn->query("SELECT * FROM `customerLeads` WHERE curdate() >= (dateUpdated + interval 2 day)  AND `assisgnedTo` = '$user_name' ORDER BY `customerID` DESC");
// show this on error
if (!$customerList) {
     // For PDO:
    echo $salesConn->errorInfo();
}
      while ($cRow = $customerList->fetch()) {
        $leadID = $cRow['customerID'];
        $firstName = $cRow['customerFirstName'];
        $lastName = $cRow['customerLastName'];
        $tele = $cRow['customerTel'];
        ....
        $dateCreated = $cRow['dateCreated'];
        $dateUpdated = $cRow['dateUpdated'];

      }
  }
  ?>

By printing $uRow it shows:

Array ( [email] => [email protected] [0] => [email protected] [firstname] => Joe [1] => Blogs [lastname] => Blogs [2] => Blogs )

Connection Page is:

<?php
$salesConn = new PDO('mysql:host=HOST;dbname=DBNAME', 'USERNAME', 'PASSWORD');
$salesConn->setAttribute(PDO::ATTR_ERRMODE);
?>

New Error: Warning: PDO::setAttribute() expects exactly 2 parameters, 1 given in /homepages/38/d735513801/htdocs/includes/conn.php on line 8

Upvotes: 0

Views: 78

Answers (3)

Martin
Martin

Reputation: 22760

This is a tester script to establish what is wrong with your SQL.

localhost, DBNAME, USERNAME, PASSWORD are hardcoded values that the OP has not given and so the OP needs to update these themself.

This script below uses proper PDO and Exceptions. Get used to using Exceptions. Read about them, Learn them. This script also properly uses Prepared Statements - You really really (really) should be using Prepared Statements in your SQL.

<?php
error_log( 'php version: ', phpversion());

try {
    $salesConn = new PDO('mysql:host=localhost;dbname=*DBNAME*;charset=utf8', '*USERNAME*', '*PASSWORD*');  
    error_log( 'client version: ', $salesConn->getAttribute(PDO::ATTR_CLIENT_VERSION));
    error_log( 'server version: ', $salesConn->getAttribute(PDO::ATTR_SERVER_VERSION));
    $salesConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $salesConn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
}
catch(PDOException $err) {
    error_log(print_r($err->getMessage(),true));
    die('Error log ONE was generated.');
}

$sql = "SELECT * FROM `customerLeads` WHERE CURDATE() >= (dateUpdated + INTERVAL 2 DAY)  AND `assisgnedTo` = :assigned ORDER BY `customerID` DESC"

$user_name = "Set ths value to whatever the username is you want to check";

try
{
    $stmt = $salesConn->prepare($sql);
    $stmt->bindValue(':assigned', $user_name, PDO::PARAM_STR);
    $stmt->execute();
    // The below result can be put into a loop to output each $row in turn.
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
}
catch(PDOException $err)
{
    error_log(print_r($err->getMessage(),true));
    error_log(print_r($salesConn->errorInfo(),true));
    die('Error log TWO was generated.');
}

echo 'done. Got this far, everything worked!';

Upvotes: 0

yunzen
yunzen

Reputation: 33439

Try this to get a proper error message from MySQL

$customerList = $salesConn->query("SELECT * FROM `customerLeads` WHERE curdate() >= dateUpdated + interval 2 day AND WHERE `assisgnedTo` = '$user_name' ORDER BY `customerID` DESC");

// show this on error
if (!$customerList) {
     /***
      * NOTE: in a perfect world this should be:
      * error_log(print_r($salesConn->errorInfo(),true)); OR
      * error_log(print_r($salesConn->error,true));
      ***/

     // For MySQLi:
     echo $salesConn->error;

     // For PDO:
     echo $salesConn->errorInfo();
}

Upvotes: 1

pr1nc3
pr1nc3

Reputation: 8338

SELECT * FROM `customerLeads` WHERE curdate() >= (dateUpdated + interval 2 day)  AND `assisgnedTo` = '$user_name' ORDER BY `customerID` DESC

You used two times WHERE clause. You had a syntax error in your mysql. And also better use parentheses in your queries when you want to compare the result of a number calculation.

Upvotes: 1

Related Questions