Welcome to the Question2Answer Q&A. There's also a demo if you just want to try it out.
+1 vote
598 views
in Plugins by
edited by

Hallo.

I'm using the plugin "Q2A User Manager" (Q2A-User-Manager) and want to extend diplayed table by one new column. I allready managed to add a column and fill the column with a dummy value. But I'am unable to adjust the command that gather the data from the database.

The original command to populate the field $users is:

$users = qa_db_read_all_assoc(qa_db_query_sub('SELECT ^users.userid, ^users.handle, ^users.email, ^users.level, ^users.flags, p.points, p.qposts, p.aposts, p.cposts FROM ^users LEFT JOIN (select userid, points, qposts, aposts, cposts ' . $extra_fields . ' from ^userpoints) AS p ON p.userid=^users.userid' . $load_filter . $limit_users));

Now, I would like to add field 'matrikelnummer' from table 'qa_userfields' to $users.

I just tried some variations of the command, e.g.

$users = qa_db_read_all_assoc(qa_db_query_sub('SELECT ^users.userid, ^users.handle, ^users.email, ^users.level, ^users.flags, p.points, p.qposts, p.aposts, p.cposts FROM ^users LEFT JOIN (select userid, points, qposts, aposts, cposts ' . $extra_fields . ', uf.matrikelnummer from ^userpoints) AS p ON p.userid=^users.userid LEFT JOIN (select userid, matrikelnummer) AS uf ON uf.userid=^users.userid' . $load_filter . $limit_users));

but this only results in an error loading the page.

I really appreciate any help here because my sql knowlede is much to limited to get that correct.

Thank you!

EDIT: error from the apache log:

PHP Question2Answer MySQL query error 1054: Unknown column 'userid' in 'field list' - Query: SELECT qa_users.userid, qa_users.handle, qa_users.email, qa_users.level, qa_users.flags, p
.points, p.qposts, p.aposts, p.cposts, uf.matrikelnummer FROM qa_users LEFT JOIN (select userid, points, qposts, aposts, cposts  from qa_userpoints) AS p ON p.userid=qa_users.userid LEFT JOIN (select userid, matrikelnummer) AS uf ON uf.userid=qa_users.userid,

2 Answers

+1 vote
by

You wrote your sub-select as

SELECT userid, matrikelnummer

when it should have the form

SELECT userid, matrikelnummer FROM table

where table is the name of the table to which you added the column.

This is assuming that the field matrikelnummer is in a different table than qa_users and qa_userpoints. Should it be in one of those two tables you'd add the field to the respective sub-select instead of adding a new sub-select.

However, note that both qa_users and qa_userpoints are part of the base install and modifying those is strongly discouraged, as it may break upgrades. It's better to create your own table if you need to add data fields.

0 votes
by
I'm missing a function to sort the tables (ascending, descending). For example, I want to show all users with 0 points first. Can someone adjust this?
...