0

I am pretty new to extending wordpress and trying to get my head around using Mysql and PHP to fetch data into a html table and print it on a wp page. What i have so far is fetching all form data to display a pass or fail by matching the result from a score and max_score column in the table wp_testing_data

This is the tricky part that i just cannot find any details of how to make the query. If the current user visits the page with the html table output, i want it to only display entries from other users that have the same site_id in the wp_usermeta table as the current user

In theory, the user viewing the page has a site_id entry in the wp_usermeta table of DE49. Now i want to only show the pass or fail results table if the results are from other users with the site_id value equal to the current users site_id value. The site_id will be unique to a group of users, so putting the actual result (DE49) as the target isn't the answer. It needs to be dynamic to the user viewing the page at that time (current user). Basically, you can only see the results from members of your own group. In this case the group is stored in the site_id column of the wp_usermeta table. I can get all results using the following, but cannot figure out only getting results from users with a matching meta value to the current user. Does anyone know how to write the query to add the conditions above to the query i use below to get the filtered data only?

global $wpdb;
$result = $wpdb->get_results( "SELECT f_name, l_name, date, IF(score = max_score, 'PASSED','FAILED') AS score 
FROM wp_testing_data, wp_usermeta
WHERE wp_usermeta.meta_key = 'site_id'
AND wp_usermeta.meta_value = 'DE49'");

echo "<style>";
echo "body {font-family: Arial;}";

echo ".table_container { padding: 10px 12px 0px 12px;  border: 1px solid #ccc;  }";
echo ".table_container th { background-color:lightblue; color:#000; font-weight:bold; border-left: 1px solid white;}";
echo "</style></head>";
echo "<body>";

echo "<div class=\"table_container\"><table>";
echo "<tr><th style=\"padding-left:10px;\">First Name</th><th>Last Name</th><th>Date</th><th>Score</th></tr>";
foreach ($result as $row) {
    echo "<tr><td>" . $row->f_name. "</td><td>" . $row->l_name . "</td><td>" . $row->date . "</td><td>" . $row->score. "</tr>";
}
echo "</table></div>";

Screenshot enter image description here Any guides or rough ideas would be greatly appreciated and thanks for taking the time to read my post.

Improve this question

1 Answer 1

Reset to default
0

You can use get_current_user_id to get the id of the user currently viewing the page, and get_user_meta to get the site_id associated with that user. For example:

<?php

$user_id = get_current_user_id();
$site_id = get_user_meta($user_id, 'site_id', true);

Note: This does assume there is a user logged in, you might get unexpected results if there isn't. So assuming you haven't already done so, make sure the page can only be viewed by a logged in user.

To then use this site_id to get the scores per 'site', you can use a LEFT JOIN. The query you can use is:

$result = $wpdb->get_results("SELECT f_name, l_name, IF(tdata.score = tdata.maxscore, 'PASSED', 'FAILED') as score FROM wp_usermeta umeta LEFT JOIN wp_testing_data tdata ON tdata.user_id = umeta.user_id WHERE umeta.meta_key = 'site_id' AND umeta.meta_value = '" . $site_id . "'");

Do note that this will give you a query result for every user that has a site_id associated that matches $site_id, even if there's no corresponding result in wp_testing_data. If you ONLY want those that have testing data, use INNER JOIN instead of LEFT JOIN.

Improve this answer
10
  • Thanks for taking the time to answer. It is the part to see other members results who share the same site_id as the current user that is baffling.
    – Davey
    Commented Feb 28, 2021 at 14:20
  • @Davey Oh, I see, your question is actually about the query you need to run? In that case, a little more information about what your database looks like (specifically wp_testing_data's structure) would be needed, so please add that to your question.
    – Patriot
    Commented Feb 28, 2021 at 15:58
  • Thankyou @Patriot. I have now added an image of the fields available. The site_id column is in wp_usermeta..
    – Davey
    Commented Feb 28, 2021 at 17:19
  • @Davey I have added the query you need to use :)
    – Patriot
    Commented Feb 28, 2021 at 21:14
  • Mmm. I am now getting a blank output table with no information at all. There are definately two test users setup with the same site_id as me (The logged in user). I will keep playing around with it and see how i go. I see what i think is a couple of errors with '''c5k0o2h7gu_usermeta''' which i have changed to wp_usermeta? and get_result as get_results. Any further ideas are appreciated.
    – Davey
    Commented Mar 1, 2021 at 8:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.