Derek2020 Posted April 29, 2020 Share Posted April 29, 2020 (edited) Hello I am using SQLite and it seems a lot of the SQL and PHP code is different to the MySQL that W3 mentions. I have tried many combinations and through trial and error achieved most of what I need to know, but I am stuck on this one bit. I am trying to filter a database (SQLite) so that the only records shown are after the current time. I have set the datafield as 'numeric' in SQLite, I have used PHP/ SQL to write the current time to the database, in order to make sure I was using a compatible time format. <!DOCTYPE html> <html> <body> <?php $timenow = date("h:i"); $db = new SQLite3('phptest2.db'); $res = $db->query('SELECT * FROM times where timings > $timenow '); while ($row = $res->fetchArray()) { echo "{$row['location']} {$row['time']}\n <br>"; } echo "hello $timenow"; ?> </body> </html> On the basis that I can write and filter based on other variables and constants, I am inclined to think this is some error in the way I'm interrogating SQLite with the SQL above. I have added in the bottom 'echo' just to make sure it was picking up and recording the current time, which it is. I have tried it with all manner of ' and " around the variable (I expected it to be ' ' but it wasn't.) I apologise for such a basic question. I have exhausted all options- none of the search engine results give any working examples to help (at least not that I can understand). Please be assured that I'm not trying to be lazy. I've learned HTML and CSS from this brilliant site over the years, and recently gone from 0 to 'advanced beginner' with Pascal (again using SQLite), so learning plenty and now trying SQL and PHP as well. If anyone could point me in the right direction, with either help or a suggestion of a site, I would be most appreciative. Many thanks Derek Edited April 29, 2020 by Derek2020 Link to comment Share on other sites More sharing options...
Ingolme Posted April 29, 2020 Share Posted April 29, 2020 In the code above, the main issue is that your string is wrapped in single-quotes, so the variable does not get parsed. In PHP, single-quote strings and double-quote strings are different. $timenow = '5'; echo 'SELECT * FROM times where timings > $timenow'; // This prints: SELECT * FROM times where timings > $timenow echo "SELECT * FROM times where timings > $timenow"; // This prints: SELECT * FROM times where timings > 5 The $timenow variable should be wrapped in quotes since it is a string and not a number. I haven't worked with SQLite before, but if possible, you shouldn't put variables in your query and instead should use prepared statements if SQLite offers this functionality. Link to comment Share on other sites More sharing options...
Derek2020 Posted April 29, 2020 Author Share Posted April 29, 2020 Many thanks Ingolme. Unfortunately if I use double quotes then it comes up with a error 500. None of the SQL tutorials on W3 work for me, so I'm inclined to think that SQLite is not as fully compliant with the ISO standard as MySQL; the irony is that that's the whole point of SQL in the first place. As I am using SQLite for a Pascal/ Lazarus project if I change to MySQL I'll have to learn all over again how those two interact. I did try: $res = $db->query('SELECT * FROM times where timings > time('now') '); and many variations on that theme (time ('%h:%i') and so on) which I think is what you alluded to in your last sentence. The SQLite manual jumps from beginner to ultra-expert, with a lot missing in between. Thanks anyway. Derek Link to comment Share on other sites More sharing options...
Derek2020 Posted April 29, 2020 Author Share Posted April 29, 2020 Ingolme, thank you- you're a star. Your comments pushed me in the right direction. I tried using a SQL generator on a 3rd party SQLite manager and it worked as I expected, so I realised it wasn't a fault with my SQL by PHP in the end. $res = $db->query("SELECT * FROM times WHERE timings < time('now')" ); works fine now. Thank you ever so much. Like anything, when you resolve one problem it helps you unlock many more. Now I know how to slightly modify W3's tutorials to take account of SQlite's ways of doing things. Many thanks again. Derek Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now