Solved

Copying/Inserting row from database prefix to another prefix.

Posted on 2008-10-17
13
471 Views
Last Modified: 2012-05-05
Hello Experts,

In my database I have prefix called "prefix_country" which contain field named "country_id" .. I would like to include this field "country_id" in another prefix called "prefix_rating" ..

I'm sure it's easy task but I'm not that good in SQL databases.
0
Comment
Question by:Dido123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 51

Expert Comment

by:Steve Bink
ID: 22747108
Can you provide some information on the tables involved?
0
 

Author Comment

by:Dido123
ID: 22750708
I have Articles website .. In my database there are two tables :

- "prefix_users" which contain authors info like (Country , Name , Email , Signup date , etc..)
- "prefix_articles" which contain articles info like (article id, author userid, post date, words count, etc..)

The "prefix_articles" doesn't contain country id for each article which can be found in the "prefix_users" under "country_id" ... I would like to include the country id for each article.

I hope this is make it more clear. Please let me know if there is anything else.
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 22750759
I presume prefix_articles is related to prefix_users through the `author userid` field?  I will assume the parent field is called `userid`.  In that case:

SELECT a.country_id, b.* FROM prefix_users a INNER JOIN prefix_articles b ON a.userid=b.`author userid` WHERE b.`article id`=<article id>
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Dido123
ID: 22750772
Thanks .. I will try that now but first, Will I do this one time and from now on all new articles will be submitted to my database will have the country id assigned automatically Or I will have to do this task from time to time to keep it updated?


Thank you so much in advance.
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 22750822
The country_id field is already present by virtue of the relationship.  There's no need to duplicate the data in the child table (prefix_articles).  The query will supply the proper country_id each and every time.
0
 

Author Comment

by:Dido123
ID: 22758296
After some errors I posted your code that way :

SELECT a.country_id, b.* FROM prefix_users a INNER JOIN prefix_articles b ON a.user_id=b.`author_id` WHERE b.`article id`=`article id`

the "<" in <article id> seems to be the reason for the errors.

Anyway no errors now but nothing changed.

Please let me know what I do wrong.
This is the code I used.
 
 
SELECT a.country_id, b.* FROM prefix_users a INNER JOIN prefix_articles b ON a.user_id=b.`author_id` WHERE b.`article id`=`article id`

Open in new window

0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 22758327
Where <article id> appeared, you need to put the actual article ID you are searching for.  For example:

SELECT a.country_id, b.* FROM prefix_users a INNER JOIN prefix_articles b ON a.user_id=b.`author_id` WHERE b.`article id`=1
0
 

Author Comment

by:Dido123
ID: 22758355
I think you misunderstood me. I would like to add country ID to each article in my database.

Let me know If you need to know anything else from me to give the accurate solution.


Thank you for your time.
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 22758612
That's what I tried to explain earlier: the information already exists.  There is no need to duplicate the information since you have access to it through the relationship between your two tables.  Every time you query for a list of articles, using this JOIN will allow you to have the country_id included without requiring the additional space of duplicate storage.
0
 

Author Comment

by:Dido123
ID: 22758743
But this query will give me the ability to check only 1 article. I would like to have country ID assigned to all articles so I can search articles by country ID.

Let's say I would like to search/list all articles from USA .. what should I do?


Thanks again for your help.
0
 
LVL 51

Accepted Solution

by:
Steve Bink earned 500 total points
ID: 22758878
In that case, just replace the WHERE clause with the requirements of your search.  If USA is country_id=1, then you would search like this:

SELECT a.country_id, b.* FROM prefix_users a INNER JOIN prefix_articles b ON a.user_id=b.`author_id` WHERE a.country_id=1

Your query is not limited to just the country_id, either.  You can include any other fields from prefix_users that you might need, or would like to use as part of a filter.  If you wanted to search based on an author's name:

SELECT a.country_id, a.author_name, b.* FROM prefix_users a INNER JOIN prefix_articles b ON a.user_id=b.`author_id` WHERE a.author_name='Mi Nam'

0
 

Author Comment

by:Dido123
ID: 22765251
You are really great person. It worked! :)


Thank you so much for your time. I wish I could reward you more than 500 points :)


BTW, I have another "open" question about SQL databases. I'm sure you can help on it.

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/Q_23827239.html


Thanks again. Question has been answered.
0
 

Author Closing Comment

by:Dido123
ID: 31507183
Thanks again for your time.
0

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question