X-Cart forums - Running Simple Selects on 3rd party table

X-Cart forums (https://forum.x-cart.com/index.php)

- Dev Questions (X-Cart 5) (https://forum.x-cart.com/forumdisplay.php?f=56)

- - Running Simple Selects on 3rd party table (https://forum.x-cart.com/showthread.php?t=73931)

Stormer

05-02-2016 04:59 PM

Running Simple Selects on 3rd party table

Hello,

I would like to run some simple selects on a 3rd party table in my X-Cart 5.2 database. I tried to create an entity class and access it that way, but I noticed the X-Cart table prefix was added on so it just created a new table.

Any sample code someone can share to point me in the right direction?

Thanks!

BurtonTech

05-03-2016 02:25 PM

Re: Running Simple Selects on 3rd party table

Not sure if this is the best solution, but it works for MySQLi:

Code:

//Get the Connection info from X-Cart 5

$xc_conn = \XLite\Core\Database::getEM()->getConnection();



//Collect the info you need for a MySQLi connection

$sql_host =$xc_conn->getHost();

$sql_db =$xc_conn->getDatabase();

$sql_user =$xc_conn->getUsername();

$sql_password =$xc_conn->getPassword();



//Collect your data using MySQLi

$conn = new mysqli($sql_host, $sql_user, $sql_password,$sql_db);



if ($conn->connect_error) {

    error_log('MySQLi could not connect: ' . $conn->connect_error);

}

        

$sql_query = "SELECT * FROM YourTable";



$db_results = $conn->query($sql_query);



$results_array = $db_results->fetch_assoc();



if ($db_results->num_rows > 0) {

    foreach($results_array  as $dbkey=>$dbvalue) {

        echo 'Key: '.$dbkey.' = '.$dbvalue.'<br />';

    }

}

Hope that helps!

BurtonTech

05-03-2016 02:36 PM

Re: Running Simple Selects on 3rd party table

I should also note you need to make sure to initialize X-Cart 5 by including top.inc.php if you're running this outside of the X-Carts's object model in your own PHP file:

Code:

//X-Cart initializtion

require_once 'top.inc.php';

Stormer

05-03-2016 03:24 PM

Re: Running Simple Selects on 3rd party table

Thank You! It Works!

qualiteam

05-03-2016 10:09 PM

Re: Running Simple Selects on 3rd party table

You can execute raw MySQL queries like this:

Code:

    $tablePrefix = \XLite::getInstance()->getOptions(array('database_details', 'table_prefix'));

    $query = 'UPDATE ' . $tablePrefix . 'orders SET lastVisitDate = 0 WHERE status = "T"';

    \XLite\Core\Database::getEM()->getConnection()->executeQuery($query, array());

You should not use this for X-Cart 5 database tables (and use ORM instead), but it is OK to use this for non-X-Cart-5 tables in the same database.

All times are GMT -8. The time now is 05:51 PM.