Help with formatting grouped mysql results

[tsp003]

Here is my code:-

//choose the table and return records
$result = mysql_query("SELECT id, state,country_id FROM state ORDER BY country_id");
$string .= '"'.$val.'", ';
$country = null;


//get group
while($row = mysql_fetch_assoc($result)) {
    if ($row['country_id'] != $country) {
        echo '<br>'.'<br>'."[".($row['country_id'])."] = parentarray [";
        $country = $row['country_id'];
    }

// get list

    echo "'".$row['id']."|". 
         $row['state']."',";

}

//end of group



$db = null;
echo "<br>Fetched data successfully\n<br>";
mysql_close($conn);
?>

This creates an output like so:-

[2] = parentarray ['136|Balkhs','481|Herat','564|Kabol','979|Qandahar',

[3] = parentarray ['167|Benguela','503|Huambo','705|Luanda','823|Namibe',

[5] = parentarray ['1208|Tirana',

Which is fine is almost what I want, but I am having trouble finishing off each line I want to remove the last comma and replace it with a closed bracket like so:-

[2] = parentarray ['136|Balkhs','481|Herat','564|Kabol','979|Qandahar']

[3] = parentarray ['167|Benguela','503|Huambo','705|Luanda','823|Namibe']

[5] = parentarray ['1208|Tirana']

Placing an echo "]";anywhere after the //get list region results in

['136|Balkhs'],'481|Herat'],'564|Kabol'],'979|Qandahar']

and that is not what I want. I need to remove the trailing comma and replace it with the bracket for the last item returned on that line. I've been googling for days but haven't really seen any examples that apply to what I am trying to achieve, so any pointers would really be helpful

Thanks guys

[broj1]

Instead of echoing the parts of the string asign it to a string variable and use substr_replace function, i.e.:

$str = "parentarray ['136|Balkhs','481|Herat','564|Kabol','979|Qandahar',";
$newStr = substr_replace($str, ']', -1, 1);

[tsp003]

Thanks for the reply,

sorry for being dumb, are you saying to replace the bit after //get list with your code or is it just an example, been playing around with it and I cant get it to display anything after the first bracketed number.

Sorry am still quite a newbie.

[broj1]

It was just an example since I am not sure if I understood your code. I will try to do an example with your code but if it doeasn't work we'll try to clarify it then.

//choose the table and return records
$result = mysql_query("SELECT id, state,country_id FROM state ORDER BY country_id");
$string .= '"'.$val.'", ';
$country = null;

// initialize $str
$str = '';

//get group
while($row = mysql_fetch_assoc($result)) {

    if ($row['country_id'] != $country) {

        // I do not know why these are
        echo '<br><br>';

        $str .= "[".($row['country_id'])."] = parentarray [";

        $country = $row['country_id'];
    }

    // get list
    $str .= "'".$row['id'] . "|" . $row['state']."',";
}

// replace trailing , with ]
$newStr = substr_replace($str, ']', -1, 1);

echo $newStr;

//end of group
$db = null;
echo "<br>Fetched data successfully\n<br>";
mysql_close($conn);
?>

[tsp003]

Thank you again,

Have tried the code provided and found that it will only work if I specify the country_id in a WHERE clause; i.e
$result = mysql_query("SELECT id, state, country_id FROM state WHERE country_id = 2");
I get
[2] = parentarray ['136|Balkhs','481|Herat','564|Kabol','979|Qandahar']

Which is exactly what I am after. However, if I change the clause to

ORDER BY country_id nothing gets returned at all apart from the "connected to MySQL" message.

[broj1]

Can you post the state table contents in a CREATE TABLE / INSERT INTO statement (you get it by exporting the table into SQL format from phpmyadmin). Also please post the whole php/html code. I am sort of guessing whatyou want but would prefer to test it in my environment. You can also PM the code if you prefer.

[tsp003]

Thank you again,

Have tried the code provided and found that it will only work if I specify the country_id in a WHERE clause; i.e
$result = mysql_query("SELECT id, state, country_id FROM state WHERE country_id = 2");
I get
[2] = parentarray ['136|Balkhs','481|Herat','564|Kabol','979|Qandahar']

Which is exactly what I am after. However, if I change the clause to

ORDER BY country_id nothing gets returned at all apart from the "connected to MySQL" message.

[broj1]

Can you post the state table contents in a CREATE TABLE / INSERT INTO statement (you get it by exporting the table into SQL format from phpmyadmin). Also please post the whole php/html code. I am sort of guessing whatyou want but would prefer to test it in my environment. You can also PM the code if you prefer.

