CODEWITHSUNDEEP
phpmysql
Adam McGurk
Adam McGurk

Reputation: 476

Get data from different table based on Id

I'm building a small e-commerce website and I want to display the reviews, and I want to create screen names for people (first letter of first name concatenated onto the last name), but I can't figure out how to get the information from my clients table. Let me show you the code I've done so far:

$invId = filter_input(INPUT_GET, 'invId', FILTER_SANITIZE_NUMBER_INT); // this is taken from a name value pair from the view
// Gets the raw data from the database    
function getProRev($invId){
        $db = acmeConnect();
        $sql = "SELECT * FROM reviews WHERE invId = :invId ORDER BY reviewId DESC";
        $stmt = $db->prepare($sql);
        $stmt->bindValue(':invId', $invId, PDO::PARAM_STR);
        $stmt->execute();
        $tn = $stmt->fetchAll();
        $stmt->closeCursor();
        return $tn;
    }

// Builds the simple review display
function buildReviewDisplay($reviews){
    $rd = "<div id='reviewView'>";
    foreach ($reviews as $review){
        $rd .= "<h2>$review[clientId]</h2>";
        $rd .= "<h3>$review[reviewDate]</h3>";
        $rd .= "<p>$review[reviewText]</p>";
        $rd .= "<hr>";
    }
    $rd .= "</div>";
    return $rd;
}

As you can see, I'm displaying the clientId (a number) which is not what I want, and now this is where I'm stuck. I have the relationship between the two tables (clients and reviews) set up, but I can't figure out how to get the data. Here is the function I tried to write, but it didn't work:

// Trying to get the dang client info
function getUsername($clientId){
$db = acmeConnect();
$sql = "SELECT * FROM clients WHERE clientId = :clientId";
$stmt = $db->prepare($sql);
$stmt->bindValue(':clientId', $clientId, PDO::PARAM_STR);
$stmt->execute();
$cd = $stmt->fetchAll();
$stmt->closeCursor();
$fletter = substr($cd['clientFirstname'], 0, 1);
$scrnam = $fletter . $cd['clientLastname'];
return $scrnam;
}

And I understand that this didn't work because there was nothing passing the $clientId parameter to the function, but the is contained in the $tn[] array, so there has to be a way that I can take the $clientId from the $tn[] array and query the database for the first and last name, but I can't figure out how.

Upvotes: 0

Views: 45

Answers (1)

Don&#39;t Panic
Don&#39;t Panic

Reputation: 41810

You can join the clients table to the reviews table in your first query.

SELECT * FROM reviews
LEFT JOIN clients ON reviews.clientId = clients.clientId
WHERE invId = :invId ORDER BY reviewId DESC

Then you'll have access to the client name columns without needing to execute an additional query for each review you display.

That way you can use your code from getUsername in buildReviewDisplay.

foreach ($reviews as $review) {
    $fletter = substr($review['clientFirstname'], 0, 1);
    $scrnam = $fletter . $review['clientLastname'];
    $rd .= "<h2>$scrnam</h2>";
    $rd .= "<h3>$review[reviewDate]</h3>";
    $rd .= "<p>$review[reviewText]</p>";
    $rd .= "<hr>";
}

Upvotes: 1

Related Questions