1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Who know's the correct SQL Query?

Discussion in 'MySQL' started by bernhard, Apr 25, 2018.

0
  1. #1
    to explain my problem, I created this short 3-Minute Video in 4k + Audio.


    Thank you so much for any little help,
    appreciate it!
     
    bernhard, Apr 25, 2018 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #2
    upload_2018-4-26_18-49-46.png
    Hi

    Line 1 is literally concatenating the two strings together so the code is doing what it should, however what you really want is this

    update `wpk6_posts` set `post_content` = replace(`post_content`, 'XXX', '<a href="https://www.here.com/">My New Anchor Text</a>');
    Code (markup):
    alternatively if you want to keep the anchor the same but you've just moved domains you might just want

    update `wpk6_posts` set `post_content` = replace(`post_content`, 'http://www.myoldsite.com', 'https://www.mynewsite.com');
    Code (markup):
     
    sarahk, Apr 25, 2018 IP
  3. bernhard

    bernhard Member

    Messages:
    18
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    46
    #3
    OMG. You are genius. It worked. Absolute brilliant. You made my day!
    THANK YOU SO MUCH!!!!!

    Where are you located?
    I would need for sure some other assistance, like making a backup of my SQL Database, before I do run a lot of changes.

    I would love to talk to you. Would that be somehow possible?
    Factime, WhatsApp, Messenger, etc. you name it.

    Again.. thank you so much for your help, I really do appreciate that!
     
    bernhard, Apr 26, 2018 IP
  4. bernhard

    bernhard Member

    Messages:
    18
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    46
    #4
    I was running into another problem, I just didn't consider at the beginning.

    I created this short 4k Video + Audio explanation. It's just easier to explain, and you can see immediately what I am doing and talking about.


    Like mentioned before.. maybe there is a chance I could talk to you over any free online app.
    That would be amazing. Ohh.. I am US NY Time Zone.

    cheers!
     
    bernhard, Apr 26, 2018 IP
  5. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #5
    Hi

    So this is a one off change? Tricky when there are thousands of links in thousands of posts. I suspect you're going to need to do it via javascript each and every time that the page is generated - or write a php parser to go through and do it once. MySql on it's own isn't going to have the tools in place.

    So if I was doing this by php I'd be looking at this flow:
    • select all posts with xxx in them
    • parse into a phpdom object
    • find the instances of xxx
    • look to see if the parent(???) is a hyperlink
      • yes, only change the text
      • no, change the text and add the hyperlink
    • save that post back into the database
    Has anyone got any examples of doing this that they wouldn't mind sharing as an example?
     
    sarahk, Apr 26, 2018 IP
  6. bernhard

    bernhard Member

    Messages:
    18
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    46
    #6
    Hi Sarah!

    I don't know what a "parser" is and how that works. But looking at your logical points, yes. I think that's spot on.

    I had big hopes that a WordPress Plugin would already exist that would find and replace specific querys.
    And if needed, also allows you to setup a specific target URL. But so far, I had no luck finding anything related.

    So what do you think?
    Is this something that can be done?
    Or do you think it's impossible to do?

    One simple solution, what if we would use EXCLUSION?
    Just about the SQL:
    Do you think it's possible to add a function, that says:
    If there is a hyperlink on the search query, don't do anything!

    Saying. If XXX would have a Hyperlink, then DO NOT replace the query, and DO NOT create a hyperlink. Just leave it as it is.

    Or, the more simplified version:
    If the query XXX has any hyperlink --> don't do anything.

    If we could add this rule to the SQL, I guess that would do the trick.

    Why? It would keep all existing links and queries without breaking them.
    And only if the XXX query is found without a hyperlink, then only replace.

    Does this kinda make any sense?

    Please let me know your thoughts, I am open for all our suggestions!
    But having just this exclusion within the SQL.. I guess that could be the solution!

    Curious about your thoughts!
    Thank you so much!!!!
     
    Last edited: Apr 26, 2018
    bernhard, Apr 26, 2018 IP
  7. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #7
    Hi

    A couple of things
    1. You can use regular expressions in MySQL, I haven't needed to and I invariably end up frustrated because while they're powerful they're also tricky little things for the infrequent user
      https://dev.mysql.com/doc/refman/8.0/en/regexp.html
    2. And some suggestions for backing up your database
      https://www.google.com/search?q=phpmyadmin+backup
      I use a tool called SQLyog, it's pricey but saves me so much time and grief that it's worth every penny and automates backups :)

    Now, back to your thread

    You could set the where to only make the change if there's no hyperlink but it'll detect any hyperlink in the string unless you do something like this

    SELECT
   body
   , LOCATE('<a', `body`) AS a_start
   , LOCATE('</a>', `body`) AS a_end
   , LOCATE('click',`body`) AS txt
FROM
   `contents`
