Query phpmyadmin table union

[builtfoxy]

Totally new to data base side applications and this is what i'm trying to do.I have 2 tables adsmanager_categories and adsmanager_subcategories and need the categories table to be able to read subcategories table.From what i've read union i think is my best solution?Being the table structure is the same.I did successfully query a union of the tables,new table appears,but does not remain in data base or serve the information to my site.I'm i wrong here in thinking thats what this function does?Nor does the new table have any functions such as,browse,edit ect. ect. They seem to union with all info. just no function ability.How can i make this work?Any advice would be appreciated!!

My cms is joomla 1.5.6 Data base info. localhost

 

* Server version: 5.0.41-community-log

* Protocol version: 10

* Server: Localhost via UNIX socket

* User: builtfo_glenn@localhost

* MySQL charset: UTF-8 Unicode (utf8)

*

MySQL connection collation: i Documentation

* Create new database: Documentation

No Privileges

* Show MySQL runtime information

* Show MySQL system variables Documentation

* Processes Documentation

* Character Sets and Collations

* Storage Engines

* Databases

* Export

* Import

* Log out

 

phpMyAdmin - 2.10.0.2

 

* MySQL client version: 4.1.15

* Used PHP extensions: mysql

*

Language Info:

*

Theme / Style:

*

Font size:

* phpMyAdmin documentation

* phpMyAdmin wiki

* Official phpMyAdmin Homepage

* [ChangeLog] [subversion][Lists]

Thanks Glenn

 

QUERY:  SELECT `id` , `parent` , `name` , `description` , `ordering` , `published`

FROM `jos_doneadsmanager_categories`

UNION SELECT `id` , `parent` , `name` , `description` , `ordering` , `published`

FROM `jos_doneadsmanager_subcategories`

 

 

[attachment deleted by admin]

[Chicken Little]

The UNION displays the data and you need to INSERT that data into the other table in order to view it. You probably need a JOIN instead.

[builtfoxy]

Ok, one more question.My adsmanager_categories table has 1400 rows and the adsmanager_subcategories will have 70,000 Will there be an issue with such a big table and will it display subcats. on website?I guess thats two questions. Anyways i will try a join.

 

Thanks much!!

 

Glenn

[builtfoxy]

Ok, one more question.My adsmanager_categories table has 1400 rows and the adsmanager_subcategories will have 70,000 Will there be an issue with such a big table and will it display subcats. on website?I guess thats two questions.

 

Thanks much!!

 

Glenn

[Chicken Little]

Can you post the code and give us an idea what you are trying to achieve.

[builtfoxy]

    Let me tell you what my issue first.I have a tables adsmanager_cats. which contains States which are the parent, and cities which are the children.Around 5000 total.Then wanted to add the categories(50 total) under each city.Once i query in around 30000 of these cats. i started losing all css.I figured i over loaded the table.Once i started backing all these out my template and css returned.

 

    Now i have limited adsmanager_cats table to 1400 and created another table adsmanager_subcats. Which will contain 70,000 rows.Right now it contains only 100 rows.I experimented with a union which i stated above in my first post.Those 100 rows where added to the cats. table.Now my question is this:even though each had its own ID no. and parent ID no. they where not view able on my site.Why? And once they are joined correctly will it be capable of bring up ad form, ad, and entered into admin/cats. page in backend? I apoligize for my lack of knowledge and hope you understand.I am truely stuck at the moment.If you can tell me the best way for function ability and speed of reading two big tables to solve my issue.

 

 

Thanks    Glenn

[Chicken Little]

If I understand correctly you want to view subcategories for each city based on a common id field. You could use

SELECT jos_adsmanager_categories.id, jos_adsmanager_categories.parent, jos_adsmanager_categories.name, jos_adsmanager_categories.description, jos_adsmanager_categories.ordering, jos_adsmanager_categories.published, jos_adsmanager_subcategories.adform, jos_adsmanager_subcategories.ad
FROM jos_adsmanager_categories
INNER JOIN jos_doneadsmanager_subcategories 
ON jos_doneadsmanager_categories.id = jos_doneadsmanager_subcategories.id

 