[tsp003]

Thanks here is the sql:

-- phpMyAdmin SQL Dump
-- version 3.5.2.2
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Feb 12, 2013 at 10:51 AM
-- Server version: 5.5.27
-- PHP Version: 5.4.7

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `development`
--

-- --------------------------------------------------------

--
-- Table structure for table `state`
--

CREATE TABLE IF NOT EXISTS `state` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `state` char(35) NOT NULL,
  `country_id` int(11) NOT NULL,
  `published` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1379 ;

--
-- Dumping data for table `state`
--

INSERT INTO `state` (`id`, `state`, `country_id`, `published`) VALUES
(1, 'Abhasia [Aphazeti]', 76, 1),
(2, 'Abruzzit', 105, 1),
(3, 'Abu Dhabi', 8, 1),
(4, 'Aceh', 97, 1),
(5, 'Acre', 31, 1),
(6, 'Adana', 214, 1),
(7, 'Addis Abeba', 67, 1),
(8, 'Aden', 233, 1),
(9, 'Adiyaman', 214, 1),
(10, 'Adygea', 180, 1),
(11, 'Adzaria [Atšara]', 76, 1),
(12, 'Afyon', 214, 1),
(13, 'Aguascalientes', 134, 1),
(14, 'Ahal', 209, 1),
(15, 'Aichi', 108, 1),
(16, 'Ajman', 8, 1),
(17, 'Akershus', 158, 1),
(18, 'Akita', 108, 1),
(19, 'Aksaray', 214, 1),
(20, 'al-Anbar', 102, 1),
(21, 'al-Asima', 116, 1),
(22, 'al-Bahr al-Abyad', 183, 1),
(23, 'al-Bahr al-Ahmar', 183, 1),
(24, 'al-Batina', 162, 1),
(25, 'al-Buhayra', 62, 1),
(26, 'al-Daqahliya', 62, 1),
(27, 'al-Faiyum', 62, 1),
(28, 'al-Gharbiya', 62, 1),
(29, 'al-Hasaka', 202, 1),
(30, 'al-Jazira', 183, 1),
(31, 'al-Khudud al-Samaliy', 182, 1),
(32, 'al-Manama', 24, 1),
(33, 'al-Minufiya', 62, 1),
(34, 'al-Minya', 62, 1),
(35, 'al-Najaf', 102, 1),
(36, 'al-Qadarif', 183, 1),
(37, 'al-Qadisiya', 102, 1),
(38, 'al-Qalyubiya', 62, 1),
(39, 'al-Qasim', 182, 1),
(40, 'al-Raqqa', 202, 1),
(41, 'al-Shamal', 118, 1),
(42, 'al-Sharqiya', 62, 1),
(43, 'al-Sulaymaniya', 102, 1),
(44, 'al-Tamim', 102, 1),
(45, 'al-Zarqa', 107, 1),
(46, 'al-Zawiya', 120, 1),
(47, 'Alabama', 222, 1),
(48, 'Alagoas', 31, 1),
(49, 'Alaska', 222, 1),
(50, 'Alberta', 38, 1),
(51, 'Aleksandria', 62, 1),
(52, 'Aleppo', 202, 1),
(53, 'Alger', 60, 1),
(54, 'Almaty', 109, 1),
(55, 'Almaty Qalasy', 109, 1),
(56, 'Alsace', 71, 1),
(57, 'Altai', 180, 1),
(58, 'Alto Paraná', 174, 1),
(59, 'Amapá', 31, 1),
(60, 'Amazonas', 31, 1),
(61, 'Amhara', 67, 1),
(62, 'Amman', 107, 1),
(63, 'Amur', 180, 1),
(64, 'An Giang', 229, 1),
(65, 'Anambra & Enugu & Eb', 154, 1),
(66, 'Ancash', 166, 1),
(67, 'Andalusia', 65, 1),
(68, 'Andhra Pradesh', 98, 1),
(69, 'Andijon', 223, 1),
(70, 'Andorra la Vella', 6, 1),
(71, 'Anhalt Sachsen', 55, 1),
(72, 'Anhui', 42, 1),
(73, 'Ankara', 214, 1),
(74, 'Annaba', 60, 1),
(75, 'Antalya', 214, 1),
(76, 'Antananarivo', 132, 1),
(77, 'Antioquia', 46, 1),
(78, 'Antofagasta', 41, 1),
(79, 'Antwerpen', 19, 1),
(80, 'Anzoátegui', 226, 1),
(81, 'Aomori', 108, 1),
(82, 'Apulia', 105, 1),
(83, 'Apure', 226, 1),
(84, 'Aqtöbe', 109, 1),
(85, 'Aqua Grande', 195, 1),
(86, 'Aquitaine', 71, 1),
(87, 'Arad', 179, 1),
(88, 'Aragonia', 65, 1),
(89, 'Aragua', 226, 1),
(90, 'Ardebil', 101, 1),
(91, 'Arecibo', 171, 1),
(92, 'Arequipa', 166, 1),
(93, 'Arges', 179, 1),
(94, 'Ariana', 213, 1),
(95, 'Arizona', 222, 1),
(96, 'Arkangeli', 180, 1),
(97, 'Arkansas', 222, 1),
(98, 'ARMM', 167, 1),
(99, 'Arusha', 217, 1),
(100, 'Ashanti', 77, 1);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

