Re: [PHP] SQL question
Kim Madsen wrote: But Skip, as the others say, use a date class, since you're passing a php var on to the SQL anyway, then you could determine the exact days from start to end of donation. Combine this with to_days and you have your solution Yes, this sounds like the best way to go. Thanks everyone! Skip -- Skip Evans PenguinSites.com, LLC 503 S Baldwin St, #1 Madison WI 53703 608.250.2720 http://penguinsites.com Those of you who believe in telekinesis, raise my hand. -- Kurt Vonnegut -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL question
At 9:54 PM -0600 1/25/10, Skip Evans wrote:
Hey all,
I have an SQL query that's stumping me.
I have two date variables, $start and $end that are in mm/dd/
format and two database fields, start_date and no_donations. The
start date is mm/dd/ format and no_donations is an integer that
represents the number of months from start_date that donations will
be made.
So if start date is say 02/01/2010 and no_dations is 4 then
donations will be made four times from the start date for four
months.
What I need to do is come up with a query that will determine if the
start_date + no_donations falls within $start and $end.
But I'm pretty stumped. How can I convert start_date + no_donations
in the database to the date when the last donation will take place
so I'll now if the donations fall between $start and $end?
Any suggestions would be very help and appreciated,
Skip
--
Skip Evans
Skip:
Here's a snip-it of code from one of my projects:
$qry = SELECT SUM(amount) AS subtotal, COUNT(*) AS num
FROM transaction
WHERE product_type = 'video'
AND UNIX_TIMESTAMP(transtime) . strtotime($startd) .
AND UNIX_TIMESTAMP(transtime) . strtotime($endd) .
AND is_charged = 1
AND notes = 'Approved'
AND is_refunded = 0
AND transnum NOT LIKE 'TEST-PNREF'
AND product_id LIKE '$key' ;
$db2-select($qry);
while ($db2-readrow())
{
$rev = $db2-data[subtotal]; // this is the total amount
collected for the sale
$num = $db2-data[num]; // this is the number
of this type of sale
}
Clearly, you don't need everything there, but the timestamp notation
will give you better insight into how to use dates in your query.
Cheers,
tedd
--
---
http://sperling.com http://ancientstones.com http://earthstones.com
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL question
Michael A. Peters wrote on 26/01/2010 06:04: I use seconds from epoch in the database simply because it works so well with the php date() function. If you need something where Julian day really is better, I assume it isn't that hard to convert between posix and julian day, though it seems odd to me that it isn't part of the date() function. It probably should be. When I do date comparisons in MySQL I use the to_days() function. What I need to do is come up with a query that will determine if the start_date + no_donations falls within $start and $end. In the given example one could determine that a month is always 30 days and then say to_days(start_date)+(no_donations*30) to_days(end). This would however be a very loose method. You could go for finding the number of days in the current month and substract that (10th. = 30-10), play with MySQLs left() function But Skip, as the others say, use a date class, since you're passing a php var on to the SQL anyway, then you could determine the exact days from start to end of donation. Combine this with to_days and you have your solution -- Kind regards Kim Emax - masterminds.dk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL question
On Mon, Jan 25, 2010 at 09:54:40PM -0600, Skip Evans wrote: Hey all, I have an SQL query that's stumping me. I have two date variables, $start and $end that are in mm/dd/ format and two database fields, start_date and no_donations. The start date is mm/dd/ format and no_donations is an integer that represents the number of months from start_date that donations will be made. So if start date is say 02/01/2010 and no_dations is 4 then donations will be made four times from the start date for four months. What I need to do is come up with a query that will determine if the start_date + no_donations falls within $start and $end. But I'm pretty stumped. How can I convert start_date + no_donations in the database to the date when the last donation will take place so I'll now if the donations fall between $start and $end? Any suggestions would be very help and appreciated, If there's a way to do this in SQL itself, I don't know what it is. But in my opinion, you need a date class which can do date comparisons. (If you end up programming one yourself, save yourself some time and convert all dates to Julian day numbers internally. This saves massive amounts of computation in determining intervals and durations. Typically, coders try to store dates in unix timestamps internally, and then add 86400 seconds for every day to calculate intervals and such. This is often inaccurate. Julian days are far more accurate.) Paul -- Paul M. Foster -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL question
Paul M Foster wrote: Typically, coders try to store dates in unix timestamps internally, and then add 86400 seconds for every day to calculate intervals and such. This is often inaccurate. Julian days are far more accurate.) Paul I use seconds from epoch in the database simply because it works so well with the php date() function. If you need something where Julian day really is better, I assume it isn't that hard to convert between posix and julian day, though it seems odd to me that it isn't part of the date() function. It probably should be. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL question
Michael A. Peters wrote: If you need something where Julian day really is better, I assume it isn't that hard to convert between posix and julian day, though it seems odd to me that it isn't part of the date() function. It probably should be. Looks like unixtojd() and jdtounix() do it. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] sql question
if i understand you question... SELECT columns FROM table WHERE (condition #1) AND (condition #2); You can add many conditions. There are many ways to do this. This one is probably the most simple for only two conditions. Depending on your needs you also may need to create a subquery. SELECT colummns FROM table WHERE (condition#1) AND ( SELECT columns FROM table WHERE (condition#1a); The second is more useful in joins or when joining a table to itself. William Stokes wrote: Hello, This is not a php but sql question. My apologies but I don't know any good sql forums and I know that someone here might be able to answer. OK so the question is. How to make a SELECT query with WHERE definition that requires 2 conditions to be satisfied so that the row gets selected? MySQL manual sayswhere_definition consists of the keyword WHERE followed by an expression that indicates the condition or conditions that rows must satisfy to be selected I just don't know how to write the query Thanks a lot -Will -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] sql question
On Thu, 7 Apr 2005, William Stokes wrote: Hello, This is not a php but sql question. My apologies but I don't know any good sql forums and I know that someone here might be able to answer. OK so the question is. How to make a SELECT query with WHERE definition that requires 2 conditions to be satisfied so that the row gets selected? MySQL manual sayswhere_definition consists of the keyword WHERE followed by an expression that indicates the condition or conditions that rows must satisfy to be selected I just don't know how to write the query SELECT cola, colb FROM table tabA WHERE cola = 'xyz' AND colb = 123 Also, try here for more... http://www.google.com/search?q=sql+tutorial -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] sql question
William Stokes wrote: Hello, This is not a php but sql question. My apologies but I don't know any good sql forums and I know that someone here might be able to answer. OK so the question is. How to make a SELECT query with WHERE definition that requires 2 conditions to be satisfied so that the row gets selected? MySQL manual sayswhere_definition consists of the keyword WHERE followed by an expression that indicates the condition or conditions that rows must satisfy to be selected I just don't know how to write the query http://lists.mysql.com/ What condition do you want your select to satisfy? All data? Data with a certain description? Data older than a certain date? Etc. Etc. Etc.? -- John C. Nichel ÜberGeek KegWorks.com 716.856.9675 [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] sql question
What about trying it first? Diana Castillo wrote: If I have a date in unix format in a numeric field in my table (Mysql) and I want to compare it to a date which is in unixformat also, will the query work like this? SELECT from tarifs where unix_from_date=.$dateinunixformat -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Question: Search Records between two dates.
select * from table_name where (start_date ='2002-10-01' and start_date ='2002-12-10') or (end_date ='2002-10-01' and end_date ='2002-12-10') At 01:08 11.12.2002, [-^-!-%- said: [snip] Hello everyone, I need to get a recordset between two dates, and am having a little trouble. I was wondering if someone could me make it more efficient. The goal: Each record has a start_date and end_date field. I want to be able to extract records that has the start_date and/or end_date, within the specified date range. What I have: select * from table_name where start_date ='2002-10-01' and end_date ='2002-12-10' ... This works, but is somewhat inconsistent. Example: 1) the query will exclude a record that has an end_date later then 12/10, but a start_date later then 10/01. *** I need it to include that*** 2) The query also list records with a january 2003 end_date. i.e. end_date=2003-01-15. Strange. I've read about the mysql BETWEEN and DATE DIFF function, but don't fully get it. I'm hoping that someone can help clarifying it. Is there a better way to do this? Am I missing something? Please help. -john =P e p i e D e s i g n s www.pepiedesigns.com Providing Solutions That Increase Productivity Web Developement. Database. Hosting. Multimedia. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php [snip] -- O Ernest E. Vogelsinger (\)ICQ #13394035 ^ http://www.vogelsinger.at/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] SQL Question
How do I sort out data from mysql_fetch_row() or mysql_fetch_array? For example say I wanted to list all my users alphabetically. How would I do this? You would sort in your SQL statement, not PHP. SELECT * FROM my table ORDER BY field_name; Or say I had a form and I wanted to present the content to the user *AFTER* they have filled it out so that they can check for mistakes. Mysql_fetch_row() returns all the rows in a field Why would the data be in the mysql database? You would simply pass the field values to the next page, where the user would verify/validate the data. Then, you'd write to the database. -Original Message- From: Sebastian A. [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 28, 2002 8:35 AM To: PHP General List (PHP.NET) Subject: [PHP] SQL Question How do I sort out data from mysql_fetch_row() or mysql_fetch_array? For example say I wanted to list all my users alphabetically. How would I do this? Or say I had a form and I wanted to present the content to the user *AFTER* they have filled it out so that they can check for mistakes. Mysql_fetch_row() returns all the rows in a field, so I would guess that I have to sort using PHP. Can anyone provide me with an idea as to how I could accomplish this? Thanks -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Question
On Thursday 28 March 2002 22:34, Sebastian A. wrote: How do I sort out data from mysql_fetch_row() or mysql_fetch_array? For example say I wanted to list all my users alphabetically. How would I do this? Or say I had a form and I wanted to present the content to the user *AFTER* they have filled it out so that they can check for mistakes. Mysql_fetch_row() returns all the rows in a field, so I would guess that I have to sort using PHP. Can anyone provide me with an idea as to how I could accomplish this? 1) This type of question should best be posted to the php-db list. 2) Sorting should best be done from mysql using the ORDER BY clause: SELECT this, that, the, other FROM table ORDER BY that 3) If you insist on sorting in PHP then read all the rows from your query into an array and use one of the array sort functions -- asort() etc. -- Jason Wong - Gremlins Associates - www.gremlins.com.hk /* Things are more like they used to be than they are now. */ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] SQL Question
What do you mean ORDER BY 'S' ?? yes, it can be a column or combination of columns and data. What are you trying to DO? -Original Message- From: Sebastian A. [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 28, 2002 10:30 AM To: PHP General List (PHP.NET) Subject: RE: [PHP] SQL Question When you say ORDER BY that, is it also possible to do that via letters such as ORDER BY 'S', because from what I understand, ORDER BY has to be a column. -Original Message- From: Jason Wong [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 28, 2002 3:49 PM To: [EMAIL PROTECTED] Subject: Re: [PHP] SQL Question On Thursday 28 March 2002 22:34, Sebastian A. wrote: How do I sort out data from mysql_fetch_row() or mysql_fetch_array? For example say I wanted to list all my users alphabetically. How would I do this? Or say I had a form and I wanted to present the content to the user *AFTER* they have filled it out so that they can check for mistakes. Mysql_fetch_row() returns all the rows in a field, so I would guess that I have to sort using PHP. Can anyone provide me with an idea as to how I could accomplish this? 1) This type of question should best be posted to the php-db list. 2) Sorting should best be done from mysql using the ORDER BY clause: SELECT this, that, the, other FROM table ORDER BY that 3) If you insist on sorting in PHP then read all the rows from your query into an array and use one of the array sort functions -- asort() etc. -- Jason Wong - Gremlins Associates - www.gremlins.com.hk /* Things are more like they used to be than they are now. */ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] SQL Question
You could do it this way WHERE column LIKE 's%' ORDER BY column Now you'll get all rows sorted and where column is staring with an 's' or 'S' - On 28-03-02 at 17:30 Sebastian A. wrote: - When you say ORDER BY that, is it also possible to do that via letters such as ORDER BY 'S', because from what I understand, ORDER BY has to be a column. -Original Message- From: Jason Wong [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 28, 2002 3:49 PM To: [EMAIL PROTECTED] Subject: Re: [PHP] SQL Question On Thursday 28 March 2002 22:34, Sebastian A. wrote: How do I sort out data from mysql_fetch_row() or mysql_fetch_array? For example say I wanted to list all my users alphabetically. How would I do this? Or say I had a form and I wanted to present the content to the user *AFTER* they have filled it out so that they can check for mistakes. Mysql_fetch_row() returns all the rows in a field, so I would guess that I have to sort using PHP. Can anyone provide me with an idea as to how I could accomplish this? 1) This type of question should best be posted to the php-db list. 2) Sorting should best be done from mysql using the ORDER BY clause: SELECT this, that, the, other FROM table ORDER BY that 3) If you insist on sorting in PHP then read all the rows from your query into an array and use one of the array sort functions -- asort() etc. -- Jason Wong - Gremlins Associates - www.gremlins.com.hk /* Things are more like they used to be than they are now. */ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Question
On Friday 29 March 2002 00:30, Sebastian A. wrote: When you say ORDER BY that, is it also possible to do that via letters such as ORDER BY 'S', because from what I understand, ORDER BY has to be a column. In my example, 'this', 'that', 'the', 'other' are all columns. So yes, ORDER BY , where is a column. -- Jason Wong - Gremlins Associates - www.gremlins.com.hk /* To refuse praise is to seek praise twice. */ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] SQL Question
On Thu, 28 Mar 2002, Sebastian A. wrote: When you say ORDER BY that, is it also possible to do that via letters such as ORDER BY 'S', because from what I understand, ORDER BY has to be a column. The object of order by should be something that changes for each row, or else the statement is meaningless. If you order by s, you aren't really doing anything, because the letter s is always the letter s, regardless of the data in the database. miguel -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] sql question
select * from tbl_lit where lit_source like 'c%'; Check out http://www.sqlcourse.com/ -Original Message- From: Wolf-Dietrich von Loeffelholz [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 15, 2002 12:09 PM To: [EMAIL PROTECTED] Subject: [PHP] sql question i want that a select query display me all words beginning with an a .. like select * from tbl_lit where lit_source = 'c*' .. thnx bSue -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] sql question
Hi do you care about case sensitivity? Nathan Cassano [EMAIL PROTECTED] wrote: select * from tbl_lit where lit_source like 'c%'; Check out http://www.sqlcourse.com/ -Original Message- From: Wolf-Dietrich von Loeffelholz [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 15, 2002 12:09 PM To: [EMAIL PROTECTED] Subject: [PHP] sql question i want that a select query display me all words beginning with an a .. like select * from tbl_lit where lit_source = 'c*' .. thnx bSue -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] Mehmet Erisen http://www.erisen.com - Do You Yahoo!? Send FREE video emails in Yahoo! Mail.
Re: [PHP] sql question
select * from tbl_lit where lit_source like c% Janet - Original Message - From: Wolf-Dietrich von Loeffelholz [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, January 15, 2002 12:09 PM Subject: [PHP] sql question i want that a select query display me all words beginning with an a .. like select * from tbl_lit where lit_source = 'c*' .. thnx bSue -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] sql question
Good day, This isn't a PHP question, but hey... This is dependant on the database platform that you're using. The suggested statement would work on MySQL. On Informix, for example, you would say 'where lit_source matches c* '. You should consult your database manual for more information. Darren Gamble Planner, Regional Services Shaw Cablesystems GP 630 - 3rd Avenue SW Calgary, Alberta, Canada T2P 4L4 (403) 781-4948 -Original Message- From: Janet Valade [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 15, 2002 3:04 PM To: Wolf-Dietrich von Loeffelholz; [EMAIL PROTECTED] Subject: Re: [PHP] sql question select * from tbl_lit where lit_source like c% Janet - Original Message - From: Wolf-Dietrich von Loeffelholz [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, January 15, 2002 12:09 PM Subject: [PHP] sql question i want that a select query display me all words beginning with an a .. like select * from tbl_lit where lit_source = 'c*' .. thnx bSue -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] SQL question
"John LYC" [EMAIL PROTECTED] wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
hi all,
does mysql support this?
select * from tablename where id in (select id from table2 where cond)
thanks
No. MySQL does not support so-called sub-selects. Here's an easy workaround
in PHP:
$result = mysql_query("SELECT id FROM table2 WHERE cond");
$idlist = array();
while($row = mysql_fetch_assoc($result)) {
$idlist[] = $row[id];
}
$result = mysql_query("SELECT id FROM table1 WHERE id IN(" . implode(", ",
$idlist) . ")");
Making sure $idlist actually contains at least one item (if it doesn't the
second query will fail) is left as an exercise to the reader.
--
- Daniel Grace http://dewin.oldbattery.com/
"Space may be the final frontier but its made in a Hollywood basement."
- Red Hot Chili Peppers - Californication
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
