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