Lifelesspeople.com
Home Hosting News Forum Links Status Support About

 Forum FAQsForum FAQs  Knowledge BaseKnowledge Base  RulesRules   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   HostingHosting   RegisterRegister 
 DonateDonate   WikiWiki   ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

counting rows from 2 tables

 
Lifelesspeople.com Forum Index -> Web Architects' Abode
Post new topic   Reply to topic View previous topic :: View next topic  
Author Message
Desbrina
Jadeite


Joined: 11 Jun 2005
Posts: 3010
Location: Earth
PostPosted: Tue Dec 04, 2007 6:15 am    Post subject: counting rows from 2 tables Reply with quote
I have 2 tables, both have a deck field
Tables: user_inventory + cards

I'm trying to make a query that checks to see if theres the same number of rows in user_inventory as in cards

How'd i do this?
_________________
Midnight Tempest - A Sailor Moon TCG
Balanced Force - A Star Wars TCG
Caretaker - A Star Trek Voyager TCG
My Stories
Back to top
 
linuxdoctor
Infallible Persona


Joined: 23 Apr 2005
Posts: 1307
Location: Ottawa, Canada
PostPosted: Tue Dec 04, 2007 8:23 am    Post subject: Reply with quote
I'll assume that you're using MySQL 5 and that the 'deck field' in both tables is called 'deck'. The easiest way to do this is the way you probably think it should be done: Count the rows and compare the results.

Code:

select deck from user_inventory;
set @x = found_rows();

select deck from cards;
set @y = found_rows();

select @x = @y;


The last 'select' is a compare which return '1' for true (meaning equal) or '0' for false.
_________________
Misanthrope: someone who realizes that humans really are as stupid as they appear.

If you think I'm 'politically' incorrect you have the wrong politics.
Back to top
 
Desbrina
Jadeite


Joined: 11 Jun 2005
Posts: 3010
Location: Earth
PostPosted: Tue Dec 04, 2007 8:43 am    Post subject: Reply with quote
do i do that through phpmyadmin or in a script? If i do it through phpmyadmin it comes up with
Quote:
SELECT deck
FROM user_inventory;# Rows: 10
SET @x = found_rows( ) ;# MySQL returned an empty result set (i.e. zero rows).
SELECT deck
FROM cards;# Rows: 3709
SET @y = found_rows( ) ;# MySQL returned an empty result set (i.e. zero rows).
SELECT @x = @y ;


though i'm not sure if that'll do what i want, i dont think my explanation was that good

I have 2 tables, both have a deck field
Tables: user_inventory + cards
I'm trying to make a system that automatically detects if a user has all then cards to a particular deck, so i need something that'll search through the user_inventory table, and pick out all the decks (deck is a field name in both the user_inventory table and the cards table) that have the same amount of rows in the user_inventory tables as well as the cards table


Example
Code:
INSERT INTO `user_inventory` (`ID`, `user_ID`, `name`, `item_id`, `deck`, `status`) VALUES
(1, 1, 'User', 11, '', 'collecting'),
(2, 1, 'User', 23, '', 'collecting'),
(3, 1, 'User', 43, '', 'willtrade'),
(4, 1, 'User', 75, '', 'keeping'),
(5, 1, 'User', 114, '', 'keeping'),
(6, 1, 'User', 234, '', 'willtrade'),
(7, 1, 'User', 2701, 'layout1', 'collecting'),
(8, 1, 'User', 2702, 'layout1', 'collecting'),
(9, 1, 'User', 2703, 'layout1', 'collecting'),
(10, 1, 'User', 2704, 'layout1', 'collecting');


Code:
INSERT INTO `cards` (`ID`, `amount`, `type`, `url`, `deck`, `name`, `category`, `alttext`, `features`, `worth`, `deny`, `donated`, `created`) VALUES
(2700, 15, 'puzzle', 'http://darknesswithin.com.ru/images/cards/shatterpoint15.png', 'shatterpoint', 'Shatterpoint', 'EU', 'shatterpoint 15', 'Shatterpoint', 1, '1', 'Desbrina', 0),
(2701, 4, 'special', 'http://darknesswithin.com.ru/images/cards/layout101.png', 'layout1', 'Layout 1', 'other', 'layout1 01', 'Images from Layout 1', 2, '0', 'Desbrina', 1),
(2702, 4, 'special', 'http://darknesswithin.com.ru/images/cards/layout102.png', 'layout1', 'Layout 1', 'other', 'layout1 02', 'Images from Layout 1', 2, '0', 'Desbrina', 1),
(2703, 4, 'special', 'http://darknesswithin.com.ru/images/cards/layout103.png', 'layout1', 'Layout 1', 'other', 'layout1 03', 'Images from Layout 1', 2, '0', 'Desbrina', 1),
(2704, 4, 'special', 'http://darknesswithin.com.ru/images/cards/layout104.png', 'layout1', 'Layout 1', 'other', 'layout1 04', 'Images from Layout 1', 2, '0', 'Desbrina', 1),
(2705, 20, 'character', 'http://darknesswithin.com.ru/images/cards/nofear01.png', 'nofear', 'No Fear', 'clone', 'nofear 01', '', 1, '0', 'Desbrina', 1),
(2706, 20, 'character', 'http://darknesswithin.com.ru/images/cards/nofear02.png', 'nofear', 'No Fear', 'clone', 'nofear 02', '', 1, '0', 'Desbrina', 1);


As you can see, both tables have 4 rows of layout 1
_________________
Midnight Tempest - A Sailor Moon TCG
Balanced Force - A Star Wars TCG
Caretaker - A Star Trek Voyager TCG
My Stories
Back to top
 
linuxdoctor
Infallible Persona


