0
\$\begingroup\$

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]);
?>
\$\endgroup\$
-2
\$\begingroup\$

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 a code once and then use it any number of times.

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

Here you created a function which you can use any number of times:

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

Whereas for the specific answer we need to review your code more closely. It does unnecessary job all the time.

First, you are making a useless call 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, you seems don't need an array 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);
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.