ptholt Posted June 10, 2008 Share Posted June 10, 2008 Hi folks, have we got anyone experienced in sql that can assist with the following request please - i would like to search through "product description" field and locate all the records that are post 1953 (year is in product description only, not title) and for those records make the "tax class" to be set to none. cheers. Link to comment Share on other sites More sharing options...
♥geoffreywalton Posted June 10, 2008 Share Posted June 10, 2008 Try www.w3schools.com, the sql command will be in this fomat Update table name set tax class = "none" where field name > data but use phpmy admin to find the table and field names. While you are there use the search facility to see how the test should be formatted as after you run a search the sql is displayed on the screen for you. Then when you have worked out your command use the sql option in phpmyadmin. Enjoy Need help installing add ons/contributions, cleaning a hacked site or a bespoke development, check my profile Virus Threat Scanner My Contributions Basic install answers. Click here for Contributions / Add Ons. UK your site. Site Move. Basic design info. For links mentioned in old answers that are no longer here follow this link Useful Threads. If this post was useful, click the Like This button over there ======>>>>>. Link to comment Share on other sites More sharing options...
ptholt Posted June 11, 2008 Author Share Posted June 11, 2008 Try www.w3schools.com, the sql command will be in this fomat Update table name set tax class = "none" where field name > data but use phpmy admin to find the table and field names. While you are there use the search facility to see how the test should be formatted as after you run a search the sql is displayed on the screen for you. Then when you have worked out your command use the sql option in phpmyadmin. Enjoy Thanks thats given me somewhere to start, a friend also suggest using navicat which appears to be a query builder so will try that as well. Link to comment Share on other sites More sharing options...
ptholt Posted June 11, 2008 Author Share Posted June 11, 2008 hmm ok, not quite as easy as id hoped as the field to change product_tax_class_is is in the the products table, yet the product description is in the table products_description........... Link to comment Share on other sites More sharing options...
Guest Posted June 11, 2008 Share Posted June 11, 2008 Hi, Peter, Not to plug, but I like NaviCat so much I became a reseller and recommend it to many of my clients. Using NaviCat, or phpMyAdmin, you would have to do an inner join, something like this: update products p inner join products_description as pd on p.products_id = pd.products_id set p.products_tax_class_id= 0 where instr(pd.products_description,'1953')>0 Also, PLEASE, PLEASE, PLEASE backup up your database before running any queries against it! Have fun! hmm ok, not quite as easy as id hoped as the field to change product_tax_class_is is in the the products table, yet the product description is in the table products_description........... Link to comment Share on other sites More sharing options...
ptholt Posted June 12, 2008 Author Share Posted June 12, 2008 Hi Jon, thanks for your assistance, i cant use navicat as my host will not open server access (ssh or telnet) to the database to allow me to use it :( I backed up the database and attempted the query below, only 11 affected rows (which doesnt sound right with nearly 4000 items, approx half of which being post 1953) Some items changed there tax class, others did not :blink: Hi, Peter, Not to plug, but I like NaviCat so much I became a reseller and recommend it to many of my clients. Using NaviCat, or phpMyAdmin, you would have to do an inner join, something like this: update products p inner join products_description as pd on p.products_id = pd.products_id set p.products_tax_class_id= 0 where instr(pd.products_description,'1953')>0 Also, PLEASE, PLEASE, PLEASE backup up your database before running any queries against it! Have fun! Link to comment Share on other sites More sharing options...
Guest Posted June 12, 2008 Share Posted June 12, 2008 Afternoon, Peter! Bummer on not being able to use NaviCat, it makes MySQL admin much, much easier. Sorry the query didn't catch them all, but, truth be told, doing record changes based upon an 'instr' is sloppy, at best. You're relying on that exact string being in just the records you want to effect, and NOT in any records you don't want to effect. A better approach would be if they have a sequential run of product ids, or maybe are unique to one category? Even if they all have a unique attribute which is only assigned to these 'post 1953' products. What you're looking for is a defined way to isolate them that is not subject to any ambiguity. Let me know! Hi Jon, thanks for your assistance, i cant use navicat as my host will not open server access (ssh or telnet) to the database to allow me to use it :( I backed up the database and attempted the query below, only 11 affected rows (which doesnt sound right with nearly 4000 items, approx half of which being post 1953) Some items changed there tax class, others did not :blink: Link to comment Share on other sites More sharing options...
ptholt Posted June 12, 2008 Author Share Posted June 12, 2008 Evening Jon, the only records to effect are those containing a number larger that 1953 in the prod description, none that ive seen have any other chars so cant think what caused it to effect so few :huh: the product ids are non sequential as the products were entered in by geographic area (which is also the categories) not year, and at the time year was considered unimportant and not worth its own database field double doh! The only way i can think of pin pointing the right products was by the year entered in the product description field. Afternoon, Peter! Bummer on not being able to use NaviCat, it makes MySQL admin much, much easier. Sorry the query didn't catch them all, but, truth be told, doing record changes based upon an 'instr' is sloppy, at best. You're relying on that exact string being in just the records you want to effect, and NOT in any records you don't want to effect. A better approach would be if they have a sequential run of product ids, or maybe are unique to one category? Even if they all have a unique attribute which is only assigned to these 'post 1953' products. What you're looking for is a defined way to isolate them that is not subject to any ambiguity. Let me know! Link to comment Share on other sites More sharing options...
Guest Posted June 12, 2008 Share Posted June 12, 2008 Hi, again, Peter... Is the site live? If so, mind shooting me a link? Here, PM, or email, whatever. And may be a single example of one of the products to update. You know, as another thought, have you looked at the EZ Product Update contribution? I'll see if I can find a link, I've used it on one cart, and it would be good for this.... Evening Jon, the only records to effect are those containing a number larger that 1953 in the prod description, none that ive seen have any other chars so cant think what caused it to effect so few :huh: the product ids are non sequential as the products were entered in by geographic area (which is also the categories) not year, and at the time year was considered unimportant and not worth its own database field double doh! The only way i can think of pin pointing the right products was by the year entered in the product description field. Link to comment Share on other sites More sharing options...
ptholt Posted June 12, 2008 Author Share Posted June 12, 2008 you have a pm :) Hi, again, Peter... Is the site live? If so, mind shooting me a link? Here, PM, or email, whatever. And may be a single example of one of the products to update. You know, as another thought, have you looked at the EZ Product Update contribution? I'll see if I can find a link, I've used it on one cart, and it would be good for this.... Link to comment Share on other sites More sharing options...
Guest Posted June 13, 2008 Share Posted June 13, 2008 Peter... oh, man, it's so simple now, we're both speaking English, and yet there's still a language barrier :lol: Here's the deal... in your original message, you stated 'post 1953'. Now I've realized, you're using 'post' as a synonym for 'after', aren't you? I was interpreting it literally, like a mechanical part. Unfortunately, that doesn't make your task much easier, but at least we know why the query didn't work. The update query we used earlier is quite literal, it's looking for any product with '1953' in the description. You'd have to rerun the query for each year string ('1954','1955', etc.), changing the year string each pass. However, in reviewing your site, it looks like the year is actually the first four characters of each product name, as well. Is that universal? If it is, it makes the SQL clause a little cleaner. you have a pm :). Link to comment Share on other sites More sharing options...
ptholt Posted June 13, 2008 Author Share Posted June 13, 2008 Morning Jon, i do recall a trip to the usa where i was ordering pizza to a perfectly spoken young american guy, who then stopped me, apologised and said he would have to get someone to speak to me in English as he didn't understand, i was :blink: as you say by post i meant anything after 1953, i did incorrectly think that > was referring to greater than 1953 and would change anything above it, i guess not hehe The year is not universally the first four chars, we had 2-3 people performing data entry along with some imports from an older site so i dont think year being the first four chars can be relied upon, so i will try to run it once for each year, sounds like a fun job. Many thanks once again for your help Peter... oh, man, it's so simple now, we're both speaking English, and yet there's still a language barrier :lol: Here's the deal... in your original message, you stated 'post 1953'. Now I've realized, you're using 'post' as a synonym for 'after', aren't you? I was interpreting it literally, like a mechanical part. Unfortunately, that doesn't make your task much easier, but at least we know why the query didn't work. The update query we used earlier is quite literal, it's looking for any product with '1953' in the description. You'd have to rerun the query for each year string ('1954','1955', etc.), changing the year string each pass. However, in reviewing your site, it looks like the year is actually the first four characters of each product name, as well. Is that universal? If it is, it makes the SQL clause a little cleaner. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.