X-Cart forums

[Stormer]

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]

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

//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]

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:

//X-Cart initializtion
require_once 'top.inc.php';

[Stormer]

Thank You! It Works!

[qualiteam]

You can execute raw MySQL queries like this:

    $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.