[PHP] Re: A MySQL Question
tedd wrote: Hi gang: I just interviewed for a job teaching at the local college (imagine me taking minds of mush and molding them to the world according to tedd -- frightening huh?) In any event, the interviewer asked me how long I've been using MySQL and I replied several years. After which she asked a single question, which was What does EXIST mean? Now without running to the manuals, please be honest and tell me how many of you know off the top of your head what EXIST means? I would be curious to know. I answered the question correctly, (I'm one of those weird types who read manuals for fun) but I have never used EXIST in a query. Have any of you? I knew it existed but have never felt the need to use it; and upon refreshing my knowledge from the manual can safely say I'll continue not to use it. On the same not does anybody else frequently use (or even know about) the awesome spatial extension for mysql? or use the information_schema tables? And while we're on the subject of MySQL -- while we all know how to write it, how do you say it? I've read that the common way is to say My Squell, or something like that. But I always sounded out each letter, such as My S-Q-L. The interviewer pronounced it the same as I, but I have heard others say it differently. What say you? I'm in the My-S-Q-L boat as well; also Post-gres and S-Q-L Server for ms.. none of this sequele malarky -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: A MySQL Question
At 3:24 PM + 12/7/08, Nathan Rixham wrote: On the same not does anybody else frequently use (or even know about) the awesome spatial extension for mysql? or use the information_schema tables? I've certainly never used them, but I can imagine information_schema tables that are similar to DOCTYPE as found in xml for defining fields -- is that it? Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: A MySQL Question
tedd wrote: At 3:24 PM + 12/7/08, Nathan Rixham wrote: On the same not does anybody else frequently use (or even know about) the awesome spatial extension for mysql? or use the information_schema tables? I've certainly never used them, but I can imagine information_schema tables that are similar to DOCTYPE as found in xml for defining fields -- is that it? Cheers, tedd not many have :p but there's so much more power and speed can be opened up by using them.. to get you started ( I highly recommend this ) try: SELECT * FROM `information_schema`.`TABLES`; then: SELECT * FROM `information_schema`.`COLUMNS` then cunning way's to combine the two.. for instance you could query these two tables to return back a list of all columns in database X which are of type text and use them to search you're entire database in one go like: select table_name, column_name from information_schema.columns WHERE table_schema='DB_NAME' AND data_type IN ('char','varchar','text') AND CHARACTER_MAXIMUM_LENGTH 3;select table_name, column_name from information_schema.columns WHERE table_schema='DB_NAME' AND data_type IN ('char','varchar','text') AND CHARACTER_MAXIMUM_LENGTH 3; replace DB_NAME for the database you want to search, you may want to include type's like mediumtext etc.. point is, the information_schema tables reside in memory so are ultra quick to query; and they are always up to date; and they give you far more information than normally available. so many options.. rows in a database (not table) SELECT SUM(TABLE_ROWS) FROM `information_schema`.`TABLES` WHERE TABLE_SCHEMA='DB_NAME'; etc etc.. regards! -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: A MySQL Question
Nathan Rixham wrote: tedd wrote: At 3:24 PM + 12/7/08, Nathan Rixham wrote: On the same not does anybody else frequently use (or even know about) the awesome spatial extension for mysql? or use the information_schema tables? I've certainly never used them, but I can imagine information_schema tables that are similar to DOCTYPE as found in xml for defining fields -- is that it? Cheers, tedd not many have :p but there's so much more power and speed can be opened up by using them.. to get you started ( I highly recommend this ) try: SELECT * FROM `information_schema`.`TABLES`; then: SELECT * FROM `information_schema`.`COLUMNS` The idea behind information_schema is it should be reasonably portable and should work on most types of db's. -- Postgresql php tutorials http://www.designmagick.com/ -- 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
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: A MySQL question
Zavaboy [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Ok, I want to delete only 1 row off of a database table... Example: I have a table with columns user and item... Lets say the table contains the following rows (user | item): 582-668243 | Toothbrush 582-668243 | Toothbrush 582-668243 | Toothbrush 582-668243 | Trash can 582-668243 | Trash can 582-668243 | Something else 582-668243 | Something else 582-668243 | Something else 582-668243 | Something else 720-387690 | Dog treats 720-387690 | Car 720-387690 | Car 720-387690 | Toothbrush 720-387690 | Toothbrush Ok, user 582-668243 is buying a lot, eh? LoL Anyway, how can I remove only 1 Toothbrush from user 582-668243? I just want the query... I've been trying to find it out for a few hours now... Thanks in advance! -- - Zavaboy [EMAIL PROTECTED] www.zavaboy.com You can try putting LIMIT 1 on the end of your query but to be honest I don't know if LIMIT works with the DELETE command. Worth a try. Ideally you should have an additional column with an autoincrementing value to act as your primary key and you delete on that key. - Kevin -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: quick mysql question
If it's a linux or unix box use ssh or telnet to get a shell on the remote system then follow the instructions in http://www.mysql.com/documentation/mysql/full/index.html . If it's a windows box then you have to use pcAnywhere or go to the machine to set it up, unless there's a ssh or telnet server running on the box. Cheers, Gardner Doug Parker wrote: where can i find info on setting up a mysql database on a remote server? i know how to set them up on a local machine, but i can't figure out how to get to the command line setting on a remote server... -- Gardner Lloyd Bickford III TriAxial Data Systems 36 Brennan Street Watsonville, CA 95076 Phone: (831) 763-3697 x101 Fax: (831) 763-3699 Please note that the views expressed in this message are the views of the author alone and not necessarily the views of TriAxial Data Systems -- 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