[PHP] Re: PHP/mySQL question using ORDER BY with logic
Rob Gould schrieb: Question about mySQL and PHP, when using the mySQL ORDER BY method... Basically I've got data coming from the database where a wine producer-name is a word like: Château Bahans Haut-Brion or La Chapelle de La Mission Haut-Brion or Le Clarence de Haut-Brion but I need to ORDER BY using a varient of the string: 1) If it begins with Château, don't include Chateau in the string to order by. 2) If it begins with La, don't order by La, unless the first word is Chateau, and then go ahead and order by La. Example sort: Notice how the producer as-in comes before the parenthesis, but the ORDER BY actually occurs after a re-ordering of the producer-string, using the above rules. Red: Château Bahans Haut-Brion (Bahans Haut-Brion, Château ) Red: La Chapelle de La Mission Haut-Brion (Chapelle de La Mission Haut-Brion, La ) Red: Le Clarence de Haut-Brion (Clarence de Haut-Brion, Le ) Red: Château Haut-Brion (Haut-Brion, Château ) Red: Château La Mission Haut-Brion (La Mission Haut-Brion, Château ) Red: Domaine de La Passion Haut Brion (La Passion Haut Brion, Domaine de ) Red: Château La Tour Haut-Brion (La Tour Haut-Brion, Château ) Red: Château Larrivet-Haut-Brion (Larrivet-Haut-Brion, Château ) Red: Château Les Carmes Haut-Brion (Les Carmes Haut-Brion, Château ) That logic between mySQL and PHP, I'm just not sure how to accomplish? I think it might involve a mySQL alias-technique but I could be wrong. Right now, my PHP call to generate the search is this: $query = 'SELECT * FROM wine WHERE MATCH(producer, varietal, appellation, designation, region, vineyard, subregion, country, vintage) AGAINST ( ' . $searchstring . ') ORDER BY producer LIMIT 0,100'; Hi, Try to solve your Logic on your programming language and to select Data with your Database... Try to normalize more your Information on the Database. Regars Carlos -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: PHP/mySQL question using ORDER BY with logic
Robert Cummings wrote: On Fri, 2008-10-24 at 00:18 -0400, Rob Gould wrote: Question about mySQL and PHP, when using the mySQL ORDER BY method... Basically I've got data coming from the database where a wine producer-name is a word like: Château Bahans Haut-Brion or La Chapelle de La Mission Haut-Brion or Le Clarence de Haut-Brion but I need to ORDER BY using a varient of the string: 1) If it begins with Château, don't include Chateau in the string to order by. 2) If it begins with La, don't order by La, unless the first word is Chateau, and then go ahead and order by La. Example sort: Notice how the producer as-in comes before the parenthesis, but the ORDER BY actually occurs after a re-ordering of the producer-string, using the above rules. Red: Château Bahans Haut-Brion (Bahans Haut-Brion, Château ) Red: La Chapelle de La Mission Haut-Brion (Chapelle de La Mission Haut-Brion, La ) Red: Le Clarence de Haut-Brion (Clarence de Haut-Brion, Le ) Red: Château Haut-Brion (Haut-Brion, Château ) Red: Château La Mission Haut-Brion (La Mission Haut-Brion, Château ) Red: Domaine de La Passion Haut Brion (La Passion Haut Brion, Domaine de ) Red: Château La Tour Haut-Brion (La Tour Haut-Brion, Château ) Red: Château Larrivet-Haut-Brion (Larrivet-Haut-Brion, Château ) Red: Château Les Carmes Haut-Brion (Les Carmes Haut-Brion, Château ) That logic between mySQL and PHP, I'm just not sure how to accomplish? I think it might involve a mySQL alias-technique but I could be wrong. Right now, my PHP call to generate the search is this: $query = 'SELECT * FROM wine WHERE MATCH(producer, varietal, appellation, designation, region, vineyard, subregion, country, vintage) AGAINST ( ' . $searchstring . ') ORDER BY producer LIMIT 0,100'; Maybe there's a good way to do it with the table as is... but I'm doubtful. I would create a second field that contains a pre-processed version of the name that performs stripping to achieve what you want. This could be done by a PHP script when the data is inserted into the database, or if not possible like that, then a cron job could run once in a while, check for entries with this field empty and generate it. Yeah I'd suspect that the storage overhead is nothing compared to the speed increase you'll get during the read operations if you don't have to dick around with the data :) (yes I'm comparing bits to time, but I don't have time to explain that bit). Col -- Colin Guthrie gmane(at)colin.guthr.ie http://colin.guthr.ie/ Day Job: Tribalogic Limited [http://www.tribalogic.net/] Open Source: Mandriva Linux Contributor [http://www.mandriva.com/] PulseAudio Hacker [http://www.pulseaudio.org/] Trac Hacker [http://trac.edgewall.org/] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: php mySql question
Sure that your php.ini is located correctly? I have installed php 5.0.4 on my windows 2000, IIS 6.0 server. PHP works but when I try to connect to MySQL I get the Fatal error: Call to undefined function mysql_connect(). I have uncommented the line in the php.ini file that says 'extension=php_mysql.dll'. I have path variables set for both c:\php and c:\php\ext. One very peculiar thing that I noticed when I ran phpinfo() is that it shows the extension_dir is set to c:\php5 even though in my php.ini file it is set to c:\php. I have a feeling that this is where the problem exists. Any advice would be appreciated. Thanks, NK -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] RE:[PHP] mysql question
Thanks James!
It is the perfect answer for my cuestion!!!
Regards! Julian
- Original Message -
From: James E. Hicks III
To: Julian ; [EMAIL PROTECTED]
Sent: Monday, April 08, 2002 5:12 PM
Subject: RE: [PHP] mysql question
Why don't you just increment a counter as you are retrieving them.
mysql_select_db(some_DB) or die(DB not available);
$query = select some_data from some_table;
$result = mysql_query($query);
$rowcounter=0;
while ($row=mysql_fetch_array($result)){
extract($row);
$rowcounter++;
echo(You are on Row $rowcounter wich contains the);
echo( data $some_data for the field named some_data);
}
James
-Original Message-
From: Julian [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 08, 2002 3:59 PM
To: [EMAIL PROTECTED]
Subject: [PHP] mysql question
Hi!!!
I want to know if there is a function to know which is the number of the row
that I selected.
Please, help me! Julian
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
