Inserting array into MySQL database

Question

This really shouldn't be that difficult, but none of the solutions on here have worked for me.

Here's my code snippet. Assume that $result contains the array without a problem. The issue is inserting it.

This code gives me the rather helpful error of:

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 '://www.asos.com/Dico-Copenhagen/DCo-Copenhagen-Suede-Fold-Over-Ankle-Boot/Prod/p' at line 1

PHP:

$result = array();

foreach ($product_urls as $i => $product_url)
{
    $result[] = array(
        'product_url' => $product_url,
        'shop_name' => $shop_name,
        'photo_url' => $photo_url[$i],
        'was_price' => $was_price[$i],
        'now_price' => $now_price[$i]
        );
}

$sql = array();

foreach($result as $row)
{
    $sql[] = "(" . mysqli_real_escape_string($con, $row['product_url']) . ", " .$row['shop_name'] . ")";
}

mysqli_query($con, 'INSERT INTO shopaholic (product_url, shop_name) VALUES '.implode(', ', $sql)) or die(mysqli_error($con));

EDIT

This code:

echo 'INSERT INTO shopaholic (product_url, shop_name) VALUES ' . implode(', ', $sql);

Outputs as:

 INSERT INTO shopaholic (product_url, shop_name) VALUES
 (http://www.asos.com/Dico-Copenhagen/DCo-Copenhagen-Suede-Fold-Over-Ankle-Boot/Prod/pgeproduct.aspx?iid=1364346&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Grey%2fblack,
 ASOS),
 (http://www.asos.com/Pollini/Pollini-for-Louise-Gray-Heeled-Sandals/Prod/pgeproduct.aspx?iid=1530692&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=White%2fpink%2fnavy,
 ASOS),
 (http://www.asos.com/Pollini/Pollini-for-Michael-van-der-Ham-Velvet-Heeled-Sandals/Prod/pgeproduct.aspx?iid=1530715&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Yellow,
 ASOS),
 (http://www.asos.com/Pollini/Pollini-Colour-Block-Heeled-Sandals/Prod/pgeproduct.aspx?iid=2059051&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Green%2fgrey,
 ASOS),
 (http://www.asos.com/B-Store/B-Store-Salavatore-12-Exclusive-Wedge-Sandals/Prod/pgeproduct.aspx?iid=2262866&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Blue,
 ASOS),
 (http://www.asos.com/ASOS/ASOS-Lace-Dress-With-Dropped-Waist/Prod/pgeproduct.aspx?iid=2435629&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Black,
 ASOS),
 (http://www.asos.com/ASOS/ASOS-Shift-Dress-With-Pleated-Dropped-Waist/Prod/pgeproduct.aspx?iid=2547064&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Green,
 ASOS),
 (http://www.asos.com/ASOS/ASOS-Skater-Dress-With-Lace-Angel-Sleeves/Prod/pgeproduct.aspx?iid=2593859&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Blue,
 ASOS),
 (http://www.asos.com/ASOS/ASOS-Cropped-Utility-Jacket/Prod/pgeproduct.aspx?iid=2639913&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Khaki,
 ASOS),
 (http://www.asos.com/ASOS/ASOS-Bodycon-Dress-In-Stripe-Print/Prod/pgeproduct.aspx?iid=2677844&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Print,
 ASOS),
 (http://www.asos.com/ASOS/ASOS-Ridley-High-Waist-Ultra-Skinny-Jeans-In-Pink-Marble-Wash/Prod/pgeproduct.aspx?iid=2688103&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Pink,
 ASOS),
 (http://www.asos.com/ASOS/ASOS-Fringe-Denim-Gilet/Prod/pgeproduct.aspx?iid=2690930&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Indigo,
 ASOS),
 (http://www.asos.com/ASOS/ASOS-Western-Jacket-with-Bird-Embroidery/Prod/pgeproduct.aspx?iid=2695926&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Light+wash+stone, ASOS),
 (http://www.asos.com/MiH-Jeans/Mih-Jeans-Breathless-Painted-Spot-Skinny-Jeans/Prod/pgeproduct.aspx?iid=2701072&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Mid+wash,
 ASOS),
 (http://www.asos.com/Lazy-Oaf/Lazy-Oaf-Exclusive-Letter-Body-Con-Dress/Prod/pgeproduct.aspx?iid=2706215&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=White%2fblack,
 ASOS),
 (http://www.asos.com/Lazy-Oaf/Lazy-Oaf-Exclusive-Chain-Print-Body-Con-Dress/Prod/pgeproduct.aspx?iid=2706256&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Black,
 ASOS),
 (http://www.asos.com/ASOS/ASOS-Brady-Low-Rise-Slim-Boyfriend-Jeans-in-Bleach-Wash/Prod/pgeproduct.aspx?iid=2707895&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Bleach,
 ASOS),
 (http://www.asos.com/ASOS/ASOS-Elgin-Supersoft-Skinny-Jeans-in-Washed-Peony/Prod/pgeproduct.aspx?iid=2715194&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Peony,
 ASOS),
 (http://www.asos.com/ASOS-Petite/ASOS-PETITE-Exclusive-Maxi-Dress-In-Textured-Stripe/Prod/pgeproduct.aspx?iid=2727450&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Nude+pink,
 ASOS),
 (http://www.asos.com/Citizens-of-Humanity/Citizens-Of-Humanity-Rose-Print-Avedon-Skinny-Jeans/Prod/pgeproduct.aspx?iid=2740983&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Light+blue,
 ASOS),
 (http://www.asos.com/ASOS/ASOS-A-Line-Skirt-in-Leather-Look/Prod/pgeproduct.aspx?iid=2744422&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Black,
 ASOS),
 (http://www.asos.com/Current/Elliott/Current/Elliott-Tribal-Fringed-Skinny-Jeans/Prod/pgeproduct.aspx?iid=2753356&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Tribal,
 ASOS),
 (http://www.asos.com/ASOS/ASOS-Elgin-Skinny-Jeans-in-Acid-Wash-Stripe-with-Rips/Prod/pgeproduct.aspx?iid=2779459&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Multi,
 ASOS),
 (http://www.asos.com/ASOS/ASOS-Stud-Skinny-Waist-Belt/Prod/pgeproduct.aspx?iid=2819373&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Black,
 ASOS),
 (http://www.asos.com/People-Tree/People-Tree-by-Zandra-Rhodes-Jungle-Trail-Statement-Dress/Prod/pgeproduct.aspx?iid=2824591&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Black,
 ASOS),
 (http://www.asos.com/ASOS/ASOS-Swing-Dress-with-Dropped-Waist/Prod/pgeproduct.aspx?iid=2855063&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Green,
 ASOS),
 (http://www.asos.com/ASOS/ASOS-Brady-Slim-Boyfriend-Jeans-in-Dark-Washed-Indigo/Prod/pgeproduct.aspx?iid=2868426&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Dark+vintage+blue,
 ASOS),
 (http://www.asos.com/ASOS/ASOS-Premium-Link-Necklace/Prod/pgeproduct.aspx?iid=2876009&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Gold,
 ASOS),
 (http://www.asos.com/Federation/Federation-Slant-Skirt/Prod/pgeproduct.aspx?iid=2878511&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Wash+out,
 ASOS),
 (http://www.asos.com/ASOS/ASOS-Pencil-Dress-With-Lace-Panels-And-Plunge-Neck/Prod/pgeproduct.aspx?iid=2888606&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Jade,
 ASOS),
 (http://www.asos.com/ASOS-Curve/ASOS-CURVE-Mixed-Plate-Waist-Belt/Prod/pgeproduct.aspx?iid=2888978&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Blue,
 ASOS),
 (http://www.asos.com/ASOS-Curve/ASOS-CURVE-Wide-Waist-Belt/Prod/pgeproduct.aspx?iid=2889088&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Black,
 ASOS),
 (http://www.asos.com/ASOS/ASOS-Arrow-Multi-Plate-Waist-Belt/Prod/pgeproduct.aspx?iid=2892493&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Black,
 ASOS),
 (http://www.asos.com/ASOS/ASOS-Stud-And-Gem-Plate-Waist-Belt/Prod/pgeproduct.aspx?iid=2892645&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Black,
 ASOS),
 (http://www.asos.com/ASOS-Petite/ASOS-PETITE-Fringe-Denim-Gilet/Prod/pgeproduct.aspx?iid=2898386&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Blue,
 ASOS),
 (http://www.asos.com/ASOS/ASOS-Shift-Dress-with-Zip-Front/Prod/pgeproduct.aspx?iid=2901965&cid=16903&sh=0&pge=0&pgesize=36&sort=-1&clr=Green,
 ASOS)

Using MySQLi, but not using prepared statements/bind variables :( — Mark Baker
– Mark Baker, Commented Mar 29, 2014 at 13:17

Abhik Chakraborty · Accepted Answer · 2014-03-29 13:39:04Z

2

change

$sql[] = "(" . mysqli_real_escape_string($con, $row['product_url']) . ", " .$row['shop_name'] . ")";

to

$sql[] = "('" . mysqli_real_escape_string($con, $row['product_url']) . "', '".$row['shop_name'] . "')";

edited Mar 29, 2014 at 13:39

answered Mar 29, 2014 at 13:30

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

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

4 Comments

Sebastian Over a year ago

Thanks for that. There was a small error with two quotes being the wrong way round which it won't let me edit. The final code is $sql[] = "('" . mysqli_real_escape_string($con, $row['product_url']) . "', '" .$row['shop_name'] . "')";

Abhik Chakraborty Over a year ago

u mean still having issues ?

Sebastian Over a year ago

No, all fixed. But you should update your answer to the code I gave. It's "', '" rather than "', "', that's all.

AlexL Over a year ago

You should know that there is a limit to the actual character length of a query. So for a large number (lets say over 1000) of value sets you will receive an error.

Funk Forty Niner · Accepted Answer · 2014-03-29 13:53:41Z

0

Just change this line:

$sql[] = "(" . mysqli_real_escape_string($con, $row['product_url']) . ", " .$row['shop_name'] . ")";

To this:

$sql[] = "('" . mysqli_real_escape_string($con, $row['product_url']) . "','" .$row['shop_name'] . "')";

You are missing ' single quotes around your values.

It should be ('aaaaa','dddddd') not (aaa,ddddd)

edited Mar 29, 2014 at 13:53

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

answered Mar 29, 2014 at 13:33

ashraf mohammed

1,36015 silver badges20 bronze badges

Collectives™ on Stack Overflow

Inserting array into MySQL database

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related