need help on queries

[pixeltrace]

hi,

 

i need help, is there any code available

that can copy specific items in a table to another table and

verifies if there's any duplicates also?

 

example

table 1

 

id | name | address | email | contact | company

 

in table 2

id | name | address | email

 

how do i copy field id, name, address and email in table 1 going to table 2

and omits items that are already existing in table 2 via id and name?

 

hope you could help me with this.

 

 

thanks so much!

[cooldude832]

yes its called a dual/multi level query

<?php
$q = "select * from `table` WHERE (select From `table2` WHERE)";
?>

I'm not exactly sure on its syntax I know it can be done similar to this method

I think its this, (not sure)

<?php
$q = "SELECT * From `table1` WHERE Name != 'SELECT Name From `table2`' && Address != 'Select address From `Table2`'";
?>

I think...

[pixeltrace]

hi,

 

i tried this, its a select function not update or copy

is there a way that i can copy items in table 1 going to table 2?

 

 

thanks!

[cooldude832]

I've never heard of a copy function for mysql (as a function)

You need to query it out with * than say

<?php
while($row = mysql_fetch_array($result){
//insert each match
}
?>

[mmarif4u]

Try it like this:

I am not sure is this u want or not but it will copy the data from one table to another.

 

$sql = "INSERT INTO table1 (name,phone,address,date)
            select name,phone,address,date 
            from table2 where id = '001' ;
            ";

 

OR from php point of view:

 

$sql = "INSERT INTO table1 (name,phone,address,date)
            select name,address,'$phone','$date' 
            from table2 where id = '$id' ;
            ";

 

Hope it will help.

[pixeltrace]

hi,

 

i apologize if my question is not clear.

currently i will be running a php page in cron

that will automatically update

table 2

with whatever new item is in table 1

 

and functions that i might needs is a query or php code that will

only copy new items from table 1 that is not yet existing

in table 2

 

table 1 has this fields

id | name | address| phone | company

 

and i only need to get new items in table 1 going to table 2

for this field

id | name | address

wherein in it will validate this 3 fields if the 3 fields

are the same, it will not copy it anymore.

 

hope you could help me with this.

 

thanks!

[mmarif4u]

U can use NOT IN clause in where statement to copy new one.

 

[ViN86]

I've never heard of a copy function for mysql (as a function)

You need to query it out with * than say

<?php
while($row = mysql_fetch_array($result){
//insert each match
}
?>

 

this is exactly how i would do it, the while loop will cycle through an array.  to refer to individual elements, use

$row['column_name']

 

to add and check you will need an embedded WHILE loop.  so execute SELECT * queries on both tables

<?php

$qry1 = "SELECT * FROM table1";
$qry2 = "SELECT * FROM table2";
$query_from_table1 = mysql_query($qry1);
$query_from_table2 = mysql_query($qry2);

while($row1 = mysql_fetch_array($query_from_table1)){
    while($row2 = mysql_fetch_array($query_from_table2)){
         if (($row1['column_name1'] != $row2['column_name1']) AND ($row1['column_name2'] != $row2['column_name2'])){  // Perform for all columns
              // add data cause the data is new
         } else {
              // report that there is a match in the data
         }
    }
}

dont copy and paste this, use your respective column names and table names where indicated.  hope this helps

 

 

also, i believe there is a function in MySQL that will allow you to merge tables.  if you think about it, copying elements in one to another without overwriting is merely merging data sets.

[pixeltrace]

thanks!

 

i will try this

 

 

[pixeltrace]

hi i just want to ask,

 

since i wanted to copy items in table 1 to table 2

which doesnt have duplicates.

 

will this work?

INSERT * FROM table1 LEFT JOIN table2 WHERE table2.id IS NULL

 

i haven't tried it, but i just wanted to check first if it will work

 

thanks!

[pixeltrace]

also for updating any items in table2 base from any changes in table 1

 

will this query work?

 

UPDATE * FROM table1 LEFT JOIN table2 WHERE table1.crud IS CHANGED

 

is there a query code call IS CHANGED?

what is the correct code for that?

thanks!

[ViN86]

heres more info on JOIN

 

http://dev.mysql.com/doc/refman/4.1/en/join.html

 

im not familiar with the function myself.  its probably not the fastest way to do what you need, but i would use the nested WHILE loop.

 

if this needs to be done repeatedly, you will want to use PHP for it so you can call it again and again.  but tbh, if this only needs to be done once, you might consider using MySQL queries into a terminal (if you have access) or via a MySQL GUI based editor, such as phpMyAdmin.