MySQL query to select specific data

Question

I would like to display the data that belongs to any of my users when they login to the site, as well as the name of each table (they completed offers on them).

This is the code I used, but when I add it it's not working.

$result = mysql_query('SELECT *,\'tbl1\' AS tablename FROM (SELECT * FROM  table1 WHERE user_id='$user_id') as tbl1 UNION SELECT *,\'tbl2\' AS tablename FROM (SELECT * FROM table1 WHERE user_id='$user_id') as tbl2'. ' ORDER BY `date` DESC');

while($sdata = mysql_fetch_array($result)){
  echo $sdata['date'];
  echo $sdata['tablename'];
  echo $sdata['user_reward'];
}

Where did I make a mistake?

Welcome to Stack Overflow! Please, before you write **any** more SQL interfacing code, you must read up on [proper SQL escaping](http://bobby-tables.com/php) to avoid severe [SQL injection bugs](http://bobby-tables.com). Also, [`mysql_*` functions](http://php.net/manual/en/ref.mysql.php) should not be used in new applications and will be removed from future versions of PHP. Please consider switching to [**`MySQLi`** or **`PDO`**](http://php.net/manual/en/mysqlinfo.api.choosing.php). As written, someone could ruin your entire website in seconds. — Petr R., Aug 31 '13 at 11:29
The first mistake is in the title. That's compounded by the use of the term 'wanna'. From there it's downhill. — Strawberry, Aug 31 '13 at 11:29
Let's start with the beginning. What errors do you get? If you get no errors, what happens? Are there any mysql errors perhaps in `mysql_error()`? Are you sure this query selects the data you actually want to select? — Sumurai8, Aug 31 '13 at 11:32
I agree with @Strawberry - _wanna_ and _pls_ tend to earn downvotes here. Remember that questions are for posterity here, and this is not a chat room or a forum. — halfer, Aug 31 '13 at 11:32
I suggest you separate out your SQL string from the call, so you can print out what you are sending to the database. Also, wrap your SQL to a ~80 character margin, so it is easier for everyone to read (in your editor and on our screens). See [my recent answer here](http://stackoverflow.com/a/18546359/472495) for more detail. — halfer, Aug 31 '13 at 11:35
See [this reference](http://stackoverflow.com/questions/12769982/reference-what-does-this-error-mean-in-php) — Sumurai8, Aug 31 '13 at 11:41
You are missing dots where you are putting your variables into PHP string — Lukas1, Aug 31 '13 at 11:42

halfer · Accepted Answer · 2013-08-31T11:47:07.357

You are missing the concatenation operators here, around $user_id:

$result = mysql_query(
    'SELECT *,\'tbl1\' AS tablename FROM (
        SELECT * FROM  table1 WHERE user_id=' . $user_id . '
     ) as tbl1
     UNION
     SELECT *,\'tbl2\' AS tablename FROM (
        SELECT * FROM table1 WHERE user_id=' . $user_id . '
     ) as tbl2' . ' ORDER BY `date` DESC'
);

I've wrapped the call for more clarity - I suggest you do the same in your own code. I'd be inclined to use " marks here instead, so you don't need to escape apostrophes.

The ORDER BY clause seems to be redundantly concatenated as well - remove the dot and add this part of the query to the as tbl2 part.

Here's how I would do it:

$sql = "
    SELECT *, 'tbl1' AS tablename FROM (
        SELECT * FROM  table1 WHERE user_id={$user_id}
     ) as tbl1
     UNION
     SELECT *, 'tbl2' AS tablename FROM (
        SELECT * FROM table1 WHERE user_id={$user_id}
     ) as tbl2
     ORDER BY `date` DESC
";
$result = mysql_query($sql);

Make sure that $user_id is properly escaped or cast, to avoid security problems. Also, this database library is no longer recommended, and will be removed in a future version of PHP. It would be better to move to PDO or mysqli, and use parameterisation.

Finally, it does rather look like the query itself is rather cumbersome - it looks like it could be simplified. Perhaps ask a separate question on that?

MySQL query to select specific data

1 Answers1