Getting var from database, then getting record based on it

[Shredon]

Hi. Im trying to make galaxy browser, where you can search for planets of other players. I want to make it like that if its good idea:

- get the record where player's ID (column users_id) is 1 (because there's no log in system yet) from it's already saved system that was watched last time (column users_galaxyViewSystem) from the "users" table

- save that last watched system number as $testowanySystem

- use $testowanySystem to get planet name (column planets_name) from the table "planets" where planets_galaxy = 1, planets_starsystem = $testowanySystem and planets_planet = 1

 

I was trying to do it in this way, but I think I'm too stupid still:

<?php
	$testowanaGalaktyka = 1;
	$testowanaPlaneta = 1;
	$testowanyGracz = 1;
							
	$sql = "SELECT users_galaxyViewSystem
	FROM users
	WHERE users_id = 1";
	$result = $conn->query($sql);
							
	$testowanySystem = $_GET['users_galaxyViewSystem'];
							
	$sql2 = "SELECT planets_name
	FROM planets
	WHERE planets_galaxy = $testowanaGalaktyka
	AND planets_starsystem = $testowanySystem
	AND planets_planet = $testowanaPlaneta";
	$result = $conn->query($sql2);
							
	if($result->num_rows>0)
	{
		while($row = $result->fetch_assoc())
		{
			echo ''.$row["planets_name"]."";
		}
	}		
?>

 

[Barand]

try

$sql = "SELECT users_galaxyViewSystem
    FROM users
    WHERE users_id = 1";
    $result = $conn->query($sql);
    $row = $result->fetch_assoc();
                            
    $testowanySystem = $row['users_galaxyViewSystem'];

However, you should be using a single query with a JOIN instead of two queries

$sql = "SELECT planets_name
            FROM planets p
                 JOIN 
                 users u ON p.planets_starsystem = u.users_galaxyViewSystem
            WHERE planets_galaxy = $testowanaGalaktyka
            AND planets_planet = $testowanaPlaneta
            AND users_id = 1    
        ";    
    $result = $conn->query($sql);
    foreach ($result as $row) {
        echo $row['planets_name'].'<br>';
    }

 

[Shredon]

Amazing 🥲. Thank you. Damn I know, Im just total newbie, but for sure I need to understand it, sooner better, because code is getting longer and longer. PHP is just a bit strange, but it's just new thing for me. Thank you for big help and your time

[mac_gyver]

this is the same 'activity' as in your previous thread, just with different meaning columns.

earlier thread -

SELECT users_username
FROM users u
JOIN
planets p ON u.users_id = p.planets_ownerid
WHERE planets_galaxy = $testowanaGalaktyka
AND planets_starsystem = $testowanySystem
AND planets_planet = $testowanaPlaneta

this thread -

SELECT planets_name
FROM planets p
JOIN
users u ON p.planets_starsystem = u.users_galaxyViewSystem
WHERE planets_galaxy = $testowanaGalaktyka
AND planets_planet = $testowanaPlaneta
AND users_id = 1

you are SELECTing things FROM a table JOINed with another table ON some condition relating the data between the tables WHERE some list of conditions that all ANDed together must be true. the learning to be had here is learning the structure of the query, so that you can write new queries based on what you have done before.

the following is the SELECT query prototype definition, which is listed in the order that the parts must appear within a query, with the parts you are using in bold (note: FROM table_references includes any JOINs) -

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [into_option]
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
    [into_option]

other common parts that you will see are - GROUP BY, occasionally HAVING, ORDER BY, and LIMIT. learning the meaning of and how to use these parts of a select query will allow you to write you own queries.

note: most queries that will match more than one row should have an ORDER BY term so that the data will be in the order that you want it.

next, you need to use a prepared query, with ? place-holders in the sql query for each value, then supply the actual values when the query is executed. this will prevent any sql special characters in a value from being able to break the sql query syntax, which is how sql injection is accomplished. since the mysqli extension is overly complicated when doing prepared queries, this would be a good time to switch to the much simpler and better designed PDO extension.

[mac_gyver]

and this is how simple it is to use a prepared query with the PDO extension -

// at the point of querying for and fetching the data
$sql = "SELECT planets_name
            FROM planets p
                 JOIN
                 users u ON p.planets_starsystem = u.users_galaxyViewSystem
            WHERE planets_galaxy = ?
            AND planets_planet = ?
            AND users_id = ?
        ";
$stmt = $pdo->prepare($sql);
$stmt->execute([ $testowanaGalaktyka, $testowanaPlaneta, $user_id ]);
$result_data = $stmt->fetchAll();


// at the point of outputting the data
if(empty($result_data))
{
	echo 'There is no data to display.';
}
else
{
	foreach($result_data as $row)
	{
		echo $row['planets_name'].'<br>';
	}
}

 

[Shredon]

Okay, thank you for explaining mac_gyver Im just too distracted to focus since last week, that's why I didn't noticed that