Result display problem in php and mysql

Question

        A                   B                   C

P_id | name | add     P_id  |  t_id        t_id |  paper  
-----------------     --------------     ------------------ 
  1   sam    ca         1        1           1      asdxa  
  2   john   de         2        1           2      dgfv  
  3   sam    jk         3        2           3      decgf  
  4   sam    ca         4        3           4      ergvtr

Now I can easily make a search for Name 'sam' in Table A group by name, add. and It shows me result like,

sam ca
sam jk

I am using php and mysql. I want to do some additional task in this:-
--> Totalcount for the rows..Like
sam ca 2
sam jk 1
(I am not taking P_id here... just focus on name and add.)

--> Make a link on 2 and 1 (above). so if I click on it. It should displays the related papers on another html page from table C. example: if I click on 2... then it should display asdxa and decgf.

You haven't given any code examples of how your php is talking to mysql. What db adapter library are you using? — Ben Lee
– Ben Lee, Commented Aug 25, 2011 at 7:35

Madara's Ghost · Accepted Answer · 2011-08-25 08:26:24Z

1

--> Totalcount for the rows..Like

SELECT `name`, `add`, count(`add`) FROM `A` WHERE (`name` = 'sam') GROUP BY `add`;

As for the linking, you just link to the page with that ID, and have your php script get the data from the given id in the C table.

To select all matching occurences while searching for a name:

SELECT  `A`.`name` AS name,  `A`.`P_id` AS aid,  `C`.`t_id` AS cid,  `C`.`paper` AS paper
FROM  `A` , `C` 
WHERE (
    `A`.`name` =  'sam'
)
AND (
    `A`.`P_id` =  `C`.`t_id`
)

result:

name    aid cid paper
sam     1   1   qwertyui
sam     3   3   zxcvbn
sam     4   4   uytrewq
sam     5   5   hfdsa

Which matches the test tables I did in my local environment

edited Aug 25, 2011 at 8:26

answered Aug 25, 2011 at 7:43

Madara's Ghost

176k52 gold badges274 silver badges314 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gagan Over a year ago

suppose if i click on 2 then it should display two values from table C. now how to fetch these two links... should i use a temp table for that, (fetch all the data in 1 query and save the link part in temp table). or something else... I m confused. btw thanks for total count.

Madara's Ghost Over a year ago

@Gagan np. if my answer was helpful, consider upvoting it (by pressing the up arrow above the answer's score). As for your question, I didn't quite understand, you want to, when clicking on the 2, to fetch both matching results from table C?

Gagan Over a year ago

Dude i cant upvote ur answer... it requires 15 (minimum) reputation and look at my score :P ok just leave the clicking part.. can u plz tell me how to fetch the final result(asdxa and decgf) from table C, if enter the name 'sam'.

Andrej · Accepted Answer · 2011-08-25 07:46:59Z

0

Try to use for the first task

$pdo = new PDO(....);
$result = $pdo->query('SELECT name, add, count(*) from table where name='sam' group by add;')->fetchAll();
if ($result) {
    foreach($result as $row) {
         ......
    }
}
//second
$sql = 'select paper from tableA a inner join TableB b on a.P_id=b.P_id inner join Tablec  c inner join b.t_id = c.t_id where a.p_id=2';

//repeat previous statements

edited Aug 25, 2011 at 7:46

answered Aug 25, 2011 at 7:40

Andrej

7,5041 gold badge22 silver badges22 bronze badges

Collectives™ on Stack Overflow

Result display problem in php and mysql

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related