PDA

View Full Version : Count records based on id - display along side?


student101
11-24-2008, 07:30 PM
table1 => Primary key
- tbl1id
- name

table2
- tbl2id => Primary key
- image
- tbl1id => foreign key to Table1

How would I be able to do this?

Display data on one page;

John - (0) <= this is from here "table2.image" which has 0 images.
Bob - (2) <= this is from here "table2.image" which has 2 images.
Luke - (2) <= this is from here "table2.image" which has 2 images.

SQL dump below...

/*
MySQL Data Transfer
Source Host: localhost
Source Database: demo
Target Host: localhost
Target Database: demo
Date: 2008/11/24 10:24:28 PM
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for table1
-- ----------------------------
CREATE TABLE `table1` (
`tbl1id` tinyint(3) NOT NULL auto_increment,
`name` varchar(50) default NULL,
PRIMARY KEY (`tbl1id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for table2
-- ----------------------------
CREATE TABLE `table2` (
`tbl2id` tinyint(3) NOT NULL auto_increment,
`image` varchar(50) default NULL,
`tbl1id` tinyint(3) default NULL,
PRIMARY KEY (`tbl2id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `table1` VALUES ('1', 'John');
INSERT INTO `table1` VALUES ('2', 'Bob');
INSERT INTO `table1` VALUES ('3', 'Luke');
INSERT INTO `table2` VALUES ('1', 'Bob\'s image', '2');
INSERT INTO `table2` VALUES ('2', 'Bob\'s image2', '2');
INSERT INTO `table2` VALUES ('3', 'Luke\'s image', '3');
INSERT INTO `table2` VALUES ('4', 'John\'s image', '1');
INSERT INTO `table2` VALUES ('5', 'John\'s image2', '1');

student101
11-25-2008, 08:26 AM
I'm not going to start an entire blog again, let's just say it's sorted.
It's actually pretty simple once you start playing with PHP.