PHP mysqli_query - INSERT INTO not working, error unclear

Question

I have a lot of variables that I'm adding into a database from all the product pages on my website. Here is my PHP code:

$args = (array(
    "post_type" => "page",
    "meta_key" => "_wp_page_template",
    "meta_value" => "products.php",
    "posts_per_page" => 50
));

$queryPages = get_posts($args);

$con= mysqli_connect("localhost","pmdot","password","pmd_ot");
foreach($queryPages as $qp) {
    $qpID = $qp->ID;
    $name = get_post_meta($qpID, "pmd_name", true);
    $code = get_post_meta($qpID, "pmd_sku", true);
    $installation = get_post_meta($qpID, "pmd_installation", true);
    $age = get_post_meta($qpID, "pmd_agefrom", true);
    $length = get_post_meta($qpID, "pmd_length", true);
    $width = get_post_meta($qpID, "pmd_width", true);
    $height = get_post_meta($qpID, "pmd_height", true);
    $areal = get_post_meta($qpID, "pmd_areal", true);
    $areaw = get_post_meta($qpID, "pmd_areaw", true);
    $cfh = get_post_meta($qpID, "pmd_cfh", true);
    $weight = get_post_meta($qpID, "pmd_weight", true);
    $type[] = get_post_meta($qpID, "pmd_playtype", true);
    $accred[] = get_post_meta($qpID, "pmd_accreditation", true);
    $descr = get_post_meta($qpID, "pmd_shortdesc", true);
    $price = get_post_meta($qpID, "pmd_price", true);

    mysqli_query($con, 'INSERT INTO pmd_productdata (Product_ID, Product_Name, Product_Code, Product_Installation, Age_Range, Product_Length, Product_Width, Product_Height, Minimum_Area_L, Minimum_Area_W, C_F_H, Product_Weight, Play_Type, Accreditations, Product_Desc, Product_Price) 
                        VALUES ($qpID, $name, $code, $installation, $age, $length, $width, $height, $areal, $areaw, $cfh, $weight, "placeholder", "placeholder", $descr, $price') or die ('Error:' .mysqli_error($con));
}

What this is doing is getting all the posts with the template products.php and then using all the custom fields to populate an SQL table with a query.

NOTE: The arrays that you can see are not currently being used in the query as I thought that was the issue. Bonus points if someone can help me get all the values in that array to display in the respective field.

All of the columns in my table have varchar(255) as their char base, just in case some are not INTs when they should be.

For some reason, I'm getting this error:

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 '' at line 1

Now, as you can see the error is rather unclear, if anyone can shine some light on the situation I'd be very grateful.

My question is: Why am I getting that error, and what does it mean?

EDIT [Resolved]

Now I'm getting a new error that I've fixed the VALUES () issue:

Unknown column '$qpID' in 'field list'

Is this because I need to separate the variables from the query: e.g.:

'VALUES (' . $qpID . ', ' . $name . ')' etc.;

EDIT 2

The errors have now been fixed and the data is in the SQL Database, all I need now is to figure out how to get the 2 arrays to print all their values into the respective column.

Jens · Accepted Answer · 2014-07-08 11:18:23Z

3

Here is a ( missing:

$price')

should:

$price)')

answered Jul 8, 2014 at 11:18

Jens

69.6k15 gold badges107 silver badges133 bronze badges

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

5 Comments

Mallander Over a year ago

Thanks for the reply! I really should've spotted that. The current error has been fixed with this suggestion, but now I'm getting a new one (Updated Question)

Jens Over a year ago

@Mallander I am not very good in php but I think you should use double quotes around the query string not a single quote

Mallander Over a year ago

I usually would do, but before I had some items in the VALUES() that had "" surround them so I just changed the two to singles instead of all the rest :P

Jens Over a year ago

@Mallander There you have to use singel quotes!

Mallander Over a year ago

I've just been through and changed it, thanks for the heads up!

Pavel Adrian · Accepted Answer · 2014-07-08 11:28:45Z

2

If you use wordpress you do not need to create a new connection. This you can help you:

global $wpdb;
$wpdb->query('INSERT INTO pmd_productdata (Product_ID, Product_Name, Product_Code, Product_Installation, Age_Range, Product_Length, Product_Width, Product_Height, Minimum_Area_L, Minimum_Area_W, C_F_H, Product_Weight, Play_Type, Accreditations, Product_Desc, Product_Price) 
                        VALUES ('".$qpID."', '".$name."', '".$code."', '".$installation."', '".$age."', '".$length."', '".$width."', '".$height."', '".$areal."', '".$areaw."', '".$cfh."', '".$weight."', "placeholder", "placeholder", '".$descr."', '".$price."')');

answered Jul 8, 2014 at 11:28

Pavel Adrian

1661 gold badge1 silver badge8 bronze badges

Comments

MRJethva · Accepted Answer · 2014-07-08 11:22:20Z

1

Updated query

mysqli_query($con, 'INSERT INTO pmd_productdata (Product_ID, Product_Name, Product_Code, Product_Installation, Age_Range, Product_Length, Product_Width, Product_Height, Minimum_Area_L, Minimum_Area_W, C_F_H, Product_Weight, Play_Type, Accreditations, Product_Desc, Product_Price) 
                        VALUES ("'.$qpID.'", "'.$name.'", "'.$code.'", "'.$installation.'", "'.$age.'", "'.$length.'", "'.$width.'", "'.$height.'", "'.$areal.'", "'.$areaw.'", "'.$cfh.'", "'.$weight.'", "placeholder", "placeholder", "'.$descr.'", "'.$price.'")') or die ('Error:' .mysqli_error($con));
}

answered Jul 8, 2014 at 11:22

MRJethva

3674 silver badges15 bronze badges

2 Comments

Mallander Over a year ago

Brilliant! Thanks very much! Errors are fixed and it's in the SQL database now.

MRJethva Over a year ago

Thanks for compliment.

turson · Accepted Answer · 2014-07-08 11:20:19Z

1

mysqli_query($con, 'INSERT INTO pmd_productdata (Product_ID, Product_Name, Product_Code, Product_Installation, Age_Range, Product_Length, Product_Width, Product_Height, Minimum_Area_L, Minimum_Area_W, C_F_H, Product_Weight, Play_Type, Accreditations, Product_Desc, Product_Price) 
                        VALUES ($qpID, $name, $code, $installation, $age, $length, $width, $height, $areal, $areaw, $cfh, $weight, "placeholder", "placeholder", $descr, $price)') or die ('Error:' .mysqli_error($con));

unclosed VALUES()

answered Jul 8, 2014 at 11:20

turson

4212 silver badges9 bronze badges

Comments

Sadikhasan · Accepted Answer · 2014-07-08 11:27:16Z

1

Try to put value variable in single quotes like '$qpID','$name' etc and whole query put in double quote.

answered Jul 8, 2014 at 11:27

Sadikhasan

18.6k23 gold badges84 silver badges126 bronze badges

Collectives™ on Stack Overflow

PHP mysqli_query - INSERT INTO not working, error unclear

EDIT [Resolved]

EDIT 2

5 Answers 5

5 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

EDIT [Resolved]

EDIT 2

5 Answers 5

5 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related