and the whole php file.

<?php
$username = "username";
$password = "password";
$hostname = "localhost"; 

//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password)
 or die("Unable to connect to MySQL");
echo "Connected to MySQL<br><br>";

//select a database to work with
$selected = mysql_select_db("database",$dbhandle)
  or die("Could not select database"); 

        //choose the table and return records
    $result = mysql_query("SELECT id, state,country_id FROM state ORDER BY country_id");
    $string .= '"'.$val.'", ';
    $country = null;
    // initialize $str
    $str = '';
    //get group
    while($row = mysql_fetch_assoc($result)) {
    if ($row['country_id'] != $country) {

    // I do not know why these are
    echo '<br><br>';
    $str .= "[".($row['country_id'])."] = parentarray [";
    $country = $row['country_id'];
    }
    // get list
    $str .= "'".$row['id'] . "|" . $row['state']."',";
    }
    // replace trailing , with ]
    $newStr = substr_replace($str, ']', -1, 1);
    echo $newStr;
    //end of group
    $db = null;
    echo "<br>Fetched data successfully\n<br>";
    mysql_close($conn);
    ?>

and a little about what I am trying to do...

I am working on a joomla site with a third party component (RSForms Pro) and I am trying to create a dynamic/chained drop down list of country/state/city for users to select on a form.

The component gives the following code as to how to go about setting up the select box:-

<script type="text/javascript">
function dynamic2(parent,child)
{
    var parent_array = new Array();
    // This is the default value
    parent_array[''] = ['Select a State/Region'];
    // All other elements
    // parent_array['PARENT NAME'] = ['CHILD 1','CHILD 2','CHILD 3','ETC'];


parent_array['2'] = ['136|Balkhs','481|Herat','564|Kabol','979|Qandahar'];

parent_array['3'] = ['167|Benguela','503|Huambo','705|Luanda','823|Namibe'];


    // Get the child
    var thechild = document.getElementById(child);

    // Remove all other options from the select element
    thechild.options.length = 0;

    // What value are we looking for ?
    var parent_value = parent.options[parent.selectedIndex].value;

    // No value found, use the default value
    if (!parent_array[parent_value]) parent_value = '';

    // Set the correct length
    thechild.options.length = parent_array[parent_value].length;

    // Add the options
    for(var i=0;i<parent_array[parent_value].length;i++)
        {
        var x = parent_array[parent_value][i].split('|');
        thechild.options[i].text = x[1];
        thechild.options[i].value = x[0];
        }
         }
</script>

So you can see I am trying to extract the "parent_array" information from the state database and output it so I can copy and paste it ready formatted into the above script. A bit long winded and not good in the long term as the database data will probably change, but due to my lack of experience i don't really see any other way to get it done at the moment, and my goal is to get it up and running quickly and work on fine tuning it later. My thinking is if I can work out how to extract that array information in php/mysql I might be able to figure out how to adapt the above code to work directly from the database and not rely on static information being added by hand.

Hope that makes sense.

[broj1]

First to let you know that I do not know Joomla at all.

You can prepare a function that will return the string, representing the arrays, appropriate for your javascript. My suggestion is the following:

