• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 476
  • Last Modified:

Copying/Inserting row from database prefix to another prefix.

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
Dido123
Asked:
Dido123
  • 7
  • 6
1 Solution
 
Steve BinkCommented:
Can you provide some information on the tables involved?
0
 
Dido123Author Commented:
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
 
Steve BinkCommented:
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
Independent Software Vendors: 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!

 
Dido123Author Commented:
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
 
Steve BinkCommented:
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
 
Dido123Author Commented:
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
 
Steve BinkCommented:
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
 
Dido123Author Commented:
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
 
Steve BinkCommented:
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
 
Dido123Author Commented:
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
 
Steve BinkCommented:
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
 
Dido123Author Commented:
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
 
Dido123Author Commented:
Thanks again for your time.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now