How to write a query with a WHERE clause with two conditions?

Question

I cannot seem to get this select statement to work with the WHERE conditions. Depending how it loads the data, I either get the first test result or all of the test results and not the user_id-specific result I desire.

I've gone through the documentation over and over and I know this is simple, but I cannot figure out what is going on. I've separately checked the getUser() function and it is working fine, the test_id condition doesn't seem to work.

$db = JFactory::getDbo();
$user = JFactory::getUser();
$query = $db->getQuery(true);

$query -> SELECT('Test_Percentile');
$query -> FROM($db->quoteName('#__calc_results'));
$query -> WHERE(($db->quoteName('test_id').'='.'1')AND($db- 
>quoteName('user_id').'='.($user->id)));

$db->setQuery($query);
$result = $db->loadObject();

echo "<p>" . $result->Test_Percentile ."</p>";

Answer 1

Try this:

$query->WHERE($db->quoteName('test_id').'= 1 AND '.$db->quoteName('user_id').'='. $user->id)

Answer 2

What's gone wrong?

You have unintentionally formed a valid expression containing two conditionals separated by AND.

($db->quoteName('test_id').'='.'1')AND($db->quoteName('user_id').'='.($user->id))

is the same as all of the following: (Demo)

("`test_id`=1")AND("`user_id`=$user->id")
"`test_id`=1" && "`user_id`=$user->id"
true && true
true

When you convert the boolean true to a string, it becomes 1.

How to investigate your issue?

You can see the generated 1 if you go to System > Global Configuration, then under the System tab, in the Debug Settings section, set the Debug System field to Yes. Then reload your script, at the bottom of your page click on the Database Queries box to reveal all of the queries executed, then scroll down until you find your query. Here is what you'd see:

SELECT Test_Percentile FROM [prefix]_calc_results WHERE 1

Beyond the generated query not being what you desire, you will also see that the number of returned rows will be all of the rows in your database table -- because WHERE 1 effectively filters out nothing. You are using $db->loadObject() in your php code, so despite all of the rows being delivered in the resultset, just the first row is accessed and displayed.

Some best practices suggestions:

Beyond fixing your query syntax, I'd like to recommend a number of other refinements for you to apply to your future development projects that will help you to keep your code clean, readable, efficient, and easy to debug.

1. Use "chaining" from your first declaration of $query. This means using -> to connect your method calls that all apply to $query instead of writing out $query on each line.
2. Use lowercase method calls to mirror what the Joomla documentation displays.
3. Because none of your tables or column names require the use of backtick-wrapping to make the query valid, you can avoid the code bloat and omit these calls. It certainly doesn't hurt your code to make a bunch of quoteName() and quote() calls, but your code will be more brief and therefore easier to read without them.
4. Use $query->dump() to check that your generated query is exactly what you intend to create.
5. Use loadResult() when you only want to access a single column from the first row of a resultset.
6. It is often useful to check if there were no rows returned in the resultset. Checking for a "falsey" result with ! (see my snippet below where I declare and check the result value in the same step) is often a sensible inclusion.
7. Using a try{}catch{} block is a quick and simple way to debug your code (compared to turning on the global error reporting and having to scroll and scroll to find the query that you're actually trying to debug). JFactory::getApplication()->enqueueMessage() is a great way to package up the diagnostic and error information because it displays it at the top of your page in a bootstrap message box.

My recommended snippet:

try {
    $db = JFactory::getDbo();
    $user = JFactory::getUser();
    $query = $db->getQuery(true)
                ->select("Test_Percentile")
                ->from("#__calc_results")
                ->where("test_id = 1 AND user_id = $user->id");
    $db->setQuery($query);
    JFactory::getApplication()->enqueueMessage($query->dump(), 'info');
    if (!$perc = $db->loadResult()) {
        echo "<p>No Rows Found</p>";
    } else {
        echo "<p>$perc</p>";
    }
} catch (Exception $e) {
    JFactory::getApplication()->enqueueMessage("Query Syntax Error: " . $e->getMessage(), 'error');
}

It would be remiss of me to not mention: You must never display queries or error messages to the public as a matter of good security practices

Alternative ways to build the WHERE clause:

The truth is, there are several ways to write a valid WHERE clause that contains two conditions with Joomla's query builder method (beyond the perfect valid single string above).

where() x2:

->where("test_id = 1")
->where("user_id = $user->id");
// WHERE test_id = 1 AND user_id = 75

where() and andWhere(): (I don't advise the unnecessary parentheses)

->where("test_id = 1")
->andWhere("user_id = $user->id");
// WHERE (test_id = 1) AND (user_id = 75)

where() with an array of conditions:

->where(array("test_id = 1", "user_id = $user->id"));
// WHERE test_id = 1 AND user_id = 75