function getStatesArrayString() {

    //choose the table and return records
    $result = mysql_query("SELECT id, state,country_id FROM state ORDER BY country_id");

    // initialize temporary string for one row
    $tempStr = '';

    // initialize the string that will be returned
    $retStr = '';

    // current value of country_id
    $country = null;

    //get group
    while($row = mysql_fetch_assoc($result)) {

        // we are starting new country
        if ($row['country_id'] != $country) {

            // echo the array string only when $country is not null
            // (which is only on beginning)
            if($country != null) {

                // first eplace trailing , with ]
                $tempStr = substr_replace($tempStr, ']', -1, 1);

                // now add the tempoprary string to return string
                // semicolon and newline chars were added for javascript
                $retStr .= $tempStr . ";\n";
            }

            $country = $row['country_id'];
            $tempStr = "[$country] = parent_array [";
        } 

        // we are appending to the existing country
        $tempStr .= "'{$row['id']}|{$row['state']}',";                

    }

    // once finished return the return string
    return $retStr;
}

This function will need a database connection already established (or you can add that to the function). The function will return a string that will represent a javascript code that assigns the to the array. You can change the function once the database changes.

To test the function do the following:

 echo '<pre>' . getStatesArrayString() . '</pre>';

[broj1]

The output I get is:

[6] = parent_array ['70|Andorra la Vella'];
[8] = parent_array ['3|Abu Dhabi','16|Ajman'];
[19] = parent_array ['79|Antwerpen'];
[24] = parent_array ['32|al-Manama'];
[31] = parent_array ['60|Amazonas','48|Alagoas','5|Acre','59|Amapá'];
[38] = parent_array ['50|Alberta'];
[41] = parent_array ['78|Antofagasta'];
[42] = parent_array ['72|Anhui'];
[46] = parent_array ['77|Antioquia'];
[55] = parent_array ['71|Anhalt Sachsen'];
[60] = parent_array ['74|Annaba','53|Alger'];
[62] = parent_array ['26|al-Daqahliya','27|al-Faiyum','28|al-Gharbiya','42|al-Sharqiya','51|Aleksandria','38|al-Qalyubiya','33|al-Minufiya','25|al-Buhayra','34|al-Minya'];
[65] = parent_array ['67|Andalusia','88|Aragonia'];
[67] = parent_array ['61|Amhara','7|Addis Abeba'];
[71] = parent_array ['56|Alsace','86|Aquitaine'];
[76] = parent_array ['11|Adzaria [Atšara]','1|Abhasia [Aphazeti]'];
[77] = parent_array ['100|Ashanti'];
[97] = parent_array ['4|Aceh'];
[98] = parent_array ['68|Andhra Pradesh'];
[101] = parent_array ['90|Ardebil'];
[102] = parent_array ['20|al-Anbar','44|al-Tamim','43|al-Sulaymaniya','35|al-Najaf','37|al-Qadisiya'];
[105] = parent_array ['2|Abruzzit','82|Apulia'];
[107] = parent_array ['45|al-Zarqa','62|Amman'];
[108] = parent_array ['15|Aichi','81|Aomori','18|Akita'];
[109] = parent_array ['84|Aqtöbe','54|Almaty','55|Almaty Qalasy'];
...

[broj1]

And this is the whole script:

$username = "username";
$password = "password";
$hostname = "localhost";
//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");
echo "Connected to MySQL<br><br>";
//select a database to work with
$selected = mysql_select_db("database",$dbhandle)
or die("Could not select database");

function getStatesArrayString() {

    //choose the table and return records
    $result = mysql_query("SELECT id, state,country_id FROM state ORDER BY country_id");

    // initialize temporary string for one row
    $tempStr = '';

    // initialize the string that will be returned
    $retStr = '';

    // current value of country_id
    $country = null;

    //get group
    while($row = mysql_fetch_assoc($result)) {

        // we are starting new country
        if ($row['country_id'] != $country) {

            // echo the array string only when $country is not null
            // (which is only on beginning)
            if($country != null) {

                // first eplace trailing , with ]
                $tempStr = substr_replace($tempStr, ']', -1, 1);

                // now add the tempoprary string to return string
                // semicolon and newline chars were added for javascript
                $retStr .= $tempStr . ";\n";
            }

            $country = $row['country_id'];
            $tempStr = "[$country] = parent_array [";
        } 

        // we are appending to the existing country
        $tempStr .= "'{$row['id']}|{$row['state']}',";                

    }

    // once finished return the return string
    return $retStr;
}

// test what function returned
echo '<pre>' . getStatesArrayString() . '</pre>';

//end of group
$db = null;
echo "<br>Fetched data successfully\n<br>";
mysql_close($conn);
?>

[tsp003]

Thank you so much.

This bit of code has done the job, absolutely spot on.

Although the Javascript doesn't work in the joomla side of things yet, you've given me a great head start and I can't thank you enough.

[broj1]

You are welcome. Happy coding.