Jump to content

Insert position in ps_category_product table


ovy79ro
 Share

Recommended Posts

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:[email protected]+1;

 

Thank you very much!

Edited by ovy79ro (see edit history)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 by ovy79ro (see edit history)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More