How to MYSQLI Select a row that has column LIKE a string variable? Reverse MYSQLI Wildcard?-CodePudding

I have a feeling like this could perhaps be accomplished with PHP and I am overthinking it. However, let's say I have a table that looks like below

TABLE: customer_urls

ID | URL
1  | joestire.com
2  | peterstire.com

In php I would have the URL of a site which changes like

$site_url= "https://joestire.com/about/our-team"

How would I then query my table to find the ID of the record for joestire.com? Assuming I can not write a substr or str_replace because I don't already know what the URL is and what to remove?

I would think something like

"SELECT ID FROM customer_urls WHERE URL in $site_url "

But this does not seem to work?

CodePudding user response：

Use LIKE to match a pattern. In this case, the pattern comes from the url column, with wildcards added around it.

$sql = "SELECT id FROM customer_urls WHERE ? LIKE CONCAT('%', url, '%')"
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $site_url);
$stmt->execute();
$results = $stmt->get_result();
while ($row = $results->fetch_assoc()) {
    echo "{$row['id']}<br>";
}

CodePudding user response：

Try with SUBSTRING_INDEX function:

SELECT *, SUBSTRING_INDEX('https://joestire.com/about/our-team', '/', 3) AS example
FROM customer_urls
WHERE
    url like CONCAT('%', SUBSTRING_INDEX('https://joestire.com/about/our-team', '/', 3), '%')

You can play with the wildcards because this solution does not remove the https or https so I would think that you should apply the wildcard only at the end url%

This work for me