Joined: 23 Apr 2005
Posts: 1307
Location: Ottawa, Canada
PostPosted: Tue Dec 04, 2007 11:15 am    Post subject: Reply with quote
Sorry, in my haste I made slight mistake in my code. You need to include the 'SQL_CALC_FOUND_ROWS' modifier to the statement. The code should read as follows:

Code:

select sql_calc_found_rows deck from user_inventory limit 0;
set @x = found_rows();

select sql_calc_found_rows deck from cards limit 0;
set @y = found_rows();

select @x = @y;



There is also a change by adding 'limit 0'. This actually counts rather than returns the rows, which is I think what you want.

This is the actual SQL so if you're doing this in phpmyadmin you should just be able to type it in. If you're using PHP then you'd have to do whatever you do to make this work through PHP.
_________________
Misanthrope: someone who realizes that humans really are as stupid as they appear.

If you think I'm 'politically' incorrect you have the wrong politics.
Back to top
 
LP-SolidRaven
Dictator of the Dump


Joined: 06 Jun 2004
Posts: 7359
Location: The cheese is made out of moon
PostPosted: Tue Dec 04, 2007 1:33 pm    Post subject: Reply with quote
Uhm linuxdoctor, couldn't this be done with a join statement.
_________________
Quote:

<bart416> I just realized something
<bart416> we celebrate the fact that this piece of rock made one rotation around a glowing ball of plasma that is kept together due to its own gravity well
<njsg> HAPPY NEW YEAR
<Easter> ^^
Back to top
 
Desbrina
Jadeite


Joined: 11 Jun 2005
Posts: 3010
Location: Earth
PostPosted: Tue Dec 04, 2007 2:57 pm    Post subject: Reply with quote
still comming up with a similar thing
Quote:
SELECT SQL_CALC_FOUND_ROWS deck
FROM user_inventory
LIMIT 0 ;# MySQL returned an empty result set (i.e. zero rows).
SET @x = found_rows( ) ;# MySQL returned an empty result set (i.e. zero rows).
SELECT SQL_CALC_FOUND_ROWS deck
FROM cards
LIMIT 0 ;# MySQL returned an empty result set (i.e. zero rows).
SET @y = found_rows( ) ;# MySQL returned an empty result set (i.e. zero rows).
SELECT @x = @y ;

_________________
Midnight Tempest - A Sailor Moon TCG
Balanced Force - A Star Wars TCG
Caretaker - A Star Trek Voyager TCG
My Stories
Back to top
 
linuxdoctor
Infallible Persona


Joined: 23 Apr 2005
Posts: 1307
Location: Ottawa, Canada
PostPosted: Wed Dec 05, 2007 3:18 pm    Post subject: Reply with quote
Desbrina: Which version of MySQL are you using? Perhaps is a version difference. Works fine on my test.

SolidRaven: Using join? I don't think so. How would you suggest doing it with a join? The idea is to discover if two different tables have the same number of rows.
_________________
Misanthrope: someone who realizes that humans really are as stupid as they appear.

If you think I'm 'politically' incorrect you have the wrong politics.
Back to top
 
Desbrina
Jadeite


Joined: 11 Jun 2005
Posts: 3010
Location: Earth
PostPosted: Wed Dec 05, 2007 3:24 pm    Post subject: Reply with quote
umm, not sure which version, its hosted here,
phpmyadmin says Server version: 5.0.45-log


i get the same thing at home using version 5
_________________
Midnight Tempest - A Sailor Moon TCG
Balanced Force - A Star Wars TCG
Caretaker - A Star Trek Voyager TCG
My Stories
Back to top
 
nooc
Master Poster


Joined: 10 Aug 2005
Posts: 215
PostPosted: Wed Dec 05, 2007 7:50 pm    Post subject: Reply with quote
Check if 2 tables are equal in row count:
Code:

SELECT (SELECT COUNT(*) FROM table1) IN (SELECT COUNT(*) FROM table2)


Returns 0 or 1.
Back to top
 
Desbrina
Jadeite


Joined: 11 Jun 2005
Posts: 3010
Location: Earth
PostPosted: Thu Dec 06, 2007 2:17 am    Post subject: Reply with quote
how do i then get the name of the deck?
_________________
Midnight Tempest - A Sailor Moon TCG
Balanced Force - A Star Wars TCG
Caretaker - A Star Trek Voyager TCG
My Stories
Back to top
 
LP-SolidRaven
Dictator of the Dump


Joined: 06 Jun 2004
Posts: 7359
Location: The cheese is made out of moon
PostPosted: Thu Dec 06, 2007 12:56 pm    Post subject: Reply with quote
linuxdoctor wrote:
Desbrina: Which version of MySQL are you using? Perhaps is a version difference. Works fine on my test.

SolidRaven: Using join? I don't think so. How would you suggest doing it with a join? The idea is to discover if two different tables have the same number of rows.

COUNT() is your friend Silly

Try:
Code:

SELECT count(user_inventory.deck)
FROM user_inventory LEFT JOIN cards
WHERE user_inventory.deck = cards.deck

_________________
Quote:

<bart416> I just realized something
<bart416> we celebrate the fact that this piece of rock made one rotation around a glowing ball of plasma that is kept together due to its own gravity well
<njsg> HAPPY NEW YEAR
<Easter> ^^
Back to top
 
Display posts from previous:   
Post new topic   Reply to topic    Lifelesspeople.com Forum Index -> Web Architects' Abode All times are GMT - 6 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


[ Home | Hosting | News | Forum | Links | System Status | About | Archive | Donate ]
Powered by phpBB © 2001, 2002 phpBB Group
All trademarks and copyrights on this page are owned by their respective owners. Posts and comments are owned by the poster. Everything else © 2001 - 2007 Lifelesspeople.com