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!

• simonthesorcerer: Just want to comment that you shouldn't do direct SQL queries in the template file... a resource model is the correct place for that, and you don't need direct SQL most of the time - instead use Magento's MVC structure. What is the structure of your extension? I could then provide an example.

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']
}

• Venelin Vasilev: Where is the echo ? :D