0

Guys, need some guidance here...
i have this php codes that copies all the value in a table to another table....some sort of table replication
now, the problem is when i tried copying them, keep getting an error

Database down
1054: Unknown column '124928F014007570' in 'where clause'...

the codes
dataverification.php

 <html>
<head>
<title>Shipping Verification System</title>
<link rel="stylesheet" href="Appcss.css" type="text/css"/> <!--Including stylesheet and media type(handheld)-->
</head>  
<?php
//---THIS FILES CALLS THE SYNC.PHP TO COMPARE AND COPY THE DATA FROM PRMS---
    include "sync.php";
    require_once 'connection_details.php';
    $sync = new SyncronizeDB();
    //masterSet(dbserver,user,password,db,table,index)
    $sync->masterSet($host,$username,$password,"shipmenttracker","serverdata","shipping_no");
    //serverSet(dbserver,user,password,db,table,index)  
    $sync->slaveSet($host,$username,$password,"shipmenttracker","maindata","shipping_no");
    //syncronizing the slave table with the master table (at row level)
   $sync->slaveSyncronization();
   $sync->direct();
?>

sync.php

<html>
<head>
<title>Shipping Verification System</title>
<link rel="stylesheet" href="Appcss.css" type="text/css"/> <!--Including stylesheet and media type(handheld)-->
</head>  
<?php
class DB
{
    //Member Zone
    var $dataserver;
    var $database;
    var $result;
    var $row_result;
    var $error_name;
    var $error_number;

    //Methode Zone
    function DB()
    {
    }

    function DBDown()
    //database down
    {
        $this->error_number = mysql_errno();
        $this->error_name = mysql_error();      
        print "Database down";
        if ($this->error_name != "")
        {
            print "<br>" . $this->error_number . ": " . $this->error_name . "<br>";
        }
        exit();
    }

    function Open($plocation,$puser,$ppassword,$pdb)
    //Open a connection to a MySQL Server
    {
        $this->database = $pdb;
        if(!$this->dataserver = mysql_pconnect($plocation, $puser, $ppassword))
        {
            DB::DBDown();
        }
    }

    function executeSQL($sql)
    //Query database
    {
        if (!$res=mysql_query($sql,$this->dataserver))
        {
            DB::DBDown();
        }
        $this->row_result=0;
        $this->result=array();
        //print "sql=".$sql."<br>";
        if ((strpos("_".$sql,'SELECT'))||(strpos("_".$sql,'SHOW'))||(strpos("_".$sql,'DESCRIBE'))||(strpos("_".$sql,'EXPLAIN')))
        {
            while ($this->result[]=mysql_fetch_array($res, MYSQL_NUM))
            {
                $this->row_result++;
            }
        }
    }

    function cleanSQL($param)
    //Clean sql parameters
    {
        return mysql_real_escape_string($param,$this->dataserver);
    }

}

class SyncronizeDB
{
    //Member Zone
    var $db_master;
    var $table_master;
    var $idx_master;
    var $col_master;
    var $db_slave;
    var $table_slave;
    var $idx_slave;
    var $col_slave;

    //Methode Zone
    function SyncronizeDB()
    //HelpdeskDB Constructor
    {
    }


    function masterColumns()
    {
        //return columns name
        $this->db_master->executeSQL("SHOW COLUMNS from ".$this->db_master->database.".".$this->table_master);
        $i = 0;
        $col = "";
        foreach ($this->db_master->result as $row)
        {
            if ($row != "")
            {           
                if ($i == 1) $col .= ",";
                $col .= $row[0];
                if ($i == 0) $i = 1;
            }
        }
        return $col;
    }

    function masterAllIndexes()
    {
        //return all index value
        $this->db_master->executeSQL("SELECT ".$this->idx_master." from ".$this->db_master->database.".".$this->table_master);
    }

    function masterDataRows($sin)
    {
        // return data from master database
        $this->db_master->executeSQL("SELECT * from ".$this->db_master->database.".".$this->table_master." where ".$this->idx_master." in (".$sin.")");
    }

