Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

Copying Items from one table to another

Codes here !

Moderators: macek, egami, gesf

Copying Items from one table to another

Postby MikeSpider » Tue Apr 30, 2013 9:07 am

Hi guys,
I have one table 'expenses' and another table 'inventory'. They don't have similar structure.
once in a while the user will add items from the 'expenses' to the 'inventory'. But I want for only items that do not already exist in the inventory to be inserted. I want to avoid duplicates or any sort of update.

I am trying to figure out the best way to do this.

the code:


Code: Select all
$sql = "SELECT * FROM expenses ORDER BY id DESC";
             $result = $this->db->connection->query($sql);
             if(!$result){
                 throw new Exception("Query failed: " . $sql . " - " . $this->db->connection->error);
             }else{
                 while($p = $result->fetch_assoc()){                     
                     $sql2 = "INSERT INTO inventory VALUES(NULL,'{$p['description']}','{$p['category']}','{$p['price']}',
                                                         '','{$p['qty']}', NOW())";
                                                         
                    $result2 = $this->db->connection->query($sql2);
                    if(!$result2){
                        throw new Exception("Query failed: " . $sql2 . " - " . $this->db->connection->error);
                    }else{
                     $this->success = true;
                    }




Thanks in advance,
Mike
MikeSpider
New php-forum User
New php-forum User
 
Posts: 49
Joined: Sun Feb 05, 2012 12:48 pm

Re: Copying Items from one table to another

Postby developerahusain » Wed May 29, 2013 10:14 pm

Here is the query for achieving what you want

$quer1=mysql_query("select * from table1");
if($num1=mysql_num_rows($quer1))
while($resul=mysql_fetch_array($quer1))
{
$row=mysql_fetch_array (mysql_query ("select * from table2"));
//checking if table1 contents match table 2 contens
if ($row ['your_column1']==$resul ['your_column2'])
{
//if there is matches leave it alone

}
//and insert all those which doesnt have matches
else {
insert query
}
}

This query will run efficiently as i have been using this for a long time.Do come back if there is any problem

developer ahusain
developerahusain
New php-forum User
New php-forum User
 
Posts: 44
Joined: Tue May 28, 2013 10:04 pm
Location: India,Lucknow

Re: Copying Items from one table to another

Postby MikeSpider » Fri Jun 07, 2013 3:36 am

Sorry for taking so long to respond.
I've been away for a while and just seen your reply.

I'm having a try now, will let you know of the outcome.

many thanks,

Mike
MikeSpider
New php-forum User
New php-forum User
 
Posts: 49
Joined: Sun Feb 05, 2012 12:48 pm

Re: Copying Items from one table to another

Postby MikeSpider » Fri Jun 07, 2013 8:15 am

Hi again,

I modified the script a little but still, isn't working properly. the second query is only returning one item, i tried to add another loop but it just multiplied the same thing. what happens is, it works only for 1 repeated item (id =1), not for all.

Here's the script:
Code: Select all


     public function InsertAllToInventory(){
         
          try{   
             
             $sql = "SELECT * FROM expenses";
             $result = $this->db->connection->query($sql);
             if(!$result){
                 throw new Exception("Query failed: " . $sql . " - " . $this->db->connection->error);
             }else{
                    while($p = $result->fetch_assoc())
                        {
                           
                        $sql2 = "SELECT * FROM inventory ";
                        $result2 = $this->db->connection->query($sql2);
                        if(!$result2){
                            throw new Exception("Query failed: " . $sql2 . " - " . $this->db->connection->error);
                        }else{
                            $row = $result2->fetch_assoc();
                            //checking if table1 contents match table 2 contens
                            if ($row ['name'] != $p['description'])       
                            {
                            //if there there is no match insert!
                                echo '<table><tr><td>'.$row['name'].'</td><td>'. $p['description'].'</td></tr></table>';
                               
                                $sql3 = "INSERT INTO inventory VALUES(NULL,'{$p['description']}','{$p['category']}','{$p['price']}',
                                                         '{$p['targetProfit']}','{$p['qty']}','{$p['id']}', NOW())";
                                                         
                                $result3 = $this->db->connection->query($sql3);
                                if(!$result3){
                                    throw new Exception("Query failed: " . $sql3 . " - " . $this->db->connection->error);
                                }else{
                                       
                                    $this->success = true;
                           
                            }
                            }  else {
                                echo 'some items already exists in the table';
                            }

                        } 
                 }
              }         

         
        }catch(Exception $e){
            echo "Message: " . $e->getMessage();
        }
         
     }


MikeSpider
New php-forum User
New php-forum User
 
Posts: 49
Joined: Sun Feb 05, 2012 12:48 pm

Re: Copying Items from one table to another

Postby developerahusain » Fri Jun 07, 2013 8:54 pm

Here is the exact code i'm using for my purpose
<? $quer5=mysql_query("select * from dump_submission");
while($resul5=mysql_fetch_array($quer5))
{
$row3=mysql_fetch_array (mysql_query ("select * from dump3 where fname='".$resul5 ['name']."'"));

if ($row3 ['fname']) { //insert query goes here }
else echo "//no matches found no insert query here";
}
?>
And my request to you that you please put this exact code change your tables and try to insert it.After that you can start modifying the code as you want.

developerahusain
developerahusain
New php-forum User
New php-forum User
 
Posts: 44
Joined: Tue May 28, 2013 10:04 pm
Location: India,Lucknow

Re: Copying Items from one table to another

Postby MikeSpider » Sat Jun 08, 2013 3:21 am

Hi again,

Yes it worked! I only added the "WHERE" clause in the second query as it was the only thing missing.

Dont know how I missed that one :oops: . Anyways, thanks a bunch! :D

Mike
MikeSpider
New php-forum User
New php-forum User
 
Posts: 49
Joined: Sun Feb 05, 2012 12:48 pm

Re: Copying Items from one table to another

Postby developerahusain » Sat Jun 08, 2013 3:31 am

You are welcome mike.I'm also happy to help you

Developerahusain
developerahusain
New php-forum User
New php-forum User
 
Posts: 44
Joined: Tue May 28, 2013 10:04 pm
Location: India,Lucknow


Return to mySQL & php coding

Who is online

Users browsing this forum: Bing [Bot] and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.

cron