0

I am building an android app that uses geo location. I am trying to improve my overall app to improve its smoothness while running. I am using volly to connect to a php page on my web sever where the php page can then access my phpmyadmin database. My php page for updating locations is a horrible mess and I was hoping it can be fixed with the right sql query.

Lets get down to it.

So I have a table named users enter image description here

and a table named friends

enter image description here

In this particular example david is friends with mark and jack. Also to clarify mark and jack are friends with david.

What I need to do is Write a query if given a user ID say for example 3 that will produce a table of that person and his friends ID, cordsV1, cordsV2 without any duplicate IDs in the table.

I was able to get this to work with using loops and variables ect but as I said it is a horrible mess.

Here is my current all sql query attempt:

SELECT DISTINCT ID, cordsV1, cordsV2 FROM `friends`,`users` WHERE user_one_ID = 1 AND status = 1;

HOWEVER this just returns all of the user IDs from the user table. I am really bad with sql so if someone could point me in the right direction it would be much appreciated.

Here is my horrible mess of code if you were wondering:

<?php error_reporting(E_ALL | E_STRICT); ?>
<?php 
$THIS_USER_ID = $_GET['THIS_USER_ID'];


try {
    $one = 1;
    $db = new PDO("");
    $sql =  "SELECT * FROM   friends   WHERE  user_one_ID = '" . $THIS_USER_ID . "' AND status = '" . $one . "' OR user_two_ID = '" . $THIS_USER_ID . "' AND status = '" . $one . "'";


  
    $rows = $db->query($sql)
            ->fetchAll(PDO::FETCH_ASSOC);

    $printMe = [];

    foreach($rows as $row){
        $printMe[] = $row;
    }

    
    $jsonArr = json_encode($printMe);
    $characters = json_decode($jsonArr, true);


    
    // Getting the size of the sample array 
    $size = sizeof($characters); 
    $neg = -1;
    $sql2 = "SELECT * FROM   users   WHERE  ID = '" . $neg . "'";
    $sql3 = "";
    $sql4 = "";
    for ($x = 0; $x < $size; $x++ ){
        if ($characters[$x]['user_one_ID'] == $THIS_USER_ID && $characters[$x]['status'] == 1){
            $hold = $characters[$x]['user_two_ID'];
            $sql3 = $sql3 . " OR ID = '" . $hold . "'";

        } else if($characters[$x]['user_two_ID'] == $THIS_USER_ID && $characters[$x]['status'] == 1) {
            $hold = $characters[$x]['user_one_ID'];
            $sql4 = $sql4 . " OR ID = '" . $hold . "'";
        }
    }

    
    
    $sql5 = $sql2 . $sql3 . $sql4;

    $sql7 = "SELECT * FROM   users   WHERE  ID = '" . $THIS_USER_ID . "'";
    $printMe2 = [];
    $rows3 = $db->query($sql7)
    ->fetchAll(PDO::FETCH_ASSOC);

    foreach($rows3 as $row3){
        $printMe2[] = $row3;
    }


    $rows2 = $db->query($sql5)
            ->fetchAll(PDO::FETCH_ASSOC);


    foreach($rows2 as $row2){
        $printMe2[] = $row2;
    }

    $jsonArr2 = json_encode($printMe2);

    echo $jsonArr2;

    $db = null;


} catch(PDOException $ex) {
    die(json_encode(array('outcome' => false, 'message' => 'Unable to connect')));
}
?>
Kyle Lynch
  • 21
  • 6
  • **Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) – Dharman Aug 12 '20 at 10:43

1 Answers1

1

Get the user-data

SELECT
    *
FROM
    users
WHERE ID = ?

Get the user-data of friends

SELECT
    users.*
FROM
    friends
JOIN
    users ON users.ID = friends.user_two_ID
WHERE
    friends.user_one_ID = ?

Better use prepared statements, or your app wont be alive very long due to SQL-Injections.

You also want to have a look at meaningful names.

SirPilan
  • 4,649
  • 2
  • 13
  • 26