RE: Blasted #$%$^$^ host has v3.23 when i need UNION
Ryan, You might well find that the 5 separate counts are quicker than the join approach. Mysql is pretty efficient at counts on indexed columns from a single table. My instincts suggest that the four table join you are proposing could be slower than the 5 separate counts, especially if the tables have thousands of rows. As long as you use the same database connection, there's shouldn't be much extra network overhead either. I may be wrong, but I suspect you're worrying unnecessarily, unless the database server is connected to the web server via a particularly slow network. Make sure you've got the right indexes on all of the tables though (ie put an index on each table that matches the where clause against that table). All the best, Andy > -Original Message- > From: Ryan A [mailto:[EMAIL PROTECTED] > Sent: 15 September 2003 13:31 > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: Blasted #$%$^$^ host has v3.23 when i need UNION > > > > Hey Andy, > True, but thats using 5 selects instead of just one, and since we > are expecting quite a bit of traffic to the site that can add up > pretty fast, expecially since we cant afford to have a dedicated > server but are on a shared hosting package. > > If we have no other alternative we will be going with the 5 > selects but since there seems to be a "join" alternative was > hoping someone could help me out. > > Thanks anyway. > Cheers, > -Ryan > > > Ryan, > > > > If this query worked, it would return you 5 rows, one for each separate > > count. > > > > If you execute 5 separate counts in PHP, you'll get 5 separate > values with > > the same numbers as above. > > > > Not radically different? > > > > Andy > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Blasted #$%$^$^ host has v3.23 when i need UNION
Hey Andy, True, but thats using 5 selects instead of just one, and since we are expecting quite a bit of traffic to the site that can add up pretty fast, expecially since we cant afford to have a dedicated server but are on a shared hosting package. If we have no other alternative we will be going with the 5 selects but since there seems to be a "join" alternative was hoping someone could help me out. Thanks anyway. Cheers, -Ryan > Ryan, > > If this query worked, it would return you 5 rows, one for each separate > count. > > If you execute 5 separate counts in PHP, you'll get 5 separate values with > the same numbers as above. > > Not radically different? > > Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Blasted #$%$^$^ host has v3.23 when i need UNION
You may also want to look at http://www.php.net/manual/en/function.mysql-num-rows.php Ryan A wrote: Hey, Thanks for replying. Nope, the whole reason for selecting the data is the count, i need to display to the client how many records of each category he has...if there is any other way to do that (me being a newbie to mysql) I would happy to know of it. Cheers, -Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Blasted #$%$^$^ host has v3.23 when i need UNION
Ryan, If this query worked, it would return you 5 rows, one for each separate count. If you execute 5 separate counts in PHP, you'll get 5 separate values with the same numbers as above. Not radically different? Andy > -Original Message- > From: Ryan A [mailto:[EMAIL PROTECTED] > Sent: 15 September 2003 11:25 > To: [EMAIL PROTECTED] > Cc: < > Subject: Re: Blasted #$%$^$^ host has v3.23 when i need UNION > > > Hey, > Thanks for replying. > > Nope, the whole reason for selecting the data is the count, i need to > display to the client how many records of each category he > has...if there is > any other way to do that (me being a newbie to mysql) I would > happy to know > of it. > > Cheers, > -Ryan > > > > Without 'count' function, you could have: > > > > SELECT user.C1, dedicated.C2, reseller.C3, colocated.C4, freehosting.C5 > > FROM user, dedicated, reseller, colocated , freehosting WHERE > > (user.user ='testing' and user.ccno=1) OR > > (dedicated.user ='testing' and dedicated.ccno=1) OR > > (reseller.user ='testing' and reseller.ccno=1) OR > > (colocated.user ='testing' and ccolocated.cno=1) OR > > (freehosting.user ='testing' and freehosting.ccno=1) OR > > > > > > > > Ryan A wrote: > > > Hi guys, > > > First of all let me warn you, i am very much a newbie to mysql, i am > pretty > > > good with the basics like selecting,deleting,updateing etc but after > that i > > > get a dazed look in my eyes with complex sql :-D > > > > > > Now that you have been warned let me explain, on my local machine > (win2k) i > > > have php and mysql 4.0 installed for testing purposes, I have > created a > site > > > locally and then uploaded it only to find out that because of > UNION it > wont > > > run as my host is on version 3.23. > > > > > > I then searched google as i was pretty sure that i am not the only > person > > > who ran into this problem and found 2 places with some kind of > explanation > > > of which this seems to be the best: (the other one is the online > manual) > > > http://jinxidoru.com/tutorials/union.html > > > > > > but being a newbie and never having used "join" in my life, (at least > not > > > knowingly) this is @$#$%^$ confusing. > > > > > > Below is my union select statement, can somebody please show me how to > > > convert it so it will work on 3x please? > > > (This one is the actual php code i am using but if you dont understand > it > > > there is the normal sql below this one) > > > > > > $tt = "SELECT COUNT(*), 'C1' FROM shared WHERE user ='".$mmmy_user."' > and > > > ccno=".$mmmy_ccno." UNION SELECT COUNT(*), 'C2' FROM dedicated where > > > user='".$mmmy_user."' and ccno=".$mmmy_ccno." UNION SELECT COUNT(*), > 'C3' > > > FROM reseller WHERE user ='".$mmmy_user."' and ccno=".$mmmy_ccno." > UNION > > > SELECT COUNT(*), 'C4' FROM colocated WHERE user ='".$mmmy_user."' and > > > ccno=".$mmmy_ccno." UNION SELECT COUNT(*), 'C5' FROM freehosting WHERE > user > > > ='".$mmmy_user."' and ccno=".$mmmy_ccno; > > > > > > // Normal version > > > > > > SELECT COUNT(*), 'C1' FROM shared WHERE user ='testing' and ccno=1 > > > UNION > > > SELECT COUNT(*), 'C2' FROM dedicated where user='testing' and ccno=1 > > > UNION > > > SELECT COUNT(*), 'C3' FROM reseller WHERE user ='testing' and ccno=1 > > > UNION > > > SELECT COUNT(*), 'C4' FROM colocated WHERE user ='testing' and ccno=1 > > > UNION > > > SELECT COUNT(*), 'C5' FROM freehosting WHERE user ='testing' and > ccno=1; > > > > > > Thanks in advance and have a fantastic day, even though its monday... > > > :-D > > > > > > Cheers, > > > -Ryan > > > > > > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Blasted #$%$^$^ host has v3.23 when i need UNION
Ryan, As you probably found out, union is only available in version 4 of mysql. As you're using PHP anyway, why don't you just break it up into 5 separate selects and combine the results in PHP? Andy > -Original Message- > From: Ryan A [mailto:[EMAIL PROTECTED] > Sent: 15 September 2003 10:41 > To: < > Subject: Blasted #$%$^$^ host has v3.23 when i need UNION > > > Hi guys, > First of all let me warn you, i am very much a newbie to mysql, i > am pretty > good with the basics like selecting,deleting,updateing etc but > after that i > get a dazed look in my eyes with complex sql :-D > > Now that you have been warned let me explain, on my local machine > (win2k) i > have php and mysql 4.0 installed for testing purposes, I have > created a site > locally and then uploaded it only to find out that because of > UNION it wont > run as my host is on version 3.23. > > I then searched google as i was pretty sure that i am not the only person > who ran into this problem and found 2 places with some kind of explanation > of which this seems to be the best: (the other one is the online manual) > http://jinxidoru.com/tutorials/union.html > > but being a newbie and never having used "join" in my life, (at least not > knowingly) this is @$#$%^$ confusing. > > Below is my union select statement, can somebody please show me how to > convert it so it will work on 3x please? > (This one is the actual php code i am using but if you dont understand it > there is the normal sql below this one) > > $tt = "SELECT COUNT(*), 'C1' FROM shared WHERE user ='".$mmmy_user."' and > ccno=".$mmmy_ccno." UNION SELECT COUNT(*), 'C2' FROM dedicated where > user='".$mmmy_user."' and ccno=".$mmmy_ccno." UNION SELECT > COUNT(*), 'C3' > FROM reseller WHERE user ='".$mmmy_user."' and ccno=".$mmmy_ccno." UNION > SELECT COUNT(*), 'C4' FROM colocated WHERE user ='".$mmmy_user."' and > ccno=".$mmmy_ccno." UNION SELECT COUNT(*), 'C5' FROM freehosting > WHERE user > ='".$mmmy_user."' and ccno=".$mmmy_ccno; > > // Normal version > > SELECT COUNT(*), 'C1' FROM shared WHERE user ='testing' and ccno=1 > UNION > SELECT COUNT(*), 'C2' FROM dedicated where user='testing' and ccno=1 > UNION > SELECT COUNT(*), 'C3' FROM reseller WHERE user ='testing' and ccno=1 > UNION > SELECT COUNT(*), 'C4' FROM colocated WHERE user ='testing' and ccno=1 > UNION > SELECT COUNT(*), 'C5' FROM freehosting WHERE user ='testing' and ccno=1; > > Thanks in advance and have a fantastic day, even though its monday... > :-D > > Cheers, > -Ryan > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Blasted #$%$^$^ host has v3.23 when i need UNION
Hey, Thanks for replying. Nope, the whole reason for selecting the data is the count, i need to display to the client how many records of each category he has...if there is any other way to do that (me being a newbie to mysql) I would happy to know of it. Cheers, -Ryan > Without 'count' function, you could have: > > SELECT user.C1, dedicated.C2, reseller.C3, colocated.C4, freehosting.C5 > FROM user, dedicated, reseller, colocated , freehosting WHERE > (user.user ='testing' and user.ccno=1) OR > (dedicated.user ='testing' and dedicated.ccno=1) OR > (reseller.user ='testing' and reseller.ccno=1) OR > (colocated.user ='testing' and ccolocated.cno=1) OR > (freehosting.user ='testing' and freehosting.ccno=1) OR > > > > Ryan A wrote: > > Hi guys, > > First of all let me warn you, i am very much a newbie to mysql, i am pretty > > good with the basics like selecting,deleting,updateing etc but after that i > > get a dazed look in my eyes with complex sql :-D > > > > Now that you have been warned let me explain, on my local machine (win2k) i > > have php and mysql 4.0 installed for testing purposes, I have created a site > > locally and then uploaded it only to find out that because of UNION it wont > > run as my host is on version 3.23. > > > > I then searched google as i was pretty sure that i am not the only person > > who ran into this problem and found 2 places with some kind of explanation > > of which this seems to be the best: (the other one is the online manual) > > http://jinxidoru.com/tutorials/union.html > > > > but being a newbie and never having used "join" in my life, (at least not > > knowingly) this is @$#$%^$ confusing. > > > > Below is my union select statement, can somebody please show me how to > > convert it so it will work on 3x please? > > (This one is the actual php code i am using but if you dont understand it > > there is the normal sql below this one) > > > > $tt = "SELECT COUNT(*), 'C1' FROM shared WHERE user ='".$mmmy_user."' and > > ccno=".$mmmy_ccno." UNION SELECT COUNT(*), 'C2' FROM dedicated where > > user='".$mmmy_user."' and ccno=".$mmmy_ccno." UNION SELECT COUNT(*), 'C3' > > FROM reseller WHERE user ='".$mmmy_user."' and ccno=".$mmmy_ccno." UNION > > SELECT COUNT(*), 'C4' FROM colocated WHERE user ='".$mmmy_user."' and > > ccno=".$mmmy_ccno." UNION SELECT COUNT(*), 'C5' FROM freehosting WHERE user > > ='".$mmmy_user."' and ccno=".$mmmy_ccno; > > > > // Normal version > > > > SELECT COUNT(*), 'C1' FROM shared WHERE user ='testing' and ccno=1 > > UNION > > SELECT COUNT(*), 'C2' FROM dedicated where user='testing' and ccno=1 > > UNION > > SELECT COUNT(*), 'C3' FROM reseller WHERE user ='testing' and ccno=1 > > UNION > > SELECT COUNT(*), 'C4' FROM colocated WHERE user ='testing' and ccno=1 > > UNION > > SELECT COUNT(*), 'C5' FROM freehosting WHERE user ='testing' and ccno=1; > > > > Thanks in advance and have a fantastic day, even though its monday... > > :-D > > > > Cheers, > > -Ryan > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
