MYSQL error! arghh!

[spsalter]

I have put together a simple form to add jobs to a career database. However, I have been getting an SQL error, no matter what I try. I used code that worked previously, but now get the error. The only time it works is when I replace the values with actual data, rather than the php code. I get the following error:

Couldn't execute query.
RESULT=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 'Consultant, Blue Bell , $38,000 , 610-555-1212 , 610-555-6666 , 

When I remove the error handler and add the echo to view the info, I get the following:

INSERT INTO `OS` ( `date_posted` , `title` , `location` , `salary` , `phone` , `fax` , `email` , `description` , `require` , `id` ) VALUES (11/7/2005 , Marketing Consultant, Blue Bell , $38,000 , 610-555-1212 , 610-555-6666 , test@test.com , Assist client Account Managers to increase sales by expanding business with existing customers. Provides healthcare professionals with value-added services and product information. • Builds strong rapport and relationship with Account Manager/s to facilitate marketing efforts. • Profiles and manages targeted list and provides value-added benefits to accounts in order to help Account Manager grow volume • Builds rapport with Account Managers / Clients. • Attends comprehensive training programs gaining knowledge of products, product application and regulations. • Completes required data entry, including details of the target’s response, rep notes and follow up action necessary (if appropriate). • Maintains phone time and call productivity as well as other measures required by the Company. • Provides seamless coordination/integration with Account Manager/s and customer accounts. • Provides ongoing communication with Account Manager/s , • Develops and maintains strong customer relationships with client • Minimum of 2 years previous Account Management experience. • Projects a professional and polished image that inspires confidence and trust. • Handles complex problems effectively. Separates key issues and generates multiple solutions. • Effective verbal communication and rapport building skills. • Good organizational skills and the ability to prioritize accurately and manage multiple tasks. • Experience informing clients, superiors, peers, and direct reports based on their information needs. • Ensures that the customer receives a superior product or service. • Listens attentively and responds effectively to client. • Is team-oriented and works collaboratively to achieve team goals. • Committed to exceeding quality standards and providing exceptional products and services. • Make sure customers expectations are met. • Combines phone etiquette and marketing skills to get results. , OS27); 

It seems it is carrying the data through, so I am not sure what the issue is. Help would be much appreciated!

[hyperActive]

Try placing quotations around the values. Also I don't think you need quotations for the table name or the column names. Might be wrong, but defintely put quotations around each value.

[spsalter]

I tried with and without quotes for the values, and both produce the same error:

$sql = "INSERT INTO `OS` ( `date_posted` , `title` , `location` , `salary` , `phone` , `fax` , `email` , `description` , `require` , `id` )
VALUES ($date_posted , $title, $location , $salary , $phone , $fax , $email , $description , $require , $id);"; 

-AND-

$sql = "INSERT INTO `OS` ( `date_posted` , `title` , `location` , `salary` , `phone` , `fax` , `email` , `description` , `require` , `id` )
VALUES (`$date_posted` , `$title`, `$location` , `$salary` , `$phone` , `$fax` , `$email` , `$description` , `$require` , `$id`);"; 

[hyperActive]

Is the salary column set to an integer by any chance? Might be that its not accepting your use of the "$" sign.

[spsalter]

It is set to varchar. I thought that may an issue, but when testing with actual values containing "/", "$", etc. it went though with no error.

[Bil]

There are different types of quotes. ` is a backtick, ' is a single quote, " is a double quote. Table names should be inside backticks (but that's not required, though it is good practice). Values to be inserted must be quotes, usually in single quotes. Integers don't have to be quoted, but again, it's a good idea to quote all values.

Try this

$sql = "INSERT INTO `OS` ( `date_posted` , `title` , `location` , `salary` , `phone` , `fax` , `email` , `description` , `require` , `id` )
VALUES ('$date_posted' , '$title', '$location' , '$salary' , '$phone' , '$fax' , '$email' , '$description' , '$require' , '$id');"; 

[r937]

Integers don't have to be quoted, but again, it's a good idea to quote all values.

no, it isn't

[arun_md]

hi,

Have u correct this error?

If not i found one single quote on ur description(target's), please remove it and now try to insert it.

[spikeZ]

$sql = "INSERT INTO OS ( `date_posted` , `title` , `location` , `salary` , `phone` , `fax` , `email` , `description` , `require` , `id` ) 
VALUES ('$date_posted' , '$title', '$location' , '$salary' , '$phone' , '$fax' , '$email' , '$description' , '$require' , '$id')"; 

One too many ; at the end of the query.
Also are you running any checks/ validation on the data such as addslashes() and trim() before using the variables in the query?

Spike

[spsalter]

same deal:
Couldn't execute query.
RESULT=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 ''OS' ( `date_posted` , `title` , `location` , `salary` , `phone


