help me on this error

Discussion in 'PHP' started by mirosoft1, Apr 3, 2008.

0
  1. #1
    i have an error
    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 '' at line 1
    SQL = UPDATE .mailinglist SET id = '1', email = 'mail@yahoo.com', date = '2008-03-22' WHERE
    modifyTable[#2]

    and this is the code

    function updateData($paramData, $paramKeys, $paramSQLDatos, $paramSQLKeys, $paramTable, $paramDBConn){
    $strSQL = "UPDATE $paramDBName.$paramTable SET ";
    for ($cont=0; $cont < count($paramSQLDatos); $cont++){
    if ($cont == 0){
    $strSQL.= "$paramSQLDatos[$cont] = '$paramData[$cont]'";
    }
    else{
    $strSQL.=", $paramSQLDatos[$cont] = '$paramData[$cont]'";
    }
    }
    $strSQL.=" WHERE ";
    for ($cont=0; $cont < count($paramSQLKeys); $cont++){
    if ($cont == 0){
    $strSQL.= "$paramSQLKeys[$cont] = '$paramKeys[$cont]' ";
    }
    else{
    $strSQL.= "AND $paramSQLKeys[$cont] = '$paramKeys[$cont]' ";
    }
    }

    $result = mysql_query("$strSQL",$paramDBConn);

    //check errors
    if (!$result) {
    exit("ERROR: " . mysql_error() . "<BR>SQL = $strSQL<BR>modifyTable[#2]");
    }
    return true;
    }
     
    mirosoft1, Apr 3, 2008 IP
  2. mulari

    mulari Peon

    Messages:
    45
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #2
    I think $paramSQLKeys is empty, so an empty WHERE cause the error. try to change:
    $strSQL.=" WHERE ";
    to:
     
    mulari, Apr 3, 2008 IP
  3. mulari

    mulari Peon

    Messages:
    45
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #3
    BTW, you know that the above query you showed will change ALL the records on your tables? Be careful with it and make sure thats what you intended to do.
     
    mulari, Apr 3, 2008 IP
  4. mirosoft1

    mirosoft1 Well-Known Member

    Messages:
    110
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    101
    #4
    this code
    if (count($paramSQLKeys) > 0)


    give me this error
    ERROR: Query was empty
    SQL =
    modifyTable[#2]
     
    mirosoft1, Apr 3, 2008 IP
  5. mulari

    mulari Peon

    Messages:
    45
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Thats weird, can you paste the complete new code, so I'll see how you added it, perhaps you didn't add it correctly?

    Did you add it above the $strSQL.=" WHERE "; line without adding anything else?

    Another solution, don't add the if, but change the $strSQL.=" WHERE "; to:
    This will make the where clause being used even if there are no keys and 1=1 is TRUE so it doesn't effect anything.
     
    mulari, Apr 3, 2008 IP
  6. mirosoft1

    mirosoft1 Well-Known Member

    Messages:
    110
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    101
    #6
    function updateData($paramData, $paramKeys, $paramSQLDatos, $paramSQLKeys, $paramTable, $paramDBConn){

    $strSQL = "UPDATE $paramDBName.$paramTable SET ";
    for ($cont=0; $cont < count($paramSQLDatos); $cont++){
    if ($cont == 0){
    $strSQL.= "$paramSQLDatos[$cont] = '$paramData[$cont]'";
    }
    else{
    $strSQL.=", $paramSQLDatos[$cont] = '$paramData[$cont]'";
    }
    }
    if (count($paramSQLKeys) > 0)

    $strSQL.=" WHERE ";
    for ($cont=0; $cont < count($paramSQLKeys); $cont++){
    if ($cont == 0){
    $strSQL.= "$paramSQLKeys[$cont] = '$paramKeys[$cont]' ";
    }
    else{
    $strSQL.= "AND $paramSQLKeys[$cont] = '$paramKeys[$cont]' ";
    }
    }

    $result = mysql_query("$strSQL",$paramDBConn);

    //check errors
    if (!$result) {
    exit("ERROR: " . mysql_error() . "<BR>SQL = $strSQL<BR>modifyTable[#2]");
    }
    return true;
    }


    this is the code
    and it gives me this error
    ERROR: Duplicate entry '3' for key 1
    SQL = UPDATE .news
     
    mirosoft1, Apr 3, 2008 IP
  7. mulari

    mulari Peon

    Messages:
    45
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #7
    Thats a different error. That error means you have duplicate keys on the table.

    Probably the ID, perhaps you are trying to update the ID of a row - when you shouldn't - this will make 2 rows with the same ID and give the error you showed.

    You should check your code, I think you are not sending the $paramSQLKeys when you should.
     
    mulari, Apr 3, 2008 IP