[PHP-DB] Re: paginating : optimising queries
no one can help me outta here ? On Mon, 21 Mar 2005 01:58:10 +0100, Zouari Fourat [EMAIL PROTECTED] wrote: Hello, i made my own paginating class, it permit me to paginate over selected data and let me do good presentation (page 1, page 2 ...) i use adodb for db related functions, my application should show in every page this set of informations : Total results : Z, Total page : Y, You are on the page number X. To show thos informations, i must do a count on all the table, example : the query built from the form submitted by the user is : select * from table where column=value and column like 'value%' to paginate that query, i'll add some LIMIT or LIMIT OFFSET for pgsql (it's adodb frontend) so i'll get only the data for one page ! i use this query : select count(*) from table with that global count i can calculate how much pages i have by dividing count on the number of rows to show per page. with this method, the server will suffer for big tables... it's a method that i've made without consulting what people do, can you do this with only ONE query so i can optimise ? thank you for your help -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Re: paginating : optimising queries
If its a big hit, are you sure that you have the proper indexes on the table. Another thing to consider is that if there is not a lot of activity on that table, you could place the total in a session object and revalidate that total every 10 queries or something to avoid querying the same table over and over. Or use another table to hold the total number of entries in that table so that you are querying a small table without a lot of load on it. You could fill this table via a CRON or trigger (if available) bastien From: Zouari Fourat [EMAIL PROTECTED] Reply-To: Zouari Fourat [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] Re: paginating : optimising queries Date: Tue, 22 Mar 2005 17:02:57 +0100 no one can help me outta here ? On Mon, 21 Mar 2005 01:58:10 +0100, Zouari Fourat [EMAIL PROTECTED] wrote: Hello, i made my own paginating class, it permit me to paginate over selected data and let me do good presentation (page 1, page 2 ...) i use adodb for db related functions, my application should show in every page this set of informations : Total results : Z, Total page : Y, You are on the page number X. To show thos informations, i must do a count on all the table, example : the query built from the form submitted by the user is : select * from table where column=value and column like 'value%' to paginate that query, i'll add some LIMIT or LIMIT OFFSET for pgsql (it's adodb frontend) so i'll get only the data for one page ! i use this query : select count(*) from table with that global count i can calculate how much pages i have by dividing count on the number of rows to show per page. with this method, the server will suffer for big tables... it's a method that i've made without consulting what people do, can you do this with only ONE query so i can optimise ? thank you for your help -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Re: paginating : optimising queries
I've run in to this situation before. And it turns out that the extra pre-count query is not worth it. So the steps become: 1) Do your main query and fetch the results. 2) Use the count of results obtained in step 1. 3) Calculate pagination numbers. 4) Use array_slice to limit the results to a page window calculated in step 3. Hope this helps. - Aman Patel, Sys Admin / Database / Web Developer, International Outreach x4076 -Original Message- From: Zouari Fourat [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 22, 2005 10:03 AM To: php-db@lists.php.net Subject: [PHP-DB] Re: paginating : optimising queries no one can help me outta here ? On Mon, 21 Mar 2005 01:58:10 +0100, Zouari Fourat [EMAIL PROTECTED] wrote: Hello, i made my own paginating class, it permit me to paginate over selected data and let me do good presentation (page 1, page 2 ...) i use adodb for db related functions, my application should show in every page this set of informations : Total results : Z, Total page : Y, You are on the page number X. To show thos informations, i must do a count on all the table, example : the query built from the form submitted by the user is : select * from table where column=value and column like 'value%' to paginate that query, i'll add some LIMIT or LIMIT OFFSET for pgsql (it's adodb frontend) so i'll get only the data for one page ! i use this query : select count(*) from table with that global count i can calculate how much pages i have by dividing count on the number of rows to show per page. with this method, the server will suffer for big tables... it's a method that i've made without consulting what people do, can you do this with only ONE query so i can optimise ? thank you for your help -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Re: paginating : optimising queries
Fetching 10 rows is much faster than 1000 ! Fourat your code is optimized just keep it as it :) just keep your code away from adodb, pear db, and such abstraction if you want speed ! you don't need to talk about optimisation with 2 queries. Regards, Hatem --- Patel, Aman [EMAIL PROTECTED] wrote: I've run in to this situation before. And it turns out that the extra pre-count query is not worth it. So the steps become: 1) Do your main query and fetch the results. 2) Use the count of results obtained in step 1. 3) Calculate pagination numbers. 4) Use array_slice to limit the results to a page window calculated in step 3. Hope this helps. - Aman Patel, Sys Admin / Database / Web Developer, International Outreach x4076 -Original Message- From: Zouari Fourat [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 22, 2005 10:03 AM To: php-db@lists.php.net Subject: [PHP-DB] Re: paginating : optimising queries no one can help me outta here ? On Mon, 21 Mar 2005 01:58:10 +0100, Zouari Fourat [EMAIL PROTECTED] wrote: Hello, i made my own paginating class, it permit me to paginate over selected data and let me do good presentation (page 1, page 2 ...) i use adodb for db related functions, my application should show in every page this set of informations : Total results : Z, Total page : Y, You are on the page number X. To show thos informations, i must do a count on all the table, example : the query built from the form submitted by the user is : select * from table where column=value and column like 'value%' to paginate that query, i'll add some LIMIT or LIMIT OFFSET for pgsql (it's adodb frontend) so i'll get only the data for one page ! i use this query : select count(*) from table with that global count i can calculate how much pages i have by dividing count on the number of rows to show per page. with this method, the server will suffer for big tables... it's a method that i've made without consulting what people do, can you do this with only ONE query so i can optimise ? thank you for your help -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Découvrez le nouveau Yahoo! Mail : 250 Mo d'espace de stockage pour vos mails ! Créez votre Yahoo! Mail sur http://fr.mail.yahoo.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: paginating : optimising queries
Hatem wrote: Fetching 10 rows is much faster than 1000 ! Fourat your code is optimized just keep it as it :) just keep your code away from adodb, pear db, and such abstraction if you want speed ! you don't need to talk about optimisation with 2 queries. Regards, Hatem Depends on the DB, in many cases the times are so similar as to not be worthwhile - but yes, I agree - limits are definitely worthwhile. Run the query without the limit, this gives you the count - don't actually fetch the rows. Now run the same query, with the limit. If your database is worth anything (most any is), it has this query cached and it takes negligible extra time, and you don't have to spend time 'skipping' ahead X rows. If your database interface functions support 'skipping' ahead - use that instead. Obviously, for page 1 of a paginated list, this performs worse than just running the single query. But if you get to page 99, you'll likely find this is faster. Feel free to do your own tests, many factors can change all of these findings, and it's best to match them to suit your own scenario. cheers, -- - Martin Norland, Sys Admin / Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: paginating : optimising queries
Martin Norland wrote: Hatem wrote: Fetching 10 rows is much faster than 1000 ! Fourat your code is optimized just keep it as it :) just keep your code away from adodb, pear db, and such abstraction if you want speed ! you don't need to talk about optimisation with 2 queries. Regards, Hatem Depends on the DB, in many cases the times are so similar as to not be worthwhile - but yes, I agree - limits are definitely worthwhile. Don't know what you are using ;) Transferring 1000 records is always going to take time, when you only need 10 to be displayed. Run the query without the limit, this gives you the count - don't actually fetch the rows. Now run the same query, with the limit. If your database is worth anything (most any is), it has this query cached and it takes negligible extra time, and you don't have to spend time 'skipping' ahead X rows. If your database interface functions support 'skipping' ahead - use that instead. The trick with any transactional database is to maintain the most used counts in a second table, and manage those counts with triggers, so you only need a single record read to access them. The counts will always be valid for your view of the database then. Obviously, for page 1 of a paginated list, this performs worse than just running the single query. But if you get to page 99, you'll likely find this is faster. Feel free to do your own tests, many factors can change all of these findings, and it's best to match them to suit your own scenario. The ADOdb pager only needs to know how many pages to indicate in the navigate bar, and how many records to download. In theory this can be very fast, and only slows down where a database engine does not support a simple limited read. pgsql driver in ADOdb supports LIMIT so it's only the calculation of COUNT(*) that needs replacing with a faster pre-calculated count. -- Lester Caine - L.S.Caine Electronic Services -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: paginating : optimising queries
Lester Caine wrote: [snip] Don't know what you are using ;) Transferring 1000 records is always going to take time, when you only need 10 to be displayed. [snip] I wasn't referring to transferring the record, only running the query. [snip] Run the query without the limit, this gives you the count - don't actually fetch the rows. Now run the same query, with the limit. If your database is worth anything (most any is), it has this query cached and it takes negligible extra time, and you don't have to spend time 'skipping' ahead X rows. If your database interface functions support 'skipping' ahead - use that instead. The trick with any transactional database is to maintain the most used counts in a second table, and manage those counts with triggers, so you only need a single record read to access them. The counts will always be valid for your view of the database then. [snip] This only works for known queries - anything that is filtered or otherwise limited won't benefit from this - but you still need to spend time maintaining the count. I'm speaking in a general, global, automated statement creation sense - you can't just wrap a count(*) around any given sql query - and in many cases, even when you can - it still ends up computing just as much information, so either way it calculates all the rows that would be returned. [snip] Obviously, for page 1 of a paginated list, this performs worse than just running the single query. But if you get to page 99, you'll likely find this is faster. Feel free to do your own tests, many factors can change all of these findings, and it's best to match them to suit your own scenario. The ADOdb pager only needs to know how many pages to indicate in the navigate bar, and how many records to download. In theory this can be very fast, and only slows down where a database engine does not support a simple limited read. pgsql driver in ADOdb supports LIMIT so it's only the calculation of COUNT(*) that needs replacing with a faster pre-calculated count. [snip] I've been talking about using LIMIT the whole time, I'm not sure where you're going with this - I was merely further explaining what was likely obvious anyway. Naturally when you pull in * ordered by an indexed column you gain speed with limits, but when you start putting conditions on things, the queries have to run across (many|all) the rows. I try to speak in general terms on this list, not everyone is using ADOdb or mysql (though I often slip into speaking about mysql since people either use it, are familiar with it, or at the very least - any standard sql engine will support the same features) My statements were always related to using pagination in a general sense - e.g. a class you create for pagination, that can paginate any manner of query cleanly. If you're dealing solely with hard-coded / hand-crafted queries, you can obviously build your pagination to fit. cheers, -- - Martin Norland, Sys Admin / Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: paginating : optimising queries
On a side note, who's sick of seeing the word optimizing misspelled in the title? :) Ah, the things we do to keep a thread clean. -- - Martin Norland, Sys Admin / Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: paginating : optimising queries
On a side note, who's sick of seeing the word optimizing misspelled in the title? :) Ah, the things we do to keep a thread clean. -- - Martin Norland, Sys Admin / Database / Web Developer, International Well, of course, _optimise_ is the British spelling of _optimize_ . Afterall, this __is__ an international list ;- David
Re: [PHP-DB] Re: paginating : optimising queries
[EMAIL PROTECTED] wrote: On a side note, who's sick of seeing the word optimizing misspelled in the title? :) Ah, the things we do to keep a thread clean. [snip] Well, of course, _optimise_ is the British spelling of _optimize_ . Afterall, this __is__ an international list ;- David My apologies to all, 'dict' tricked me - it has 'optimize', 'optimise', 'optimizing', but not 'optimising' - and I foolishly only checked the -ing conjugated tense instead of the root! (I do try a cursory check before posting such nonesense, honest!) Now back to your regularly scheduled international discussion... cheers, -- - Martin Norland, Sys Admin / Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Urgent DATA help needed
Hi there everyone, My client suddenly dropped on me that they need to be able to search by date, IE: the past 1 day, 3 days, 5 days or 7 days. In the DB, the date format is: 2001-07-05 (For example) but I have no clue how to count the days in between to know how many days I am counting. Say they have something at the end of feb and it runs into march, I have no clue how to work this out. Could anyone show me an example of how to do this? So to take 2001-07-05 and add 3 days to it? So if they choose 3 days, it would have to be CURRENT DATE + 2 I guess, that way you would see all dates from CURRENT DATE OR date = '2001-07-06' OR date = '2001-07-07'. Any help really would be appreciated. Chris
RE: [PHP-DB] Re: paginating : optimising queries
nonesense?? Sorry, couldn't resist, Martin. ;) This isn't a spelling bee anyway. I don't think it actually matters to anybody. I'll take solid programming advice with a few spelling errors over a shot in the dark with perfect spelling any day. -Original Message- From: Martin Norland [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 22, 2005 2:36 PM To: php-db@lists.php.net Subject: Re: [PHP-DB] Re: paginating : optimising queries [EMAIL PROTECTED] wrote: On a side note, who's sick of seeing the word optimizing misspelled in the title? :) Ah, the things we do to keep a thread clean. [snip] Well, of course, _optimise_ is the British spelling of _optimize_ . Afterall, this __is__ an international list ;- David My apologies to all, 'dict' tricked me - it has 'optimize', 'optimise', 'optimizing', but not 'optimising' - and I foolishly only checked the -ing conjugated tense instead of the root! (I do try a cursory check before posting such nonesense, honest!) Now back to your regularly scheduled international discussion... cheers, -- - Martin Norland, Sys Admin / Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Urgent DATA help needed
Can't you let the user tell you how many days back they want to count then just use that value in a query with the date calculation functions available in MySQL? You didn't specify your db type, but MySQL makes date calculations pretty easy. Check that out and see if it gives you something usable. HTH, Rich -Original Message- From: Chris Payne [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 22, 2005 2:39 PM To: php-db@lists.php.net Subject: [PHP-DB] Urgent DATA help needed Hi there everyone, My client suddenly dropped on me that they need to be able to search by date, IE: the past 1 day, 3 days, 5 days or 7 days. In the DB, the date format is: 2001-07-05 (For example) but I have no clue how to count the days in between to know how many days I am counting. Say they have something at the end of feb and it runs into march, I have no clue how to work this out. Could anyone show me an example of how to do this? So to take 2001-07-05 and add 3 days to it? So if they choose 3 days, it would have to be CURRENT DATE + 2 I guess, that way you would see all dates from CURRENT DATE OR date = '2001-07-06' OR date = '2001-07-07'. Any help really would be appreciated. Chris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: paginating : optimising queries
Hutchins, Richard wrote: nonesense?? Sorry, couldn't resist, Martin. ;) This isn't a spelling bee anyway. I don't think it actually matters to anybody. I'll take solid programming advice with a few spelling errors over a shot in the dark with perfect spelling any day. Elbonian spelling. (tribute/apologies to Scott Adams) cheers, -- - Martin Norland, Sys Admin / Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: paginating : optimising queries
[EMAIL PROTECTED] wrote: On a side note, who's sick of seeing the word optimizing misspelled in the title? :) Ah, the things we do to keep a thread clean. [snip] Well, of course, _optimise_ is the British spelling of _optimize_ . Afterall, this __is__ an international list ;- David My apologies to all, 'dict' tricked me - it has 'optimize', 'optimise', 'optimizing', but not 'optimising' - and I foolishly only checked the -ing conjugated tense instead of the root! (I do try a cursory check before posting such nonesense, honest!) Now back to your regularly scheduled international discussion... Martin, No apologies necessary from my perspective. You are a very active member of these php lists, and you have helped a great many php users the world over. I'm afraid some cross cultural sensitization kicked in on my end from many years working in human services. David
Re: [PHP-DB] Urgent DATA help needed
Chris Payne wrote:
Could anyone show me an example of how to do this? So to take 2001-07-05
and add 3 days to it?
To use SQL to do this:
SELECT FROM_DAYS((TO_DAYS('2001-07-05') + 3));
The TO_DAYS and FROM_DAYS are nice functions to use when you are
fildling with dates as numbers (i.e. doing arithmatic operations to the
dates). There are also a bunch of other function dedicated to do math
operations to date and they are listed here:
http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: paginating : optimising queries
[EMAIL PROTECTED] wrote: [snip] My apologies to all, 'dict' tricked me - it has 'optimize', 'optimise', 'optimizing', but not 'optimising' - and I foolishly only checked the -ing conjugated tense instead of the root! (I do try a cursory check before posting such nonesense, honest!) Now back to your regularly scheduled international discussion... Martin, No apologies necessary from my perspective. You are a very active member of these php lists, and you have helped a great many php users the world over. I'm afraid some cross cultural sensitization kicked in on my end from many years working in human services. David Last post on this from me, honest! Next one will be helpful and totally on-target. Pose questions at will. --- Okay, now I think I just got an apology in response to my apology. We better stop this right here and now or we're all going to end up with cavities and false teeth, it's getting awfully sweet in here. :) On a quick final note - you were right IMO. There is entirely too little acknowledgement of anything international in the average mindset I see or come across. Look at character sets / encodings support in applications - I8LN - it's all only barely starting to come together these days, after how many years of computer usage? It took forever to break down the mindset that 'not everyone who uses a computer speaks/writes English'. (defensive statement: yes I know it (the correct British spelling) is still English - but it does open the wider concern.) cheers, -- - Martin Norland, Sys Admin / Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Content Management
So I was looking at PHP products the other day, and I realized there are a few areas of web development in which I'm utterly ignorant. The most important, right now, is Content Management. (i.e. You design a site so that the client can manage it after you leave, even if they don't know HTML, and preferably can even farm out sub-sections of the site to other team members). I've heard that PHP-Nuke and PostNuke are powerful and free (free is good here) but fairly complex. I've also seen good reviews of SubDreamer, less powerful than the *Nukes but also less complicated and easier for clients to understand. I'm helping to provide a community website for a condo, with mostly-static content (regulations, by-laws, forms, etc) and frequent-update content (committee events, board of directors' meeting minutes, newsletters, home tips, area news, etc), so my needs aren't very complicated. Anyone have any practical experience to share? Apologies if this has been rehearsed a thousand times before, I see discussions pop up in the archive but they never seem to come to a concensus so I'm going to risk asking again... -P -- Peter @ westergaard .ca A byte walks into a bar and orders a pint. Bartender asks him What's wrong? Byte says Parity error. Bartender nods and says Yeah, I thought you looked a bit off. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Newbie: phpmyadmin and searching Unix Timestamps
is there an easy way to search through unixtimestamps ? like Select * from SessionTable Where ConvertToMonthFunction(unixTimeStamp) = 'March' ? be great to use the Variable function within phpmyAdmin many thanks g -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Date problem again ;-)
Hi there everyone, OK I'm using the following in my query to display entries in the LAST 3 days: DATE_SUB(CURDATE(),INTERVAL 3 DAY) = ListingDate But it doesn't seem to affect the results, the date format in my DB column (ListingDate - datatype is Date) is 2000-00-00 as in year, month and date - is THAT the problem? That it needs to be an actual timestamp? If that is the case I'm not sure how to approach this because the data MUST be in the above format as it comes from a central DB every night. All I need to do is display the current date plus also the previous x amount of days in between. I'm using PHP 4 with MySQL 4.0.22 (Not the latest MySQL I know, sigh). Chris
[PHP-DB] dbase and Debian
Hi all, today i found that debian has removed the php4-dbase package from unstable/testing due maintainer request. This has brought me some problems with many programs at work, so I decided to create my own PHP4 package with dbase support, using apt-build. This mail is to ask, if there is any plan/project/intention to change the dbase code (that is non-free in debian policy) with other library like libxbase. I'll look if that can be done without much effort, so I can send the patch to PHP project, or if I release a php module called php-xbase separatly. Unfortunately, I can't convert the dbf files to SQL tables, so, i need dbase support in PHP :( Thx. Ricardo -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Newbie: phpmyadmin and searching Unix Timestamps
SELECT date_format(FROM_UNIXTIME(875996580),'%Y-%m-%d'); is what I used yesterday to get around this problem bastien From: Graham Anderson [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] Newbie: phpmyadmin and searching Unix Timestamps Date: Tue, 22 Mar 2005 12:10:15 -0800 is there an easy way to search through unixtimestamps ? like Select * from SessionTable Where ConvertToMonthFunction(unixTimeStamp) = 'March' ? be great to use the Variable function within phpmyAdmin many thanks g -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Newbie: phpmyadmin and searching Unix Timestamps
Graham Anderson wrote: is there an easy way to search through unixtimestamps ? like Select * from SessionTable Where ConvertToMonthFunction(unixTimeStamp) = 'March' ? be great to use the Variable function within phpmyAdmin many thanks g On Mysql (works on 4.0 atleast), you can use the FROM_UNIXTIME() function to convert a unix timestamp back to universal date/time format. From then on you can use any standard mysql date/time functions to compare the month. So your query becomes something like: SELECT * FROM SessionTable WHERE MONTH(from_unixtime(527115)) = 3; MONTH() returns a numeric month number from 1-12. See the mysql documentation on date and time functions here: http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html Hope this helps. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: paginating : optimising queries
My apologies to all, i've mispelled that word, because in frensh we say optimisation/optimiser ... there's no snoop dog's in zat :) as about the subject, i think am no going to change my method and i'll use ADOdb for some reasons (performance alse) : * my application should be db independant, so it can work on mysql or postgres without changing code. * ADOdb offers caching, i think it's the better way to get performance on this kind of queries (big tables) what do you think all ? did i choose the best solution ? thanks very much On Tue, 22 Mar 2005 15:03:51 -0600, Martin Norland [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: [snip] My apologies to all, 'dict' tricked me - it has 'optimize', 'optimise', 'optimizing', but not 'optimising' - and I foolishly only checked the -ing conjugated tense instead of the root! (I do try a cursory check before posting such nonesense, honest!) Now back to your regularly scheduled international discussion... Martin, No apologies necessary from my perspective. You are a very active member of these php lists, and you have helped a great many php users the world over. I'm afraid some cross cultural sensitization kicked in on my end from many years working in human services. David Last post on this from me, honest! Next one will be helpful and totally on-target. Pose questions at will. --- Okay, now I think I just got an apology in response to my apology. We better stop this right here and now or we're all going to end up with cavities and false teeth, it's getting awfully sweet in here. :) On a quick final note - you were right IMO. There is entirely too little acknowledgement of anything international in the average mindset I see or come across. Look at character sets / encodings support in applications - I8LN - it's all only barely starting to come together these days, after how many years of computer usage? It took forever to break down the mindset that 'not everyone who uses a computer speaks/writes English'. (defensive statement: yes I know it (the correct British spelling) is still English - but it does open the wider concern.) cheers, -- - Martin Norland, Sys Admin / Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Content Management
Peter, Unfortunately it's not a PHP solution, but I recently launched a site for a client and used the Macromedia Contribute product and Dreamweaver Templates to allow the user to maintain certain areas of the site. Now, before everybody else on the list blasts it, I'm not here touting Macromedia products or anything like that. This was a very simple solution that worked for the very simple requirements in this case. And my client was happy to spend the $159 on the Contribute license rather than pay me to make silly text and graphics updates to the site. Essentially, I worked with DW a little to find out what codes it inserted in the HTML to set up the templates. Then I worked on a set of templates for the different pages in the site without using DW. Once you see what Contribute is looking for, you can pretty much set the whole site up without DW, which is what I ended up doing. Now, Contribute isn't the absolute best product out there, but it allows my client who knows nothing about HTML or FTP or inserting links to go in and add/modify/delete content in the areas left open by the templates. It also lets him set up tables, insert images, links, and new pages without knowledge of HTML. Contribute sets up some additional folders that don't follow the same logic I would personally use, but they seem to work. Contribute does not have much in the way of hard-core content management like real collaboration or threaded discussions, or a built-in calendar, but it does a decent job of versioning the pages as they are changed. There is also a simple permissions set you can use to allow only certain people to access certain parts of the template. It's a low-end tool for very simple needs. If that's all you're looking for, it's something worth considering along with engines like *Nuke which might take more implementation time on your end (haven't used them so I don't know for sure) but don't cost anything. The content and maintenance activities you listed in your post are very similar to the content my client maintains with Contribute so I think the contexts are parallel. Just something for you to think about. Rich -Original Message- From: Peter Westergaard [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 22, 2005 4:06 PM To: php-db@lists.php.net Subject: [PHP-DB] Content Management So I was looking at PHP products the other day, and I realized there are a few areas of web development in which I'm utterly ignorant. The most important, right now, is Content Management. (i.e. You design a site so that the client can manage it after you leave, even if they don't know HTML, and preferably can even farm out sub-sections of the site to other team members). I've heard that PHP-Nuke and PostNuke are powerful and free (free is good here) but fairly complex. I've also seen good reviews of SubDreamer, less powerful than the *Nukes but also less complicated and easier for clients to understand. I'm helping to provide a community website for a condo, with mostly-static content (regulations, by-laws, forms, etc) and frequent-update content (committee events, board of directors' meeting minutes, newsletters, home tips, area news, etc), so my needs aren't very complicated. Anyone have any practical experience to share? Apologies if this has been rehearsed a thousand times before, I see discussions pop up in the archive but they never seem to come to a concensus so I'm going to risk asking again... -P -- Peter @ westergaard .ca A byte walks into a bar and orders a pint. Bartender asks him What's wrong? Byte says Parity error. Bartender nods and says Yeah, I thought you looked a bit off. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Newbie: phpmyadmin and searching Unix Timestamps
So how would you build the query without knowing the unix TimeStamp as '527115' is the current date ? I am trying to build the query so it dynamically know what to look for like SELECT * FROM SessionTable WHERE MONTH(from_unixtime(all available records)) = 'March'; is something like this possible ? many thanks g On Mysql (works on 4.0 atleast), you can use the FROM_UNIXTIME() function to convert a unix timestamp back to universal date/time format. From then on you can use any standard mysql date/time functions to compare the month. So your query becomes something like: SELECT * FROM SessionTable WHERE MONTH(from_unixtime(527115)) = 3; MONTH() returns a numeric month number from 1-12. See the mysql documentation on date and time functions here: http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html Hope this helps. On Mar 22, 2005, at 1:35 PM, Bastien Koert wrote: SELECT date_format(FROM_UNIXTIME(875996580),'%Y-%m-%d'); is what I used yesterday to get around this problem bastien From: Graham Anderson [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] Newbie: phpmyadmin and searching Unix Timestamps Date: Tue, 22 Mar 2005 12:10:15 -0800 is there an easy way to search through unixtimestamps ? like Select * from SessionTable Where ConvertToMonthFunction(unixTimeStamp) = 'March' ? be great to use the Variable function within phpmyAdmin many thanks g -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Newbie: phpmyadmin and searching Unix Timestamps
SELECT * FROM SessionTable WHERE date_format((from_unixtime(fieldname),'%M') = 'March'; should do the trick bastien From: Graham Anderson [EMAIL PROTECTED] To: php-db@lists.php.net Subject: Re: [PHP-DB] Newbie: phpmyadmin and searching Unix Timestamps Date: Tue, 22 Mar 2005 15:06:35 -0800 So how would you build the query without knowing the unix TimeStamp as '527115' is the current date ? I am trying to build the query so it dynamically know what to look for like SELECT * FROM SessionTable WHERE MONTH(from_unixtime(all available records)) = 'March'; is something like this possible ? many thanks g On Mysql (works on 4.0 atleast), you can use the FROM_UNIXTIME() function to convert a unix timestamp back to universal date/time format. From then on you can use any standard mysql date/time functions to compare the month. So your query becomes something like: SELECT * FROM SessionTable WHERE MONTH(from_unixtime(527115)) = 3; MONTH() returns a numeric month number from 1-12. See the mysql documentation on date and time functions here: http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html Hope this helps. On Mar 22, 2005, at 1:35 PM, Bastien Koert wrote: SELECT date_format(FROM_UNIXTIME(875996580),'%Y-%m-%d'); is what I used yesterday to get around this problem bastien From: Graham Anderson [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] Newbie: phpmyadmin and searching Unix Timestamps Date: Tue, 22 Mar 2005 12:10:15 -0800 is there an easy way to search through unixtimestamps ? like Select * from SessionTable Where ConvertToMonthFunction(unixTimeStamp) = 'March' ? be great to use the Variable function within phpmyAdmin many thanks g -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Timestamp question
Hi there everyone, I have to convert dates in the following format to a valid unix timestamp to be stored in a mysql DB, how should I write it for this format? 12/03/04 (Day,month,year) I'm not sure how to format it correctly. I've tried to following but I don't think it's right: list( $date, $time ) = split( , $listingdate); list( $month, $day, $year ) = split( /, $date ); list( $hour, $minute, $second ) = split( :, $time ); $newtimestamp = mktime( $hour, $minute, $second, $month, $day, $year ); Chris
Re: [PHP-DB] Date problem again ;-)
I suppose there is a submit_time field in your DB table, so you could: SELECT * FROM `tablename` WHERE `submit_time`=.lastdays(3) lastdays() is a function you could create using mktime(),time(), and date() On Tue, 22 Mar 2005 16:28:39 -0500, Chris Payne [EMAIL PROTECTED] wrote: Hi there everyone, OK I'm using the following in my query to display entries in the LAST 3 days: DATE_SUB(CURDATE(),INTERVAL 3 DAY) = ListingDate But it doesn't seem to affect the results, the date format in my DB column (ListingDate - datatype is Date) is 2000-00-00 as in year, month and date - is THAT the problem? That it needs to be an actual timestamp? If that is the case I'm not sure how to approach this because the data MUST be in the above format as it comes from a central DB every night. All I need to do is display the current date plus also the previous x amount of days in between. I'm using PHP 4 with MySQL 4.0.22 (Not the latest MySQL I know, sigh). Chris -- Sincerely, Forest Liu() -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Re: Handling database calculations with a php page
Thankyou :) --- Bastien Koert [EMAIL PROTECTED] wrote: Yes, the 'a' value can also be a table alias name. Bastien From: JeRRy [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] Re: Handling database calculations with a php page Date: Mon, 14 Mar 2005 19:46:46 +1100 (EST) Hi, So in other words when we are talking queries anything before a . for SELECT etc means table name and after the . means column name within that table? So... (table_name).(Column_name) ? Is that right? J Find local movie times and trailers on Yahoo! Movies. http://au.movies.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Find local movie times and trailers on Yahoo! Movies. http://au.movies.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL 4.X Client Library
I'm currently using MySQL 4 and php 4.3.10 right now but as you may already know that, mysql 4.X has new Client Library(it comes with new PASSWORD encryption method) and it is bundled with php and mysql itself. the problem is i cannot use that latest MySQL client Library, php still using the 3.23.X Client library version. Is there anyway that i can install the new MySQL Client Library on my php box. Thanks -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] database connection timeout
Hi, I'm trying to connect to an Oracle database (using ocilogon) which is in a different country and connected to our serverfarm via a VPN (the database has no public access). Unfortunately the VPN or the database is not always available. In those cases I will present the visitor with a page explaining that the service is unavailable. My problem is that ocilogon takes a long time to return control if the connection cannot be established - often even more than 30 seconds. I've learned that if a connection can be established it'll happen in only a few seconds (usually within 1 second) and am convinced that if the connection is not established in eg 5 seconds, there is no point in waiting another eg 30 seconds. How can I tell ocilogon to timeout after eg 5 seconds (like you can with fsockopen)? Rgds, Jos -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
