asked on

PDO function not working

I am trying to write a function that will display the users message in out CRM. I am having some trouble with this:

//load Mysql connector
require_once($_SERVER['DOCUMENT_ROOT'] . '/connections/pdoConnect.php');

function getMessages($pdo,$ID) {
    $messages = "SELECT CONCAT(B.firstName, B.lastName) as senderName, (SELECT CONCAT(firstName,' ', lastName) FROM `leads_userInfo` WHERE `userID`=A.recipientID LIMIT 1) as recipientName, A.`subject`, A.`status`, A.`dateCreated` as `date`
FROM simpleNote as A
INNER JOIN leads_userInfo as B
ON A.senderID=B.userID
WHERE `recipientID`=$ID";
    foreach ($pdo->query($messages) as $message) {

        $row =  "<tr>";
        $row .= "<td>" . $message['recipientName'] . "</td>td>";
        $row .= "<td>" . $message['subject'] . "</td>td>";
        $row .= "<td>" . $message['name'] . "</td>td>";
        $row .= "<td>" . $message['name'] . "</td>td>";
        $row .=  "</tr>";
        return $row;

    }
}

Open in new window

What I want to do is call this function down in the table body in the html.

<?= getMessages($_SESSION['id';); ?>

Open in new window

I need help getting the function working correctly.

Julian Hansen

What is wrong with it -
a) does not do what it is expected - if so describe what is expected
b) Shows an error - if so what is the error

At first glance this does not appear to be correct

foreach ($pdo->query($messages) as $message) {
  $row =  "<tr>"; // THIS SHOULD BE A CONCATENATION
  $row .= "<td>" . $message['recipientName'] . "</td>td>";
  $row .= "<td>" . $message['subject'] . "</td>td>";
  $row .= "<td>" . $message['name'] . "</td>td>";
  $row .= "<td>" . $message['name'] . "</td>td>";
  $row .=  "</tr>";
  return $row; // I AM PRETTY SURE YOU DON'T WANT THIS HERE
}

Open in new window

Your loop is never going to go around more than once - the return statement executes after the first iteration and terminates the loop.

In addition your first $row assignment is not a concatenation - so even if the code was looping the $row variable would be re-initialised each time.

I imagine you probably want something like this

// INITIALISE $row HERE
$row = '';
foreach ($pdo->query($messages) as $message) {
  // ADD TO ROW ON EACH LOOP
  $row .=  "<tr>"; 
  $row .= "<td>" . $message['recipientName'] . "</td>td>";
  $row .= "<td>" . $message['subject'] . "</td>td>";
  $row .= "<td>" . $message['name'] . "</td>td>";
  $row .= "<td>" . $message['name'] . "</td>td>";
  $row .=  "</tr>";
}
return $row; // MOVE OUTSIDE OF LOOP

Open in new window

Julian Hansen

One more thing - this might be a typo but you have this line twice

        $row .= "<td>" . $message['name'] . "</td>td>";

Open in new window

And more importantly it is referencing an index 'name' that does not appear to be in the field list of the query.

Ray Paseur

If you're new to PHP and trying to learn the language, this article has pointers to well-vetted learning resources.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html

austinfx

ASKER

OK So I changed it up This code is working but I am only getting the one record because I am trying to echo the variable outside the loop.

require_once($_SERVER['DOCUMENT_ROOT'] . '/connections/pdoConnect.php');
$_SESSION['id'];
// connect to MySQL
mysql_select_db($database_medigap, $medigap);

// load the new messages
    $messages = "SELECT CONCAT(B.firstName, B.lastName) as senderName, (SELECT CONCAT(firstName,' ', lastName) FROM `leads_userInfo` WHERE `userID`=A.recipientID LIMIT 1) as recipientName, A.`subject`, A.`status`, A.`dateCreated` as `date`

FROM simpleNote as A
INNER JOIN leads_userInfo as B
ON A.senderID=B.userID
WHERE `recipientID`=312";

 $query =  $pdo->query($messages);
$message='';

foreach ($query as $row) {
    $message = "<tr>";
    $message .= "<td>" . $row['recipientName'] . "</td>";
    $message .= "<td>" . $row['subject'] . "</td>";
    $message .= "<td>" . "New" . "</td>";
    $message .= "</tr>";

}

// down in the page:
<?php echo $message; ?>

Open in new window

How does this need to be written to show each table set?

Ray Paseur

This article shows the way its done. It looks like you're mixing up MySQL and PDO. Best to choose one or the other and stick with it!
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

This page maps the familiar but obsolete MySQL functions to their closest relatives in the MySQLi and PDO extensions.
http://iconoun.com/mysql_mysqli_pdo_function_map.php

ASKER CERTIFIED SOLUTION

Julian Hansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial