Re: [PHP] mysql question
The mysql forum is the best place. Note that their holiday schedule may mean some lag in getting answers. Bastien Sent from my iPod On Dec 29, 2008, at 7:51 AM, ann kok oiyan...@yahoo.ca wrote: Hi all Do you know any websites for mysql question? I do submit the mysql forum but I would like to have more to learn Now I have mysql replication question. Thank you __ Ask a question on any topic and get answers from real people. Go to Yahoo! Answers and share what you know at http://ca.answers.yahoo.com -- 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
[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
Re: [PHP] mysql question
On Sun, February 10, 2008 11:52 am, Per Jessen wrote: nihilism machine wrote: $ret = mysql_result($r, 0); mysql_free_result($r); if ($this-auto_slashes) return stripslashes($ret); else return $ret; } what is $ret, an array? No, it's a mysql result object. No, it's a single field value from the database. $r is the result object. http://php.net/mysql_result -- Some people have a gift link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql question #2
At any rate, just seeing this tells me that you've got a real mess on your hands... Or you could say, You're going to have some fun cleaning that. -- Richard Heyes http://www.websupportsolutions.co.uk Knowledge Base and Helpdesk software hosted for you - no installation, no maintenance, new features automatic and free ** New Helpdesk demo now available ** -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql question #2
On Sun, February 10, 2008 12:12 pm, nihilism machine wrote: public function select_one($sql) { if ($this-auto_slashes) { return stripslashes($ret); If you have to call stripslashes() on data coming FROM MySQL, then you have really messed up... You've put in data that was escaped TWICE, probably with Magic Quotes ON followed by addslashes (or mysql_real_escape_string). At any rate, just seeing this tells me that you've got a real mess on your hands... } else { return $ret; } } how can i get the contents of a column in the returned row say for something called Email as the column name. here is my code now: Since it's only returning ONE piece of data, how confused can it be? $this-whatever['Email'] = $result; -- Some people have a gift link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql question
On Feb 10, 2008 1:03 PM, Per Jessen [EMAIL PROTECTED] wrote: Yep, you're right - I read mysql_query where the OP said mysql_result. Don't feel bad. I did the exact same thing when I was reading over the post just now. -- /Dan Daniel P. Brown Senior Unix Geek ? while(1) { $me = $mind--; sleep(86400); } ? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql question
On Feb 10, 2008 12:52 PM, Per Jessen [EMAIL PROTECTED] wrote: nihilism machine wrote: $ret = mysql_result($r, 0); mysql_free_result($r); if ($this-auto_slashes) return stripslashes($ret); else return $ret; } what is $ret, an array? No, it's a mysql result object. no, its the contents of the first cell in the first record of the result set; from the doc on mysql_result(), http://www.php.net/manual/en/function.mysql-result.php which is what the function is using. if so how can i access the individual rows in it? this method does not return a result set to the caller. -nathan
Re: [PHP] mysql question
nihilism machine wrote: $ret = mysql_result($r, 0); mysql_free_result($r); if ($this-auto_slashes) return stripslashes($ret); else return $ret; } what is $ret, an array? No, it's a mysql result object. if so how can i access the individual rows in it? Look up mysql_fetch_assoc(). /Per Jessen, Zürich -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql question
Nathan Nobbe wrote: what is $ret, an array? No, it's a mysql result object. no, its the contents of the first cell in the first record of the result set; from the doc on mysql_result(), http://www.php.net/manual/en/function.mysql-result.php which is what the function is using. Yep, you're right - I read mysql_query where the OP said mysql_result. Ignore the rest of my previous answer too. /Per Jessen, Zürich -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql question #2
On Feb 10, 2008 1:12 PM, nihilism machine [EMAIL PROTECTED] wrote: Ok, I read the php.net info. so with this function though: public function select_one($sql) { $this-last_query = $sql; $r = mysql_query($sql); if (!$r) { $this-last_error = mysql_error(); return false; } if (mysql_num_rows($r) != 1) { return false; } $ret = mysql_result($r, 0); mysql_free_result($r); if ($this-auto_slashes) { return stripslashes($ret); } else { return $ret; } } as the function stands you wont be able to. you can alter it though: public function select_one($sql, $columnName) { $this-last_query = $sql; $r = mysql_query($sql); $ret = false; /// default return value is false if (!$r) { $this-last_error = mysql_error(); return false; } if (mysql_num_rows($r) != 1) { return false; } $result = mysql_fetch_assoc($r); if(isset($result[$columnName])) { $ret = $result[$columnName]); } mysql_free_result($r); if ($this-auto_slashes) { return stripslashes($ret); } else { return $ret; } } note: i just hacked that together in my mail client :) -nathan
Re: [PHP] mysql question #2
2008. 02. 10, vasárnap keltezéssel 13.12-kor nihilism machine ezt írta: Ok, I read the php.net info. so with this function though: public function select_one($sql) { $this-last_query = $sql; $r = mysql_query($sql); if (!$r) { $this-last_error = mysql_error(); return false; } if (mysql_num_rows($r) != 1) { return false; } $ret = mysql_result($r, 0); mysql_free_result($r); if ($this-auto_slashes) { return stripslashes($ret); } else { return $ret; } } how can i get the contents of a column in the returned row say for something called Email as the column name. here is my code now: // Attempt to login a user public function CheckValidUser($Email, $Password) { $PasswordEncoded = $this-encode($Password); $sql = SELECT * FROM CMS_Users WHERE Email='$Email' AND Password='$PasswordEncoded'; $result = $this-DB-select_one($sql); if ($result) { // User info stored in Sessions $_SESSION['Status'] = loggedIn; $_SESSION['ID'] = $row['ID']; $_SESSION['Email'] = $row['Email']; $_SESSION['AdminLevel'] = $row['AdminLevel']; $_SESSION['FirstName'] = $row['FirstName']; $_SESSION['LastName'] = $row['LastName']; return true; } else { return false; } } it seems to me you do not want a real 'select_one' but instead a 'select_one_row' like this: public function select_one_row($sql) { $this-last_query = $sql; $r = mysql_query($sql); if (!$r) { $this-last_error = mysql_error(); return false; } if (mysql_num_rows($r) != 1) { return false; } $ret = mysql_fetch_assoc($r); mysql_free_result($r); if ($this-auto_slashes) { return array_map('stripslashes', $ret); } else { return $ret; } } and then you would call it in your code like: public function CheckValidUser($Email, $Password) { $PasswordEncoded = $this-encode($Password); $sql = SELECT * FROM CMS_Users WHERE Email='$Email' AND Password='$PasswordEncoded'; $row = $this-DB-select_one_row($sql); if ($row) { // User info stored in Sessions $_SESSION['Status'] = loggedIn; $_SESSION['ID'] = $row['ID']; $_SESSION['Email'] = $row['Email']; $_SESSION['AdminLevel'] = $row['AdminLevel']; $_SESSION['FirstName'] = $row['FirstName']; $_SESSION['LastName'] = $row['LastName']; return true; } else { return false; } } note the changes: - use of mysql_fetch_assoc in the select_one_row function - putting the return value of the function into $row and then using that between the if function // this above might contain bugs as I just wrote it up here in my mailer greets Zoltán Németh -- 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
Re: [PHP] mysql question
Well let me try and describe a simplified version of what I am doing. I work for a group of analysts, and they analyse technical products. Each products has about 300+ fields associated with it, and each product is vastly different from the other. Let's say the product foo has a table fields associated with it, which describes each field in foo (its name, description, type[float,boolean,text], order) and another table called data_foo which is 300+ fields in length. Each record in data_foo represents the technical specfiications of a single company/manufacturer. So if there are 200+ companies, there will be at least one data_foo record associated with it (in reality there is a one to many association between a company and the number of technical specifications they have defined).There are many different applications designed around the tables foo and data_foo, and course the relationships are slightly more involved, but I digress. Each new product specification requires that I create a new table to hold that data, and with new product specifications being created every month, you see that the N number of tables will increase. If I store that data in the fields table or create a table that has 1-many relationship with it, I endup with thousands and thousands of records (Remember, each product has 300+ fields associated with it, and each company has at least on product specfication, and the N number of products continues to increase). Any ideas? Miles Thompson [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Christian, A red flag is flying. Usually, when people start talking about how the number of tables will multiply in an application, there is a problem with the design of data structures. If that's the case, now is the time to fix it. Miles At 05:35 PM 1/15/2004 +, Christian Calloway wrote: Hey, Sorry to post a mysql question here, but I wanted an answer from a PHP perspective. I am currently working on a database (for work) which contains 40 tables, and will continue to expand. The reason for expansion of tables is to obscure to get into, but the relationships all do make sence. I have never worked on anything over like 50 tables, and I was wondering if I should expect a performance hit, when say, I have 100 tables, 200 tables, etc. Would it be wiser to break up the logical sections into there own databases? Wouldn't that cause a bigger hit (to performance) with multiple tables from multiple databases being opened on every user query? Thanks in advance Christian -- 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
Re: [PHP] mysql question
Christian, I did a bit of digging in the MySQL docs to try and find the limits on number of fields in a table, etc. Didn't have much luck. I would not worry about thousands, or even hundreds of thousands of rows in a table for two reasons. First MySQL is becoming a heavier-duty database, and second, when the proper indexes are used it is blindingly fast at retrievals. Apparently the database engine also handles a large number of tables quite well. When tables will be multiplying like bunnies, as you posit here, I foresee major code headaches. I've thought about following type of design but have never implemented it. Might it work in your situation? Maybe this is what you are already doing, and I've just relabelled it. If so, please accept my apologies. Have standard tables for the basic stuff.: company, with co_key, address fields etc; products with prod_key, co_key, prod_name, etc. Have two other tables, let's call them prod_descriptors and prod_data. We're violating one of Codd's principles here because the prod_data table has meaningless field names, just f1, f2, f3, f4 ... fn. How you sort out which data types to use is your business. Prod_descriptors contains the meta information which makes prod_data useful. Its fields, at a minimum, would be prod_key, prod_characteristic, prod_data_field. For a given prod_key M56H there would be a record for each product characteristic which you have to track, and the field used in prod_data. This may be a somewhat extreme example, as it is highly likely that there are a common characteristics for all products, so things would not have to be totally generalized. This design is also somewhat wasteful of disk space, but MySQL does a remarkable job, internally, of conserving space so it is used efficiently. The thorny issue of which data types to use in what columns hasn't been addressed either. The crunch is whether or not you can ask questions of these tables and fetch the data you need. At 12:52 PM 1/16/2004 +, Christian Calloway wrote: Well let me try and describe a simplified version of what I am doing. I work for a group of analysts, and they analyse technical products. Each products has about 300+ fields associated with it, and each product is vastly different from the other. Let's say the product foo has a table fields associated with it, which describes each field in foo (its name, description, type[float,boolean,text], order) and another table called data_foo which is 300+ fields in length. Each record in data_foo represents the technical specfiications of a single company/manufacturer. So if there are 200+ companies, there will be at least one data_foo record associated with it (in reality there is a one to many association between a company and the number of technical specifications they have defined).There are many different applications designed around the tables foo and data_foo, and course the relationships are slightly more involved, but I digress. Each new product specification requires that I create a new table to hold that data, and with new product specifications being created every month, you see that the N number of tables will increase. If I store that data in the fields table or create a table that has 1-many relationship with it, I endup with thousands and thousands of records (Remember, each product has 300+ fields associated with it, and each company has at least on product specfication, and the N number of products continues to increase). Any ideas? Miles Thompson [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Christian, A red flag is flying. Usually, when people start talking about how the number of tables will multiply in an application, there is a problem with the design of data structures. If that's the case, now is the time to fix it. Miles At 05:35 PM 1/15/2004 +, Christian Calloway wrote: Hey, Sorry to post a mysql question here, but I wanted an answer from a PHP perspective. I am currently working on a database (for work) which contains 40 tables, and will continue to expand. The reason for expansion of tables is to obscure to get into, but the relationships all do make sence. I have never worked on anything over like 50 tables, and I was wondering if I should expect a performance hit, when say, I have 100 tables, 200 tables, etc. Would it be wiser to break up the logical sections into there own databases? Wouldn't that cause a bigger hit (to performance) with multiple tables from multiple databases being opened on every user query? Thanks in advance Christian -- 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql question
Hey Mike, That is almost exactly what I am doing. I have a table, lets say product_fields which describes each characteristic/field of a product (with N possible fields). A definition of a field includes, its name (as the customer see's it), its type, a named-reference to the field in the data table, and of course the id of the particular product. The products_data table is composed of fields sequentially labeled from f1 to fn, and contains the actual data. The only difference being that I am creating a new products_data table for each product (so something like products_data_$productid), as opposed to storing all field data in a singular large table. So the real question is, is it favorable to have one large table with N number of fields (products * fields), or N number of smaller tables (N products). I have absolutely no idea, but I will do some digging myself. Thanks for the help Christian Miles Thompson [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Christian, I did a bit of digging in the MySQL docs to try and find the limits on number of fields in a table, etc. Didn't have much luck. I would not worry about thousands, or even hundreds of thousands of rows in a table for two reasons. First MySQL is becoming a heavier-duty database, and second, when the proper indexes are used it is blindingly fast at retrievals. Apparently the database engine also handles a large number of tables quite well. When tables will be multiplying like bunnies, as you posit here, I foresee major code headaches. I've thought about following type of design but have never implemented it. Might it work in your situation? Maybe this is what you are already doing, and I've just relabelled it. If so, please accept my apologies. Have standard tables for the basic stuff.: company, with co_key, address fields etc; products with prod_key, co_key, prod_name, etc. Have two other tables, let's call them prod_descriptors and prod_data. We're violating one of Codd's principles here because the prod_data table has meaningless field names, just f1, f2, f3, f4 ... fn. How you sort out which data types to use is your business. Prod_descriptors contains the meta information which makes prod_data useful. Its fields, at a minimum, would be prod_key, prod_characteristic, prod_data_field. For a given prod_key M56H there would be a record for each product characteristic which you have to track, and the field used in prod_data. This may be a somewhat extreme example, as it is highly likely that there are a common characteristics for all products, so things would not have to be totally generalized. This design is also somewhat wasteful of disk space, but MySQL does a remarkable job, internally, of conserving space so it is used efficiently. The thorny issue of which data types to use in what columns hasn't been addressed either. The crunch is whether or not you can ask questions of these tables and fetch the data you need. At 12:52 PM 1/16/2004 +, Christian Calloway wrote: Well let me try and describe a simplified version of what I am doing. I work for a group of analysts, and they analyse technical products. Each products has about 300+ fields associated with it, and each product is vastly different from the other. Let's say the product foo has a table fields associated with it, which describes each field in foo (its name, description, type[float,boolean,text], order) and another table called data_foo which is 300+ fields in length. Each record in data_foo represents the technical specfiications of a single company/manufacturer. So if there are 200+ companies, there will be at least one data_foo record associated with it (in reality there is a one to many association between a company and the number of technical specifications they have defined).There are many different applications designed around the tables foo and data_foo, and course the relationships are slightly more involved, but I digress. Each new product specification requires that I create a new table to hold that data, and with new product specifications being created every month, you see that the N number of tables will increase. If I store that data in the fields table or create a table that has 1-many relationship with it, I endup with thousands and thousands of records (Remember, each product has 300+ fields associated with it, and each company has at least on product specfication, and the N number of products continues to increase). Any ideas? Miles Thompson [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Christian, A red flag is flying. Usually, when people start talking about how the number of tables will multiply in an application, there is a problem with the design of data structures. If that's the case, now is the time to fix it. Miles At 05:35 PM 1/15/2004 +, Christian Calloway wrote: Hey, Sorry to post a mysql question here, but I
Re: [PHP] MySQL Question
From: John Taylor-Johnston [EMAIL PROTECTED] Sorry, don't want to be off-topic, but have found something curious about MySQL 4.0.16-standard. It does not seem to prioritise properly. Searching for 'English Canada' (as opposed to +English +Canada) gives me all instances of both words but does not prioritize the display order for 'English Canada' first and then 'English' then 'Canada'. And how would you know that? SELECT * FROM ccl_main WHERE MATCH (YR,AU,ST,SD,SC,BT,BD,BC,AT,AD,AC,KW,AUS,GEO,AN,RB,CO) AGAINST ('English Canada' IN BOOLEAN MODE) ORDER BY id asc; versus SELECT * FROM ccl_main WHERE MATCH (YR,AU,ST,SD,SC,BT,BD,BC,AT,AD,AC,KW,AUS,GEO,AN,RB,CO) AGAINST ('English Canada' IN BOOLEAN MODE); gives the same order. Given these queries, you're not ordering by the relevance MySQL determines, so you really don't know. You need to also use your MATCH ... AGAINST condition in the SELECT columns and then order by that. SELECT *, MATCH (YR,AU,ST,SD,SC,BT,BD,BC,AT,AD,AC,KW,AUS,GEO,AN,RB,CO) AGAINST ('English Canada' IN BOOLEAN MODE) AS relevancy FROM ccl_main WHERE MATCH (YR,AU,ST,SD,SC,BT,BD,BC,AT,AD,AC,KW,AUS,GEO,AN,RB,CO) AGAINST ('English Canada' IN BOOLEAN MODE) ORDER BY relevancy DESC; Now I can't honestly say that MySQL determines English Canada is more relevant than the two words found by themselves, but this will show you whether it does or not. ---John Holmes... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql question
Christian, A red flag is flying. Usually, when people start talking about how the number of tables will multiply in an application, there is a problem with the design of data structures. If that's the case, now is the time to fix it. Miles At 05:35 PM 1/15/2004 +, Christian Calloway wrote: Hey, Sorry to post a mysql question here, but I wanted an answer from a PHP perspective. I am currently working on a database (for work) which contains 40 tables, and will continue to expand. The reason for expansion of tables is to obscure to get into, but the relationships all do make sence. I have never worked on anything over like 50 tables, and I was wondering if I should expect a performance hit, when say, I have 100 tables, 200 tables, etc. Would it be wiser to break up the logical sections into there own databases? Wouldn't that cause a bigger hit (to performance) with multiple tables from multiple databases being opened on every user query? Thanks in advance Christian -- 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
Re: [PHP] Mysql question
Creating new databases is usually done with the mysql root account. Bad idea to use this account in a php script. Lists wrote: I have a db in sql, and I need a php/mysql query/command to copy the entire db (schema and data) to a new db. I know how to do this with a table, but I can not figure out how to do this with a whole db. I also know that I could do it using mysql dump, but I don't want to have to run a shell command. Please help, MIchael -- Raditha Dissanayake. http://www.radinks.com/sftp/ | http://www.raditha.com/megaupload Lean and mean Secure FTP applet with | Mega Upload - PHP file uploader Graphical User Inteface. Just 150 KB | with progress bar. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql question
This is for an intranet application, I trust my users. Michael On Wed, 19 Nov 2003, Raditha Dissanayake wrote: Creating new databases is usually done with the mysql root account. Bad idea to use this account in a php script. Lists wrote: I have a db in sql, and I need a php/mysql query/command to copy the entire db (schema and data) to a new db. I know how to do this with a table, but I can not figure out how to do this with a whole db. I also know that I could do it using mysql dump, but I don't want to have to run a shell command. Please help, MIchael -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Mysql question
I don't believe there is one command that does all this, so you've already ruled out one good option, but you can use PHPMyAdmin. Do you have PHPMyAdmin installed on either or both sites? Warren -Original Message- From: Lists [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 5:37 PM To: [EMAIL PROTECTED] Subject: [PHP] Mysql question I have a db in sql, and I need a php/mysql query/command to copy the entire db (schema and data) to a new db. I know how to do this with a table, but I can not figure out how to do this with a whole db. I also know that I could do it using mysql dump, but I don't want to have to run a shell command. Please help, MIchael -- 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
Re: [PHP] Mysql question
Lists wrote: I have a db in sql, and I need a php/mysql query/command to copy the entire db (schema and data) to a new db. I know how to do this with a table, but I can not figure out how to do this with a whole db. I also know that I could do it using mysql dump, but I don't want to have to run a shell command. Please help, MIchael There may be a shorter way, but the only thing I can think of at the moment is to do this via multiple queries ie query to create new database query to read tables and structure in old db queries to create new tables in new db queries to dump data from old tables queries to load data to new tables You may want to try the mysql list to see if this can be done in one query. -- By-Tor.com It's all about the Rush http://www.by-tor.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql question
Hi John, ASAIK you are right. Michael, if you use the root account in php you will be able to do what John says. John Nichel wrote: Lists wrote: I have a db in sql, and I need a php/mysql query/command to copy the entire db (schema and data) to a new db. I know how to do this with a table, but I can not figure out how to do this with a whole db. I also know that I could do it using mysql dump, but I don't want to have to run a shell command. Please help, MIchael There may be a shorter way, but the only thing I can think of at the moment is to do this via multiple queries ie query to create new database query to read tables and structure in old db queries to create new tables in new db queries to dump data from old tables queries to load data to new tables You may want to try the mysql list to see if this can be done in one query. -- Raditha Dissanayake. http://www.radinks.com/sftp/ | http://www.raditha.com/megaupload Lean and mean Secure FTP applet with | Mega Upload - PHP file uploader Graphical User Inteface. Just 150 KB | with progress bar. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql question
Lists wrote: I have a db in sql, and I need a php/mysql query/command to copy the entire db (schema and data) to a new db. I know how to do this with a table, but I can not figure out how to do this with a whole db. I also know that I could do it using mysql dump, but I don't want to have to run a shell command. You'll need to do it with PHP... Here's one method... ?php //connect to database $database = 'xxx'; //name of database to copy $ndatabase = 'yyy'; //name of new database mysql_query(CREATE DATABASE $ndatabase); $rs = mysql_query(SHOW TABLES FROM $database); while($r = mysql_fetch_row($rs)) { mysql_select_db($database); $rs2 = mysql_query(SHOW CREATE TABLE {$r[0]}); $ctable = myqsl_result($rs,0); mysql_select_db($ndatabase); mysql_query($ctable); mysql_query(INSERT INTO {$r[0]} SELECT * FROM {$database}.{$r[0]}); } ? Untested... but should work (when run as a user with suitable permissions). :) -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql question
On Tue, 18 Nov 2003, Lists wrote: I have a db in sql, and I need a php/mysql query/command to copy the entire db (schema and data) to a new db. I know how to do this with a table, but I can not figure out how to do this with a whole db. I also know that I could do it using mysql dump, but I don't want to have to run a shell command. Have you tried mysqlhotcopy? http://www.mysql.com/doc/en/mysqlhotcopy.html -- Greg Donald http://destiney.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql question
Yes...off topic... Join the table with itself. untested... SELECT t1.shopnumber, t1.item from table t1, table t2 where t1.shopnumber = 1 and t1.shopnumber = t2.shopnumber and t1.item != t2.item Something like that?? Play around with it... ---John Holmes... - Original Message - From: scott [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, October 30, 2002 3:43 PM Subject: [PHP] mysql question Very slightly OT but great minds here I have a table with two columns and I need to get all the items that are not in the shop number I select, with the exception of items that are in the shop number I select. Table Shopnumber item 1 orange 1 banana 1 apple 1 pear 2 grape 2 coca cola 2 pepsi 3 orange 4 orange 4 pepsi 4 7 up 4 sunny delite I need to be able to work out all the items that are in the other shops. For example if a customer picks orange from shopnumber 1 I need to get a result that has all items in all shops except shop1 The problem is my query still picks items that are in shop1 if they are in another shop as well, which is not what I need My current query is select * from table where shopnumber!=$shoprvar. As an example if I use select * from table where shopnumber!=1 I would get grape, coca-cola,pepsi (x2),7 up, sunny delite and orange (x2). I don't want orange because it is in shop1! Help and BIG THANKS for the ANSWER? Scott -- 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
Re: [PHP] mysql question
From: Christian Calloway [EMAIL PROTECTED] Sorry this may be a little offtopic, but I am currently moving a site I was developing from coldfusion+MSAccess to PHP+MySQL. I remembered reading somewhere that there is a utility that will convert/transfer (data and structure) a MSAcess database to Mysql, and vice versa. Anyone know? Thanks MS Access2MySQL Converter is at http://www.dmsofttech.com/downloads.html. -- Lowell Allen -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql question
Congrats! Good choice! Take a look here: http://www.convert-in.com/acc2sql.htm or here: http://www.google.com/search?hl=enlr=ie=ISO-8859-1q=convert+access+databa se+to+mysql Jim Grill Support Web-1 Hosting http://www.web-1hosting.net - Original Message - From: Christian Calloway [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, July 26, 2002 3:30 PM Subject: [PHP] mysql question Sorry this may be a little offtopic, but I am currently moving a site I was developing from coldfusion+MSAccess to PHP+MySQL. I remembered reading somewhere that there is a utility that will convert/transfer (data and structure) a MSAcess database to Mysql, and vice versa. Anyone know? Thanks Chris -- 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
Re: [PHP] mysql question
ðÒÉ×ÅÔ! John Holmes wrote: Gesundheit *if* that was on Oracle *and* the table was big you'd notice that your performance goes down. Don't ask me why. And I never checked it on MySql. But watch out for betweens. Check them. Yes, good point. I don't know if it matters in MySQL either, but always test your queries and see which is faster. EXPLAIN may come in handy here. I don't see why it would be different, it seems like both would be interpreted the same... Most PHP apps underlying storage simply does not reach the dimensions needed for the difference to show up (that is, when rows come in hundred of thousands and you have a fairly good normalized data structure, say at least in the third form). Yest if you don't normalize data you might see it degrade when you are just in the pale realm of thousands. For what I could make out of it myself, it has to do with the fact that an SQL macro instruction (if you pardon the use of the macro term in this context) needs to be translated to some set of and...or clauses before the engine can actually execute it. Some engine does the translation on a per row basis, which is why they can end-up killing your performance. That's just my own fantasy about it, no such assertion is in *any* docs that I saw. But if it was done just once the size of your query set should be irrilevant to performance, methinks. ÐÏËÁ áÌØÂÅÒÔÏ ëÉÅ× -_=}{=_-@-_=}{=_--_=}{=_-@-_=}{=_--_=}{=_-@-_=}{=_--_=}{=_- LoRd, CaN yOu HeAr Me, LiKe I'm HeArInG yOu? lOrD i'M sHiNiNg... YoU kNoW I AlMoSt LoSt My MiNd, BuT nOw I'm HoMe AnD fReE tHe TeSt, YeS iT iS ThE tEsT, yEs It Is tHe TeSt, YeS iT iS ThE tEsT, yEs It Is... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql question
Yes, though I'd probably add parentheses to make it clearer. select * from table where ((start_shot = $current_shot) and (end_shot = $current_shot)) Though that's probably not necessary. Make certain you have your less than's and greater thans set the right way, I've often sat slack-mouthed wondering why I had no data, simply because I was using when it should have been . Cheers - Miles Thompson At 12:06 PM 7/11/2002 -0400, Alexander Ross wrote: I realize this isn't a php question, but I figured that someone here knows of a good mysql newsgroup and in the mean time someone here probaby knows the answer to my question. Can I set up a query like this: select * from table where start_shot = $current_shot and end_shot = $current_shot note everything will be of type INT Thanks ya'll -- 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
Re: [PHP] mysql question
How about SELECT * FROM table WHERE $current_shot BETWEEN start_shot AND end_shot Also, I hope you are validating $current_shot if register globals is on, to make sure it's really just an integer and not some extra SQL that'll allow someone to view your entire table/database... ---John Holmes... - Original Message - From: Miles Thompson [EMAIL PROTECTED] To: Alexander Ross [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, July 11, 2002 12:24 PM Subject: Re: [PHP] mysql question Yes, though I'd probably add parentheses to make it clearer. select * from table where ((start_shot = $current_shot) and (end_shot = $current_shot)) Though that's probably not necessary. Make certain you have your less than's and greater thans set the right way, I've often sat slack-mouthed wondering why I had no data, simply because I was using when it should have been . Cheers - Miles Thompson At 12:06 PM 7/11/2002 -0400, Alexander Ross wrote: I realize this isn't a php question, but I figured that someone here knows of a good mysql newsgroup and in the mean time someone here probaby knows the answer to my question. Can I set up a query like this: select * from table where start_shot = $current_shot and end_shot = $current_shot note everything will be of type INT Thanks ya'll -- 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql question
ðÒÉ×ÅÔ! 1LT John W. Holmes wrote: How about SELECT * FROM table WHERE $current_shot BETWEEN start_shot AND end_shot *if* that was on Oracle *and* the table was big you'd notice that your performance goes down. Don't ask me why. And I never checked it on MySql. But watch out for betweens. Check them. ÐÏËÁ áÌØÂÅÒÔÏ ëÉÅ× -_=}{=_-@-_=}{=_--_=}{=_-@-_=}{=_--_=}{=_-@-_=}{=_--_=}{=_- LoRd, CaN yOu HeAr Me, LiKe I'm HeArInG yOu? lOrD i'M sHiNiNg... YoU kNoW I AlMoSt LoSt My MiNd, BuT nOw I'm HoMe AnD fReE tHe TeSt, YeS iT iS ThE tEsT, yEs It Is tHe TeSt, YeS iT iS ThE tEsT, yEs It Is... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql question
ðÒÉ×ÅÔ! Alexander Ross wrote: I realize this isn't a php question, but I figured that someone here knows of a good mysql newsgroup and in the mean time someone here probaby knows the answer to my question. Can I set up a query like this: select * from table where start_shot = $current_shot and end_shot = $current_shot note everything will be of type INT Yes, you can, providing that those fields would exist and be of a comparable type. Just one question, wasn't it quicker to just give it a try? :) ÐÏËÁ áÌØÂÅÒÔÏ ëÉÅ× -_=}{=_-@-_=}{=_--_=}{=_-@-_=}{=_--_=}{=_-@-_=}{=_--_=}{=_- LoRd, CaN yOu HeAr Me, LiKe I'm HeArInG yOu? lOrD i'M sHiNiNg... YoU kNoW I AlMoSt LoSt My MiNd, BuT nOw I'm HoMe AnD fReE tHe TeSt, YeS iT iS ThE tEsT, yEs It Is tHe TeSt, YeS iT iS ThE tEsT, yEs It Is... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql question
ðÒÉ×ÅÔ! Gesundheit 1LT John W. Holmes wrote: How about SELECT * FROM table WHERE $current_shot BETWEEN start_shot AND end_shot *if* that was on Oracle *and* the table was big you'd notice that your performance goes down. Don't ask me why. And I never checked it on MySql. But watch out for betweens. Check them. Yes, good point. I don't know if it matters in MySQL either, but always test your queries and see which is faster. EXPLAIN may come in handy here. I don't see why it would be different, it seems like both would be interpreted the same... ---John Holmes... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MySQL question
In brief, and in no way finished (no error trapping, commenting, etc)... ? $db = mysql_connect(localhost, root); mysql_select_db(test,$db); $result = mysql_query(SELECT CountryID, CountryName FROM tblcountries,$db); echo table border=1\n; echo tr\n; echo tdCatNumber/td\n; echo tdTitle/td\n; echo /tr\n; while ($myrow = mysql_fetch_row($result)) { echo tr\n; echo td$myrow[0]/td\n; echo td$myrow[1]/td\n; echo /tr\n; } echo /table\n; ? hth, .ben -Original Message- From: Mantas Kriauciunas [mailto:[EMAIL PROTECTED]] Sent: 22 April 2002 01:07 To: PHP General List Subject: [PHP] MySQL question Hey PHP General List, Amm... can anybody point me to some good tutorial that talks about SELECT from detabase? what i need is ... like i have table with lots of rows and i need to output them all to the page... it goes like most of news sections in the page... thanks for help. (i need to learn more about SELECT'ing things from database) :--: Have A Nice Day! Mantas Kriauciunas A.k.A mNTKz Contacts: [EMAIL PROTECTED] Http://mntkz-hata.visiems.lt -- 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
RE: [PHP] mysql question
mysql is a relational database. therefore, the concept of a row number is irrelevant. that said, what do you REALLY want to do -Original Message- From: Julian [mailto:[EMAIL PROTECTED]] Sent: Monday, April 08, 2002 2: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
Re: [PHP] mysql question
On Mon, 8 Apr 2002, Julian wrote: I want to know if there is a function to know which is the number of the row that I selected. Not in MySQL. You should add an auto_increment index field to your table, and then you can always use that to see where you are. miguel -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
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
[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
Re: [PHP] mysql question
Hello, The length of VARCHAR must be specified like; CREATE TABLE sailordata ( sailorid INT(8) NOT NULL DEFAULT 0, lastname VARCHAR(255) NOT NULL, firstname VARCHAR(255) NOT NULL, middlenames VARCHAR(255) NOT NULL, dob VARCHAR(255) NOT NULL, telephone VARCHAR(255) NOT NULL, fax VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL); Regards, Hiroshi Ayukawa http://hoover.ktplan.ne.jp/kaihatsu/php_en/index.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL question...not sure if this is the correct forum to ask.
Peter, I have a quick MySQL question...if this is not the correct forum for it, then someone please point me to the right one. Can the UPDATE statement have conditional check embedded in it? I have a page that displays a record (in a FORM format) that the user can change the information on each column. I want to check each column and see which has been changed and update the table for entries that were changed only. for each column data { if column is changed then update; else do nothing; } Maybe I am making this too complicated than it needs and just go ahead and update all of the columns regardless with the new values, regardless they are actually different or not. There is a MySQL list, and a separate PHP-DB list. UPDATE is 'intelligent', it will only modify the row if there is something to change. Regards, =db -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql question
Yes, sorry by the off-topic but I wanted to know if wnybody here could help me because in mysql I couldn´t find any answer. Also I have seen a lot of questions relative to mysql answered here. But you´re right it´s a off-topic. Emiliano. -Original Message- From: Sam Masiello [EMAIL PROTECTED] To: Emiliano Marmonti [EMAIL PROTECTED] Date: Wednesday, February 06, 2002 12:45 PM Subject: Re: [PHP] Mysql question This is really a question for a MySQL mailing list, not a PHP list since this question has nothing to do with PHP. You can email the MySQL mailing list at [EMAIL PROTECTED], and you can join the MySQL mailing list off of the MySQL web site at www.mysql.com. HTH Sam Masiello Software Quality Assurance Engineer Synacor (716) 853-1362 X289 [EMAIL PROTECTED] - Original Message - From: Emiliano Marmonti [EMAIL PROTECTED] To: Lista PHP [EMAIL PROTECTED] Sent: Wednesday, February 06, 2002 4:32 AM Subject: [PHP] Mysql question I have a site using PHP Mysql. About a month ago to now one of the tables gets corrupted with the message Got Error 127... I fix it and everything works OK, except every time I loose 1 record. Today I could obtain mysql.err from the machine and I could see whenever an error is produced by date, previously I have the following line: 020205 9:57:46 Aborted connection 137 to db: 'Biblioteca' user: 'ODBC' host: `localhost' (Server shutdown in progress) 020205 9:57:46 Aborted connection 124 to db: 'Biblioteca' user: 'ODBC' host: `localhost' (Server shutdown in progress) or 020205 12:55:51 Aborted connection 377 to db: 'Biblioteca' user: 'ODBC' host: `localhost' (Unknown error) 020205 13:05:37 Aborted connection 504 to db: 'Biblioteca' user: 'ODBC' host: `localhost' (Unknown error) It doesn´t seems too clear for me because no ODBC client should be accessing to the database. Should I suppose that an unautorized ODBC client is breaking the table or could be another problem? Thanks a lot. Emiliano. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL Question....
How about actually using distinct? SELECT DISTINCT(email_addr) FROM myTable WHERE x = 1 --Toby - Original Message - From: "Jason Caldwell" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, April 18, 2001 1:47 AM Subject: [PHP] mySQL Question if i perform a SELECT query (say) like the following: SELECT email_addr FROM myTable WHERE x = 1 in this query i want to pull-out (or list) all email addresses where x equals 1, however, suppose i don't want any duplicate email addresses... would i use ORDER BY and COUNT(*) to get listing of non-duplicate email addresses, and just ignore the COUNT() ? Thanks. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] mySQL Question....
if i perform a SELECT query (say) like the following: SELECT email_addr FROM myTable WHERE x = 1 in this query i want to pull-out (or list) all email addresses where x equals 1, however, suppose i don't want any duplicate email addresses... would i use ORDER BY and COUNT(*) to get listing of non-duplicate email addresses, and just ignore the COUNT() ? You would use neither :) To get the distict email addressese you can use SELECT email_addr FROM myTable WHERE x = 1 GROUP BY email_addr Check MySQL manual for SELECT options for more info. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] mySQL Question....
Opps. I meant GROUP BY... and it looks like I don't need to include the COUNT(). SELECT email_addr, x GROUP BY email_addr HAVING x = 1; Is this the only way to display a listing without duplicates, or is there a more efficient (faster) way? I don't want to DELETE the duplicates, just don't want dups to show up in my SELECT queries. Thanks. Jason ""Jason Caldwell"" [EMAIL PROTECTED] wrote in message 9bj9ld$pad$[EMAIL PROTECTED]">news:9bj9ld$pad$[EMAIL PROTECTED]... if i perform a SELECT query (say) like the following: SELECT email_addr FROM myTable WHERE x = 1 in this query i want to pull-out (or list) all email addresses where x equals 1, however, suppose i don't want any duplicate email addresses... would i use ORDER BY and COUNT(*) to get listing of non-duplicate email addresses, and just ignore the COUNT() ? Thanks. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] mySQL Question....
On 18-Apr-01 Jason Caldwell wrote: Opps. I meant GROUP BY... and it looks like I don't need to include the COUNT(). SELECT email_addr, x GROUP BY email_addr HAVING x = 1; Is this the only way to display a listing without duplicates, or is there a more efficient (faster) way? I don't want to DELETE the duplicates, just don't want dups to show up in my SELECT queries. Thanks. Jason select distinct(email_addr) ... -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Mysql Question
Can i know the date of the last table update in a Mysql database? You can add a field named 'modified' that is of type 'timestamp' and use: select max(modified) from blah; I don't know of any documented way to check the last modified time for a whole table. -- Visit the Zend Store at http://www.zend.com/store/ Wanna help me out? Like Music? Buy a CD: http://l-i-e.com/artists.htm Volunteer a little time: http://chatmusic.com/volunteer.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]