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]);
?>

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? — mickmackusa
– mickmackusa, Commented Feb 1, 2022 at 13:19

Sᴀᴍ Onᴇᴌᴀ · Accepted Answer · 2022-01-31 23:51:44Z

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);

Stack Exchange Network

Populate two arrays with two different SQL queries

1 Answer 1

You must log in to answer this question.

Hot Network Questions

Populate two arrays with two different SQL queries

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions