CODEWITHSUNDEEP
phpmysqlsqldatabase
Aishah91
Aishah91

Reputation: 97

PHP Make 2 different database table rows correspond with each other

enter image description hereI'm working on getting data from a particular database. I have to work with 3 different columns 'nid', 'vid' and 'title'. Once I get the 'vid' the has the highest value I have to get the content that it's 'title' but I have to get it from the row that corresponds with the highest 'vid' value. For example if vid is set to 1253 I have to grab content from the title that corresponds with vid 1253. How do I code it to grab the title that has a vid of 1253. I need these two rows to correspond with eachother. Is this some type of query line I should do. Is it a where query statement I need?

$queryNodeRevision = "SELECT nid, MAX(vid) as vid, title FROM node_revision GROUP BY nid";
    // line above creates variable $queryNodeRevision > selects column "nid" from table "node_revision"

    $results = mysqli_query($connection, $queryNodeRevision) or die("Bad Query: $results");
    // line above creates variable $results > actually queries that database and passes in variable "$queryNodeRevision"

    $storeNIDAndVIDValues = [];

    for ($i = 0; $i < 8000; $i++) {
      $storeNIDAndVIDValues[$i] = 0;
      // line above assigns initial 'vid'; starts at 0
    }

    while ($row = mysqli_fetch_array($results)) {
      $currentNID = $row['nid'];
      $currentVID = $row['vid'];
      $theTitleIWant = $row['title'];
    if ($currentVID > $storeNIDAndVIDValues[$currentNID]) {
        $storeNIDAndVIDValues[$currentNID] = $currentVID;
        echo "<h1>" . $row['title'] . "</h1>";[![enter image description here][1]][1]
    } 
    } // line closes while loop

Updated Code:

<?php
    // Establish all database credential variables
    $serverName = "localhost";
    $username = "root";
    $password = "root";
    $databaseName = "redesign_static";

    // Create Database Connection
    $connection = new mysqli($serverName, $username, $password, $databaseName);

    // Check Database Connection
    if ($connection->connect_error) {
      die("Connection failed:" . $connection->connect_error);
    } // line ends if statement

    $queryNodeRevision = "SELECT nid, vid, title FROM node_revision WHERE vid in (SELECT max(vid) FROM node_revision);";
    // line above creates variable $queryNodeRevision > 

    $results = mysqli_query($connection, $queryNodeRevision) or die("Bad Query: $results");
    // line above creates variable $results > actually queries that database and passes in variable "$queryNodeRevision"

    while ($row = mysqli_fetch_array($results)) {
      $currentNID = $row['nid'];
      $currentVID = $row['vid'];
      $theTitleIWant = $row['title']; 
      echo "<h1>" . $row['title'] . "</h1>";
      // line prints out desired 'title' into h1 tag
    } // line closes while loop
  ?>

Upvotes: 0

Views: 56

Answers (1)

Romain B.
Romain B.

Reputation: 654

SELECT node_revision.nid, node_revision.vid, node_revision.title 
FROM node_revision 
JOIN
(
    SELECT nid, max(vid) as vid 
    FROM node_revision 
    GROUP BY nid
) as nr
ON node_revision.nid = nr.nid 
AND node_revision.vid = nr.vid

Little explanation :

To get the highest vid for every nid as you already did :

SELECT nid, max(vid) as vid FROM node_revision GROUP BY nid

Which you join to node_revision to get the title of highest vid for each nid :

SELECT node_revision.nid, node_revision.vid, node_revision.title FROM 
node_revision 
JOIN ( <rows with highest vid for each nid> ) as nr
ON node_revision.nid = nr.nid AND node_revision.vid = nr.vid

Upvotes: 1

Related Questions