-4

I would like to get data from two tables in MySQL, ordered by date.

$sql = "
SELECT 
    items.*, invoice.* 
FROM 
    items 
JOIN 
    invoice 
ON 
    items.user_id = invoice.buyer_id 
WHERE 
    items.user_id = '$user_id'" 
LIMIT 
    10 
ORDER BY 
    date;
";
Dharman
  • 30,962
  • 25
  • 85
  • 135
Hassan Ibrahim
  • 7
  • 2
  • 1
    What is the question? `limit 10 order by date` is probably throwing PHP syntax errors, that should be inside the double quote. Also your order in that bit is incorrect, `limit` should be last https://dev.mysql.com/doc/refman/5.7/en/select.html. – chris85 Dec 04 '17 at 16:51
  • We are always glad to help and support new coders but ***you need to help yourself first. :-)*** After [**doing more research**](https://meta.stackoverflow.com/q/261592/1011527) if you have a problem **post what you've tried** with a **clear explanation of what isn't working** and provide [a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). Read [How to Ask](http://stackoverflow.com/help/how-to-ask) a good question. Be sure to [take the tour](http://stackoverflow.com/tour) and read [this](https://meta.stackoverflow.com/q/347937/1011527). – Jay Blanchard Dec 04 '17 at 16:52
  • Can you clarify where the problem is? Try to explain where the problem is. Are you getting an error message? Sort order wrong? No records? – ventiseis Dec 04 '17 at 16:52
  • 1
    You have a quotation mark in an odd place, and LIMIT cannot come before ORDER BY. Apart from that, I don't know what you're after. – Strawberry Dec 04 '17 at 16:55
  • 1
    Also, it's better to use prepared statements, to avoid SQL inection – jeprubio Dec 04 '17 at 17:43
  • I'm sorry all, i fixed the quotations, but the query is not working at all, i want to take the last 10 rows added to table items or invoice – Hassan Ibrahim Dec 04 '17 at 17:43
  • If it's the latest 10 just finish it with "... ORDER BY date DESC, LIMIT 10" – jeprubio Dec 04 '17 at 17:44
  • As previously mentioned, trying moving `LIMIT` after `ORDER BY`, if that still doesn't work, have a look to see which `date` field you are ordering by, `items.date` or `invoice.date`. – MCMXCII Dec 04 '17 at 17:49
  • I fixed it, the problem was, the table invoice was empty, so items.user_id = invoice,buyer_id was the problem, i did check on table invoice and all worked perfectly!! thank you all for your support, and sorry again for not making myself clear!! – Hassan Ibrahim Dec 04 '17 at 17:55
  • **WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST`, `$_GET` or **any** user data directly into a query, it can be very harmful if someone seeks to exploit your mistake. – tadman Dec 04 '17 at 17:56

1 Answers1

0

Try with:

$sql = "SELECT *";
$sql .= " FROM items, invoice";
$sql .= " WHERE items.user_id = invoice.buyer_id";
$sql .= " AND items.user_id = '$user_id'";
$sql .= " ORDER BY date DESC";
$sql .= " LIMIT 10";

Also it is better if you use it as a prepared statement instead of having the variable inside the SQL query to avoid SQL injection.

jeprubio
  • 17,312
  • 5
  • 45
  • 56
  • Why with all the `.=` stuff? [Multi-line strings are fine in PHP](https://stackoverflow.com/questions/9744192/multi-line-strings-in-php). The mistake was an extra `"`. – tadman Dec 04 '17 at 17:55
  • To concatenate, is a way of not having so many spaces in the sql statement so the sql remains cleaner. It's not obligatory to do it this way. Without them and all in the same string must also work. – jeprubio Dec 04 '17 at 17:57
  • The server process doesn't care about spaces while people editing this code bristling with syntax do. It's all about priorities. – tadman Dec 04 '17 at 17:58
  • But imagine you print the content of your $sql var, this way it's much cleaner. But yes, I agree with you in that one, it's all about priorities. – jeprubio Dec 04 '17 at 18:00
  • I don't know about you but I prefer the formatting in the code to be whatever gets logged. It's consistent that way. This ends up a gnarly one-liner that can be difficult to read. This also has a horrendous [SQL injection bug](http://bobby-tables.com/) in it, so... – tadman Dec 04 '17 at 18:01
  • Yes, the sql injection I said it on a comment on the question that I would change it with a prepared statement to avoid sql injection. But anyway, it's up to him whether to follow that instructions or not. Also, it depends on the $user_id value, if this value is sanitized the sql injection is not possible at all. – jeprubio Dec 04 '17 at 18:04
  • Sanitized means nothing. It's still injectable code. A single mistake and this is suddenly vulnerable. This is not the case with prepared statements where you often must make two or more mistakes to have vulnerable code. – tadman Dec 04 '17 at 18:11
  • 1
    If you check that user_id is an integer you can not put anything strange in that select, that is what sanitize means, and the code would not be vulnerable to sql injection. However, I've just edited the answer to say it's better to use prepared statements. – jeprubio Dec 04 '17 at 18:13
  • It's always better to assume *nothing* about the quality of these values. You're hoping, praying, downright depending on that value to have been sanitized, which means if for some reason it isn't... Queries should be *obviously* safe, not presumed safe. – tadman Dec 04 '17 at 18:21