    function masterSet($mplocation, $mpuser, $mppassword, $mpdb, $mptable, $mpidx)
    //set master database
    {
        $this->table_master = $mptable;
        $this->idx_master = $mpidx;
        $this->db_master = new DB();
        $this->db_master->Open($mplocation,$mpuser,$mppassword,$mpdb);  
        $this->col_master = $this->masterColumns();
    }

    function slaveColumns()
    {
        //return columns name
        $this->db_slave->executeSQL("SHOW COLUMNS from ".$this->db_slave->database.".".$this->table_slave);
        $i = 0;
        $col = "";
        foreach ($this->db_slave->result as $row)
        {
            if ($row != "")
            {
                if ($i == 1) $col .= ",";
                $col .= $row[0];
                if ($i == 0) $i = 1;
            }
        }
        return $col;
    }

    function slaveAllIndexes()
    {
        //return all index value
        $this->db_slave->executeSQL("SELECT ".$this->idx_slave." from ".$this->db_slave->database.".".$this->table_slave);
    }

    function slaveSet($mplocation, $mpuser, $mppassword, $mpdb, $mptable, $mpidx)
    //set slave database
    {
        $this->table_slave = $mptable;
        $this->idx_slave = $mpidx;          
        $this->db_slave = new DB();
        $this->db_slave->Open($mplocation,$mpuser,$mppassword,$mpdb);   
        $this->col_slave = $this->slaveColumns();           
    }   

    function slaveSyncronization()
    {
        $this->masterAllIndexes();
        $masterid=array();
        foreach ($this->db_master->result as $id)
        {
            $masterid[] = $id[0];
        }           

        $this->slaveAllIndexes();
        $slaveid=array();
        foreach ($this->db_slave->result as $id)
        {
            $slaveid[] = $id[0];
        }   

        $in = array_diff($masterid, $slaveid);
        echo "Added the data rows with the following ids: ";
        $sin = implode(",", $in);
        if ($sin == "")
        {
            echo "none";
            echo ".<br />";              
        }
        else
        {
            echo $sin;
            echo ".<br />";              
            $this->masterDataRows($sin);
            $sql = "INSERT INTO ".$this->db_slave->database.".".$this->table_slave." (".$this->col_slave.") values ";
            $i = 0;
            foreach ($this->db_master->result as $row)
            {
                if (count($row) > 1)
                {
                    if ($i == 1) $sql .= ",";
                    $sql .= "(";
                    $j=0;
                    //print_r($row);
                    foreach ($row as $col)
                    {
                        if ($j == 1) $sql .= ",";
                        $col = $this->db_slave->cleanSQL($col);
                        $sql .= "'".$col."'";
                        if ($j == 0) $j = 1;
                    }
                    $sql .= ")";
                    if ($i == 0) $i = 1;
                }
            }       
            echo "Adding rows sql:<br />".$sql;
            echo ".<br />";
            $this->db_slave->executeSQL($sql);              
        }

        $out = array_diff($slaveid, $masterid);
        echo "Deleted the data rows with the following ids: ";
        $sout = implode(",", $out);
        if ($sout == "")
        {
            echo "none";
            echo ".<br />";              
        }
        else
        {
            echo $sout;
            echo ".<br />";

            $sql = "DELETE FROM ".$this->db_slave->database.".".$this->table_slave." where ".$this->idx_slave." in (".$sout.")";
            echo "Deleted rows sql:<br />".$sql; 
            echo ".<br />";
            $this->db_slave->executeSQL($sql);  
        }
    }
    function direct()
    {
        echo "Data verified";
        header("refresh:1;url=customercode.php");
    }
}

?>

example of the data i'm trying to copy is : 124928F014007570 - varchar(20)

can someone guide me...thank you

2
Contributors
1
Reply
6
Views
5 Years
Discussion Span
Last Post by pritaeas
0

Looks like you are trying to use a varchar value in an IN clause. Make sure those values are surrounded by single quotes.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.