PHP Query a table column based on value from another column in same table

<a href="https://i.stack.imgur.com/NNyIW.png" target="_blank"><img src="https://i.stack.imgur.com/NNyIW.png"></a>I have a database I have to query using PHP. There are 3 different columns I'm dealing with. What I have to do require a handful of steps so I'll list the steps 1 by 1...... 1) I have to go through each value in the 'nid' column. However, some of the 'nids' have duplicate value. Therefore I have to choose the 'nid' with the highest 'vid' value. 2) Once I select the 'nid' with the highest 'vid' value I then have to get the value of the 'title' column that's in the same row of the highest 'vid'. For example if I have a 'vid' of 1253 I have to select the content that's in column title that corresponds with 'vid' 1253. I have a lot of the steps. However, I'm getting stuck on once I grab the highest vid, being able to grab the content in the title column. Below is my code

I have a database I have to query using PHP. There are 3 different columns I'm dealing with. What I have to do require a handful of steps so I'll list the steps 1 by 1...... 1) I have to go through each value in the 'nid' column. However, some of the 'nids' have duplicate value. Therefore I have to choose the 'nid' with the highest 'vid' value. 2) Once I select the 'nid' with the highest 'vid' value I then have to get the value of the 'title' column that's in the same row of the highest 'vid'. For example if I have a 'vid' of 1253 I have to select the content that's in column title that corresponds with 'vid' 1253. I have a lot of the steps. However, I'm getting stuck on once I grab the highest vid, being able to grab the content in the title column. Below is my 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";
    // 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 = []; // empty array to store max 'vid' values
for ($i = 0; $i &lt; 8000; $i++) {
  $storeNIDAndVIDValues[$i] = 0;
  // line above assigns initial 'vid'; starts at 0
}

while ($row = mysqli_fetch_array($results)) {
  $currentNID = $row['nid'];
  // line above creates variable that represents the current 'nid' of row (aka the key)
  $currentVID = $row['vid'];
  // line above creates variable that represents the current value of the 'vid' (the number you want to compare)
if ($currentVID &gt; $storeNIDAndVIDValues[$currentNID]) {
    // if the value of'$currentVID' is greater than what's stored in array '$storeNIDAndVIDValues' at current nid position
    // $storeNIDAndVIDValues[$currentNID] = goes into array $storeNIDAndVIDValues and gets the value of nid key (in this case value represents what position the nid is at)
       $storeNIDAndVIDValues[$currentNID] = $currentVID;
       // line above &gt; becomes max 'vid' at that time 
       $titleOfSelectedVID = $row['title'];
      // $row['title'] = gets the value of the current 'title'
    $queryTitle = "SELECT title FROM node_revision WHERE $currentVID ";
    // line above is query variable that targets 'title' column row that has highest 'vid' value
 } // line ends if statement
} // line closes while loop

?>

$queryTitle = "SELECT title FROM node_revision WHERE

The $queryTitle line is where I'm getting stuck. This is where I want to get the content of title column but only the title that corresponds with the highest vid.

MySQL Database Bootcamp: Go from SQL Beginner to Expert | Simpliv

MySQL Database Bootcamp: Go from SQL Beginner to Expert | Simpliv

MySQL Database Bootcamp: Go from SQL Beginner to Expert

Description
This course will help you in reading and writing complex SQL queries using one of the most demanding database in industry which is mysql. These skills are also applicable to any other major SQL database like Microsoft SQL Server,Oracle, and much more.

This course is designed for Students as well Software professionals who are willing to learn, understand the technique to create databases, tables or how to query that database.This course includes Creating database, different ways to insert values in the database as well as selecting queries and different types of joins.

Each section includes practice problems or additional e-books to help reinforce what you learn in the video tutorials.

If you are already a SQL programmer and looking for a reference course then this course is not for you.

Basic knowledge
No prior SQL or technical experience is required.
No software License is required. We will install MySQL and workbench which is freely available on site
A Windows or Mac machine where we will install the database and practice our queries
Basic computer knowledge is required to learn from this course
What will you learn
Create your own database or insert values in existing databases
Write Advance SQL queries
Become a proficient MySQL Workbench user(Create, import, export and query databases)
Handle complex SQL joins(inner,Outer,Cross & Self)
Student will be able to Create & modify tables with setting constraints to columns.
Learn some of the most useful built-in functions in SQL
To continue:

PHP / MySQL - Count how many table in database contain Specific name

I'm building an admin to control an interactive game, and I'm using PHP/MySql to store all the data for all the rounds. Each of the tables that I want to load into the game via PHP are titled "Puzzle*" with the * being an incremental index number. There are a few other tables in my database for global game data and bonus rounds.

I'm building an admin to control an interactive game, and I'm using PHP/MySql to store all the data for all the rounds. Each of the tables that I want to load into the game via PHP are titled "Puzzle*" with the * being an incremental index number. There are a few other tables in my database for global game data and bonus rounds.

What I want to be able to do is count the number of tables in my database that contain the name "Puzzle", and then use that total number as a randomizer to select a puzzle randomly. I plan to have roughly 50 puzzles, so an automated way of selecting a random puzzle, and then if I want to select another puzzle - to remove that previous puzzle from the list so that there is no chance of it being repeated until I start the whole game over again.

MySQL Database

Database Name: puzzleGame
Table 1: puzzle1
Table 2: puzzle2
Table 3: puzzle3
Table 4: puzzle4
Table 5: globals
Table 6: bonus


SQL Full Course - SQL Tutorial For Beginners - MySQL Essentials - SQL Training

SQL Full Course - SQL Tutorial For Beginners - MySQL Essentials - SQL Training

This SQL Full Course video will cover all the topics of SQL starting from scratch. This video is great for beginners who want to learn SQL and for advanced people to brush up their skills.


Thanks for watching

If you liked this post, share it with all of your programming buddies!

Follow us on Facebook | Twitter

Learn More

The Complete SQL Bootcamp

The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert

The Complete Oracle SQL Certification Course

MySQL Databases With Python Tutorial

SQL vs NoSQL or MySQL vs MongoDB

MySQL Database Tutorial for Beginners to Advanced Part 1/2

An Introduction to Queries in MySQL

How To Troubleshoot MySQL Queries?

SQL with MySQL - Complete Tutorial for Beginners

Build a Basic CRUD App with PHP and MySQL