[RESOLVED] Fatal Error: MySQL error: 1065 : Query was empty (# 256)

sibenye

Hi guys,

I need your help, have gone thru my syntax several times, can't seem to find why I am having this error.
The function is meant to take an array of inputs from an HTML form, format it using the 'sprintf' and then insert it into the database.

 function insertRecord ($fieldarray)
   {
      $this->errors = array();
	   global $dbconnection, $query;
      $dbconnection = db_connect($this->dbname) or trigger_error("SQL", E_USER_ERROR);


   $query_str = "\"INSERT INTO $this->tablename SET ";
  foreach ($fieldarray as $item => $value) {
     $query_str .= $value."='%s', ";
  } // foreach

   $query_str = rtrim($query_str, ', ');
   $query_str .= "\"";
   $escape_str = NULL;
   foreach ($fieldarray as $item => $value) {
	   $escape_str .= "mysql_real_escape_string('".$_REQUEST[$value]."'), ";
  } // foreach

  $escape_str = rtrim($escape_str, ', ');

 // $query = sprintf($query_str,  $escape_str);

  $result = mysql_query(sprintf($query_str,  $escape_str), $dbconnection);
  if (mysql_errno() <> 0) {
     if (mysql_errno() == 1062) {
        $this->errors[] = "A record already exists with this ID.";
     } else {
        trigger_error("SQL", E_USER_ERROR);
     } // if
  } // if

  return;

   } // insertRecord

During my diagnosis I echoed $query_str and $escape_str just to make sure they contained what I wanted and they did, here is the output;

For $query_str:
"INSERT INTO registration_table SET ConfirmationID='%s', EventID='%s', ParticipantFirstName='%s', ParticipantLastName='%s', ParticipantAddress='%s', ParticipantCity='%s', ParticipantState='%s', ParticipantCountryID='%s', ParticipantEmail='%s', ParticipantPhoneNumber='%s', CompanyName='%s', JobTitle='%s', CompanyAddress='%s', CompanyCity='%s', CompanyState='%s', CompanyCountryID='%s', RegistrationDate='%s', EventDateID='%s'"

For $escape_str:
mysql_real_escape_string('8433544222JJJ'), mysql_real_escape_string('1'), mysql_real_escape_string('jkhfuhiuh'), mysql_real_escape_string('hjgjhkjhl'), mysql_real_escape_string('b,bjj,jjkhkj'), mysql_real_escape_string('b,kjkjhjkh'), mysql_real_escape_string('jhb,kjhkj'), mysql_real_escape_string('4'), mysql_real_escape_string('ag-silver@live.com'), mysql_real_escape_string('5748673687'), mysql_real_escape_string('jgkgkiulh'), mysql_real_escape_string('uiyiuhky'), mysql_real_escape_string('uiyuhki'), mysql_real_escape_string('kukluhll'), mysql_real_escape_string('uguilhli'), mysql_real_escape_string('3'), mysql_real_escape_string('2012-04-04'), mysql_real_escape_string('1')

I don't know what I am doing wrong, all i know is that the 'sprintf' isn't outputing anything. Can any one help?

sneakyimp

[man]mysql_real_escape_string[/man] is not a MySQL function, it's a PHP function. You shouldn't be putting that into your query. Read the docs and you can see some examples of its use.

Also, I don't think you are using [man]sprintf[/man] correctly either. It looks to me like your $query_string arg is set up to receive numerous strings and you are sending it only one. Try seeing what comes out of that sprintf statement before you try to run a query on it. I bet it's outputting the empty string or something that looks nothing like the query you want.

sibenye

Yes I do know that mysql_real_escape_string is not an a MySQL function, it was just my desperate attempt of trying out different things.

I did as you said, and indeed the output of the sprintf statement is empty, which means something is wrong the args i'm putting in. But I can't seem to find out what.

Derokorian

My next question is do you have display_errors (development) / log_errors (production) turned on with error_reporting set to E_ALL or better? If you did you would be getting errors from PHP telling you why the sprintf failed to complete.

sneakyimp

How about reading the documentation on [man]sprintf[/man] like I suggested? If you do so, you will see that it is expecting A LOT of arguments to replace all those %s items you have in the first string and yet you are only supplying it one.

bradgrafelman

sneakyimp;11000941 wrote:
it is expecting A LOT of arguments to replace all those %s items you have in the first string and yet you are only supplying it one.

To be more specific, each instance of the '%' placeholder in the format string should correspond with another parameter being added to the [man]sprintf/man function call. So if you've got 5 instances of '%s' in the format string, your sprintf() call should have 6 total parameters.

Now, if that second parameter in the OP's code were an array of values that corresponded with each '%s' in the format string, then I'd say you want to be using [man]vsprintf/man instead.

sibenye

Thanks guys for all your help, the vsprintf worked but I decided to escape the form inputs before I put them in the query. Like this:

function insertRecord ($fieldarray)
   {
      ........
      .........
     ..........
 $query = "INSERT INTO $this->tablename SET ";
      foreach ($fieldarray as $item => $value) 
	  {
		 $content = mysql_real_escape_string($_REQUEST[$value]);
         $query .= $value." = '".$content."', ";
      } // foreach

   $query = rtrim($query, ', ');

  $result = mysql_query($query, $dbconnection);

   ..........
  ...........
    ........
     return true;

   } // insertRecord

Derokorian

Note you could still use vsprintf, if you combined it with [man]array_map[/man] like:

$fieldarray = array_map('mysql_real_escape_string',$fieldarray);