[builtfoxy]

I figured out all the syntax errors.Can't figure out this one: SELECT jos_doneadsmanager_categories.id, jos_doneadsmanager_categories.parent, jos_doneadsmanager_categories.name, jos_doneadsmanager_categories.description, jos_doneadsmanager_categories.ordering, jos_doneadsmanager_categories.published, jos_doneadsmanager_subcategories.adform, jos_doneadsmanager_subcategories.ad

FROM jos_doneadsmanager_categories

INNER JOIN jos_doneadsmanager_subcategories ON jos_doneadsmanager_categories.id = jos_doneadsmanager_subcategories.id

LIMIT 0 , 30

 

#1054 - Unknown column 'jos_doneadsmanager_categories.id' in 'field list'

 

 

And also had to back out jos_doneadsmanager_subcategories.ads and jos_adsmanager_subcategories.adform since they do not exist.The correct names of these tables are jos_doneadsmanager_ads and jos_doneadsmanager_fields.Do i have to add these in to my query to utilize there function when user chooses sub cat. to enter ad. or will the inner join automatically pick up these functions? And should i complete jos_doneadsmanager_subcategories table before inner join or can i add to this table after its joined?

 

Thanks much for your responce!!      Glenn 

[Chicken Little]

Ok. I hope I'm on the right track. Does this make sense?

 

SELECT jos_doneadsmanager_categories.id, jos_doneadsmanager_categories.parent, jos_doneadsmanager_categories.name, First(jos_doneadsmanager_categories.description) AS FirstOfdescription, First(jos_doneadsmanager_categories.ordering) AS FirstOfordering, First(jos_doneadsmanager_categories.published) AS FirstOfpublished, jos_doneadsmanager_subcategories.id, jos_doneadsmanager_subcategories.parent, First(jos_doneadsmanager_subcategories.name) AS FirstOfname, First(jos_doneadsmanager_subcategories.description) AS FirstOfdescription1, First(jos_doneadsmanager_subcategories.ordering) AS FirstOfordering1, First(jos_doneadsmanager_subcategories.published) AS FirstOfpublished1, jos_doneadsmanager_ads.ads, jos_doneadsmanager_fields.fields
FROM ((jos_doneadsmanager_categories INNER JOIN jos_doneadsmanager_ads ON jos_doneadsmanager_categories.id = jos_doneadsmanager_ads.id) INNER JOIN jos_doneadsmanager_fields ON jos_doneadsmanager_categories.id = jos_doneadsmanager_fields.id) INNER JOIN jos_doneadsmanager_subcategories ON jos_doneadsmanager_categories.id = jos_doneadsmanager_subcategories.id
GROUP BY jos_doneadsmanager_categories.id, jos_doneadsmanager_categories.parent, jos_doneadsmanager_categories.name, jos_doneadsmanager_subcategories.id, jos_doneadsmanager_subcategories.parent, jos_doneadsmanager_ads.ads, jos_doneadsmanager_fields.fields;

[builtfoxy]

Still have error: #1054 - Unknown column 'jos_doneadsmanager_categories.id' in 'field list. This is the code i used: SELECT `jos_doneadsmanager_categories.id` , `jos_doneadsmanager_categories.parent` , `jos_doneadsmanager_categories.name` , 'First' `jos_doneadsmanager_categories.description` , 'AS' 'FirstOfdescription', 'First' `jos_doneadsmanager_categories.ordering` , 'AS' `FirstOfordering` , 'First' `jos_doneadsmanager_categories.published` , 'AS' `FirstOfpublished` , `jos_doneadsmanager_subcategories.id` , `jos_doneadsmanager_subcategories.parent` , 'First' `jos_doneadsmanager_subcategories.name` , 'AS' 'FirstOfname', 'First' `jos_doneadsmanager_subcategories.description` , 'AS' 'FirstOfdescription1', 'First' `jos_doneadsmanager_subcategories.ordering` , 'AS' 'FirstOfordering1', 'First' `jos_doneadsmanager_subcategories.published` , 'AS' 'FirstOfpublished1', 'jos_doneadsmanager_ads.ads', 'jos_doneadsmanager_fields.fields',  'FROM' `jos_doneadsmanager_categories` , 'INNER JOIN' 'jos_doneadsmanager_ads', 'ON' 'jos_doneadsmanager_categories.id' = 'jos_doneadsmanager_ads.id', 'INNER JOIN' 'jos_doneadsmanager_fields', 'ON' 'jos_doneadsmanager_categories.id' = 'jos_doneadsmanager_fields.id', 'INNER JOIN' 'jos_doneadsmanager_subcategories', 'ON' 'jos_doneadsmanager_categories.id' = 'jos_doneadsmanager_subcategories.id',  'GROUP BY' 'jos_doneadsmanager_categories.id', 'jos_doneadsmanager_categories.parent', 'jos_doneadsmanager_categories.name', 'jos_doneadsmanager_subcategories.id', 'jos_doneadsmanager_subcategories.parent', 'jos_doneadsmanager_ads.ads', 'jos_doneadsmanager_fields.fields' 

 

