[SOLVED] Total Newbie SQL Query Question

[shellyrobson]

Good morning, I am a total newbie to php and I hope that someone on here maybe able to assit me.

 

I will try and explain in fine detail so please bear with me

 

I have 4 database tables, they are called

 

* people

* table1

* table2

* table3

 

the table "people" holds members of staff, it holds their name and also an autoincreasing ID number, the other 3 tables "table1", "table2" and "table3" are all the same in structure and they each hold ID numbers of the staff (stored in the "people" table) along with an autoincreasing ID number.

 

here is an export on my 4 tables so you can at least re-create what I have

 

CREATE TABLE `people` (
  `id` int(5) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

-- 
-- Table structure for table `table1`
-- 

CREATE TABLE `table1` (
  `id` int(5) NOT NULL auto_increment,
  `peopleid` int(5) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

-- 
-- Table structure for table `table2`
-- 

CREATE TABLE `table2` (
  `id` int(5) NOT NULL auto_increment,
  `peopleid` int(5) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

-- 
-- Table structure for table `table3`
-- 

CREATE TABLE `table3` (
  `id` int(5) NOT NULL auto_increment,
  `personid` int(5) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

 

What these tables store is user activity, so each of the 3 tables called "table1", "table2" and "table3" can store the ID numbers of staff in the "people" table, the ID number of each member of staff is stamped in the field called "peopleid" in "table1" and "table2" but the field is called "personid" in "table3", but they all act the same.

 

My question is, how can I write a QUERY to list the names and ID numbers of the top 4 staff in "people" table, but ordering it by the most active member of staff.

 

I guess the QUERY would need to count how many times each staff ID number is found in "table1", "table2" and "table3" and then order it by the most popular found.

 

I may also need to add a few more tables to the QUERY, how would I do that.

 

Can someone please help me, im in a bit of a pickle and really could do with some help.

 

Below I have put a full SQL export of my tables containing data

 

Thank you so very much

 

Here's my full export

 

-- 
-- Table structure for table `people`
-- 

CREATE TABLE `people` (
  `id` int(5) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

-- 
-- Dumping data for table `people`
-- 

INSERT INTO `people` (`id`, `name`) VALUES 
(1, 'D Edwards'),
(2, 'T Fail'),
(3, 'T Woods'),
(4, 'B Davies');

-- --------------------------------------------------------

-- 
-- Table structure for table `table1`
-- 

CREATE TABLE `table1` (
  `id` int(5) NOT NULL auto_increment,
  `peopleid` int(5) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

-- 
-- Dumping data for table `table1`
-- 

INSERT INTO `table1` (`id`, `peopleid`) VALUES 
(1, 1),
(2, 1),
(3, 1),
(4, 2),
(5, 2),
(6, 3),
(7, 4),
(8, 4);

-- --------------------------------------------------------

-- 
-- Table structure for table `table2`
-- 

CREATE TABLE `table2` (
  `id` int(5) NOT NULL auto_increment,
  `peopleid` int(5) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

-- 
-- Dumping data for table `table2`
-- 

INSERT INTO `table2` (`id`, `peopleid`) VALUES 
(1, 1),
(2, 2),
(3, 2),
(4, 2),
(5, 2),
(6, 2),
(7, 3),
(8, 4),
(9, 4),
(10, 4),
(11, 4),
(12, 4);

-- --------------------------------------------------------

-- 
-- Table structure for table `table3`
-- 

CREATE TABLE `table3` (
  `id` int(5) NOT NULL auto_increment,
  `personid` int(5) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

-- 
-- Dumping data for table `table3`
-- 

INSERT INTO `table3` (`id`, `personid`) VALUES 
(1, 1),
(2, 2),
(3, 2),
(4, 2),
(5, 3),
(6, 3),
(7, 3),
(8, 3),
(9, 4);

[Third_Degree]

Helpful Links

 

http://www.w3schools.com/SQL/func_count_ast.asp

http://www.w3schools.com/SQL/sql_orderby.asp

http://www.w3schools.com/SQL/sql_create.asp

[shellyrobson]

Thanks, but do you know what QUERY i should be using? I am totally new so I'm not that clued up on how to even start writting that.

 

Please can anyone help me :'(

[MasterACE14]

this is 1 way to grab the database contents and display them...

<?php

// connect to your database here...

// grab table/column contents
$query = mysql_query("SELECT `your_column` FROM `your_table`'") or die("Query Failed.");

// put it into an array
$row = mysql_fetch_array($query);

// display the contents
echo $row['your_column'];

?>

 

that should get you started. There is plenty of free scripts and tutorials online, just google them.

 

Regards ACE

[shellyrobson]

Sure, thanks.

 

I have had a look and I see how you can combine tables, but I am totally stumped on how you combine more than 2 tables, but then get it to sort the results based on a score generated from adding up rows in other tables.

 

Am I making any sense or do you need me to detail my problem some more.

 

I really am a total newbie, so you need to hold my hand

[MasterACE14]

try this:

<?php

// connect to your database here...

// grab table/column contents
$query = mysql_query("SELECT * FROM `your_table1`,`your_table2`,`your_table3`,`your_table4`") or die("Query Failed.");

// put it into an array
$row = mysql_fetch_array($query);

// display the contents
echo $row['your_column1'];
echo "<br>";
echo $row['your_column2'];

?>

[shellyrobson]

Sorry I don't think im making much sence

 

Okay, what I have is 1 table called "people" which holds users data, I also have 3 tables called "table1" "table2" and "table3" each of these tables can contain ID numbers of users stored in the "people" table. So the ID number from row 3 of the "peoples" table may appear twice in "table1" five times in "table2" and twenty times in "table3".

 

What I want to do is calculate how many times each users ID number from the table "people" appears in both "table1" "table2" and "table3" and then sort the results of "people" but ORDER the results of the table "people" with the most popular at the top and the least popular at the bottom.

 

So if ID number 3 (from the "people" table) appears a total of 10 times within "table1" "table2" and "table3" and ID number 1 (from the "people" table) appears 4 times within "table1" "table2" and "table3" then ID 3 would be shown at the top of the QUERY results and ID 1 would be shown at the bottom of the QUERY results.

 

Does that make any better sense?

 

 

Thanks

[MasterACE14]

thats slightly more complicated then I first thought lol. I think you need to read up on some MySQL, such as JOIN's so you can join all 4 tables in the 1 query then workout the math all within the 1 query. This isn't so much a PHP question but 1 of Mysql.

[shellyrobson]

Oh okay then, I'll have to find some time to do some reading up, as I said, im a newbie so everything seems very complex to me

[MasterACE14]

I recommend the PHP and MySQL tutorials on tizag.com , not to mention the tutorials here on PHP Freaks 

[shellyrobson]

Looks like its will be much harder to do than I first thought.

[LemonInflux]

No one said it was going to be easy

 

----------------

Now playing: Enter Shikari - The Feast (Demo)

via FoxyTunes

[shellyrobson]

your telling me

 

If I get stuck after having a read, am I ok to post for some more help?

[LemonInflux]

Of course. If you're willing to learn, you're welcome here

 

----------------

Now playing: Flyleaf - Cassie

via FoxyTunes

[niranjnn01]

Hello,..

 

I think you need to rethink ur logic...

 

instead of adding ids of ppl to different table when they do some activity, and then counting how many times there ids appear in different tables to get there level of activity, try this logic..

 

make a separate field in the first table itself, called "activity" and then, when ever the person logs in and does some activity, simply increase the value in this field.

 

now if u need to track the persons activity over a number of areas, like sleepin, eating travelling , then make more fields like

 

activity_sleeping, activity_eating, activity_travelling, and do increase there value when each of the activities are done.

 

 

I think this is a better logic.. see if it suits to ur needs. AND YEAH.. its not that difficult as it seems in the begining...

 

Regards

Rakesh

[shellyrobson]

Thanks everyone, I am going to go with the idea of just increasing a value by 1 each time

 

What would be quickest QUERY to write to increase a number by 1? It's just that I need to do this is lots of places so I didnt want to write a long QUERY and slow things down.