1

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.

0

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

?>
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.