SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Enthusiast spsalter's Avatar
    Join Date
    Apr 2005
    Location
    A little to the left
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MYSQL error! arghh!

    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:
    PHP Code:
    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 '
    ConsultantBlue 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:
    PHP Code:
    INSERT INTO `OS` ( `date_posted` , `title` , `location` , `salary` , `phone` , `fax` , `email` , `description` , `require` , `id` ) VALUES (11/7/2005 Marketing ConsultantBlue Bell , $38,000 610-555-1212 610-555-6666 test@test.com Assist client Account Managers to increase sales by expanding business with existing customersProvides 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 productsproduct application and regulations• Completes required data entryincluding details of the target’s responserep 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/and customer accounts• Provides ongoing communication with Account Manager/• 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 effectivelySeparates 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 clientssuperiorspeers, 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!

  2. #2
    SitePoint Enthusiast hyperActive's Avatar
    Join Date
    Nov 2004
    Location
    none
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Enthusiast spsalter's Avatar
    Join Date
    Apr 2005
    Location
    A little to the left
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried with and without quotes for the values, and both produce the same error:

    PHP Code:
    $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-

    PHP Code:
    $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`);"

  4. #4
    SitePoint Enthusiast hyperActive's Avatar
    Join Date
    Nov 2004
    Location
    none
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is the salary column set to an integer by any chance? Might be that its not accepting your use of the "$" sign.

  5. #5
    SitePoint Enthusiast spsalter's Avatar
    Join Date
    Apr 2005
    Location
    A little to the left
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Location
    Beautiful Rhode Island, USA
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    PHP Code:
    $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');"

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Bil
    Integers don't have to be quoted, but again, it's a good idea to quote all values.
    no, it isn't
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot arun_md's Avatar
    Join Date
    Aug 2005
    Location
    chennai
    Posts
    157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  9. #9
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,788
    Mentioned
    151 Post(s)
    Tagged
    3 Thread(s)
    PHP Code:
    $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
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  10. #10
    SitePoint Enthusiast spsalter's Avatar
    Join Date
    Apr 2005
    Location
    A little to the left
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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:
    Code:
    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?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Enthusiast spsalter's Avatar
    Join Date
    Apr 2005
    Location
    A little to the left
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    PHP Code:
    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();
        } 

  13. #13
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Location
    Beautiful Rhode Island, USA
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

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

  14. #14
    SitePoint Enthusiast spsalter's Avatar
    Join Date
    Apr 2005
    Location
    A little to the left
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    still stumped

    Bil, I inserted your code and rceived the following:
    PHP Code:
    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.

  15. #15
    SitePoint Enthusiast spsalter's Avatar
    Join Date
    Apr 2005
    Location
    A little to the left
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Tried something else that didn't work

    Used slightly different query:
    PHP Code:
    $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:
    PHP Code:
    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 = '(610555-5555', fax = '666-666-6666', email = 'test@test.com', description = 'Work hardplay hard', require = 'Fancy Drinkin\' Pants'id 'AF201' 
    Why isn't this simple insert working?

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Enthusiast spsalter's Avatar
    Join Date
    Apr 2005
    Location
    A little to the left
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Enthusiast spsalter's Avatar
    Join Date
    Apr 2005
    Location
    A little to the left
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    fixed!

    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •