php - wordpress sql regex to find urls starting with http not matching my own domain

505

I need an sql query to find all posts (insidepost_content) containing urls starting exactly byhttp://, but not matching my own domain to avoid finding posts containing internal links.

I know that it can be done and I'm currently using this:

$posts = $wpdb->get_results( "SELECT * FROM wp_posts WHERE post_content LIKE '%http://%' ORDER BY ID ASC" );

The problem with this is that I can find all urls starting byhttp://, but I also see posts containing internal links.

Answer

Solution:

Is this what you want?

WHERE post_content LIKE '%http://%' AND
      post_content NOT LIKE '%http://<mydomain>%'

People are also looking for solutions to the problem: wordpress - Php loop through categories (and generated urls for all post in)

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question