Error in sql syntax, PHP

Question

i am copying a one database tables into other with same schema. i have three tables in one database 1)customer 2)order 3)product. when i run my code it successfully copy data of customer table into the customer table of database 2 but when it times for order table it generates an 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 'order' at line 1 my code is here:

$dbname = "db1";
$db1= new database_connection();
$link = $db1->get_connection($dbname);

$dbname1="db2"; 
$db2=new database_connection();
$link1=$db2->get_connection($dbname1);

$sql = "SHOW TABLES FROM $dbname";
$result = mysql_query($sql,$link);

if (!$result) {
    echo "DB Error, could not list tables\n";
    echo 'MySQL Error: ' . mysql_error();
    exit;
}
$tables= array();
while ($row = mysql_fetch_row($result)) {
    array_push($tables,$row[0]);
}
//print_r($tables);
$i=0;
echo "tables = ". count($tables);
while($i<count($tables)){
    echo "i= $i";

    $fields=array();
    $query="SHOW COLUMNS FROM $tables[$i]";
    $result = mysql_query($query,$link) or die(mysql_error($link));

    if (!$result) {
        echo 'Could not run query1: ' . mysql_error();
        exit;   
    }


    //fields of table
    if (mysql_num_rows($result) > 0) {
        while ($row = mysql_fetch_assoc($result)) {
                array_push($fields, $row['Field']);
              }
             //print_r($fields);

             $value=$db1->get_column($tables[$i],$fields,$link);
             echo "extracted";
              $db2->insert_column($tables[$i],$fields,$value,$link1); 
              echo "insertrd"; 
             //print_r($value);

    }
    unset($fields);
    $i = $i + 1; 
}

functions in other file

function get_column($table,$row,$link){

        $result=mysql_query("SELECT * FROM $table",$link) or die();
            $data=array();
            while($row = mysql_fetch_assoc($result)){
            //unset($row["0"]); //get rid of the "sorting col"
            array_push($data,$row ); //or whatever ;)


            }
            return $data;

    }
        //return $result;
    }
    function insert_column($table,$field,$value,$link1){
        $count_fields=count($value);
        echo "count $count_fields";
            $j=0;
           while ($j <$count_fields) {
                    $array=array();
                    $array=$value[$j];

                    $sqll= " ('".implode("', '", $array)."') ";
                    $sql="INSERT INTO $table VALUES " .  ($sqll);;
                    //$sql=mysql_real_escape_string($sql);
                    echo "$sql";
                    mysql_query($sql,$link1) or die(mysql_error());
                    $j= $j +1;
                }    


}

please help me thanks

Note that `order` is a reserved word – Strawberry Feb 08 '15 at 13:29 — Strawberry, Feb 08 '15 at 13:29

Jakub Matczak · Answer 1 · 2015-02-08T13:42:24.143

0

order is a reserved word in SQL used for ORDER BYclause. Therefore you can't use it like that.

You should change table name to something else, or you have to use backticks every time you use this table name in your sql queries.

For example instead of

 $sql="INSERT INTO $table VALUES " .  ($sqll);

do it like:

 $sql="INSERT INTO `$table` VALUES " .  ($sqll);

Here you have full list of reserved words in MySQL

edited Feb 08 '15 at 13:42

answered Feb 08 '15 at 13:31

Jakub Matczak

15,341
5
46
64

Error in sql syntax, PHP

1 Answers1