Uncaught exception 'ErrorException' with message 'Wrong SQL: INSERT INTO

Question

I'm not getting the right syntax with my INSERT INTO for MySQL.

        $v0="`" . $this->mysqli->real_escape_string($cooke[0]) . "`";
    $v1="`" . $this->mysqli->real_escape_string($cooke[1]) . "`";
    $v2="`" . $this->mysqli->real_escape_string($cooke[2]) . "`";
    $v3="`" . $this->mysqli->real_escape_string($cooke[3]) . "`";
    $v4="`" . $this->mysqli->real_escape_string($cooke[4]) . "`";
    $sql="INSERT INTO index(surname, surChart, immigration, residence) VALUES ($v0,$v1,$v2, $v3)";

    if($this->mysqli->query($sql) === false) {
      trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $this->mysqli->error, E_USER_ERROR);
    } else {
      $last_inserted_id = $this->mysqli->insert_id;
      $affected_rows = $this->mysqli->affected_rows; echo 'af: '. $affected_rows;
    }

the error is:

Fatal error: Uncaught exception 'ErrorException' with message 'Wrong SQL: INSERT INTO index(surname, surChart, immigration, residence) VALUES (`COOK/E`,`COOK/E|CHARDE|FORD| |COOKE|WHITE|DENSLOW|NASH|FOOTE|SMITH||STRONG|FORD||WOOLCOTT|PITKIN|CLARK|COOLEY|GUNN|GRAVES|FIELD|LONGLEY|ELDRIDGE|O.C. AVERY||WESTWOOD|BARNARD|CATLIN||ALLEN||Abner Avery|NIMS|WOOD|WRIGHT|H.L. AVERY||BURDSALL|PUTNEY|COOKE||HAMBLEN?|FOSKETT|CLARK|ELDRIDGE|LONGLEY|O.C. AVERY||LAWRENCE|BATCHELOR|MORSE|TARBOL/|TARBALL|HOUGHTON|LONGLEY|[COOKE^ Contd.]|FIELD|ELDRIDGE|O.C. AVERY`,`Brideport^ Dorset|England;||||Dorchester|Windsor^ CT|Northampton Westfield|New Haven CT Hadley|||Windsor CT|Windsor CT|||Springfield|Sunderland|Sunderland|Hawley|Charlemont|Hawley|Charlemont||Hadley||Hadley|Hadley|Shelburne||Hardwick|Shelburne||Charlemont|Ohio|Ohio|Ohio|Charlemont||Groton^ Salem||Salem|Salem||Charlton|Charlton||Goshen|Hawley|Charlemont|Charlemont||Groton MA||Sudbury|Groton MA|Lexington||Groton MA|Hawley||||Hawley||Charlemont|`, `---||1630 The Mary & John|||Bef 1637 in C:\xampp\htdocs\AveryChart\mySqlCsv.php on line 107

Sql echo:

INSERT INTO ancestorIndex(surname, surChart, immigration, residence) VALUES (`COOK/E`,`COOK/E+CHARDE+FORD+ +COOKE+WHITE+DENSLOW+NASH+FOOTE+SMITH++STRONG+FORD++WOOLCOTT+PITKIN+CLARK+COOLEY+GUNN+GRAVES+FIELD+LONGLEY+ELDRIDGE+O.C. AVERY++WESTWOOD+BARNARD+CATLIN++ALLEN++Abner Avery+NIMS+WOOD+WRIGHT+H.L. AVERY++BURDSALL+PUTNEY+COOKE++HAMBLEN?+FOSKETT+CLARK+ELDRIDGE+LONGLEY+O.C. AVERY++LAWRENCE+BATCHELOR+MORSE+TARBOL/+TARBALL+HOUGHTON+LONGLEY+[COOKE^ Contd.]+FIELD+ELDRIDGE+O.C. AVERY`,`Brideport^ Dorset+England;++++Dorchester+Windsor^ CT+Northampton Westfield+New Haven CT Hadley+++Windsor CT+Windsor CT+++Springfield+Sunderland+Sunderland+Hawley+Charlemont+Hawley+Charlemont++Hadley++Hadley+Hadley+Shelburne++Hardwick+Shelburne++Charlemont+Ohio+Ohio+Ohio+Charlemont++Groton^ Salem++Salem+Salem++Charlton+Charlton++Goshen+Hawley+Charlemont+Charlemont++Groton MA++Sudbury+Groton MA+Lexington++Groton MA+Hawley++++Hawley++Charlemont+`, `---++1630 The Mary & John+++Bef 1637+1638+1678+Aft 1650+1676+1688++++1656+1661++Aft 1687++1680+1709+1736+1783+1805+1847+1895++++1661+1706+1743++1783+By 1813++1815++1858+1885+1913++By 1643+By 1662+By 1678+By 1700+By 1738+By 1766++1791+1812+1847+1895++By 1639+bef 1667+1660+1687+By 1732+By 1720+By 1749+1773++++By 1827+1847+1895`)

