-1

I can't explain it properly but I have several different loop queries on a page (news website homepage) to select articles and want to "record" the ID of each article to exclude such ID's from further query results.

Select rand from database ... result = ID's 3, 99, 6
$selected = 3, 99, 6
Select rand from database WHERE ID != $selected ... result = ID 51
$selected = 3, 99, 6, 51
Select rand from database WHERE ID != $selected ... result = ID 4

I can't wrap my head around on how to "record" the already selected ID's incremential and then use it further down to prevent the same article to appear twice.

Frank Coetzee
  • 29
  • 8
  • do you have the number to exclude or it's a variable ? i mean it's always numbers in your exemple ? – Robin Jan 16 '18 at 08:10
  • Are these articles excluded in absolute terms or just for individual user eg. never show this article again or never show it to the same user again? Does it have any exceptions regarding showing eg. cases where news should appear again? – Perfect Square Jan 16 '18 at 08:12
  • The news homepage show a limited overall collection of articles, Latest news, featured, business, sport etc so I use appropriate queries to select it but the problem is that an query can select article 23 under latest, but then ALSO display article 23 under featured (and even under business etc) so I want to record the ID's and exclude it from the queries further down so that article 23 only appear once. – Frank Coetzee Jan 16 '18 at 08:19
  • Show your table schemas. You could simply have a table containing two columns;: User_ID, and Article_ID. Insert into that table each time a user is shown a particular article. You might add a timestamp if an article might be presented at a later date. – Sloan Thrasher Jan 16 '18 at 08:23
  • Article ID = 23, date = today, featured = yes, cat = business so latest articles query will obviously show article 23, but with not many articles at this stage, the change is good that article 23 can also appear under "featured" and "business" queries and that is what I want to prevent. – Frank Coetzee Jan 16 '18 at 08:31

1 Answers1

0

make an array where you add all result ids into, then explode that array for the next sql. something like this:

$selectedIds = array();
// make first query, get results in loop. im not writing the query execution here, just the loop after
while($row = mysqli_fetch_assoc($res)){
    $selectedIds[] = $row['id']; 
    // do your other stuff here (single article generation)
}

// for further queries you can make the WHERE clause like this
$query = "SELECT rand FROM table WHERE ID not in (".explode(', ', $selectedIds).")";

// this will make the actual query look like this:
// SELECT rand FROM table WHERE ID not in (3, 99, 6)
kscherrer
  • 5,486
  • 2
  • 19
  • 59
  • That should work and I will test it now thanks. It's a HUGE system with 50+ town portals that I developed without any formal training. (I used something similar but without the array part so it was a bit ugly) – Frank Coetzee Jan 16 '18 at 08:37
  • I can recommend that you treat yourself with some training then. As you can see by the downvotes to your question, this is kinda basic stuff ;) - not that i want to say that my solution is the standard solution for these kind of problems, but getting any solution to this problem that is not too ugly should be no problem for a developer that handles 50+ portals. glad I could help, though, cheers. – kscherrer Jan 16 '18 at 08:46
  • At 50, deaf, no job opportunities in South Africa, working 10+ years for myself in competitive hearing world, there is no time for study and I learn what I NEED to know the hard trial and error way. Thanks Cashbee – Frank Coetzee Jan 16 '18 at 08:59
  • In that case I wish you all the best. Also I wanted to point out that you tagged this question `mysql` - mysql has been deprecated for a while now and it is [*STRONGLY RECOMMENDED*](https://stackoverflow.com/questions/8891443/when-should-i-use-mysqli-instead-of-mysql) that you switch to `mysqli`. The refactoring is an easy task and really worth the time. – kscherrer Jan 16 '18 at 09:16