[PHP/MYSQL] Selectare doua tabele in aceasi bucla

WIK · February 9, 2021

bun pentru inceput am o baza in care in tabelul "users" se face stocheaza ip / id / etc etc etc in tabelul user_ids se stocheaza un id generat aleatoriu un cod de licenta si user_id care corespunde tot timpul cu id-ul din users acum am nevoie pe urmatorul script

(if $row['id'] == $row['user_id'] ) {

}

Sa verifice daca id-ul din users corespunde cu id-ul din user_ids dar am nevoie ca sa fie cumva in acelasi query ca am nevoie de ambele date in while

$ip = $_SERVER['REMOTE_ADDR'];  // aici i-au ip-ul
if(isset($_POST['a']) or isset($_POST['cod'])){ // setez parametri
	session_start();
	require_once "menu.php";
	$sql = "SELECT * FROM vrp_users";
	$result = $conn->query($sql);
		if ($result->num_rows > 0) {
			while($row = $result->fetch_assoc()) {
			preg_match('/(.*) (.*) (.*)/',$row['last_login'], $matches);
				$id = $row['id'];
					if($ip == $matches[1]){
						
					
							if (isset($_POST['cod'])) {
								if ($_POST['cod'] == $_SESSION['captcha']) {
										echo "cod valid ai fost permis sa intri pe server id-ul tau este".$id. 
										'<meta http-equiv="refresh" content="0; URL=fivem://'.$ipserver.'" />';
										$validare = "UPDATE `server`.`vrp_users` SET `whitelisted` =1 WHERE `id` = $id";
										$result = mysqli_query ($conn,$validare)
										or die ("nu am putut executa query-ul");
								}
								
								else {
									echo "Cod invalid, incearca din nou";
								}
							}

							$_SESSION['captcha'] = mt_rand(10000, 99999);
									
								 echo '	<center><b><h3> ai fost identificat in baza noastra de date ca avand id-ul: <font color="red">'.$row['id']. '</font> introdu codul de mai jos .</h3>
								 <div id="loginfivem">
											<form action="" method="post">
												<p>Tasteaza numarul: <font color="red">'.$_SESSION['captcha']. '</font></p>
												<p><input type="text" name="cod" /> <input type="submit" value="Submit" />
											</form>
										</div>';
						
					
				}else{

				}
			  

		}
		echo $row['user_id'];
	}else {
		echo "0 results";
	}
	$conn->close();
}else{
	echo '
<div class="container">
	<div class="vertical-center">
		<form action="verify.php" method="POST" id="form1">
		</form>
<button type="submit" class="btnn" form="form1" name="a" value="Submit">Verifica daca nu esti robot</button> 
</div>';
}

c-am asta ar fi codul...

UnixDevel · February 9, 2021

ba cei cu codul asta WTFK ?

ma dor ochii

faceti si voi niste functii pupezii

singura scuza a ta este sa fii incepator

aaa si vezi ca ai o eroare de logica !

pentru ca daca cineva cu IP dinamic din acelash block incearca serverul tau o sa aiba access la contul celuilat user logat ultima oara cu ip-ul respectiv.

de ce nu faci un join daca ai 2 tabele si un key user_id si selectezi bazat pe ip si user

sau de ce nu faci Select * from users where IP = $ip order by id desc limit 1

ca tu efectiv scoti toata tabela aia in memorie si dupa care faci un pregmatch si daca baza ta de date ajunge la 1GB o sa te plingi de ce merge greu

daca vrei ajutor calumea pune schema UML sau structura tabelelor la DB daca ai 2 tabele

Edited February 9, 2021 by lzomedia

WIK · February 9, 2021

23 minutes ago, lzomedia said:

ba cei cu codul asta WTFK ?

ma dor ochii

faceti si voi niste functii pupezii

singura scuza a ta este sa fii incepator

aaa si vezi ca ai o eroare de logica !

pentru ca daca cineva cu IP dinamic din acelash block incearca serverul tau o sa aiba access la contul celuilat user logat ultima oara cu ip-ul respectiv.

de ce nu faci un join daca ai 2 tabele si un key user_id si selectezi bazat pe ip si user

sau de ce nu faci Select * from users where IP = $ip order by id desc limit 1

ca tu efectiv scoti toata tabela aia in memorie si dupa care faci un pregmatch si daca baza ta de date ajunge la 1GB o sa te plingi de ce merge greu

daca vrei ajutor calumea pune schema UML sau structura tabelelor la DB daca ai 2 tabele

