[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
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
[PHP-DB] paginating : optimising queries
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] JOIN problem
am using mysql 3 so i can't do that subquery, how can we do that in mysql 3 ? On Mon, 7 Feb 2005 19:47:08 +, Simon Rees [EMAIL PROTECTED] wrote: On Monday 07 February 2005 18:22, Zouari Fourat wrote: Hello I have 2 tables with two columns in each one (cloned tables) like this : ID int(6) UserName varchar(25) and i would like to select usernames from table1 that doesnt appear in table2 so i did this : Depending on which database you're using you may be able to do this: SELECT a.username FROM table1 a WHERE a.username NOT IN ( SELECT b.username FROM table2 b ) cheers Simon -- ~~ Simon Rees | [EMAIL PROTECTED] | ORA-03113: end-of-file on communication channel ~~ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] JOIN problem
Hello I have 2 tables with two columns in each one (cloned tables) like this : ID int(6) UserName varchar(25) and i would like to select usernames from table1 that doesnt appear in table2 so i did this : SELECT a.UserName FROM table1 a RIGHT OUTER JOIN table2 b ON (a.UserName = b.username) and that wont work, as i saw from sql joint docs, using OUTER JOIN significate that we want to join the 2 tables where the inverse of (a.UserName=b.username) is, so it's equivalent of (a.UserNameb.username) and the LEFT option is about to show the lines from the left side table wich is a (table1) in my query why didnt that work fine ? how to resolve it and is my view on sql joins is incorect ? thanks all -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] JOIN problem
that wont work :( and either when changing RIGHT to LEFT JOIN that wont work On Mon, 7 Feb 2005 13:41:01 -0500, Joseph Crawford [EMAIL PROTECTED] wrote: try doing this SELECT a.UserName FROM table1 a RIGHT OUTER JOIN table2 b ON (a.UserName != b.username) On Mon, 7 Feb 2005 19:22:15 +0100, Zouari Fourat [EMAIL PROTECTED] wrote: Hello I have 2 tables with two columns in each one (cloned tables) like this : ID int(6) UserName varchar(25) and i would like to select usernames from table1 that doesnt appear in table2 so i did this : SELECT a.UserName FROM table1 a RIGHT OUTER JOIN table2 b ON (a.UserName = b.username) and that wont work, as i saw from sql joint docs, using OUTER JOIN significate that we want to join the 2 tables where the inverse of (a.UserName=b.username) is, so it's equivalent of (a.UserNameb.username) and the LEFT option is about to show the lines from the left side table wich is a (table1) in my query why didnt that work fine ? how to resolve it and is my view on sql joins is incorect ? thanks all -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Joseph Crawford Jr. Codebowl Solutions [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] JOIN problem
:( nothing On Mon, 7 Feb 2005 14:10:10 -0500, Joseph Crawford [EMAIL PROTECTED] wrote: try INNER JOIN? On Mon, 7 Feb 2005 19:47:29 +0100, Zouari Fourat [EMAIL PROTECTED] wrote: that wont work :( and either when changing RIGHT to LEFT JOIN that wont work On Mon, 7 Feb 2005 13:41:01 -0500, Joseph Crawford [EMAIL PROTECTED] wrote: try doing this SELECT a.UserName FROM table1 a RIGHT OUTER JOIN table2 b ON (a.UserName != b.username) On Mon, 7 Feb 2005 19:22:15 +0100, Zouari Fourat [EMAIL PROTECTED] wrote: Hello I have 2 tables with two columns in each one (cloned tables) like this : ID int(6) UserName varchar(25) and i would like to select usernames from table1 that doesnt appear in table2 so i did this : SELECT a.UserName FROM table1 a RIGHT OUTER JOIN table2 b ON (a.UserName = b.username) and that wont work, as i saw from sql joint docs, using OUTER JOIN significate that we want to join the 2 tables where the inverse of (a.UserName=b.username) is, so it's equivalent of (a.UserNameb.username) and the LEFT option is about to show the lines from the left side table wich is a (table1) in my query why didnt that work fine ? how to resolve it and is my view on sql joins is incorect ? thanks all -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Joseph Crawford Jr. Codebowl Solutions [EMAIL PROTECTED] -- Joseph Crawford Jr. Codebowl Solutions [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
