mySQL query error?

[chunalt787]

I have the following php code:

$query = "INSERT INTO flashcards VALUES (null, '".$_POST['front0']."', '".$_POST['back0']."', ".$link.", ".$details.", NOW());
INSERT INTO bundleflashcardlink VALUES (null, ".$flashcardbundleid.", LAST_INSERT_ID());";
				$result = mysql_query($query);

which gives me the following error:

DB Connection 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 'INSERT INTO bundleflashcardlink VALUES (null, 37, LAST_INSERT_ID())' at line 2

When I put the following into phpmyadmin it executes properly:

INSERT INTO flashcards VALUES (null, 'front', 'back', 'link', 'details', NOW());# Affected rows: 1

INSERT INTO bundleflashcardlink VALUES (null, 37, LAST_INSERT_ID());# Affected rows: 1

I'm kinda a newb at php so any help would be appreciated. Also is there a better way to formulate the $query string than using concatenation operators to throw in the php variables?

Thanks

[PatrickV]

I would not use $_POST for your values. From memory that has casued a problem not sure. I don't think the dot from the start and end of string are not required

[Fest3er]

The error message is saying something is wrong with the first SQL statement. It expected more, but saw the start of a new statement.

Only difference I can really see is that you did not quote the 'link' and 'details' values in the PHP code.

The following might be more readable and maintainable:

$query = "
  INSERT INTO flashcard
         VALUES (NULL,
                 '$_POST['front0']',
                 '$_POST['back0']',
                 '$link',
                 '.$details',
                 NOW());
  INSERT INTO bundleflashcardlink
         VALUES (NULL,
                 '$flashcardbundleid',
                 LAST_INSERT_ID());";

Finally, the online PHP manual is a very valuable reference, as is the downloadable MySQL reference manual PDF.

[chunalt787]

Ok so I replaced it with the following code

$query = "INSERT INTO flashcards VALUES (null, ".$front.", ".$back.", ".$link.", ".$details.", NOW() );";
$result = mysql_query($query) or die('DB Connection error1: ' . mysql_error());
								
$query = "INSERT INTO bundleflashcardlink VALUES (null, ".$flashcardbundleid.", LAST_INSERT_ID());";
$result = mysql_query($query) or die('DB Connection error2: ' . mysql_error());

and sure enough its a problem on the first line as PatrickV said. I am now getting this error:

DB Connection error1: 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 ' NOW() )' at line 1

The last column is a datetime but I cant seem to see what is wrong.

Fest3er: The strings in the query include the ' characters so that should be right.

Thanks

[Fest3er]

I've found the following function to be invaluable in all my PHP/MySQL work. Uncomment the print() to see the actual query used. It's much easier to debug when you can see exactly what is going on.

// function do_sql executes the specified statements, handles error,
//   and returns the resource by reference.

  function do_sql($db, &$resource, $err_msg, $qstring) {
      //print "<p>$qstring</p>\n";
      $resource = mysql_query($qstring, $db)
        or die ($err_msg.": ".mysql_error());
  }

An example of calling do_sql :

do_sql ($dso, $color_rsp, "Couldn't get today's banner color", "
            SELECT o_color
              FROM ordo_colors
             WHERE o_date=CURDATE()");

$dso is returned from mysql_connect().

[digital-ether]

Multiple queries are not supported by mysql_query().
see: http://php.net/manual/en/function.mysql-query.php

Also make sure to quote the string you send as well as escape them with mysql_real_escape_string(). For the integers use intval() on them. This prevents sql injection, and ensures the values do cause an error if they contain special characters.

[chunalt787]

Thank you to everyone who replied. I found out that for some reason one of the variables was an empty string causing the problem.

Thanks again.