Re: [PHP-DB] Re: SELECT
On Mon, Oct 17, 2011 at 12:58 PM, Jim Giner jim.gi...@albanyhandball.com wrote: I would do it this way: Where $sel_d = (the day # you want) $sel_m = (the month # you want) The where clause would be: Where (start_month = $sel_m and start_day = $sel_d) and (end_month = $sel_m and end_day = $sel_d) Hmm, no this won't work. Check this: start_month=10 start_day=15 end_month=1 end_day=1 sel_day=23 sel_month=12 in this case, sel_month IS greater than start_month and sel_day IS greater than start_day, HOWEVER sel_month is ALSO greater than end_month and sel_day is ALSO greater than end_day in this case. (wrap around to new year problem, wrap around to new month problem) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT
On Tue, Oct 18, 2011 at 5:36 AM, Ford, Mike m.f...@leedsmet.ac.uk wrote: -Original Message- From: Ron Piggott [mailto:ron.pigg...@actsministries.org] Sent: 17 October 2011 18:38 I need help creating a mySQL query that will select the correct introduction message for a website I am making. The way I have designed the table I can’t wrap my mind around the SELECT query that will deal with the day # of the month. The part of the SELECT syntax I am struggling with is when the introduction message is to change mid month. The reason I am struggling with this is because I haven’t used ‘DATE’ for the column type. The reason I didn’t use ‘DATE’ is because the same message will be displayed year after year, depending on the date range. What I am storing in the table is the start month # (1 to 12) and day # (1 to 31) and then the finishing month # (1 to 12) and the finishing day # (1 to 31) This is a little bit of a tricky one, as you have to consider both start_month and end_month as special cases - so you need a three-part conditional, for the start month, the end month, and the months in between. Something like this: SELECT * FROM `introduction_messages` WHERE (month`start_month` AND month`end_month`) OR (month=`start_month AND day=`start_day`) OR (month=`end_month` AND day=`end_day`); This still suffers from the problem in Jim's offer -- wrap of year and wrap of month This might be best handled in a stored procedure, converting the values stored in the table to dates to do the comparison with in the where clause. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT
On Thu, Oct 20, 2011 at 3:36 PM, tamouse mailing lists tamouse.li...@gmail.com wrote: On Tue, Oct 18, 2011 at 5:36 AM, Ford, Mike m.f...@leedsmet.ac.uk wrote: -Original Message- From: Ron Piggott [mailto:ron.pigg...@actsministries.org] Sent: 17 October 2011 18:38 I need help creating a mySQL query that will select the correct introduction message for a website I am making. The way I have designed the table I can’t wrap my mind around the SELECT query that will deal with the day # of the month. The part of the SELECT syntax I am struggling with is when the introduction message is to change mid month. The reason I am struggling with this is because I haven’t used ‘DATE’ for the column type. The reason I didn’t use ‘DATE’ is because the same message will be displayed year after year, depending on the date range. What I am storing in the table is the start month # (1 to 12) and day # (1 to 31) and then the finishing month # (1 to 12) and the finishing day # (1 to 31) This is a little bit of a tricky one, as you have to consider both start_month and end_month as special cases - so you need a three-part conditional, for the start month, the end month, and the months in between. Something like this: SELECT * FROM `introduction_messages` WHERE (month`start_month` AND month`end_month`) OR (month=`start_month AND day=`start_day`) OR (month=`end_month` AND day=`end_day`); This still suffers from the problem in Jim's offer -- wrap of year and wrap of month This might be best handled in a stored procedure, converting the values stored in the table to dates to do the comparison with in the where clause. In thinking further on this, the OP might consider this problem as well -- it is going to be difficult to determine the correct response if all that is stored is the start and ending month and day of month in the case where the desired time stretch wraps over to the new year. When your start month is 12 and your end month is 1, what do you expect to happen? It can't generally be solved by using current year for the start and current year + 1 for the end. For example, you may want to have something start at current year, month=6 and end as next year, month=8, so simply checking if end month start month won't give you the ability to discern if you've wrapped the year. (I realize this may not be the OP's case, but it is still an issue if seeking a general solution.) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] PHP + SQLite - Issues with update
Hello,
I am running into some issues trying to update a column in a table in a
SQLite database using PHP on Windows and was hoping could get some help on
this mailer.
Here is the create and insert statement -
CREATE TABLE SERVER (
IPAddress varchar(100) not null unique primary key,
Token varchar(100) unique
);
INSERT INTO SERVER (IPAddress, Token) VALUES
('192.168.1.100', '');
I am trying to update the Token field using the following code -
[snip]
$db = new SQLiteDatabase('db/reg.s3db');
$intoken = uniqid();
$db-query(update SERVER set Token = '$intoken');
$res = $db-query(select * from SERVER);
$row = $res-fecth();
$outtoken = $row['Token'];
echo $outtoken;
[snip]
After the script exits successfully I inspect the value in the Token column
and its empty. But the echo statement in the snippet above prints the proper
value.
Interestingly, if I error out the script with a syntax error or some such
after the update but before it exits, the value shows up in the Token
column.
Any idea what is happening here and I need to do here. Seems like there is
some sort of flush that needs to happen which happens only if the script
errors out.
Any help is appreciated.
Thanks,
Prashant.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: PHP + SQLite - Issues with update
Prashant Prabhudesai wrote:
Hello,
I am running into some issues trying to update a column in a table in a
SQLite database using PHP on Windows and was hoping could get some help on
this mailer.
Here is the create and insert statement -
CREATE TABLE SERVER (
IPAddress varchar(100) not null unique primary key,
Token varchar(100) unique
);
INSERT INTO SERVER (IPAddress, Token) VALUES
('192.168.1.100', '');
I am trying to update the Token field using the following code -
[snip]
$db = new SQLiteDatabase('db/reg.s3db');
$intoken = uniqid();
$db-query(update SERVER set Token = '$intoken');
$res = $db-query(select * from SERVER);
$row = $res-fecth();
$outtoken = $row['Token'];
echo $outtoken;
[snip]
After the script exits successfully I inspect the value in the Token
column and its empty. But the echo statement in the snippet above prints
the proper value.
Interestingly, if I error out the script with a syntax error or some such
after the update but before it exits, the value shows up in the Token
column.
Any idea what is happening here and I need to do here. Seems like there is
some sort of flush that needs to happen which happens only if the script
errors out.
Any help is appreciated.
Thanks,
Prashant.
Spelling matters :-)
$row = $res-fecth(); Wrong
$row = $res-fetch(); Right
Cheers
--
David Robley
Caterpillar: Scratching post.
Today is Prickle-Prickle, the 2nd day of The Aftermath in the YOLD 3177.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
