Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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?

share|improve this question
1  
WHERE CODE IN ('value1','value2','value3') is how it should look – gunnx May 18 '12 at 21:54

2 Answers

up vote 2 down vote accepted

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') ;
share|improve this answer
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 – Nathan Link Abiola May 18 '12 at 22:07
up vote 1 down vote

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 .= ")";
share|improve this answer
1  
or you could add $Q to array then implode using comma separator – gunnx May 18 '12 at 21:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.