Transform PHP SQL to Joomla SQL query

Question

I am moving a PHP page to my Joomla website and I was advised that I "should use Joomla's coding standards and methods for everything, this includes database queries"

My question is:

How should I transform my old PHP code regarding Joomla standards:

$query = "SELECT * FROM `TABLE 2` WHERE Power LIKE ".$input->get('Power', '', 'RAW')." AND Poles LIKE ".$input->get('Poles', '', 'RAW')."";

$results = mysql_query($query)
or die(mysql_error());

echo '<table class="table table-striped">';
echo '<tr><th>Тип по БДС IEC 60034</th><th>Мощност</th><th>Обороти</th><th>Тегло</th></tr>';
while ($row = mysql_fetch_array($results)) 
{
extract($row);


echo '<tr><td class="first">Електромотор тип '.$TYPE.'</td><td>'.($Power+0).' kW</td><td>'.$Speed.' мин.<sup>-1</sup></td><td>'.$Weight.' кг.</td></tr>';
}
echo '</table>';

?>  

This is the TABLE 2 contents. I use the values of each row as a variables on my page.

Updated 01.04.2016---------------------

Answer 1

Perhaps something like the following:

$db = JFactory::getDbo();

$poles = $input->get('Poles', '', 'RAW');
$power = $input->get('Power', '', 'RAW');

$query = $db->getQuery(true);     
$query->select($db->quoteName(array('*')))
      ->from($db->quoteName('TABLE 2'))
      ->where($db->quoteName('Power') . ' LIKE ' . $db->quote($power))
      ->where($db->quoteName('Poles') . ' LIKE ' . $db->quote($poles));
$db->setQuery($query);

$results = $db->loadObjectList();

echo '<table>';

foreach ($results as $row)
{
    echo '<tr>';
    echo '<td class="first">Електромотор тип ' . $row->TYPE . '</td>';
    echo '<td>' . ($row->Power+0) . ' kW</td>';
    echo '<td>' . $row->Speed . ' мин.<sup>-1</sup></td>';
    echo '<td>' . $row->Weight . ' кг.</td>';
    echo '</tr>';
}

echo '</table>';

I would seriously suggest though that you keep all your table column names as lowercase. It can become confusing sometimes when you start capitalising word or using uppercase.

If the above code doesn't work with the where() clauses, try replacing them with:

->where($db->quoteName('Power') . ' LIKE ' . $db->quote('%'.$power.'%'))
->where($db->quoteName('Poles') . ' LIKE ' . $db->quote('%'.$poles.'%'));

Hope this helps

Answer 2

Based on your database table screen shot, your Power values are (float) type and your Poles values are (int) type. For this reason, you can be more restrictive in your user input validation process.

$jinput = JFactory::getApplication()->input;
$power = $jinput->get('Power', '', 'float');  // sufficiently sanitize for querying
$poles = $jinput->get('Poles', '', 'int');    // sufficiently sanitize for querying

echo "<div>Резултати за мощност = $power & поляците = $poles</div>"; // Results for...

Now for the tablular output:

echo "<table class=\"table table-striped\">";
    $db = JFactory::getDBO();
    try {
        $query = $db->getQuery(true)
                    ->select("TYPE AS Type, TRIM(Power) + 0 AS Power, Speed, Weight")
                    ->from($db->qn("TABLE 2"))
                    ->where("Power = $power AND Poles = $poles);
        $db->setQuery($query);
        if (!$results = $db->loadAssocList()) {
            echo "<tr><td>Няма резултати</td></tr>";  // No Results
        } else {
            echo "<tr>";
                echo "<th>Тип по БДС IEC 60034</th>";
                echo "<th>Мощност</th>";
                echo "<th>Обороти</th>";
                echo "<th>Тегло</th>";
             echo "</tr>";
             foreach ($results as $row) {
                 echo "<tr>";
                     echo "<td class=\"first\">Електромотор тип {$row['Type']}</td>";
                     echo "<td>{$row['Power']} kW</td>";
                     echo "<td>{$row['Speed']} мин.<sup>-1</sup></td>";
                     echo "<td>{$row['Weight']} кг.</td>";
                 echo "</tr>";
             }
        }
    } catch (Exception $e) {
        echo "<tr><th>Query Syntax Error - Please notify developer</th></tr>";
        /*
        echo "<tr><th>DO NOT MAKE THESE DETAILS PUBLIC:";
            echo "<div>Query: " . $query->dump() . "</div>";
            echo "<div>Error: " . $e->getMessage() . "</div>";
        echo "</th></tr>";
        */
    }
echo "</table>";

Explanations:

• Rather than using RAW as the filter parameter while collecting the submitted values, tighten restrictions by using float and int respectively. Then your values are sufficiently sanitized to use in the query via variable interpolation.
• Inside of your <table>, I am employing a try {} catch {} block because it is a clean/readable way of handling errors that may occur during the querying process.
• In the select() method, I am removing the trailing zeros from Power (rather than at display time) and changing the casing of TYPE to conform with the other columns (personal preference).
• $db->qn() (shorthand for $db->quoteName()) is called on the table name; this is necessary only because the table name has a space in it. If there was no space, the backtick-quoting could be omitted.
• Because you are seeking exact value matches on the two nominated columns, you shouldn't use the more expensive LIKE operator; = will do nicely. Because your $power and $poles values are sure to be numeric they don't need to be single-quoted.
• The !$results = ... expression is both declaring the resultset to a variable and checking it for a false-y value to reduce lines of code.
• There are three possible outcomes in my snippet: 1. Show the qualifying rows. 2. State that there are no rows found 3. State that there was a syntax error.
• If there is a syntax error, I've included some useful calls which will help you to debug. Keep in mind that the raw query and exact error message should never be made public for security reasons.