index it's a SQL reserved word! So put it in ` like this `index` — Rizier123
– Rizier123, Commented Dec 12, 2014 at 22:05
Furthermore backticks in SQL are meant for identifiers, not string values. — mario
– mario, Commented Dec 12, 2014 at 22:06

O. Jones · Accepted Answer · 2014-12-12 22:34:34Z

Three things for you to work on.

First, INDEX is an incredibly lousy choice of name for a table. It is a reserved word in MySQL.

Second, Your lines like this:

 $v1="`" . $this->mysqli->real_escape_string($cooke[1]) . "`";

result in values like this.

 `someValue`.

You want your text values enclosed not in backticks, but in apostrophes (single quotes). Use this.

 $v1="'" . $this->mysqli->real_escape_string($cooke[1]) . "'";

Better yet, use bind variables. Read this: http://php.net/manual/en/mysqli-stmt.bind-param.php

Third, the string values you're inserting into some columns of your table are very long indeed. Here are the values you're trying to insert. I think it's possible that some of them are too long for the columns you've declared in your table.

Surname: COOK/E
Surchart: COOK/E+CHARDE+FORD+ +COOKE+WHITE+DENSLOW+NASH+FOOTE+SMITH++STRONG+FORD++WOOLCOTT+PITKIN+CLARK+COOLEY+GUNN+GRAVES+FIELD+LONGLEY+ELDRIDGE+O.C. AVERY++WESTWOOD+BARNARD+CATLIN++ALLEN++Abner Avery+NIMS+WOOD+WRIGHT+H.L. AVERY++BURDSALL+PUTNEY+COOKE++HAMBLEN?+FOSKETT+CLARK+ELDRIDGE+LONGLEY+O.C. AVERY++LAWRENCE+BATCHELOR+MORSE+TARBOL/+TARBALL+HOUGHTON+LONGLEY+[COOKE^ Contd.]+FIELD+ELDRIDGE+O.C. AVERY
Immigration: Brideport^ Dorset+England;++++Dorchester+Windsor^ CT+Northampton Westfield+New Haven CT Hadley+++Windsor CT+Windsor CT+++Springfield+Sunderland+Sunderland+Hawley+Charlemont+Hawley+Charlemont++Hadley++Hadley+Hadley+Shelburne++Hardwick+Shelburne++Charlemont+Ohio+Ohio+Ohio+Charlemont++Groton^ Salem++Salem+Salem++Charlton+Charlton++Goshen+Hawley+Charlemont+Charlemont++Groton MA++Sudbury+Groton MA+Lexington++Groton MA+Hawley++++Hawley++Charlemont+
Residence: ---++1630 The Mary & John+++Bef 1637+1638+1678+Aft 1650+1676+1688++++1656+1661++Aft 1687++1680+1709+1736+1783+1805+1847+1895++++1661+1706+1743++1783+By 1813++1815++1858+1885+1913++By 1643+By 1662+By 1678+By 1700+By 1738+By 1766++1791+1812+1847+1895++By 1639+bef 1667+1660+1687+By 1732+By 1720+By 1749+1773++++By 1827+1847+1895

Lynn · Accepted Answer · 2014-12-14 17:59:48Z

1

this worked:

    public function arrayToMySQL2($line_of_text){
    if ($line_of_text[0]){
        $stmt = mysqli_prepare($this->mysqli, "INSERT INTO ancestorIndex VALUES (?, ?, ?, ?, ?)");
        mysqli_stmt_bind_param($stmt, 'sssss', $line_of_text[0], $line_of_text[1], $line_of_text[2], $line_of_text[3], $line_of_text[4]);
        mysqli_stmt_execute($stmt);
        printf("%d Row inserted.<br>", mysqli_stmt_affected_rows($stmt));
        mysqli_stmt_close($stmt);
    }
}

answered Dec 14, 2014 at 17:59

Lynn

831 silver badge10 bronze badges

Collectives™ on Stack Overflow

Uncaught exception 'ErrorException' with message 'Wrong SQL: INSERT INTO

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related