CODEWITHSUNDEEP
phpmysqlsql
Ranveer
Ranveer

Reputation: 6863

Transferring information from one table to another using PHP and MySQL

I have a database my_db and in it two tables t1 and t2.

t1 has two columns ID and count. ID is a list of some integers, say 1,3,4,6,7,8,9 and count is all 0s by default.

t2 also has two columns, ID2 which has a list of integers which are same as that of ID in t1. But, it is possible that they may repeat or may not be present. The second column contains some value, that isn't of much importance to this question. Clarification: ID2 can be 1,1,3,4,3,1,9,8,7,7,7.

Now, what I need to do is for every ID in t1 I need to fill in count, i.e., the number of occurrences of ID as ID2 in t2.

Running a loop through all the values in ID2 and incrementing by 1 every time in corresponding count ought to do it. But I'm unable to code it up, being new to php and sql. I can work in one table. How to work across multiple?

Upvotes: 1

Views: 116

Answers (5)

Suhel Meman
Suhel Meman

Reputation: 3852

Are you looking for this :

select t1.id,count(t2.id) as total_record_in_t2
from t1
     left join t2 on (t1.id=t2.id)
group by t1.id;

SQL Fiddle Example - Click to See

Upvotes: 2

kwelsan
kwelsan

Reputation: 1219

Another answer (with sub query)

update d1
set d1.count_d1 = (select count(d2.id)
                   from d2
                   where d2.id = d1.id group by d1.id)

SQL Fiddle

Upvotes: 2

Chun Lin
Chun Lin

Reputation: 544

Maybe you can try MySQL update join?

UPDATE t1 
       LEFT JOIN (SELECT id2, 
                         Count(1) AS num 
                  FROM   t2 
                  GROUP  BY id2) ref 
              ON t1.id = ref.id2 
SET    t1.count = ref.num

Please correct me if I'm wrong.

Upvotes: 3

Muhammad Rizwan Kaim Khani
Muhammad Rizwan Kaim Khani

Reputation: 2080

As I have understand your question is that, you need to count all ID's from Table t2 by looking ID in table t1 and then you want to insert the count of all ID's in your count column of table t1. 

<?php 

$query = mysql_query("SELECT * from t1");
while($record = mysql_fetch_array($query )) {

$query2 = mysql_query("SELECT COUNT(*) from t2 WHERE id='".$record['ID']."'");
$fetch = mysql_fetch_array($query2);
$count = $fetch['COUNT(*)'];

$query3 = mysql_query("UPDATE t1 SET count='".$count."' WHERE id='".$record['ID']."'");

}

?>

If you get any error. You may inform me.

Hope it will works for you.

Thanks

Upvotes: 2

Till1234
Till1234

Reputation: 73

Here is the sql part. First get the count of occurences of ID2 in t2:

select id2, count(*) from ts group by id2;

Then loop over the result and update t1:

update t1 set count = $value where id1 = $id2

Upvotes: 1

Related Questions