CODEWITHSUNDEEP
phpmysql
sycoi001
sycoi001

Reputation: 74

Join table and get value from table if value in this table is exist on other table

I have 2 tables. I need to do this job because I am starting to open free service for my game. I hope people can help.

Table 1 "online"

      |ID |
       ---
      | 1 |
      | 2 |
      | 3 |
      | 12|
      | 55|

Table 2 "User"

UserID|       IP
------------------------
  1   | 123.123.123.123
  2   | 123.123.123.123
  3   | 123.123.123.123
  12   | 123.123.123.123
  55   | 22.12.122.22

First. I get the value(ID) from "online" table. To get ID.

I have ID of one user is online.

Next. I use the value(ID) that I gotten from "online" table to select IP Address from "User" table.

When I got the IP from single user online. I get UserID from "User" table which have same "IPAddress". It will show many users who have same IP address.

My question. How can I make a query(my bonus query) for only 3 ID online from "online" table if there are so many user have same IP and online.

MYSQL version don't support select top. So I could not do this job. Please help.

Thank you all!

<?php

session_start();
header('Content-Type: text/html; charset=utf-8');
include ("config.php");
error_reporting(0);
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "data";
$account_Name = $_SESSION['userlogin'];


// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}


// get id online

$id_online = $conn->query("select ID from online");

$online = "";
while($row = $id_online->fetch_assoc())
{
	// list account online
	
	$online = $row["ID"];
	echo "- ID  online: $online";
	echo "</br>";
	$id_basetab = $conn->query("select UserID, IP from User where UserID='$online'");
	$ip = "";
	//show same ip list
	while($list = $id_basetab->fetch_assoc())
	{
		$ip = $list["LastIP"];
		echo "<hr>- IP: $ip</br>";
		$queryIP = $conn->query("SELECT UserID, IP FROM User where IP=$ip");
		echo "<hr>";
		//echo " acc Clone: </br>";
		
		while($row1 = $queryIP->fetch_assoc())
		{
				 //echo "id: " . $row1["AccountID"]. " - Name: " . $row1["name"]. " - IP: " . $row1["LastIP"]. "<br>";
		}
		
	
		
	}
	
	// join table
	echo "<hr>";
	$queryshow = $conn->query("select  UserID, IP from online FULL JOIN User ON UserID=ID where UserID=$online ");
		while($result = $queryshow->fetch_assoc())
		{
       // is my $queryshow correct? I want to get value ID is exist or not if exist UserID on User table.
       // How can I create a query for only 3 users online if they have multiple account online.
		  //I will make my custom query here.
		
		}
}


?>

Upvotes: 1

Views: 52

Answers (1)

Jonny
Jonny

Reputation: 1329

This query should get the data you need at one time. Now you can restructure and echo the results in your html and php one time.
This will cut down on a lot of code. I used inner join and got both table's data at once. If you need a where clause just place it between the inner join and order by section of this code. The LIMIT 3 shows the top 3 results in the data set

SELECT User.UserID, User.IP, online.ID 
FROM User 
INNER JOIN online ON User.UserID = online.ID  
ORDER BY User.IP DESC limit 3

Upvotes: 1

Related Questions