Selecting columns using variables

Question

Probably not the best description for a title but I really couldn't think of anything. What I want to happen is that I can create a query that selects the type from listings using the userID as a relationship and then selecting the town.

TABLE listings
saleID userID   type          description
1      23     clothing       nice clothing
2      45     clothing     More nice Clothing

TABLE users
userID    country      county         town
23         uk          county       townname1
24         uk          county       townname2

The variables are set in the url as a get eg) ?type=clothing&town=townname2

if (!isset($type)){
$query = "SELECT * FROM listings";
}
if (isset($type)) {
$query = "SELECT * FROM listings WHERE type = '{$type}'";
}

This is the bit i'm stuck on I want to get all listings with the variable $type 'clothing' and then select from users with the town of the variable $town

if (isset($town)) {
   $query = "SELECT users.town listings.userID listings.type FROM listings 
   WHERE type = '{$type}' 
   INNER JOIN users 
   ON users.town = '{$town}'";       
}

Hope I've explained this well enough to understand. Please help me with this last query

also validate input values for SQL injections, take a look at http://en.wikipedia.org/wiki/SQL_injection — Eugen Halca, Sep 23 '13 at 14:12

juergen d · Accepted Answer · 2013-09-23T14:10:49.747

1

SELECT u.town l.userID, l.type 
FROM listings l
INNER JOIN users u on u.userID = l.userID   
WHERE l.type = '{$type}' 
AND u.town = '{$town}'

edited Sep 23 '13 at 14:10

answered Sep 23 '13 at 14:10

juergen d

201,996
37
293
362

$results = mysql_query($query); while ($listing = mysql_fetch_assoc($results)) { – Ultigma Sep 23 '13 at 15:03
sorry, i keep pressing enter, how do I access the description in TABLE listings? would I do $listings['l.description'] because it doesnt seem to find it ? – Ultigma Sep 23 '13 at 15:04
figured it out. needed it to be all instead of just specifying particular columns. CHANGED l.userID, l.type TO l.* Thanks again for the solution – Ultigma Sep 23 '13 at 15:20

score 0 · Answer 2 · edited May 23 '17 at 12:05

0

SELECT  b.town, a.userID, a.type 
FROM    listings a
        INNER JOIN users b 
            ON  a.userID  = b.userID    
WHERE   a.type = '{$type}' AND
        b.town = '{$town}'

To further gain more knowledge about joins, visit the link below:

Visual Representation of SQL Joins

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 12:05

Community

1
1

answered Sep 23 '13 at 14:10

John Woo

258,903
69
498
492

Thanks for the reply. I'm assuming the "a" is acting as a variable for listings? "FROM listings **a**" – Ultigma Sep 23 '13 at 14:55

Selecting columns using variables

2 Answers2