how to set $_SESSION variable to use in sql query?

Question

im not sure if i've got this right but i could do with some help to point me in the right direction please.

basically i have a table called ptb_stats. this table lay out looks like this:

user_id  |  user_postcode

   1             m3 4
   2             m3 4
   3             kt1 3
   4             sm2 7

i am trying to generate a mysql query that will bring up all the users that have matching postcodes. so for instance if user 1 / user_id 1 is logged in then they will see user 2 who has the same postcode as them (begining with m3 4)

this tells the user aproximately that user 1 and user 2 are within 5 miles of each other as an example.

i've got a working query which is this:

function get_local_users() {
            global $connection;
            $query = "
            SELECT *
            From ptb_stats, ptb_users
            WHERE ptb_stats.user_id=ptb_users.id
            AND ptb_stats.user_postcode='m3 4'
            AND ptb_users.id!=".$_SESSION['user_id']."";
            $local_set = mysql_query($query, $connection);
            confirm_query($local_set);
            return $local_set;  

        }

at the moment im having to enter the postcode manually into the query for it to work. my problem is that i need to assign a session variable i believe which will tell the query to match users with the same postcodes to the user who is logged in.

at the moment i have $_SESSION['user_id'] set as a variable but someone previously set this variable and i am trying to fix all their work but am unable to get in touch with the guy who did it. So i don't understand session variables. i thought i could just change $_SESSION['user_id'] to $_SESSION['user_postcode'] and it would work, but basically what i need to do is some how get the query to say:

if the logged in user's user_postcode is (whatever value) and their are other users with matching user_postcodes then display these.

can someone please show me what i would need to do to get this to work. i would really appreciate it. thank you.

[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](http://j.mp/XqV7Lp). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. — Kermit, Mar 12 '13 at 14:16
I suggest, you look at the answers to this question http://stackoverflow.com/q/15338359/1741542 — Olaf Dietsche, Mar 12 '13 at 14:39

score 0 · Answer 1 · edited May 23 '17 at 10:32

0

SELECT  a.*
FROM
        tableName a
        INNER JOIN
        (
            SELECT  user_postcode
            FROM    tableName
            GROUP   BY user_postcode
            HAVING  COUNT(*) > 1
        ) b ON a.user_postcode = b.user_postcode

UPDATE 1

function get_local_users() 
{ 
    global $connection; 
    $query = "  SELECT  a.* 
                FROM    ptb_stats a 
                        INNER JOIN 
                        ( 
                            SELECT user_postcode 
                            FROM ptb_stats 
                            GROUP BY user_postcode 
                            HAVING COUNT(DISTINCT user_id  ) > 1 
                        ) b ON a.user_postcode = b.user_postcode 
                WHERE a.user_id <> " . $_SESSION['user_id']; 
    $local_set = mysql_query($query, $connection); 
    confirm_query($local_set); 
    return $local_set; 
}

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

edited May 23 '17 at 10:32

Community

1
1

answered Mar 12 '13 at 14:15

John Woo

238,432
61
456
464

but how does this filter out the results to the logged in user? – James Pale Mar 12 '13 at 14:20
i tried with the filter and i get a mysql error: function get_local_users() { global $connection; $query = " SET @sessionID = ".$_SESSION['user_id']."; SELECT a.* FROM ptb_stats a INNER JOIN ( SELECT user_postcode FROM ptb_stats GROUP BY user_postcode HAVING COUNT(*) > 1 ) b ON a.user_postcode = b.user_postcode WHERE a.user_id <> @sessionID; "; $local_set = mysql_query($query, $connection); confirm_query($local_set); return $local_set; } – James Pale Mar 12 '13 at 14:29
this is the error im getting: Database query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT a.* FROM ptb_stats a INNER JOIN ( SE' at line 3 – James Pale Mar 12 '13 at 14:29
I tried your updated answer, thansk for this, but it brings up all users, it doesn't select only those with matching postcodes? – James Pale Mar 12 '13 at 14:35
it is the postcode the return from the subquery and which it has multiple `user_id`. – John Woo Mar 12 '13 at 14:38
ok, is there a way i can get it to only show those users who have matching user_postcodes as the session user? – James Pale Mar 12 '13 at 14:39

how to set $_SESSION variable to use in sql query?

1 Answers1