Jump to content
Sign in to follow this  
Derek2020

A PHP or SQL problem (using SQLite)

Recommended Posts

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 by Derek2020

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...