Results 1 to 9 of 9

Thread: INsert Query

  1. #1
    Join Date
    Feb 2010
    Posts
    4

    Unanswered: INsert Query

    Hey guys

    I want to insert values into a MySql database.I am using c++ in netbeans. The following line works fine:

    Code:
    mysql_query(connection, "INSERT INTO main (NumofShows , Title) VALUES('12', 'Home')");
    but if the data to be inserted is from a string... like....


    Code:
    string NewTitle = "Monaco";
    string NumofShows = "2";

    How do i edit my sql statements so variables can be inserted into the Values ( ) , and it will be able to read the variables and then store it into the database.
    Last edited by gregarion; 02-09-10 at 11:47.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this is actually a php question, not a mysql question, but i'll answer it as though it were a mysql question, since that's the forum you asked it in

    strings must be enclosed in single quotes

    for example...

    INSERT INTO main ( NumofShows, Title )
    VALUES ( 12, 'Home' )

    notice that the numeric value 12 does ~not~ have quotes

    mysql will accept numeric values with quotes, but this is non-standard behaviour and will not necessarily work in other database systems -- better to do it the standard sql way

    so when you use php variables in the sql statement, string values still need their single quotes

    INSERT INTO main ( NumofShows, Title )
    VALUES ( $NumofShows , '$NewTitle' )

    make sense?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if its a numeric value then don't encapsulate it with quote or speech marks
    if its a string value then encapsulate it with quote or speech marks, preferably quote marks
    dates are treated as string/text literals so pass your date as 'yyyy/mm/dd'

    PHP Code:
    mysql_query(connection"INSERT INTO main (NumofShows , Title) VALUES(12, 'Home')");
    mysql_query(connection"INSERT INTO main (NumofShows , Title) VALUES($mynumericvaraible, 'Home')"); 
    incidentally for debugging purposes itsd always better to assignthe SQL to a variable so you can easily debug./view the SQL to make certain that what you are sending tot eh SQL engine is the same as you think you are sending

    PHP Code:
    $strSQL "INSERT INTO main (NumofShows , Title) VALUES ($mynumericvaraible, 'Home');
    mysql_query(
    $connection$strSQL) or die ("MySQL chickened out on the following SQL:\n".$strSQL."\nThe server whinge was:".MySQL_ERROR()); 
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Feb 2010
    Posts
    4
    Does it work the same for c++. As im using c++ on netbeans.

    What i am trying to do is the user has to input values. And i will then take this values and store them into the database. This is why i had to declare it into a variable first.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    my apologies for thinking this was a php question, i guess i was just going on autopilot, and i failed to see that you mentioned c++ in your first post

    what we've tried to explain about quotes is required for mysql and is independent of whatever application language is constructing the sql

    by the time the query hits the mysql server, there are no language variables in the sql, just numeric or string constants
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2010
    Posts
    4
    But how do i get the query to understand that "Newtitle" and "NumofShows" are variables and not the values i want to save?

    I tried playing around with my insert statement .. for example ,

    Code:
     mysql_query(conn,"INSERT INTO movie_info (movie_title , movie_actor) values ('$Newtitle' ,' $NumofShows' ) " );
    
     mysql_query(conn,"INSERT INTO movie_info (movie_title , movie_actor) values ($Newtitle ,$NumofShows ) " );

    But even after trying all that , the database does not give me the values i want. Instead either , they save no values or they just save $Newtitle as a value.

    So if i have to use variables, what should i do to make it readable to the query?

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ignore the $ form my posts, I too read it as a PHP question.
    that also means or die construct wont work
    so look up how MySQL responds to errors in C++
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Feb 2010
    Posts
    4
    I read up about prepared statements. But i do not think its the best option for my case where the user has to key in something. Can you tell me what else i could do to help solve this problems guys?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gregarion View Post
    Can you tell me what else i could do to help solve this problems guys?
    this is the mysql forum, not the c++ forum

    click on the "report post" button (on the post header bar, at the right, just beside the post number and the permalink link) and ask the moderators to move this thread to the Delphi, C etc - dBforums forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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