CODEWITHSUNDEEP
phpmysqlwordpress
Anne Vandu
Anne Vandu

Reputation: 113

How to query MySQL where the parent name of a post is contained in a column of database table

Currently, I am able to fetch results where a column matches the title of a post. I have duplicate post titles which have unique parent posts. For example, I have a name James whose parent post is a country England and James whose parent post is Australia.

I created a variable $title = get_the_title( $post_id ); When I query SELECT * from mytable WHERE name = '$title' I am able to fetch results of James but this is also pulling James from other countries. I don't wish to hard code the country name into the query. The columns in the database which I wish to compare the country with contains this kind of data:

country                           | name
England - some random text        | James
Australia - more text             | James
Northern Ireland - even more text | Mary

So basically, I am comparing the parent name of a post say /people/england/james which in this case is england, (people is the post type) against the column in database country which should find England - some random text and return the correct James.

So to break this down I am looking for something along the lines of

SELECT * from mytable WHERE name = '$title' AND (country column part before the hyphen contains parent post name.)

Using the slug of parent may not be appropriate considering it contain hyphens for countries like people/republic-of-ireland/mary so sticking with the parent name Republic of Ireland - will be a better option. How do you achieve this kind of query?

With Thanks

Upvotes: 1

Views: 124

Answers (1)

Aaron Morefield
Aaron Morefield

Reputation: 995

Since it looks like you are using PHP as well as MySQL you should be looking to use "Explode" logic on your post.

$country = mysqli_real_escape_string(str_replace($_POST['Country'], '-', ' ')); //Get the country and replace the dashes with spaces

mysqli_query('SELECT * from mytable WHERE name = "' . $title . '" AND country LIKE "' . $country . ' - %"');

Be careful to escape your strings or use PDO binding here since, you would be very vulnerable otherwise placing variables directly into your query.

Also of note, depending on your DB's character encoding, you might need to wrap a Lower and a strtolower() around your MySQL and PHP strings respectively, since they will otherwise not match "Republic-of-Ireland" to "Republic Of Ireland"

Upvotes: 2

Related Questions