PDOStatement中可捕获的致命错误

Question

I'm not sure what I'm doing wrong. My code should read user_id from users table in mysql then use that in the next sql statement to read a line of sales for that sales person for the past week and then generate into a PDF. On line 56, I'm getting a Catchable fatal error: Object of class PDOStatement could not be convert to string, but as far as I can tell it should be a string. Any help would be greatly appreciated!

for($i=0;$i<$user_array;$i++) {
$uid = $user_array[$i];
try {
    //Create connection
    $con = new PDO("mysql:host=$servername; dbname=$database", $username, $password);
    $con->exec("SET CHARACTER SET utf8");
}
catch(PDOException $ee) {
    echo $ee->getMessage();
}

$con->beginTransaction();
$query = "SELECT CONCAT(fname,' ',lname) FROM users WHERE user_id = '$uid'";
$result = $con->query($query);

if($result !== false) {

    //This throws catchable fatal error: Object of class PDOStatement could not be converted to string - line 56
    $sql = "select saledate, custname, straddr from dplgalionsales 
    WHERE CONCAT(agent_first_name,' ',agent_last_name) = '$result'         //<-- line 56 
    and saledate > DATE_SUB(NOW(), INTERVAL 1 WEEK)"; 

    foreach($res->query($sql) as $row) {
        $mydate = date('m/d/Y');
        $dateadd = date('m/d/Y', strtotime($mydate.' + 3 days'));

        $html_table = '<div>Week Ending: ' .$mydate. '<br>Payroll Issued: ' .$dateadd. '</div><br>';
        $html_table .= '<table border="1" cellspacing="0" cellpadding="2" width="100%"><tr><th>Date</th><th>Customer Name</th><th>Address</th></tr>';

        $html_table .= '<tr><td>' .$row['saledate']. '</td><td>' .$row['custname']. '</td><td>' .$row['straddr']. ' ' .$row['city']. ' ' .$row['state']. ' ' .$row['zip']. '</td></tr>';

        $html_table .= '</table>'; //ends HTML table

    }


}

$mpdf = new mPDF();
$mpdf->SetTitle('DPL Galion Sales');
$mpdf->WriteHTML($html_table);
$mpdf->Output('./reports/'.$uid.'/'.date('m-d-Y').'_'.$uidname.'.pdf','F');
exit;
}

I was wondering if it has something to do with the MySQL CONCAT()? I don't really know a better way to match the salesperson's info though, because first and last name are separate and the sales reporting comes in without their sales ID on it, so name is the only reference point between the two tables. Thanks!

Answer 1

You should use an alias for that custom field:

CONCAT(fname,' ',lname) as fullName

then change $result to $result['fullName'].

Additional suggesstion

1. Put the whole transaction in a try/catch, roll back if you catch an exception
2. Use prepared statements

---

try {
    for ($i = 0; $i < $user_array; $i++) {
        $uid = $user_array[$i];

        //Create connection
        $con = new PDO("mysql:host=$servername; dbname=$database", $username, $password);
        $con->exec("SET CHARACTER SET utf8");


        $con->beginTransaction();
        $sql = "SELECT CONCAT(fname,' ',lname) as fullName FROM users WHERE user_id = :uid";
        $result = $con->prepare($sql);


        if ($result !== false) {
            $result->bindValue(':uid', $uid);
            $row = $result->fetch(PDO::FETCH_ASSOC);
            //This throws catchable fatal error: Object of class PDOStatement could not be converted to string - line 56
            $sql = "select saledate, custname, straddr from dplgalionsales
                    WHERE CONCAT(agent_first_name,' ',agent_last_name) = :fullname         //<-- line 56
                    and saledate > DATE_SUB(NOW(), INTERVAL 1 WEEK)";
            $result = $con->prepare($sql);
            $result->bindValue(':fullName', $row['fullName']);
            $rows = $result->fetchAll();
            foreach ($rows as $row) {
                $mydate = date('m/d/Y');
                $dateadd = date('m/d/Y', strtotime($mydate . ' + 3 days'));

                $html_table = '<div>Week Ending: ' . $mydate . '<br>Payroll Issued: ' . $dateadd . '</div><br>';
                $html_table .= '<table border="1" cellspacing="0" cellpadding="2" width="100%"><tr><th>Date</th><th>Customer Name</th><th>Address</th></tr>';

                $html_table .= '<tr><td>' . $row['saledate'] . '</td><td>' . $row['custname'] . '</td><td>' . $row['straddr'] . ' ' . $row['city'] . ' ' . $row['state'] . ' ' . $row['zip'] . '</td></tr>';

                $html_table .= '</table>'; //ends HTML table

            }


        }

        $mpdf = new mPDF();
        $mpdf->SetTitle('DPL Galion Sales');
        $mpdf->WriteHTML($html_table);
        $mpdf->Output('./reports/' . $uid . '/' . date('m-d-Y') . '_' . $uidname . '.pdf', 'F');
        exit;
    }
} catch (PDOException $ee) {
    $con->rollBack();
    echo $ee->getMessage();
}