PHP MySQL arrays SELECT, FROM, WHERE

Question

I'm trying to get a system where the array data is changed to match the ID of its entry on another MySQL table. I am getting this from checkboxes on a PHP script.

This is the code

$insertSQL2 = "INSERT INTO test (Testing) VALUES SELECT Course_id FROM courses WHERE Code IN ";
foreach ($_POST['CheckboxGroup1'] as $Q){
    $Q = mysql_real_escape_string($Q);
    $insertSQL2.= "('$Q'), ";
} 
$insertSQL2 = rtrim($insertSQL2, ", ");

I can get the raw data output when I want. e.g. when the four checkboxes are selected, they will be placed in the table in seperate rows (which I require). However when I try to change it to match the code (When the checked box = a code in the table, the id is placed instead of the code) I get the following 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 'SELECT Course_id FROM courses WHERE 
Code IN ('CNMD'), ('EEM')' at line 1

Has anybody got any possible solutions?

WHERE CODE IN ('value1','value2','value3') is how it should look — gunnx
– gunnx, Commented May 18, 2012 at 21:54

ypercubeᵀᴹ · Accepted Answer · 2012-05-18 22:03:23Z

2

INSERT statements can use either VALUES or SELECT syntax. Not both.

You probably want the second:

INSERT INTO test (Testing) 
  SELECT Course_id 
  FROM courses 
  WHERE Code IN ('CNMD', 'EEM') ;

answered May 18, 2012 at 22:03

ypercubeᵀᴹ

116k19 gold badges181 silver badges248 bronze badges

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

1 Comment

Nathan Link Abiola Over a year ago

Have spent hours trying different methods, first time posting on this site. Cant believe it was because of values - select. You are a ledge Thanks

Pablo Santa Cruz · Accepted Answer · 2012-05-18 21:54:48Z

1

You have to make your SQL sentence look like:

SELECT Course_id FROM courses WHERE Code IN ('CNMD', 'EEM')

So you PHP code would need to be something like:

$insertSQL2 = "INSERT INTO test (Testing) VALUES SELECT Course_id FROM courses WHERE Code IN (";
foreach ($_POST['CheckboxGroup1'] as $Q) {
    $Q = mysql_real_escape_string($Q);
    $insertSQL2.= "'$Q', ";
} 
$insertSQL2 = rtrim($insertSQL2, ", ");
$insertSQL2 .= ")";

answered May 18, 2012 at 21:54

Pablo Santa Cruz

182k33 gold badges250 silver badges300 bronze badges

1 Comment

gunnx Over a year ago

or you could add $Q to array then implode using comma separator

Collectives™ on Stack Overflow

PHP MySQL arrays SELECT, FROM, WHERE

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related