X-Cart forums

[cleverwise]

Does anyone know the best way to execute SQL queries inside a function call?

I need to query the database get results then process data from query A to build query B. Then once done I will return the results by leaving the function call.

I am not sure of the safest way to query the database that won't break with X-Cart upgrades. I can't find a developer help document at http://kb.x-cart.com/ explaining the ways on working with database SQL queries in function calls.

Any thoughts?

Thank you!

For example:

 protected function doNoAction() 
    {
        // Execute query A and get results
        // Now read queried results and build query B then run that query

        return $var1;
    }

[rogue]

There are many 'built in' database functions in Xcart. Look in the file include/func/func.db.php

[qualiteam]

I believe Jeremy is asking about X-Cart 5 that does not have the include/func/func.db.php file.

Instead of working with direct SQL queries X-Cart uses Doctrine 2 ORM library that constructs such queries from objects.

Please check this article on retrieving data from the database:
http://kb.x-cart.com/display/XDD/search%28%29+method

Also, you may explain here what query you need and I will try to describe how you can achieve it.

[cleverwise]

Thanks for the feedback. It is correct I am trying to execute custom queries via X-Cart 5.

My queries are to custom tables I have added to the xcart database.

As example how would you handle.

select node_id,node_name,node_status from nodes where nodes_enabled='1';

Thank you!

[qualiteam]

Hello Jeremy,

You should create a new Model class and a new Repository class for that model.

This article should help you:
http://kb.x-cart.com/display/XDD/Creating+new+entity+--+Introduction+of+editable+ItemsList+in+admin+area

[cleverwise]

Thanks for the reply. It takes a lot of files just to get a database query to run.

I am still not completely sure how you would take the results from query A, parse it via PHP, then run query B, parse that information, then display to the browser (visitor).

I guess one needs multiple Model classes.

I wish I could just run a query via:

$qb="select col1,col2,col3 from tableA where id='1'";

Get results in a row

foreach ($qb as $row) {
$col1=$row[0];
$col2=$row[1];
// etc
}

Then from that:

$qb="select colA,colB from tableB where id='$col1'";

foreach ($qb as $row) {
$colA=$row[0];
// etc
}

[qualiteam]

For each table you need its own model class and the repository class. And you are not to create the SQL table manually - X-Cart 5 will create it from your Model class automatically.

Model classes define table columns and methods to work with values stored in a single entity, while the repository class provides methods to search/retrieve model classes.

You should avoid using direct SQL queries as this makes your code less future-proof and less customization-friendly.

PHP Code:

$qb="select col1,col2,col3 from tableA where id='1'"; 


When using model and repository classes this will look as follows:

PHP Code:

$repo = \XLite\Core\Database::getRepo('\XLite\Modules\YOUR_ID\MODULE_ID\Model\YOUR_MODEL_CLASS');

foreach ($repo->findById(1) as $entity) {
   $col1 = $entity->getColumn1(); // reading the $column1 property/column of the model
   $col2 = $entity->getName();     // reading the $name property/column of the model
} 


For more complex queries you may use a custom search() method (see the example in the article).

If you have two linked tables you should look into using @ManyToOne, @OneToMany and @OneToOne relations. X-Cart 5 uses Doctrine 2 ORM library, so most of these will work for X-Cart 5 too:
- http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/association-mapping.html
- http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/working-with-associations.html
You may also look for real examples in the classes\XLite\Model\ directory.