Populate two arrays with two different SQL queries

Question

I am using Joomla and connecting to an MSSQL database to store the resulting set(s) in arrays. I am utilizing this syntax, but there must be a more efficient way of coding this.

<?php
 $option = array();
 $option['driver'] = 'mssql';
 $option['host'] = '555.555.55.5';
 $option['user'] = 'username';
 $option['password'] = 'password';
 $option['database'] = 'database';
 $option['prefix'] = '';
 $db = JDatabaseDriver::getInstance($option);
 $sql = $db->getQuery(true);
 $sql = "Select ranchstyle from information";
 $db->setQuery($sql);
 $rows = $db->loadRowList();
 $output = array();
 foreach ($rows as $row) {
 array_push($output, $row);
 }
 $data = json_encode($output[0]);
 $query2 = $db->getQuery(true);
 $query2 = "Select maestro from musicinfo";
 $db->setQuery($query2);
 $rows1 = $db->loadRowList();
 $output1 = array();
 foreach ($rows1 as $r) {
 array_push($output1, $r);
 }
 $data1 = json_encode($output1[0]);
?>

• mickmackusa: What version of Joomla was this script using? Why are your queries only accessing the first row from each table, but not including a WHERE clause? Why are you separately json_encoding these two arrays? What is going to happen next with this data? What is the intention of this script?

Answer 1

There are two possible answers to this question, a generalized one and a specific one.

To help with such questions in general, there is one programming concept which is often underestimated by PHP users. It is called user-defined functions

In a nutshell, you can write code once and then use it any number of times. For example:

function getArrayFromSql($db, $sql)
{
 $db->setQuery($sql);
 $rows = $db->loadRowList();
 $output = array();
 foreach ($rows as $row) {
 array_push($output, $row);
 }
 return $data;
}

Now this function can be used any number of times - for example:

$sql = "Select ranchstyle from information";
$output = getArrayFromSql($db, $sql);
$data = json_encode($output[0]);
$sql = "Select maestro from musicinfo";
$output = getArrayFromSql($db, $sql);
$data1 = json_encode($output[0]);

The rest of this answer will be a general review. The code does unnecessary work in multiple places.

First, you are making a useless calls to $db->getQuery(true);, as with the very next step the $sql variable gets overwritten.

Second, you actually have an array already, as $db->loadRowList(); gives you a first class array. But for some reason you are duplicating it into $data. So your code actually should be:

$sql = "Select ranchstyle from information";
$db->setQuery($sql);
$rows = $db->loadRowList();
$data = json_encode($rows[0]);
$sql = "Select maestro from musicinfo";
$db->setQuery($sql);
$rows = $db->loadRowList();
$data1 = json_encode($rows[0]);

Third, given you are encoding only the first item of the array, it seems an array is not needed at all, so select and fetch one line only.

$sql = "Select ranchstyle from information limit 1";
$db->setQuery($sql);
$row = $db->loadRow();
$data = json_encode($row);
$sql = "Select maestro from musicinfo limit 1";
$db->setQuery($sql);
$row = $db->loadRow();
$data1 = json_encode($row);