First and foremost, I highly recommend you buy the PDF/E-Book from PHP Architect. It's $20, but is the only straight forward "Here's how Magento works" resource I've been able to find. I've also started writing Magento tutorials at my own website.
Second, if you have a choice, and aren't an experienced programmer/don't have access to an experienced programmer (ideally in PHP AND Java), pick another cart. Magento is well engineered, but it was engineered to be a shopping cart solution that other programmers can build modules on top of. It was not engineered to be easily understood by people who are smart, but aren't programmers.
Third, Magento MVC is very different from the Rails/Django/Code Igniter/Cake/etc MVC model that's popular with PHP developers these days. I think it's based on the Zend model, and the whole thing is very Java OOP-like. There's two controllers you need to be concerned about. The module/frontName controller, and then the MVC controller.
Fourth, the Magento application itself is built using the same module system you'll be using, so poking around the core code is a useful learning tactic. Also, a lot of what you'll be doing with Magento is overriding existing classes. What I'm covering here is creating new functionality, not overriding. Keep this in mind when you're looking at the code samples out there.
I'm going to start with your first question, showing you how to setup a controller/router to respond to a specific URL. This will be a small novel. I might have time later for the model/template related topics, but for now, I don't. I will, however, briefly speak to your SQL question.
Magento uses a EAV database architecture. Whenever possible, try to use the model objects the system provides to get the information you need. I know it's all there in the SQL tables, but it's best not to think of grabbing data using raw SQL queries, or you'll go mad.
Final disclaimer. I've been using Magento for about two or three weeks, so caveat emptor. This is an exercise to get this straight in my head as much as it is to help StackOverflow.
Create a Module
All additions and customizations to Magento are done through modules. So, the first thing you'll need to do is create a new module. Create an XML file in app/modules named as follows
cd /path/to/store/app
touch etc/modules/MyCompanyName_HelloWorld.xml
<?xml version="1.0"?>
<config>
<modules>
<MyCompanyName_HelloWorld>
<active>true</active>
<codePool>local</codePool>
<MyCompanyName_HelloWorld>
</modules>
</config>
MyCompanyName is a unique namespace for your modifications, it doesn't have to be your company's name, but that the recommended convention my magento. HelloWorld is the name of your module.
Clear the Application Cache
Now that the module file is in place, we'll need to let magento know about it (and check our work). In the admin application
- Go to System->Cache Management
- Select Refresh from the All Cache menu
- Click Save Cache settings
Now, we make sure that magento knows about the module
- Go to System->Configuration
- Click Advanced
- In the "Disable modules output" setting box, look for your new module named "MyCompanyName_HelloWorld"
If you can live with the performance slow down, you might want to turn off the application cache while developing/learning. Nothing is more frustrating then forgetting the clear out the cache and wondering why your changes aren't showing up.
Setup the Directory Structure
Next, we'll need to setup a directory structure for the module. You won't need all these directories, but there's no harm in setting them all up now.
mkdir -p app/code/local/MyCompanyName/HelloWorld
mkdir -p app/code/local/MyCompanyName/HelloWorld/Block
mkdir -p app/code/local/MyCompanyName/HelloWorld/controllers
mkdir -p app/code/local/MyCompanyName/HelloWorld/Model
mkdir -p app/code/local/MyCompanyName/HelloWorld/Helper
mkdir -p app/code/local/MyCompanyName/HelloWorld/etc
mkdir -p app/code/local/MyCompanyName/HelloWorld/sql
And add a config file
touch app/code/local/MyCompanyName/HelloWorld/etc/config.xml
and inside the config file, add the following, which is essentially a "blank" config.
<?xml version="1.0"?>
<config>
<modules>
<mycompanyname_helloworld>
<version>
0.1.0
</version>
</mycompanyname_helloworld>
</modules>
</config>
Oversimplifying things, this config file will let you tell Magento what code you want to run.
Setting up the Router
Next, we need to setup the module's routers. This will let the system know that we're handling any urls in the form of
http://example.com/magento/index.php/helloworld
So, in your config file, add the following section.
<config>
<!-- ... -->
<frontend>
<routers>
<!-- the <helloworld> tagname appears to be arbitrary, but by
convention is should match the frontName tag below-->
<helloworld>
<use>standard</use>
<args>
<module>MyCompanyName_HelloWorld</module>
<frontName>helloworld</frontName>
</args>
</helloworld>
</routers>
</frontend>
<!-- ... -->
</config>
What you're saying here is "any url with the frontName of helloworld ...
http://example.com/magento/index.php/helloworld
should use the frontName controller MyCompanyName_HelloWorld".
So, with the above config in place, when you load the helloworld page above, you'll get a 404 page. That's because we haven't created a file for our controller. Let's do that now.
touch app/code/local/MyCompanyName/HelloWorld/controllers/IndexController.php
Now try loading the page. Progress! Instead of a 404, you'll get a PHP/Magento Exception
Controller file was loaded but class does not exist
So, open the file we just created, and paste in the following code. The name of the class needs to be based on the name you provided in your router.
class MyCompanyName_HelloWorld_IndexController extends Mage_Core_Controller_Front_Action{
public function indexAction(){
echo "We're echoing just to show that this is what's called, normally you'd have some kind of redirect going on here";
}
}
What we've just setup is the module/frontName controller. This is NOT the MVC controller. Try the following URL, and you'll get a 404, even if you had a fooAction method in MyCompanyName_HelloWorld_IndexController
http://example.com/magento/index.php/helloworld/foo
So, to setup the MVC controller, create a new file, and enter the following code (Yes, this also extends the Mage_Core_Controller_Front_Action controller, even though it's not a Front Action. Yes, this is confusing, but I think it's how things are done)
touch app/code/local/MyCompanyName/HelloWorld/controllers/FooController.php
class MyCompanyName_HelloWorld_FooController extends Mage_Core_Controller_Front_Action{
public function indexAction(){
echo 'Foo Index Action';
}
public function addAction(){
echo 'Foo add Action';
}
public function deleteAction(){
echo 'Foo delete Action';
}
}
Yo should now be able to hit the following URLs and see the results of your echo statments
http://example.com/magento/index.php/helloworld/foo
http://example.com/magento/index.php/helloworld/foo/add
http://example.com/magento/index.php/helloworld/foo/delete
So, that should give you a basic idea on how Magento dispatches to a controller. From here I'd recommended poking at the existing Magento controller classes to see how models and the template/layout system should be used.