HAVING a_start > a_end
    Code (markup):
    but it's fraught with assumptions and I wouldn't recommend it.

    Now, for parsing, you'd be looking here: http://php.net/manual/en/class.domdocument.php
    and it's probably getting into programming that you don't really want to tackle.

    Have you looked at something like this: https://wordpress.org/plugins/wp-sheet-editor-bulk-spreadsheet-editor-for-posts-and-pages/
     
    sarahk, Apr 27, 2018 IP
  8. bernhard

    bernhard Member

    Messages:
    18
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    46
    #8
    Hi Sarah!
    Thank you so much again for your help, I really do appreciate that.

    @MySQL and regular expressions:
    I am so sorry. I am not a programmer. This is just to much for me. I have no clue how I could craft some expressions that would do the job.

    @backup-mySQL:
    I was able to create dump file, which I could download. I guess that's all I need in case the database would be damaged, I could always replace it with the DUMP.sql file.

    @your new code:
    Hmmm.. so I don't know what to do with your new code anymore. I am able to understand simply codes, copy and paste it and run it within the SQL.
    But that's about it. The new code you provided: I should just copy and paste it within my SQL and run it?

    What do I have to replace, for example if I would like to replace the query XXX and set a hyperlink to https://example.com/xxx/

    Well.. sorry for my little knowledge in coding.
    I know you are pro and on a complete different level than me.

    I was just hoping to find somehow a simply solution to search for a query, and replace it with a new hyperlink.
    And if the Query has already a hyperlink, I don't want to change anything.

    @your plugin:
    Thank you so much also for the link to the plugin. I was looking into it. I think it's great. However, it doesn't allow me to find and replace a specific query + automatically setting a new hyperlink.
    Editing a post is doable, but not what I am looking for. Because editing blog posts manually, just takes to long, it's not practicable.

    That's the main reason, why I am looking for a plugin that can do this kinda thing with an automated search and replace function.

    Again, thank you so much for all your help and great input!
    I really do appreciate that!

    Bernhard

    ps: perhaps, this plugin needs to be coded and developed from scratch, since it doesn't exist anywhere, right?
     
    bernhard, Apr 28, 2018 IP
  9. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #9
    The plugin basically puts all your posts into a spreadsheet so you can run find and replace over the spreadsheet, get it right and upload. Take another look.
     
    sarahk, Apr 28, 2018 IP
  10. bernhard

    bernhard Member

    Messages:
    18
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    46
    #10
    Hi Sarah!

    Woopaaa... really? I totally missed that. That means.. all posts will be downloaded into one spreadsheet.
    And you say.... I should use the find and replace function in google sheets, make all the changes.. and then upload the edits to overwrite the current articles?

    Wow... I need to test that.

    I am so sorry I didn't see that before. You rock.. I need to test this right now!

    Thank you so much,
    Bernhard
     
    bernhard, Apr 29, 2018 IP
  11. bernhard

    bernhard Member

    Messages:
    18
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    46
    #11
    Wow..wow..

    Sarah.. I guess this Plugin could do the trick. However... I don't know if the plugin can replace a query and also set a hyperlink.
    I mean.. I guess the PRO-Version might be able to handle it, but it requires to enter the correct "SEARCH & REPLACE CODE".. the Syntax.

    I was searching on the plugins-website for a solution, but I was not able to find the right answer.
    Since I am not a coder, I have no clue what SYNTAX I have to use, in order to make that work.

    Here is the Login to the Demo site
    Log in to our demo site and use the plugin for real.
    Log in: http://labs.vegacorp.me/sheet-editor-demo/wp-login.php

    User: demo
    Password: password

    I created a post called sarah. you can see and edit here:
    http://labs.vegacorp.me/sheet-editor-demo/sarah/

    I guess everything is ready and setup. I just don't know what I have to enter within the FORMULA when using APPLY CHANGES IN BULD Feature.

    So to speak.. I would need to know the code for the Formula, to search for a query I would like to replace with a new query plus the hyperlink.

    ufff... ufff.. uff.. I wish I would know just a little bit more coding!

    Thank you so much for all your help - you are just a true genius!
     
    bernhard, Apr 29, 2018 IP
  12. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #12
    damn, that didn't do as much as I hoped.
    you might be better off outsourcing this one.
     
    sarahk, Apr 29, 2018 IP
  13. bernhard

    bernhard Member

    Messages:
    18
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    46
    #13
    Hi Sarah!

    I got this code for now... but for some reason, it doesn't replace the Query with the Hyperlink.

    set @newtxt = concat( "new link is ", "https:www.here.com" );
update wp_posts set post_content = replace(post_content,'oldtext', @newtxt );
    Code (markup):
    But I guess that could do the trick.
    Just something is still broken in the code, because it doesn't work.

    Do you have any clue what's wrong with that code?

    Here is the video to it, to see live what it is doing:
     
    bernhard, Apr 30, 2018 IP
  14. bernhard

    bernhard Member

    Messages:
    18
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    46
    #14
    In case somebody else would know how to do that: Here is the summary of it.
    http://punch.business/x/



    And yes, of course I am willing to hire someone who knows how to do that!

    Thank you very much!
     
    bernhard, May 1, 2018 IP
  15. bernhard

    bernhard Member

    Messages:
    18
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    46
    #15
    How can you actually search within the phpMyAdmin just for a query and get a result, that shows you how many times that query was found within a blog post/page.
    And that also shows you what post/page has this query included?
     
    bernhard, May 16, 2018 IP