Could i be using the wrong kind of Punctuation marks? Should it be back ticks for tables and prime for fields? Maybe thats why i keep getting the 1054 error, for cats_id in field list. What are your thoughts? And could you please answer my questions from my last post.Suppling me with the code is all good, but sure could use some explanation of how it works. I sure appreciate your time and effort!!!

 

Glenn

[Chicken Little]

Does the jos_doneadsmanager_categories table have a field called id?

[builtfoxy]

 

    Yes it does. Take a look at the image of the table in my first post.

[Chicken Little]

I've duplicated your database (with bogus data) and tried some queries but am unsure of what you want. The following is a UNION using your first posted query:

 

id  parent  name  description  ordering  published 

1 0 parent1 parent1 0 0

2 0 parent2 parent2 0 0

3 0 parent3 parent3 0 0

1 0 child1 child1 0 0

2 0 child2 child2 0 0

3 0 child3 child3 0 0

 

Is this the result you want? In your first post you state that a "new table appears,but does not remain in data base or serve the information to my site." The "table" that appears is the data queried and does not remain. In order to "serve" the info to your site you will need to develop some code to display the info. Probably not a good idea to develop another table of the queried data since this will negate the dynamic nature of using the database in the first place.

 

Or am I completely off base? Can you post some examples of what you are trying to achieve?

 

Also, can you dump the code and post it here (or send it to my email). Only a small portion of the data will be needed to populate the tables, say 10 records from each table.

[fenway]

No-one can possibly understand that query formatted as such.

[builtfoxy]

You got that right Fenway.Lets try this one more time.If i just insert the sub cats. into the adsmanager_categories table there will be some where around 70,000 rows in this table.Will it still perform?Do i need to change the intregal of the table to read more info?Stuck like chuck!!!

 

Glenn

[fenway]

You got that right Fenway.Lets try this one more time.If i just insert the sub cats. into the adsmanager_categories table there will be some where around 70,000 rows in this table.Will it still perform?Do i need to change the intregal of the table to read more info?Stuck like chuck!!!

 

Glenn

Could you elabortate a bit more?

[builtfoxy]

Ok Fenway, I am using joomla cms and running adsmanager software.In my adsmanager software there are these tables.

 

[attachment deleted by admin]

[builtfoxy]

Ok Fenway, I am using joomla cms and running adsmanager software.In my adsmanager software there are these tables.

Need to know how to join subcats table to function with the other adsmanager tables.New to this and need your help.

 

Glenn

[builtfoxy]

You got that right Fenway.Lets try this one more time.If i just insert the sub cats. into the adsmanager_categories table there will be some where around 70,000 rows in this table.Will it still perform?Do i need to change the intregal of the table to read more info?Stuck like chuck!!!

In my adsmanager cats table there are 1400 rows right now and if i insert the subcats (50) of them in each row there will be 70,000 rows.I created the subcats table you see below.Can i just insert the subcats into table and it function properly or should i join subcats table?

Glenn

Could you elabortate a bit more?

[fenway]

I don't see why it wouldn't work.