0

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!

  • 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. – simonthesorcerer Sep 23 '15 at 19:30
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']
}

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.