Efficient 2 dimensional array search in MySql

Question

I am trying to design an application and part of it is to show users new articles in different categories after the last visit of the user to the webapp. To this I use MySql and have a table that keeps track of last visits and I can query the table to get a php array like below:

$array =[[user1,category1, datetime1],[user1,category2, datetime2],[user1,category3,datetime3]];

Where user is the user id and datetime is the visited datetime and category is the article category.

Having the setup above, I am trying to get new articles from the article table where the publish date is after user last visited to categories.

I can achieve this by multiple OR in a query like below, however it is not really a good and nice looking query, and probable not scalable. Is there any other way of doing this which is simpler and faster?

$multiwhere=[];
foreach($array as $a){
$multiwhere[]="select article_id from articles where category=".$a[1]." and publish_date>".$a[2];
}

And the final query would be like this:

"Select * from articles where article_id in (".implode(" or ".$multiwhere.")";

I deeply appreciate any suggestion to improve the query above.

KIKO Software · Accepted Answer · 2022-05-02 06:56:35Z

2

Your query is almost correct, apart from the fact that you first retrieve all the article_id you want, and then use them to query for those articles. You can do that in one step, like so:

$multiwhere = [];
foreach ($array as $a) {
    $multiwhere[] = "(category = " . $a[1] . " AND publish_date >= " . $a[2] .")";
}
$query = "SELECT * FROM articles";
if (count($multiwhere) > 0) {
    $query = " WHERE " . implode(" OR ", $multiwhere);
}

One query will do.

I kept the way you use the $array, but it looks weird to me. Especially around publish_date. I cannot change that because I don't know the type of the field. And, of course, $array is quite a bad name. It tells you what the type of the variable is, not what it contains, as it should. A better name would be: $lastCategoryVisits, or something like that. Your loop should look something like this:

foreach ($lastCategoryVisits as $lastCategoryVisit) {
    $category  = $lastCategoryVisit["category"];
    $lastVisit = $lastCategoryVisit["lastVisit"];
    $QueryConditions[] = "(category = '$category' AND publish_date >= '$lastVisit')";
}

Don't be afraid to write out what your code actually does. It might be a bit longer, but now you can see what is going on. This will not slow down the execution of your code at all.

Finally, it would be better to always use prepared statements to prevent the possibility of SQL-injection. If you get into the habit of always doing this you don't have to use excuses like: "It is not important in this project.", "I'll to it later when the code works." or "The data for this query doesn't come from an user.".

answered May 2, 2022 at 6:56

KIKO Software

17.5k3 gold badges33 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

MyQ Over a year ago

Thanks for the detailed answer. Is there any other way to write this query?

KIKO Software Over a year ago

@MyQ You're welcome. Yes, there might be another way. You could separate the publish dates from the categories. Something like: category IN (cat1, cat2, ... catN) AND publish_date >= last_visit_date, but that depends on what you want to do.

MyQ Over a year ago

Thanks. I guess the query in your comment considers only one datetime which may not be the best.

KIKO Software Over a year ago

@MyQ Yes, that's the compromise. It is worth considering whether you would always need to take all the individual dates into consideration. When you have many-many articles and many categories, using OR many times in the query, will become inefficient. But I'm talking about thousands of articles and many dozen of categories.

Collectives™ on Stack Overflow

Efficient 2 dimensional array search in MySql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related