XRayden

oh... I think i just tought of something good... what if... I first ask what is the "points" of the #10 THEN ask (without limits) who is under this points? like in my exemple, if I ask for "everybody under 74" i'll lget 11 results :\ but I think it'll only work with a tie in the 10th place...

So.. yes, is there a way to change the limit if 2 values are the same? i dont think so, but ask anyway

Thanks awjudd, but that's not my problem I do not have a pilot problem, but a point problems, here's a exemple: Position # Name Points 1 66 Simon Roussin 99 2 10 Michaël Lavoie 94 3 40 Sylvain Labbé 97 4 25 Pier-Luc Labbé 90 5 2 Dany Poulin 92 6 27 Marco Savoie 84 7 07 Jonathan Côté 87 8 7 Réjean Blanchet 81 9 77 Stéphane Roy 77 10 45 Steve Malouin McKibbin 74 11 17 Jimmy Nadeau 74 All of them should have gotten a "top 10" mention, but there is a tie.... and mysql will only give me 10 ppl.

MySQL 5.1 PHP 5.3 Explaination: We have a stockcar website, and everybody wanted some stats for the shows, so I added an option to enter events/pilots/results. That was for last year, they only required total points, total $ win, and qualifications points for each event, and global. Now they ask us to "count" the number of "win" "top5" and "top10" that a pilot do. I tought i had done it, but at the FIRST event, a problem arrived: a tie so the system i did gave 10 top10's but there was 11 pilots in the top 10! Ok, here's my DB schematics (my names are in french, i'll tell you what they do) courses_evenement (Table for events) (only need to know that it 1 record by events) courses_pilotes is the pilot info tables. CREATE TABLE IF NOT EXISTS `courses_pilotes` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `Numero` varchar(4) NOT NULL COMMENT 'Pilot Number', `Nom` varchar(150) NOT NULL COMMENT 'Pilot name', `Ville` varchar(100) NOT NULL COMMENT 'Pilot city', `PageFR` int(10) unsigned NOT NULL COMMENT 'Pilot page # (fr)', `PageEn` int(10) unsigned DEFAULT NULL COMMENT 'Pilot page # (en)', `s2011` int(11) unsigned DEFAULT '0' COMMENT 'participation in Season 2011', `s2012` int(11) DEFAULT '0' COMMENT 'participation in Season 2012', `datemod` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; courses_resultats is the raw event/result per pilots data CREATE TABLE IF NOT EXISTS `courses_resultats` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `event_id` int(10) unsigned NOT NULL COMMENT 'Event ID', `pilote_id` int(10) unsigned NOT NULL COMMENT 'Pilot ID', `Qualif1` int(11) NOT NULL COMMENT '1st Qualification points', `Qualif2` int(11) NOT NULL COMMENT '2nd Qualification points', `Finale` int(11) NOT NULL COMMENT 'Final Points', `Bonus` int(11) NOT NULL COMMENT 'Bonus Points', `tours` int(11) NOT NULL COMMENT 'Number of laps', `temps` varchar(15) NOT NULL COMMENT 'Best timelap', `gagner` int(11) NOT NULL COMMENT 'Is the winner (not really used)', `bilan` text NOT NULL COMMENT 'Text about this entry', PRIMARY KEY (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; courses_pilotes_points is the compile data of the season for each pilots CREATE TABLE IF NOT EXISTS `courses_pilotes_points` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `PID` int(10) unsigned NOT NULL COMMENT 'Pilot ID', `saison` varchar(4) NOT NULL COMMENT 'Season ', `pointsFinale` int(11) NOT NULL COMMENT 'Finals points', `pointsQualifs` int(11) NOT NULL COMMENT 'Qualifications points', `recru` tinyint(1) NOT NULL COMMENT 'Rookie flag', `n_courses` int(11) NOT NULL COMMENT 'number of event', `n_victoires` int(11) NOT NULL COMMENT 'Number of wins', `n_top5` int(11) NOT NULL COMMENT 'Number of top 5', `n_top10` int(11) NOT NULL COMMENT 'Number of top 10', PRIMARY KEY (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Right now while "compiling" data for each event of the season for 1 pilot, I use this PHP/MYSQL code: function check_top($quantite,$pid,$saison) { $top = 0; $qi = 'SELECT `ID` FROM `courses_evenement` WHERE `saison` = \''.$saison."'"; if(!$de = mysql_query($qi)) { die($qi.'<br>'.mysql_error()); } while($re = mysql_fetch_assoc($de)) { $cd = 'SELECT `event_id` FROM `courses_resultats` WHERE `event_id` = '.$re['ID'].' AND `pilote_id` = '.$pid.' AND `pilote_id` IN (select * from (SELECT `pilote_id` FROM `courses_resultats` WHERE `event_id` = '.$re['ID'].' ORDER BY (`Finale`+`Bonus`) DESC LIMIT '.$quantite.') alias)'; if(!$cq = mysql_query($cd)) { die(mysql_error().'<br>'.$cd); } else { if(mysql_fetch_assoc($cq)) { $top++; } } } return $top; } As you can see, the MYSQL ask for each events that is the event that this pilote is in the "quantity" requires... exemple of MYSQL: SELECT `event_id` FROM `courses_resultats` WHERE `event_id` = 1 AND `pilote_id` = 2 AND `pilote_id` IN (select * from (SELECT `pilote_id` FROM `courses_resultats` WHERE `event_id` = 1 ORDER BY (`Finale`+`Bonus`) DESC LIMIT 10) alias) MYSQL will only give me 10... even if there is 2 row with the same number... I HAVE ABSOLUTLY no idea about this... it's already 2 weeks in the season and everybody is bothering me with this bug!

i've found out that their is some things to know about that : http://dev.mysql.com/doc/refman/5.0/en/symbolic-links-to-tables.html seams that modification to the structure or repair or optimise on a symlink table will break the link some ppl told me that I need to restart mysql server more often, like after every backup or one time per day and if i repair or optimise, it need to be on the original database. hehe. some ppl told me todo a shell script of making the symlink and that every month or week i auto-recreate the link for security reason. seam to be a lot of work keeping that stable, but i think it's less problematic than to rework a WORKING software to do that...

DONE! woohoo! it worked this way... i've create a database called "database1" and create 2 tables inside copied it to "database2" now database1 and database2 are identical... what i've done, on linux i've done that : # rm -f table1.* # ln -s /var/lib/mysql/database1/table1.frm /var/lib/mysql/database2/table1.frm # ln -s /var/lib/mysql/database1/table1.MYD /var/lib/mysql/database2/table1.MYD # ln -s /var/lib/mysql/database1/table1.MYI /var/lib/mysql/database2/table1.MYI # service mysqld restart so this make 3 symbolic links of the table1 of database1 to database2... result? seamless work between database... db1 and db2 see and work with table1 like it was it's own... that's kinda cool...

I Used MySQL for a long time dev under PHP 3, 4 and now 5 now I'm facing a problems that i solved in the past with php, but i wonder if MySQL could do it on it's own, that'll help me a lot. Multiple database, 1 shared table, is it possible? i mean, i already done with 5 install of Xoops CMS and modified it that every install is on the same database, with different prefixes (like db1_ db2_ ...) then i mod Xoops for each time it called for it's users or saved open sessions id in the DB it use the same table on all install... so the users seamlessly wonder about in 5 different xoops install without disconnect and change username. now i face the same problem, i need a multi-install or a single install of a core system that will act as a multi-install software, but with the same set of users, sessions and some other things is it possible to do that kind of trick to a MySQL database ? Telling a database "this table is shared between all theses other database" and each change or sync result in a instant change in all platforms ? can we use Linux "link" to do this kind of thing ?

this is a picasaweb / simple viewer manipulation, it work... not as intented. you can specifie an album or a user and an album like that : http://www.sportsmotorises.com/newviewer/?album=17052008AutodromeChaudiere http://www.sportsmotorises.com/newviewer/?user=martin.bourget&album=StPierreDeBroughton when the user is not specified, the code use 'olivierlabbe" as a user, but in the php side. here is the code with "index.php" first I check for a user and an album. <?php if ($_GET['album'] == "") {print 'No album was specified'; } if (empty($_GET['user'])) { $userIDTag = ''; } else { $userIDTag = '\&user='.$_GET['user']; } ?> then it call the script. <div id="flashcontent">SimpleViewer requires Macromedia Flash. <a href="http://www.macromedia.com/go/getflashplayer/">Get Macromedia Flash.</a> If you have Flash installed, <a href="index.html?detectflash=false">click to view gallery</a>.</div> <script type="text/javascript"> var fo = new SWFObject("viewer.swf", "viewer", "100%", "100%", "7", "#000000"); fo.addVariable("preloaderColor", "0xffffff"); fo.addVariable("xmlDataPath", "<?php print "PicasaViewer.php?album=" . $_GET['album'].$userIDTag; ?>"); fo.write("flashcontent"); </script> it work when only an album is there, but when a user is specified... do not work. i've checked and the page generated by "<?php print "PicasaViewer.php?album=" . $_GET['album'].$userIDTag; ?>" is working (output an xml) so I thought it was the "&" that was not working I tried : \&, &, \& none of it worked... got an idea?

i'm trying to create a content box with a design for the title by it's own for the first time. i'm well used to css, but this one eludes me. i've create it the way i would like it to be here but as you can see, there is a table in this! i've never seen this kind of design done without table... is it possible ? CSS : <style type="text/css"> <!-- .contentbox_wrapper { background-image:url('images/cont_ent_bck.gif'); background-repeat:repeat-x; border:solid #000000 1px; } .contentbox_entete { height:25px; background-image:url('images/cont_ent_txt_gauche.gif'); background-repeat:no-repeat; padding-left:9px; } .contentbox_ent_txt { background:url('images/cont_ent_txt_bck.jpg'); color:#FFFFFF; } .content_txt { padding:4px; } --> </style> HTML : <div class="contentbox_wrapper" style="width:300px;"> <div class="contentbox_entete"> <table border="0" cellspacing="0" cellpadding="0" style="height:25px;"> <tr> <td class="contentbox_ent_txt">Extendable Header</td> <td><img src="images/cont_ent_txt_droit.gif" width="34" height="25" alt=""></td> </tr> </table> </div> <div class="content_txt">Content</div> </div>

forgot to mention : Apache/2.2.6 (Fedora) with php5/mysql5 (not relevent)

i dont know f*** why, this webserver make me crazy. i normaly have no problems with subdomain in virtualhost, but this one is so much fun...... i have the website (default) www.borntobestar.tv i'm trying to ad a simple ads.borntobestar.com so that an OpenX software be installed there. so i create a new virtualhost : <VirtualHost *> DocumentRoot "/var/www/ADSERVER" ServerName ads.borntobestar.com <Directory "/var/www/ADSERVER"> Order allow,deny AllowOverride none Allow from all </Directory> </VirtualHost> then... out, the main site BECOME the virtualhost, www.borntobestar.tv now trie to install openx... wtf ? i dont know why this one is so giving me an headache... i dont know why it tries the last virtualhost, i have an virtual host setted before it that do : <VirtualHost _default_:*> DocumentRoot /var/www/html DirectoryIndex index.html index.php ScriptAlias /cgi-bin/ "/var/www/html/cgi-bin/" <Directory "/var/www/html/cgi-bin/"> AllowOverride None Options None Order allow,deny Allow from all </Directory> <Directory "/var/www/html"> Order allow,deny AllowOverride none Allow from all </Directory> </VirtualHost>

Finaly i've arrived at this kind of request : SELECT `BTBS_Videos`.`ID`, AVG( `BTBS_Activites`.`Result` ) AS `moyenneVote` FROM `BTBS_Activites`, `BTBS_Videos`, (SELECT `ID` FROM `BTBS_Membres` WHERE DATE_FORMAT( FROM_DAYS( TO_DAYS( NOW( ) ) - TO_DAYS( `DDN` ) ) , '%Y' ) +0 > '10') as `Members` WHERE `BTBS_Activites`.`Action` = 'Vote' AND `BTBS_Videos`.`State` = 'Online' AND `BTBS_Activites`.`VID` = `BTBS_Videos`.`ID` AND `Members`.`ID` = `BTBS_Videos`.`MID` GROUP BY `BTBS_Activites`.`VID` ORDER BY `moyenneVote` DESC that work pretty well and compile in 0.0010 sec that'll be a very big big big thank you! and now i know a little more about MySQL. i'm a pro in PHP and right now i'm trying to take as much as possible of the cpu in php calculation.

how do we do that kind of thing? first time i've heard of a selected select query?

what i need is this . the top 5 per average of videos like this query does : SELECT `BTBS_Activites`.`VID`, AVG(`BTBS_Activites`.`Result`) as moyenneVote FROM `BTBS_Activites`,`BTBS_Videos` WHERE `BTBS_Activites`.`Action` = 'Vote' AND `BTBS_Videos`.`State` = 'Online' AND `BTBS_Activites`.`VID` = `BTBS_Videos`.`ID` GROUP BY `BTBS_Activites`.`VID` ORDER BY moyenneVote DESC; but video(s) (`BTBS_Videos`) from wich the user (`BTBS_Membres`) who uploaded it is under an age restriction (more than 20, or between 13 and 19) they are linked this way : `BTBS_Activites`.`VID` -> `BTBS_Videos`.`ID` `BTBS_Videos`.`MID` -> `BTBS_Membres`.`ID`

this query : SELECT `Login` , ( YEAR( CURDATE( ) )-YEAR(`DDN`) ) - ( RIGHT(CURDATE(),5 ) < RIGHT(`DDN`,5) ) AS `age` FROM `BTBS_Membres` WHERE ( YEAR( CURDATE( ) )-YEAR(`DDN`) ) - ( RIGHT(CURDATE(),5) < RIGHT(`DDN`,5) ) > '19'; give me somthing like this : +---------------+------+ | Login | age | +---------------+------+ | olivier.labbe | 30 | | sitajul | 57 | | jjbrig | 51 | | jj | 51 | | aaa | 51 | | qqq | 51 | | ccc | 51 | | 123 | 51 | | Lilvero | 26 | +---------------+------+ and this one : SELECT `BTBS_Activites`.`VID` , AVG( `BTBS_Activites`.`Result` ) AS moyenneVote FROM `BTBS_Activites` , `BTBS_Videos` WHERE `BTBS_Activites`.`Action` = 'Vote' AND `BTBS_Videos`.`State` = 'Online' AND `BTBS_Activites`.`VID` = `BTBS_Videos`.`ID` GROUP BY `BTBS_Activites`.`VID` ORDER BY moyenneVote DESC LIMIT 5; give me : +-----+-------------+ | VID | moyenneVote | +-----+-------------+ | 2 | 5.0000 | | 3 | 4.0000 | | 4 | 3.0000 | +-----+-------------+ Activities look like this mysql> SELECT * FROM `BTBS_Activites` LIMIT 10; +----+-----+-----+--------+--------+-------+ | ID | MID | VID | Action | Result | infos | +----+-----+-----+--------+--------+-------+ | 1 | 1 | 2 | Lu | 0 | | | 2 | 1 | 2 | Vote | 5 | | | 3 | 1 | 3 | Lu | 0 | | | 4 | 1 | 3 | Vote | 4 | | | 5 | 1 | 4 | Lu | 0 | | | 6 | 1 | 4 | Vote | 3 | | | 7 | 1 | 5 | Lu | 0 | | | 8 | 1 | 5 | Vote | 1 | | | 9 | 3 | 5 | Lu | 0 | | | 10 | 3 | 5 | Vote | 4 | | +----+-----+-----+--------+--------+-------+ videos look like this : mysql> SELECT * FROM `BTBS_Videos` LIMIT 3; +----+-----+--------+---------------------------------------------------------------+--------------------+------------+ | ID | MID | State | Fichier | Description | Date | +----+-----+--------+---------------------------------------------------------------+--------------------+------------+ | 2 | 1 | Online | olivier.labbe_c2afd6b89d5e961d4e2a62c81ffdb0f9_OLL_000001.mp4 | PublicationWeb.com | 2007-12-21 | | 3 | 2 | Online | jj.brigaudet_d972541fcfa0464de682e4d8478a2397_JJB_000001.mp4 | Casse-cou | 2007-12-21 | | 4 | 3 | Online | sitajul_8b19f2ce2620c5adc8a00c62c27ba231_JJB_000002.mp4 | yasse-you #U | 2007-12-21 | +----+-----+--------+---------------------------------------------------------------+--------------------+------------+ and users (membres) : mysql> SELECT `ID`, `Login`, `DDN` FROM `BTBS_Membres` LIMIT 5; +----+---------------+------------+ | ID | Login | DDN | +----+---------------+------------+ | 1 | olivier.labbe | 1977-11-26 | | 2 | jj.brigaudet | 1999-11-19 | | 3 | sitajul | 1950-03-12 | | 7 | jjbrig | 1956-03-01 | | 8 | jj | 1956-03-01 | +----+---------------+------------+ i'm at a lost...