I used $sql = "INSERT INTO OS ( `date_posted` , `title` , `location` , `salary` , `phone` , `fax` , `email` , `description` , `require` , `id` )
VALUES ('$date_posted' , '$title', '$location' , '$salary' , '$phone' , '$fax' , '$email' , '$description' , '$require' , '$id')";

I am stumped.

[r937]

seems to be complaining about your table name

do you, in fact, have a table named OS ?

what do you get when you run this query:

select count(*) from OS

also, note the error message has a single quote after OS -- are you sure you're echoing the sql statement that's giving the error?

[spsalter]

I have 3 tables...OS, AF and IT. Select count pulls back 4, which is correct. Is there any chance this could be an issue with the hosting company? This is the same code I have used on another server with the same host. The full query is:

if ($site == "AF") {
    $sql = "INSERT INTO AF (date_posted, status, title, location, salary, phone, fax, email, description, require, id) VALUES ('$date_posted', '$status', '$title', '$location', '$salary', '$phone', '$fax', '$email', '$description', '$require', '$id')";
} else if ($site == "OS") {
     $sql = "INSERT INTO OS (date_posted, status, title, location, salary, phone, fax, email, description, require, id) VALUES ('$date_posted', '$status', '$title', '$location', '$salary', '$phone', '$fax', '$email', '$description', '$require', '$id')";
} else if ($site == "IT") {
    $sql = "INSERT INTO IT (date_posted, status, title, location, salary, phone, fax, email, description, require, id) VALUES ('$date_posted', '$status', '$title', '$location', '$salary', '$phone', '$fax', '$email', '$description', '$require', '$id')";
}    
$result = mysql_query($sql,$connection);
if (! $result) {
    print "Couldn't execute query.<BR>RESULT=";
    print mysql_error();
    print "<BR>";
    die();
    } 

[Bil]

When I have problems like this, I have the script output the $sql variable to see if that helps me debug the problem. Do this and tell us what the error message outputs:

if (! $result) { 
    print "Couldn't execute query.<BR>RESULT="; 
    print mysql_error(); 
    print "<BR>"; 
    print $sql; 
    die(); 
    } 

[spsalter]

Bil, I inserted your code and rceived the following:

Couldn't execute query.
RESULT=
INSERT INTO AF (date_posted, status, title, location, salary, phone, fax, email, description, require, id) VALUES ('11/9/2005', 'Full Time', 'King', 'Philly', '$2,000,000', '555-555-5555', '555-555-5555', 'test@test.com', 'Rule with an iron fist', 'Fancy pants', 'Af333') 

I also opened a ticket with the host to see if they changed anything that would prevent this from working as it did before.

[spsalter]

Used slightly different query:

$sql = "INSERT INTO AF SET date_posted = '$date_posted', status = '$status', title = '$title', location = '$location', salary = '$salary', phone = '$phone', fax = '$fax', email = '$email', description = '$description', require = '$require', id = '$id'"; 

And still received an error:

Couldn't execute query.
RESULT=
INSERT INTO AF SET date_posted = '11/10/2005', status = 'full time', title = 'King', location = 'England', salary = '$20,000,000', phone = '(610) 555-5555', fax = '666-666-6666', email = 'test@test.com', description = 'Work hard, play hard', require = 'Fancy Drinkin\' Pants', id = 'AF201' 

Why isn't this simple insert working?

[r937]

mysql doesn't like anything other than year-month-day

however, it will substitute 0000-00-00 instead of dying

listen, your best bet when up against this type of testing conundrum is to isolate where the error is occurring

run your query outside of php to obtain the actual mysql error message

[spsalter]

I removed date_posted from the query and still got the error. As for outside of PHP, how do you mean? In phpmyadmin? If so, I have been able to use an insert statement (with the actual values) to get the data into the database.
Oy vey.

[r937]

yes, phpmyadmin is fine, any method which allows you to separate the actual sql statement and run it directly in mysql

since the query works in mysql, this means you have some sort of script error --probably a variable value not being properly substituted

thus, you must now echo the query string immediately before (or instead of) execution

[spsalter]

It seems the query didn't take well to the variable '$require'. I removed that and now everything works. Thanks to all for the help