#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    153
    Rep Power
    5

    How do i find a specific string in an entire db?


    Hello all,

    Hope this is the right place for the question, apologies if its not.

    I imagine the question posed above is possible, but I'm not 100% sure. I have no idea what to write as I'm new to SQL.

    Could someone please help me in trying to answer this question.

    Im looking to search a MySQL database for the following string
    "Grinding and sharpening".

    Your help as always is greatly appreciated and welcome.

    Kind regards
    MG
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    first, you will have to do some investigation and determine all the tables in the database

    then, for each table, you will have to do some investigation and determine all the character columns which are long enough to hold the string you're looking for

    then, for each table, write a query that looks like this --
    Code:
    SELECT * 
      FROM daTable
     WHERE column3 LIKE '%Grinding and sharpening%'
        OR column5 LIKE '%Grinding and sharpening%'
        OR column7 LIKE '%Grinding and sharpening%'
    in this example, column3, column5, and column7 are the character columns that are longer than 23 characters

    repeat for every table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    153
    Rep Power
    5
    Hi thanks for the reply.

    So once i know which tables allow the length of string, use the query you provided on all tables, changing 'daTable' to the name of the table.

    Thanks and regards
    MG
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    153
    Rep Power
    5
    Hello All,

    please bare with me here, as im not sure if i'll be clearly expressing what i mean.

    When i've used SQL server in past, i've been able to view the entire table as one large diagram, and see how all the tables connect to one another. is this possible in MySQL?

    kind regards
    MG
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by mind_grapes
    is this possible in MySQL?
    sure, you just need some reverse-engineer software to do it

    it's separate from the actual mysql databaser install

    (the sql server software to reverse-engineer a diagram isn't part of the actual database system either)


    p.s. for your information...

    bear with me = have patience
    bare with me = let's get undressed together

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    153
    Rep Power
    5
    hahahahaha, sorry, i've been making so many mistakes lately. reading it back, I know its wrong, but i'm not sure why i wrote it. im getting lazy. thanks for correcting me - respect.

    thanks for your help. i think i've found the issue...i think. *nervous smile*

    i followed the db angle first as someone suggested it could be to do with that - apparently we've had this issue before, and I only joined the company recently. However, that was back in 2007 so he wasn't too sure.

    No self deprecation intended, merely truth, but im not very good at this programming business. So, with that said, I think I need to do the following.

    I'm using a system called aMember, but for some reason, something created in the back-end isn't being replicated on the front-end. This is the crux of the problem. I think I need to add this checkbox to the .html form and then include a variable to send to the db.

    Im not sure if that is everything, but i guess i have a starting point. problem is there is no test environment, so all changes will have to be made on the live site and this makes me nervous.

    Also, i dont know where to include the S11 - Site Staff. it needs to go in the following i believe. but where abouts? my guess is in red, but ive no idea what those s:29, s:11 etc mean. any ideas?

    Accreditation areas";s:4:"type";s:8:"checkbox";s:11:"description";s:0:"";s:13:"validate_func";s:0:"";s:17:"additio nal_fields";a:11:{s:3:"sql";i:0;s:8:"sql_type";s:4:"BLOB";s:4:"size";s:2:"20";s:7:"default";a:0:{}s: 7:"options";a:43:{s:17:"PHS - Primary H&S";s:17:"PHS - Primary H&S";s:25:"SCHS - Secondary Core H&S";s:25:"SCHS - Secondary Core H&S";s:25:"SFHS - Secondary Food H&S";s:25:"SFHS - Secondary Food H&S";s:30:"SMHS - Secondary Materials H&S";s:30:"SMHS - Secondary Materials H&S";s:17:"SMHS - Hand tools";s:17:"SMHS - Hand tools";s:24:"SMHS - Drilling machines";s:24:"SMHS - Drilling machines";s:39:"SMHS - Guillotines, shears and trimmers";s:39:"SMHS - Guillotines, shears and trimmers";s:33:"SMHS - Off-hand grinding machines";s:33:"SMHS - Off-hand grinding machines";s:33:"SMHS - Sanding/linishing machines";s:33:"SMHS - Sanding/linishing machines";s:16:"SMHS - Mortisers";s:16:"SMHS - Mortisers";s:35:"SMHS - Polishing (buffing) machines";s:35:"SMHS - Polishing (buffing) machines";s:22:"SMHS - Power fret saws";s:22:"SMHS - Power fret saws";s:44:"SMHS - Power hacksaws/metal cutting bandsaws";s:44:"SMHS - Power hacksaws/metal cutting bandsaws";s:21:"SMHS - Heat processes";s:21:"SMHS - Heat processes";s:34:"SMHS - Plastics moulding processes";s:34:"SMHS - Plastics moulding processes";s:27:"SMHS - Portable power tools";s:27:"SMHS - Portable power tools";s:40:"SSHS - Secondary Systems and Control H&S";s:40:"SSHS - Secondary Systems and Control H&S";s:29:"STHS - Secondary Textiles H&S";s:29:"STHS - Secondary Textiles H&S";s:27:"S1 - Narrow Band - Band Saw";s:27:"S1 - Narrow Band - Band Saw";s:28:"S1 - Circular - Circular Saw";s:28:"S1 - Circular - Circular Saw";s:23:"S1 - Vertical Panel Saw";s:23:"S1 - Vertical Panel Saw";s:19:"S1 - Power Fret Saw";s:19:"S1 - Power Fret Saw";s:33:"S1 - Radial Arm - Chop Radial Arm";s:33:"S1 - Radial Arm - Chop Radial Arm";s:17:"S2 - Centre Lathe";s:17:"S2 - Centre Lathe";s:31:"S3 - Casting non-ferrous metals";s:31:"S3 - Casting non-ferrous metals";s:8:"S4 - MMA";s:8:"S4 - MMA";s:8:"S4 - MIG";s:8:"S4 - MIG";s:8:"S4 - MAG";s:8:"S4 - MAG";s:8:"S4 - TIG";s:8:"S4 - TIG";s:36:"S5 - Oxy-acetylene welding & cutting";s:36:"S5 - Oxy-acetylene welding & cutting";s:13:"S6 - Vertical";s:13:"S6 - Vertical";s:15:"S6 - Horizontal";s:15:"S6 - Horizontal";s:8:"S6 - CNC";s:8:"S6 - CNC";s:23:"S7 - Wood turning lathe";s:23:"S7 - Wood turning lathe";s:20:"S8 - Overhand Planer";s:20:"S8 - Overhand Planer";s:16:"S8 - Thicknesser";s:16:"S8 - Thicknesser";s:21:"S9 - Portable grinder";s:21:"S9 - Portable grinder";s:26:"S9 - Rotating portable saw";s:26:"S9 - Rotating portable saw";s:31:"S9 - Reciprocating portable saw";s:31:"S9 - Reciprocating portable saw";s:19:"S9 - Biscuit cutter";s:19:"S9 - Biscuit cutter";s:29:"S9 - Portable planing machine";s:29:"S9 - Portable planing machine";s:20:"S9 - Portable router";s:20:"S9 - Portable router";s:29:"S10 - Grinding and sharpening";s:29:"S10 - Grinding and sharpening";s:29:"S11 - Site Staff ";s:29:"S11 - Site Staff";}s:4:"cols";s:2:"20";s:4:"rows";s:1:"5";s:14:"display_signup";s:1:"0";s:15:"display_profile";s:1:"0" ;s:24:"display_affiliate_signup";s:1:"0";s:25:"display_affiliate_profile";s:1:"0";}}}\' where name=\'member_fields\'; ';



    Sorry if this follow up questions is in the wrong section. If it is please let me know and ill move it to the php area. I think that's where it needs to be.

    Thank you all.



    Kind regards
    MG
    Last edited by mind_grapes; January 28th, 2013 at 09:25 AM.
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    sorry, man, i have no idea what your problem is, but chances are good it isn't a database problem

    that humoungous block of ugly code can only mean somebody decided to serialize a lot of information into a single TEXT or BLOB column, so you should really contact the creator or vendor of your application
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    153
    Rep Power
    5
    No worries mates, thanks for all your all.

    I think i'll give the php area a try.

    side issue, what's the job market like for web in canada?

    Regards
    MG
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by mind_grapes
    side issue, what's the job market like for web in canada?
    in toronto, as far as word on the street goes, it's excellent

    check linkedin and workopolis
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    153
    Rep Power
    5
    Cool, thanks for the pointers. any other job sites you know of.

    Ill have to find out what i need to work abroad.

    Thanks for everything.

    Kind regards
    MG

IMN logo majestic logo threadwatch logo seochat tools logo