CREATE TABLE `vrp_user_ids`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `identifier` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `user_id` int NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `id_2`(`id`) USING BTREE,
  INDEX `fk_user_ids_users`(`user_id`) USING BTREE,
  INDEX `id`(`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1120 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = DYNAMIC;


CREATE TABLE `vrp_users`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
  `last_login` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL,
  `whitelisted` tinyint(1) NULL DEFAULT NULL,
  `x` float NOT NULL DEFAULT -541,
  `y` float NOT NULL DEFAULT -210,
  `z` float NOT NULL DEFAULT 37,
  `banned` tinyint(1) NULL DEFAULT NULL,
  `bannedReason` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL,
  `DmvTest` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'Required',
  `bannedBy` char(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT 'Consola',
  `adminLvl` int NOT NULL DEFAULT 0,
  `vipLvl` int NOT NULL DEFAULT 0,
  `pet` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `job` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'Somer',
  `faction` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  ` DmvTest` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'Required',
  `isFactionLeader` int NOT NULL DEFAULT 0,
  `factionRank` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `bankMoney` int NOT NULL DEFAULT 500000,
  `walletMoney` int NOT NULL DEFAULT 4500000,
  `xzCoins` int NOT NULL DEFAULT 0,
  `diamonds` int NOT NULL DEFAULT 0,
  `questFinished` int NOT NULL DEFAULT 0,
  `canDisableAC` int NOT NULL DEFAULT 0,
  `lvl` int NOT NULL DEFAULT 1,
  `xp` int NOT NULL DEFAULT 0,
  `warns` int NOT NULL DEFAULT 0,
  `hoursPlayed` float NOT NULL DEFAULT 0,
  `vehTaxEx` int NOT NULL DEFAULT 0,
  `gotXmasGift` int NOT NULL DEFAULT 0,
  `dblPayday` int NOT NULL DEFAULT 0,
  `age` int NOT NULL DEFAULT 18,
  `firstName` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'Nume',
  `secondName` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'Prenume',
  `vehLim` int NOT NULL DEFAULT 10,
  `bizLim` int NOT NULL DEFAULT 5,
  `CarInsurance` varchar(55) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'No',
  `GunLicense` varchar(55) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'Required',
  `refferedBy` int NOT NULL DEFAULT 0,
  `aJailTime` int NOT NULL DEFAULT 0,
  `aJailReason` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL,
  `duty` tinyint NOT NULL DEFAULT 0,
  `premium` tinyint NOT NULL DEFAULT 0,
  `fWarns` tinyint NOT NULL DEFAULT 0,
  `level` int NULL DEFAULT 1,
  `experience` int NULL DEFAULT 0,
  `sange` int NULL DEFAULT 100,
  `skin` longtext CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL,
  `hwarns` int NULL DEFAULT 0,
  `raport` int NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 118 CHARACTER SET = latin1 COLLATE = latin1_swe

c-am asta ar fi structura si da sunt incepator si vreau sa invat usor usor .

UnixDevel · February 9, 2021

deci acel cod de care zici tu ca se genereaza aleatoriu este stocat in primul tabel cu

  `identifier` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL

ca daca este asa poti face ceva de genu

da va trebui sa schimbi codul

SELECT 
    c.identifier, 
    c.user_id, 
    o.last_login, 
    o.id
FROM
    vrp_user_ids c
LEFT JOIN vrp_users o 
    ON c.user_id = o.id
WHERE
    o.last_login = $ip;

WIK · February 9, 2021

6 minutes ago, lzomedia said:
deci acel cod de care zici tu ca se genereaza aleatoriu este stocat in primul tabel cu
  `identifier` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL
ca daca este asa poti face ceva de genu

da va trebui sa schimbi codul
SELECT 
    c.identifier, 
    c.user_id, 
    o.last_login, 
    o.id
FROM
    vrp_user_ids c
LEFT JOIN vrp_users o 
    ON c.user_id = o.id
WHERE
    o.last_login = $ip;
    
    
    
    
    
    

imi poti da o sugestie cam cum ar trebui sa il schimb macar prin ce metoda sa culeg informatiile din baza de date. e gresita bucla while in ceea ce vreau sa fac?

UnixDevel · February 9, 2021

asta as face doar asa sa nu mai trebuiasca sa am brain overload

acu daca e cum zic eu cu acel identifier iti pot arata si cum se simplifica in partea aia

<?php

$ip = $_SERVER['REMOTE_ADDR'];
if(isset($_POST['a']) or isset($_POST['cod'])){
    session_start();
    require_once "menu.php";
    $sql = "SELECT * FROM vrp_users";
    $result = $conn->query($sql);
    if ($result->num_rows > 0) {
        while($row = $result->fetch_assoc()) {
            preg_match('/(.*) (.*) (.*)/',$row['last_login'], $matches);
            $id = $row['id'];
            if($ip == $matches[1]){
                if (isset($_POST['cod'])) {
                    if ($_POST['cod'] == $_SESSION['captcha']) {
                        echo "cod valid ai fost permis sa intri pe server id-ul tau este".$id.
                            '<meta http-equiv="refresh" content="0; URL=fivem://'.$ipserver.'" />';
                        $validare = "UPDATE `server`.`vrp_users` SET `whitelisted` =1 WHERE `id` = $id";
                        $result = mysqli_query ($conn,$validare)
                        or die ("nu am putut executa query-ul");
                    }
                    codInvalid();
                }

                $session = $_SESSION['captcha'] = mt_rand(10000, 99999);
                checkCapcha($id, $session);
            }


        }
        echo $row['user_id'];
    }else {
        echo "0 results";
    }
    $conn->close();
}else{

    //replace with function
    generateVerify();
}


function codInvalid(){
    echo "Cod invalid, incearca din nou";
}
function checkCapcha($id, $session){

    echo '	<center><b><h3> ai fost identificat in baza noastra de date ca avand id-ul: <font color="red">'.$id. '</font> introdu codul de mai jos .</h3>
								 <div id="loginfivem">
											<form action="" method="post">
												<p>Tasteaza numarul: <font color="red">'.$session. '</font></p>
												<p><input type="text" name="cod" /> <input type="submit" value="Submit" />
											</form>
										</div>';
}
function generateVerify(){
    echo '
<div class="container">
	<div class="vertical-center">
		<form action="verify.php" method="POST" id="form1">
		</form>
<button type="submit" class="btnn" form="form1" name="a" value="Submit">Verifica daca nu esti robot</button>
</div>';
}

Sign In

[PHP/MYSQL] Selectare doua tabele in aceasi bucla

Recommended Posts

WIK

UnixDevel

WIK

UnixDevel

WIK

UnixDevel

Join the conversation

Browse

Activity

Pages