Retrieving size attribute text via MySQL in Magento 2

Question

I'm working on a PHP file that will run on cron (outside of the Magento 2 directory). I'm writing some scripts that fetch various data such as customer details and the items they've ordered. Retrieving customer information was easy as it was available in the sales_order table.

All my products are set up as configurable products so I'm struggling to work out how I can get the "size" attribute. I can see ID's throughout the catalog_product and eav tables that link together but I can't quite figure it out!

In this case, I need to retrieve "Small" or "Large", for example - which are value attributes of the "size" EAV attribute.

Answer 1

Thats an old question but here is the way if you still need it.

create database_connection.php in your magento folder

<?php
/*
 * Mysql database class - only one connection alowed
 */
class Database {
    private $_connection;
    private static $_instance; //The single instance
    private $_host = "localhost";
    private $_username = "yourdatabaseusername";
    private $_password = "yourdatabasepass";
    private $_database = "databasename";
    /*
     Get an instance of the Database
     @return Instance
     */
    public static function getInstance() {
        if(!self::$_instance) { // If no instance then make one
            self::$_instance = new self();
        }
        return self::$_instance;
    }
    // Constructor
    private function __construct() {
        $this->_connection = new mysqli($this->_host, $this->_username,
                                        $this->_password, $this->_database);

        // Error handling
        if(mysqli_connect_error()) {
            trigger_error("Failed to conencto to MySQL: " . mysql_connect_error(),
                          E_USER_ERROR);
        }
    }
    // Magic method clone is empty to prevent duplication of connection
    private function __clone() { }
    // Get mysqli connection
    public function getConnection() {
        return $this->_connection;
    }
}
?>

next step is create new php file database_create.php and you have to work with 5 table in phpmyadmin, I assume you know, how to check phpmyadmin tables;

1- eav_attribute: Learn attribute_id for size mine is 137

2- eav_attribute_option: get option_id for 137, we will use array to find that

3- eav_attribute_option_value: learn option_id and value for size

4- catalog_product_entity: get entity_id for your product

5- catalog_product_entity_int: We know all numbers meaning now in this table

I will give you an example how you can collect information to an array from database step 1 to 3 you can use same logic for 4 - 5 steps

<?php
include('database_connection.php');
$db = Database::getInstance();
$mysqli = $db->getConnection();
$sql_query = "SELECT * FROM  `eav_attribute_option` WHERE  `attribute_id` =137";// 137 my size attribute id
$result = $mysqli->query($sql_query);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
    //  echo "id: " . $row["option_id"]. " - Name: " . $row["attribute_id"]. "<br>";
        $sizes_value_id[]=$row["option_id"];//adding array all option_id 
    }
} else {
    echo "0 results";
}
//add a query all value informations
foreach($sizes_value_id as $size){
    $sql_query = "SELECT * FROM `eav_attribute_option_value` WHERE `option_id`='.$size.'"; 
    $result = $mysqli->query($sql_query);
    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
        echo "id: " . $row["option_id"]. " - Name: " . $row["value"]. "<br>";
            $sizes_value[]=$row["value"];//adding all values to an array
        }
    } else {
        echo "0 results";
    }
}
// for understand what we have 
echo "entity_id=".$sizes_value_id[0]."Value=".$sizes_value[0];
// you can use same logics for other steps

?>

Answer 2

You dont need to add the credentials as your class member you can just simply do like this to create connection:

1. declare private object:

private $_objectManager;

2. inject in the constructor and initialize:

public function __construct(
    \Magento\Framework\ObjectManagerInterface $objectmanager
) {
    $this->_objectManager = $objectmanager;
}

3. just use this to create connection:

public function getConnection() {
        $resource = $this->_objectManager->get("Magento\Framework\App\ResourceConnection");
        $connection = $resource->getConnection();
        return $connection;
    }