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...

#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

the tested query give me somthing like this : what i need is more like this :

so ok, i've ffound that this : worrk exactly the same as thee first one, only ~50 times faster... (from 0.0234 to 0.0006) so my quest is for the unique one... i've tried : but it dont work as well, it seam it will output only one video per user, not all videos from all user(may have more than one videos) witch the user himselff is over 19.. seam pretty to dawm hard for my taste... and talent

Hi, I've got 2 queries that i need only one of... i could do the math in PHP, but I KNOW MySQL can do it faster and better... some infos : so this is my problem : I Have 3 tables a User one : CREATE TABLE `BTBS_Membres` ( `ID` tinyint(3) unsigned NOT NULL auto_increment COMMENT 'Primary Key (Clé Primaire)', `Login` varchar(32) NOT NULL default '' COMMENT 'Username (Nom usager)', `Password` varchar(32) NOT NULL default '' COMMENT 'md5 password hash (Mot de passe encrypter)', `Prenom` varchar(150) NOT NULL default '' COMMENT 'First Name (Prénom)', `Nom` varchar(150) NOT NULL default '' COMMENT 'Last Name (Nom de famille)', `Gender` enum('M','F') NOT NULL default 'M' COMMENT 'Gender Type (Sexe)', `Courriel` varchar(255) NOT NULL default '' COMMENT 'e-mail address (Courriel)', `DDN` date NOT NULL default '0000-00-00' COMMENT 'Birthdate (Date De Naissance)', `LocalID` int(11) NOT NULL default '0' COMMENT 'ID of local voting (Lieu de vote)', `CodePostal` varchar(10) NOT NULL default '' COMMENT 'Postal Code / Zip (Code Postal)', `MobilePhone` varchar(25) default NULL COMMENT 'Mobile Phone Number (Téléphone cellulaire)', `PrefG1` int(11) NOT NULL default '0' COMMENT 'Musical Preference #1', `PrefG2` int(11) default NULL COMMENT 'Musical Preference #1', `PrefG3` int(11) default NULL COMMENT 'Musical Preference #1', `Comment` text COMMENT 'Comments or infos (Commentaires ou informations)', `Type` enum('Membre','Validation','Bannie') NOT NULL default 'Membre' COMMENT 'Member Type (Membre = active, Validation = in validation process, Bannie = banned) (Type de membre)', `Admin` tinyint(1) NOT NULL default '0' COMMENT 'is a user Administrator? (Admin ou non)', PRIMARY KEY (`ID`) ) ENGINE=MyISAM; a video file one : CREATE TABLE `BTBS_Videos` ( `ID` int(10) unsigned NOT NULL auto_increment COMMENT 'Primary Key (Clé Primaire)', `MID` int(11) NOT NULL default '0' COMMENT 'Member ID (ID du membre)', `State` enum('Uploaded','Approoved','Online','Refused') NOT NULL default 'Uploaded' COMMENT 'State of file (État du vidéo)', `Fichier` text NOT NULL COMMENT 'Filename (nom du fichier)', `Description` text NOT NULL, `Date` date NOT NULL default '0000-00-00', PRIMARY KEY (`ID`) ) ENGINE=MyISAM; and a activities one : CREATE TABLE `BTBS_Activites` ( `ID` int(10) unsigned NOT NULL auto_increment COMMENT 'Primary Key (Clé Primaire)', `MID` int(10) unsigned NOT NULL default '0' COMMENT 'Member ID (ID du membre)', `VID` int(11) NOT NULL default '0' COMMENT 'video id', `Action` enum('Lu','Vote') NOT NULL default 'Lu' COMMENT 'Action type : Lu = this action mark a viewed video, Vote = this action mark a voted result', `Result` int(11) NOT NULL default '0' COMMENT 'If voted on, this is the result', `infos` text NOT NULL COMMENT 'Other information (IP + header)', PRIMARY KEY (`ID`) ) ENGINE=MyISAM; this is a simple case of videos upload and vote, now i need to do is search and fine the best top 5 voted on video in AGE categories! what i've done so far : i can find the total top five of all videos : and i can do a age search on users : how do you think i should proceed to look for the top 5 videos uploaded by ppls aving an age over 19 ??? do you think it's even possible in one mysql statement ?

i'm working with firebug (one of the best plugin a developper can have) and it return only 4 error. it seams in the script they find 4 404 errors. but... anyway : syntax error http://www.arteinsania.net/js/builder.js Line 1 syntax error http://www.arteinsania.net/js/dragdrop.js Line 1 syntax error http://www.arteinsania.net/js/controls.js Line 1 syntax error http://www.arteinsania.net/js/slider.js Line 1 one of your script is calling /js/ some scripts, and they gives a 404 page.

i'm unable to connect. page is blank but i've got the favicon

wont work well with foreight language. i've test this : àéôï output this : ny ijnsdc: à éôï

still doesn't work... is there a way to know WHY the mail() is returning false ?

hello... i've got a php class to validate form intry, it's supposed to send back the result in an email. so far was working good, now i've got it to work on a PHP5 environment and it works, partialy... here the error : the faulty code reside in my html_mail() function : [code]function html_mail() { $this->headers  = "MIME-Version: 1.0\n"; $this->headers .= "Content-type: text/html; charset=iso-8859-1\n"; $this->headers .= "X-Priority: 3\n"; $this->headers .= "X-MSMail-Priority: Normal\n"; $this->headers .= "X-Mailer: php\n"; $this->headers .= "From: \"".$this->fromname."\" <".$this->fromaddress.">\n"; return mail($this->to, $this->subject, stripslashes($this->message), $this->headers); }[/code] it was causing RANDOM unknown... it works perfectly if my $this->to variable is set to info@xagone.com and seams to NOT WORK if it's anything other than this one... but here comes the part that thrown me out of my chair. it's not the $this->to that cauze the error! check this out : i've modified the code to output some elements [code]function html_mail() { $this->headers  = "MIME-Version: 1.0\n"; $this->headers .= "Content-type: text/html; charset=iso-8859-1\n"; $this->headers .= "X-Priority: 3\n"; $this->headers .= "X-MSMail-Priority: Normal\n"; $this->headers .= "X-Mailer: php\n"; echo $this->headers .= "From: \"".$this->fromname."\" <".$this->fromaddress.">\n"; echo '<br />'; echo $this->fromaddress; echo '<br />'; echo $this->to; return mail($this->to, $this->subject, stripslashes($this->message), $this->headers); }[/code] here is the output : [code]MIME-Version: 1.0 Content-type: text/html; charset=iso-8859-1 X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: php From: "Olivier Labbé" info@xagone.com emploisofferts@publicationweb.com[/code] php5 seams to shit the line : $this->headers .= "From: \"".$this->fromname."\" <".$this->fromaddress.">\n"; and not being able to output the first "<" ... i've tried escaping it... dont work... any idea ?

I'm a total beginner in AJAX.... but, I know PHP a lot. I want to know if there is a book or a reference to get started and maybe have much better idea how it work and what i can do with it ? i've heard about "Modern Web Design Using JavaScript and DOM" by Stuart Langridge ... some ppl said it's the best thing about ajax, without saying it in the title. what are your suggestions ?