Code problem: mysql guru needed!

[stace]

Hi guys I'm working on a screenshots gallery posted over in the New Modules Announcements thread, soon to be on the sourceforge mods site - and I'm having a heck of a time excluding results in the table that don't have a screenshot. Anybody want to take a stab at this?

What I'm looking for is to add some kind of WHERE clause so that anything from the 'files' table that has no 'files_screenshot' entry will not be shown in the table

code:

// SQL text for count the total recs from the selected option
$q = new DBQuery;
$q->addQuery('count(file_id)');
$q->addTable('files', 'f');
$q->addJoin('projects', 'p', 'p.project_id = file_project');
$q->addJoin('tasks', 't', 't.task_id = file_task');
if (count ($allowedProjects)) {
$q->addWhere('( ( ' . implode(' AND ', $allowedProjects) . ') OR file_project = 0 )');
}
if (count ($allowedTasks)) {
$q->addWhere('( ( ' . implode(' AND ', $allowedTasks) . ') OR file_task = 0 )');
}
if ($catsql) {
$q->addWhere($catsql);
}
if ($company_id) {
$q->addWhere("project_company = $company_id");
}
if ($project_id) {
$q->addWhere("file_project = $project_id");
}
if ($task_id) {
$q->addWhere("file_task = $task_id");
}
$q->addGroup('file_version_id');


// SETUP FOR FILE LIST
$q2 = new DBQuery;
$q2->addQuery('f.*'.
', max(f.file_id) as latest_id'
.', count(f.file_version) as file_versions, round(max(f.file_version),2) as file_lastversion');
$q2->addQuery('ff.*');
$q2->addTable('files', 'f');
$q2->addJoin('file_folders','ff','ff.file_folder_id = file_folder');
$q2->addJoin('projects', 'p', 'p.project_id = file_project');
$q2->addJoin('tasks', 't', 't.task_id = file_task');
if (count ($allowedProjects)) {
$q2->addWhere('( ( ' . implode(' AND ', $allowedProjects) . ') OR file_project = 0 )');
}
if (count ($allowedTasks)) {
$q2->addWhere('( ( ' . implode(' AND ', $allowedTasks) . ') OR file_task = 0 )');
}
if ($catsql) {
$q2->addWhere($catsql);
}
if ($company_id) {
$q2->addWhere("project_company = $company_id");
}
if ($project_id) {
$q2->addWhere("file_project = $project_id");
}
if ($task_id) {
$q2->addWhere("file_task = $task_id");
}
$q2->setLimit($xpg_pagesize, $xpg_min);
// Adding an Order by that is different to a group by can cause
// performance issues. It is far better to rearrange the group
// by to get the correct ordering.
//$q2->addGroup('project_id');
$q2->addGroup('file_date DESC');


$q3 = new DBQuery;
$q3->addQuery('file_id, file_version, file_screenshot, file_version_id, file_project, file_name, file_screenshot, file_task, task_name, file_description, file_checkout, file_co_reason, u.user_username as file_owner, file_size, file_category, file_type, file_date, cu.user_username as co_user, project_name, project_color_identifier, project_owner, con.contact_first_name, con.contact_last_name, co.contact_first_name as co_contact_first_name, co.contact_last_name as co_contact_last_name');
$q3->addQuery('ff.*');
$q3->addTable('files');
$q3->addJoin('users', 'u', 'u.user_id = file_owner');
$q3->addJoin('contacts', 'con', 'con.contact_id = u.user_contact');
$q3->addJoin('file_folders','ff','ff.file_folder_id = file_folder');
$q3->addJoin('projects', 'p', 'p.project_id = file_project');
$q3->addJoin('tasks', 't', 't.task_id = file_task');
$q3->leftJoin('users', 'cu', 'cu.user_id = file_checkout');
$q3->leftJoin('contacts', 'co', 'co.contact_id = cu.user_contact');
if (count ($allowedProjects)) {
$q3->addWhere('( ( ' . implode(' AND ', $allowedProjects) . ') OR file_project = 0 ) ');
}
if (count ($allowedTasks)) {
$q3->addWhere('( ( ' . implode(' AND ', $allowedTasks) . ') OR file_task = 0 )');
}
if ($catsql) {
$q3->addWhere($catsql);
}
if ($company_id) {
$q3->addWhere("project_company = $company_id");
}
if ($project_id) {
$q3->addWhere("file_project = $project_id");
}
if ($task_id) {
$q3->addWhere("file_task = $task_id");
}



$files = array();
$file_versions = array();
if ($canRead) {

$files = $q2->loadList();
$file_versions = $q3->loadHashList('file_id');
}
// counts total recs from selection
$xpg_totalrecs = count($q->loadList());

// How many pages are we dealing with here ??
$xpg_total_pages = ($xpg_totalrecs > $xpg_pagesize) ? ceil($xpg_totalrecs / $xpg_pagesize) : 1;

//shownavbar($xpg_totalrecs, $xpg_pagesize, $xpg_total_pages, $page);

?>

[ajdonnison]

You could try adding:

$q->addWhere('files_screenshot is not null');
$q->addWhere('files_screenshot != \'\'');

They are both required unless the field is defined as NOT NULL.

[stace]

thanks a ton - you've saved me a lot of frustration!

It needed to be in the q2 query - but there the code works like a charm.

Still working on gettin an upload screenshot box below the upload file box - but haven't had time to work on it.