0

I need a user-specific mySQL SELECT of the last n entries within n different topics.

I have two tables: areas and posts.

areas has the columns id, coordinates, userID

posts has the columns iD, tOPIC, cONTENT, aREAid, cREATED

with the value of the aREAid in each row is the corresponding id of the area table. This means the chain between the two tables is: id <=> aREAid

user-specific query in table "areas":

$go4Row1 = $pdo->prepare ('SELECT id,coordinates,userID FROM areas WHERE userID = ?');
$go4Row1->bindParam(1, $gottenUsersID, PDO::PARAM_INT);
$go4Row1->execute();

to show the last 15 entries within 15 different topics in table "posts":

$latest_n_distinct_posts = 
      $pdo->prepare('SELECT MAX(cREATED) AS max_date, iD, tOPIC, cONTENT, aREAid, cREATED 
                     FROM posts
                     GROUP BY
                          tOPIC  
                     ORDER BY
                          max_date DESC
                     LIMIT 15');                

// the WHERE-clause was no solution here 
// ...$latest_n_distinct_posts->bindParam(1, $aREAid, PDO::PARAM_INT);
$latest_n_distinct_posts->execute();

I can do a functioning: 

while ($areaRow = $go4Row1->fetch(PDO::FETCH_ASSOC)) {
    echo' show: '.$areaRow['id'].' with coordinates: 
        '.$areaRow['coordinates'].' userID: '.$areaRow['userID'].'<br>';
}

or a functioning:

while ($latestRow = $latest_n_distinct_posts->fetch(PDO::FETCH_ASSOC)) {
     echo' last date: '.$latestRow['cREATED'].' with topic: 
         '.$latestRow['tOPIC'].' text: '.$latestRow['cONTENT'].'<br>';
}

But I fail in making the posts query user-specific by finding a working solution in chaining the restriction WHERE id <=> aREAid somehow. I am not sure if it is necessary to create an array with the aREAid_s and use an IN clause for generating the needed "bloc"/ "filtered population" from which I can use the GROUP BY / ORDER BY / LIMIT then. (But I would appreciate a simpler solution...)

I'd be really happy if someone could give me a hint / help me out with this issue.

Thanx in advance, -Flow

p.s. did change the column-name from tEXT to cONTENT - to avoid using a keyword (here is a keyword-list, one might be interested. more words then expected: https://dev.mysql.com/doc/refman/5.7/en/keywords.html )

p.p.s. this is an example-code. the origin has different column-names and each query for itself is working properly..

flowfab
  • 99
  • 11
  • Note: `text` is a mysql keyword and will likely cause a problem. It is a data type. You need to wrap it with backticks (`) to use it as a column name. Also, you usually need to group by every column that you select that isn't inside an aggregate function. Mysql 5.7 will now throw errors if you do not. So if you plan on ever upgrading mysql, it is better to do it now. – Jonathan Kuhn Jan 13 '16 at 00:47
  • Thanx for the hint, Jonathan! Unfortunately I did not need to wait for an upgrade - The query did produce some strange results (sometimes worked, sometimes not / had wrong order. However, I could solve it by creating an adaption following this example: http://stackoverflow.com/questions/14770671/mysql-order-by-before-group-by – flowfab Jan 22 '16 at 19:30

1 Answers1

0

According to the poor responses to this question, I assume SQL is not the right way to accomplish this issue. Therefore my approach was in doing the filtering and chaining via PHP instead. There are quite a lot of available php array functions: http://php.net/manual/en/ref.array.php.

My steps so far:

  • create two arrays from query (ASSOC)
  • create target-array for adding-up all the "sub-arrays"
  • create a function which is looking for the needle in the haystack, and takes the whole sub-array to the target-array, if successful with its search.

    At the end, I am having a user-specific target-array for iterating through with a foreach-loop and echoing the values of the keys row-by-row.

    • flowfab
    • 99
    • 11