Compare integer inside url in database

Question

I have the following rows in the database inside url column:

http://some_url/something/34123122.json
http://some_url/something/53124322.json
http://some_url/something/22214322.json

And I want to retrieve them in some function, like this (pseudocode):

function retrieve($ids) {
   return $this->fetchAll("SELECT * FROM table WHERE url IN $ids");
}

The problem is that $ids parameter MUST BE an array with ids from those urls only, like:

array(
  [0] => 34123122
  [1] => 22214322
)

So I have to do something in this function so that I can retrieve rows with urls that contain those ids. How can I do that? Urls can change, but the /******.json ending has always the same pattern.

I don't want to make another query selecting the beginning of the url, it will slow down the application too much.

Your app will slow down as the records grow, you're issuing full table scans. Accepted answer isn't the optimal solution. Future visitors should pay attention to that. — Mjh
– Mjh, Commented Jul 21, 2015 at 13:51

N.B. · Accepted Answer · 2015-07-21 10:10:15Z

The proper way to do this is to query only the part of the data that you are interested in - the number. So, you receive an instant +10 to intelligence from performing a quest nearby and you determine that you could create another column to save that number. Your table looks like this now:

CREATE TABLE mytable (
    id int not null auto_increment,
    url varchar(255) not null,
    json_number int not null,
    PRIMARY KEY(id),
    INDEX(json_number)
) ENGINE = InnoDB;

Before inserting into the table, you use integer sanitizing filter to extract the number from the URL without wasting too much time

Given a URL like this: http://some_url/something/34123122.json you can easily extract the number like this:

$url = 'http://some_url/something/34123122.json';
$number = filter_var($url, FILTER_SANITIZE_NUMBER_INT);

echo $number; // echoes 34123122

And now your query is trivial, you check the json_number column which is also indexed at the same time.

Naturally, you can ignore all I wrote and try other answers which are ugly hacks and worst of all - they're all full table scans.

manjeet · Accepted Answer · 2015-07-25 14:39:18Z

you will have to use regex in mysql, change your function:

function retrieve($ids) {
    $regex = '('.implode('|', $ids).').json$';
    return $this->fetchAll("SELECT * FROM table WHERE url REGEXP '$regex'");
}

Note: this is not an optimal solution for large tables. I would suggest you to create an id field in your table and if all ids are unique then you can make id a primary key. Also whenever you insert in that table take out the id part from url and insert it into the id field. In that way you can skip regex. If you are willing to create an id field, then you can execute the following query to update your current table id field:

mysql> update your_table_name set id=replace(substring_index(url, '/', -1), '.json', '');

Emil · Accepted Answer · 2015-07-21 09:27:57Z

0

I do not know if this is a neat solution, but it should work.

function getData($ids) {

    foreach($ids as $item) {

        $str[]  = $item . ".json";

    }

    $where  = "";

    foreach($str as $item) {

        $where .= "url LIKE '%$item' OR ";

    }

    return substr("SELECT * FROM table WHERE " . $where, 0, -4);    

}

$ids = array(34123122, 53124322, 22214322);
echo getData($ids);

Result:

SELECT * FROM table WHERE url LIKE '%34123122.json' OR url LIKE '%53124322.json' OR url LIKE '%22214322.json'

I think this should do it. Of course you have to run the query aswell.

edited Jul 21, 2015 at 9:27

answered Jul 21, 2015 at 8:48

Emil

1,8161 gold badge20 silver badges22 bronze badges

2 Comments

khernik Over a year ago

But array contains only ids, not urls. Urls are in the database.

Emil Over a year ago

My mistake. I misunderstood the question.

Collectives™ on Stack Overflow

Compare integer inside url in database

3 Answers 3

Comments

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Related