PHP/MYSQL - Insert multiple rows into one column, works via phpmyadmin but only partially via myqli prepared statement?

Question

Using a mysqli prepared statement I would like to insert an array into a mysql database table.

Being aware that bind-param and arrays do not go together, we would like to write the query in php first, then process this as a prepared statement:

$tagQuery = "INSERT INTO word_tags(speaks) VALUES ";
// Count total array values
$icoderayInsideCount = count($icoderayInside);
foreach ($icoderayInside as $icoderayInsideKey=>$icoderayInsideValue)
{
    // Last value
    //  Currrent Array Key    Total / Last Array Value
    if ($icoderayInsideKey == $icoderayInsideCount)
    {
        $tagQuery .= "('$icoderayInsideValue')";
    }
    // All other values
    else
    {
        $tagQuery .= "('$icoderayInsideValue'), ";
    }           
}

// Send array (keywords) to database
if ($stmt2 = $link2->prepare($tagQuery))
{
    if (!$stmt2->execute())
    {
        // #2 If it can prepare but can't execute, why?
        echo "Error {$link2->errno}  :  {$link2->error} (Cant execute?) <br/>";
        // Dump query to check the end result
        var_dump($tagQuery);
        exit();
    }
    $stmt2->close();
}
else
{
    // #1 If it cant prepare, why?
    echo "Error {$link2->errno}  :  {$link2->error} (Cant prepare?)";
    exit();
}

When i run this via PHP / Server i get:

Error 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('DONGLES'),' at line 1 (Cant prepare?)

So, the statement prepares but is not being executed.

The value of the generated query, $tagQuery is:

INSERT INTO word_tags(speaks) VALUES ('PMP3670B_BK'), ('PRESTIGIO'), ('MULTIPAD'), ('7"'), ('800X480'), ('1GHZ'), ('ARM'), ('CORTEX'), ('A8'), ('CPU'), ('512MB'), ('DDR3'), ('DRAM'), ('ANDROID'), ('4.1'), ('JELLY'), ('BEAN'), ('MALI'), ('400'), ('MP'), ('GPU'), ('VIDEO'), ('4GB'), ('INTERNAL'), ('FLASH'), ('MEMORY'), ('SUPPORT'), ('32GB'), ('SDHC/SD'), ('USB/WI-FI/HEADSET'), ('PORT'), ('LITHIUM'), ('POLYMER'), ('BLACK'), ('HDMI'), ('OUTPUT'), ('UPTO'), ('1080'), ('HD'), ('USB2.0'), ('MINI'), ('HIGH'), ('SPEED'), ('FOR'), ('3G'), ('DONGLE'), ('OTG'), ('CABLE'), ('INCLUDED')

The fourth value from the end is('DONGLE') which is what the error message is complaining about.

When i run this exact same query through phpmyadmin there is no error involved.

What i assume is happening, is that there is some kind of length limit involved within creating a prepared statement... Or something to this effect.

Have scratched my brains for hours now to try to solve this and have not found any relating information.

If anyone could offer some assistance / advice / indication / input or otherwise as to what the conflict of problem may be within this, it would be GREATLY appreciated.

Thanks so much for the time and effort in readying through this!

EDIT:

@Mihai - Thanks for the thought.

It seems that the word dongle does have something string to it. In the original string, before being parsed to an array, it looks like this: DONGLE,

I run preg_replace to remove this comma from the string before parsing it to an array:

$icode = preg_replace('#,#', '', $icode);

Then into an array:

$icoderayInside = explode(" ", $icode);

Still cannot think of any reason this would cause a conflict as the output string, the query is as i have previously stated and includes no comma, or anything... Any would be greatly appreciated!

EDIT 2:

@ShadyAtef

Original input is stored in mysql as varchar, latin_general_ci:

PRESTIGIO MULTIPAD, 7" 800X480, 1GHZ ARM CORTEX A8 CPU, 512MB DDR3 DRAM, ANDROID 4.1 JELLY BEAN, MALI 400 MP GPU, VIDEO, 4GB INTERNAL FLASH MEMORY, SUPPORT 32GB SDHC/SD, USB/WI-FI/HEADSET PORT, LITHIUM POLYMER, BLACK, HDMI OUTPUT UPTO 1080 HD, USB2.0 MINI HIGH SPEED PORT FOR 3G DONGLE, OTG CABLE INCLUDED

Brought into php then processed to an array with additional requirements:

// Convert Var a + b to String      
$icode = $itemCode . ' ' . $description;  
// Clean of Unwanteds
$icode = preg_replace('#,#', '', $icode);
$icode = preg_replace('#\(#', '', $icode);
$icode = preg_replace('#\)#', '', $icode);
// Creates array from sting
$icoderayInside = explode(" ", $icode);
// Remove array duplicates
$icoderayInside = array_unique($icoderayInside);

Before being built into the query. Any assistance would be GREATLY appreciated!

EDIT 3:

@ShadyAtef

//  Currrent Array Key    Total / Last Array Value
if ($icoderayInsideKey == $icoderayInsideCount)
{
    // dump here shows:
    $icoderayInsideKey == 49
    $icoderayInsideCount == 49
}

Try removing the dongle string(from the array) and see the result. — Mihai
– Mihai, Commented Sep 7, 2013 at 22:58
@Mahai Hi, thanks for your feedback! please see the edit at the end of the question. — Craig van Tonder
– Craig van Tonder, Commented Sep 7, 2013 at 23:07
@ShadyAtef Hi and thank you for your input! Please see the Edit 2 to my question. — Craig van Tonder
– Craig van Tonder, Commented Sep 7, 2013 at 23:34
This was really tricky,but I got it : The tricky wrong part is that if ($icoderayInsideKey == $icoderayInsideCount) Because the last key in the array equals to the array (length -1) So you should change your if condition — Shady Atef
– Shady Atef, Commented Sep 8, 2013 at 0:16

Shady Atef · Accepted Answer · 2013-09-08 09:16:49Z

1

This was really tricky,but I got it : The tricky wrong part is that

    if ($icoderayInsideKey == $icoderayInsideCount)

It should be

        if ($icoderayInsideKey == ($icoderayInsideCount-1))

Because the last key in the array equals to the array (length -1) So you should change your if condition

answered Sep 8, 2013 at 9:16

Shady Atef

2,4011 gold badge25 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Craig van Tonder Over a year ago

Very, VERY well done... Took very long for me to understand this but you are right. The array keys start from 0 but when we use count, it is counting from 1 and not from 0. THANK YOU!!!!!

Collectives™ on Stack Overflow

PHP/MYSQL - Insert multiple rows into one column, works via phpmyadmin but only partially via myqli prepared statement?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related