CODEWITHSUNDEEP
phpmysql
flvsme
flvsme

Reputation: 47

Two tables, display multiple results from one matching the ID

I have two tables:

post_languages with the following columns: languageID, languageName

post_to_languages with: postID, postLanguage

What I'm trying to achieve is to display all languages associated with a post.

example: Post 1, languages: French, Russian

Maybe my approach is wrong but this is one of the methods I have tried:

//get language id
$stmt2 = $db->prepare('SELECT languageID FROM post_to_languages WHERE postID = :postID');
$stmt2->execute(array(':postID' => $row['postID']));

//Count total number of rows
$rowCount2 = $stmt2->rowCount();

if ($rowCount2 > 0) {
    $row2 = $stmt2->fetch(PDO::FETCH_ASSOC);
    foreach ($row2 as $langID) {
        $stmt3 = $db->prepare('SELECT languageName FROM post_languages WHERE languageID = :languageID');
        $stmt3->execute(array(':languageID' => $langID));
        $row3 = $stmt3->fetch();
        $lang_string = $row3['languageName'];
    }
} else {
    $lang_string = "Unknown";
}

Doesn't matter what I tried, I get only one language. Maybe I should select post_to_languages by ID first.

Upvotes: 2

Views: 45

Answers (2)

Abhishek Gupta
Abhishek Gupta

Reputation: 109

you are selected different column from diff. table. in table post_to_language must be same datatype as languageId.

please try this code

//get language id

$stmt2 = $db->prepare('SELECT postLanguage FROM post_to_languages WHERE postID = :postID');
$stmt2->execute(array(':postID'=>$row['postID']));

    //Count total number of rows
    $rowCount2 = $stmt2->rowCount();

    if($rowCount2 > 0){

     $row2 = $stmt2->fetch(PDO::FETCH_ASSOC);

   foreach($row2 as $langID) {

      $stmt3 = $db->prepare('SELECT languageName FROM post_languages WHERE languageID = :languageID');
     $stmt3->execute(array(':languageID'=>$langID));

    $row3 = $stmt3->fetch();
    $lang_string = $row3['languageName'];

    }

    } else {
          $lang_string = "Unknown";
    }

Thank you.

Upvotes: 0

e4c5
e4c5

Reputation: 53734

You don't need this nested loop, try a join or a subquery. Showing you a subquery. If you have a large number of rows, an inner join will be faster. But a subquery is still heaps faster than a nested loop.

SELECT languageName FROM post_languages WHERE languageID IN 
 (SELECT languageID FROM post_to_languages WHERE postID = :postID')

If you still don't see any results, replace :postID with a real value and try it in the console.

Upvotes: 1

Related Questions