Insert position in ps_category_product table

[ovy79ro]

Hello,

 

Can someone tell me how to insert consecutive position in ps_category_product table? I have to insert products from external xml file and i use an foreach function:

 

foreach ($xml as $value)
{..................}

 

$sql = "INSERT INTO `ps_category_product` (
`id_category`,
`id_product`,
`position`
)
VALUES (
'72',
LAST_INSERT_ID(),
'???'
)";

 

I think i should use the following syntax but i don't know how to implement it in my script:

SELECT @i:=0;
UPDATE ps_category_product SET position = @i:=@i+1;

 

Thank you very much!

Edited April 26, 2015 by ovy79ro (see edit history)

[PascalVG]

Assuming you do the foreach from php, why don;t you justt create a php variable i, which you increase in the loop, and add as the value in the sql insert statement directly, instead of updating afterwards?

 

Something like:

 

function importMyXML($xml)

{

    $i = 0;

    foreach ($xml as $value) {

 

        $sql = "INSERT INTO `ps_category_product` (
                `id_category`,
                `id_product`,
                `position`
            )
            VALUES (
                '72',
                LAST_INSERT_ID(),
                ".$i."
            )";

 

        if (!$res = Db::getInstance()->executeS($sql))

            return false;

        $i++;

    }

    return true;

}

 

 

 

 

 

 

(I assume LAST_INSERT_ID() comes from the insert of a product just before this import here, right?)

 

 

 

Something like this.

 

Hope this helps,

pascal

[ovy79ro]

Thank you very much. This is exactly what i need. But now I have another issue: every time i make an import of products from xml file, position is entered from zero. For example, if i make totay an import of 1000 products from xml, position is insertet from 0 to 999 (which is correct). Tomorow, if i make another insert of 1000 products from xml file, the position is also insertet from 0 to 999 (which is incorrect, the position must be from 1000 to 1999 in the same category. In my script, category id is set to 72).

 

Yes, LAST_INSERT_ID() comes from the insert of a product just before this import.

 

Sorry for my bad english.

Edited April 27, 2015 by ovy79ro (see edit history)

[PascalVG]

Hi Ovi,

 

If you want to start he position somewhere else, just change the number of $i:

 

So change

$i = 0;

Into:

$i = 1001;

 

In the code above

 

Hope that does it,

Pascal

[ovy79ro]

Hello.

 

 

Is there a problem if i don't insert position in ps_category_product? I did a test and products are displayed without problems.

Another solution would be to take somehow the last value from the column position and assign it to $i. I can't change the number of $i every time i insert products.

[PascalVG]

Hi Ovy,

 

Sure, if you just always want to continue where you left off the last time, you can find the max value of position, and add one to it:

 

function importMyXML($xml)

{

    foreach ($xml as $value) {

 

        $sql = "INSERT INTO `ps_category_product` (

                `id_category`,

                `id_product`,

                `position`

            )

            SELECT 

                '72',

                LAST_INSERT_ID(),

                MAX(position)+1

             FROM `ps_category_product`";

 

        if (!$res = Db::getInstance()->executeS($sql))

            return false;

    }

    return true;

}

 

(Hope the syntax is OK, didn't check)

 

pascal.