How to fetch data from MySQL table and create a custom select menu

Question

I am using Magento 1.9.0.1 developing a custom extension. For that purpose i have to ask how can i run a custom MySQL query.

I want to run simple MySQL query with while loop. If it was a simple PHP script i am going to make it this way:

$r = mysql_query("SELECT * FROM `extensa_econt_city`");

    while($rowi = mysql_fetch_array($r))
            {
            $name = $rowi['name'];
            $city_id = addslashes($rowi['city_id']);
            echo "<option value='$city_id'>$name</option>";
            }

With this code in simple PHP i'll get all the rows and make them as options. I do not know however how can i get the information from table extensa_econt_city which is in the Magento database.

I will use this in a custom template file where i'll display that select menu. So guys can you please show me how can i run custom MySQL queries with while loop in Magento ?

Thanks in advance!

Answer 1

Take a look at Direct SQL Queries In Magento and https://magento.stackexchange.com/a/31329/519

/**
 * Get the resource model
 */
$resource = Mage::getSingleton('core/resource');

/**
 * Retrieve the read connection
 */
$readConnection = $resource->getConnection('core_read');

$query = 'SELECT * FROM ' . $resource->getTableName('extensa_econt_city');
//take a look at your config.xml that added extensa_econt_city table for the correct name   
/**
 * Execute the query and store the results in $results
 */
$results = $readConnection->fetchAll($query);

// display result

foreach($results as $row){
   echo $row['fieldName']
}