Solved

MySQL Newbie Question

Posted on 2011-03-19
11
380 Views
Last Modified: 2013-11-05
I am embaressed to ask but is there a tutorial site that I can look at to help me.  I am simply trying to take data from 2 MySQL databases format the data and insert the result in another database.

I am using Navicat and making my way through everything but don't want to loose anymore hair and waste time when it is something simple like this.

Thank you in advance.
0
Comment
Question by:Deacil
  • 6
  • 5
11 Comments
 
LVL 20

Expert Comment

by:Mark Brady
Comment Utility
Hi there

There are truck loads of free tutorials for beginners right up through advanced out there. you only have to google them but my advise is to actually post what you are trying to achieve right here and we will show you how to achieve it. We will even write and post the code for you.

Getting data from one database table and inserting it into another database table even in a different database is very simple. If you can be more specific, we can give you precise answers with examples if you want. Welcome to mysql by the way.
0
 

Author Comment

by:Deacil
Comment Utility
Thank you.  I spent some time on Google last night and found bits and pieces of info.  First let me say, I took down my servers a month or so ago and trying to use MySQL on GoDaddy (don't laugh).  This small project doesn't warrant me turning my servers back on.

Here is what I am trying to accomplish.  So I get 2 XML files every day.  The data is similar but 1 overrides everything in the other if it is different.  I would like to pull the info into a 3rd database which would be a clean self-containing file that I can use PHP to modify the db.  The biggest piece is the data in either file needs to be formatted with HTML tags so that I can import it into a website.  The data is a product database for an eCommerce site, approximately 5000 records.

The reason for the PHP edit is some products might need to be adjusted (price, availability, stock, etc...)  In addition to the HTML formatting I will also need to do some calculations such as price * 30%.  

I have tried with some success to do this in excel but it is miserable.  I have minor MySQL experience but have always wanted to know a lot more.

Thank you for the reply.
0
 
LVL 20

Expert Comment

by:Mark Brady
Comment Utility
Well you could do that in mysql and adjust the records using
PHP and php would display the records. You definately need a server that has both Php and mysql installed on. What you describe is a normal commercial type website that has CMS attached. That is a login section where the owner/operator can login and update/edit records and the changes are immediate on the website. Very common. In fact that's what I build on a daily basis. Not cheap though but you can either download something like drupal and build your own, or learn php/mysql and build your own, or pay a developer to build it for you.

Or of course if you are trying to do just one thing you could ask how to do it on here (EE) but we don't do full projects.
0
 

Author Comment

by:Deacil
Comment Utility
Thank you for the reply.  I understand about the full project, and I wouldn’t do that to anyone without compensation.  I was just trying to answer your question of providing more specifics.

I guess the only thing I want to find out what is the command or function to do a compare of one field and then insert the match to a different database/table.  If you only want to point me in a direction on the web I understand.  I don't mind doing the research and learning something new.  I am just trying to save a few hours and a headache from staring at a screen reading multiple sites.
0
 
LVL 20

Expert Comment

by:Mark Brady
Comment Utility
There is no specific function to look at data and compare it but I can write one for you. Firstly, I need to know what the tablename is and what sort of data is in there. What is it being compared to and what will you called your other table? Are both tables going to be in the same database or do you want it in a separate database? You can have both the tables in the same DB if you want, it would be easier.

Anyway, give me a little more info on what you want to compare (some examples) and I will write you a Php code to fetch the data from mysql and compare it to something then insert it into another table. Does it need to match something before you insert it into the different table or what? How does that work?

Thanks
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Deacil
Comment Utility
You are awesome!

All are in 1 db now.

Table 1 / Field {Product ID} match to Table 2 / Field {Stock_Code} items in record (Table 2 records) should be entered in Table 3 (same field names as Table 2).  Fields to be inserted/updated are {Brand} {Title} {Price}

Thank you again.  There are a lot more fields but that will get me started in a HUGE way!
0
 
LVL 20

Expert Comment

by:Mark Brady
Comment Utility
So can you give a quick example of a record for all three tables so I can get a hold of this. Then I can write an insert/update query to update or insert into the tables. Thanks.

What I need is something like

table1_name // the actual name for this table
list the columns in this table then put an item beneath each column so i can see how it should look. Just a quick example would be ok. Show all three tables though.
0
 

Author Comment

by:Deacil
Comment Utility
Thank you for looking at this.  The table names are just table1, table2, table3.

I hope the attached images explain things a little better.

Anything you can offer would be great!  Even if it is go buy a book.

Thank you again.
 Example table1 example table1 exampletable3.JPG
0
 
LVL 20

Accepted Solution

by:
Mark Brady earned 500 total points
Comment Utility
Ok this code should do what you ask for. It takes each record from table one and reads table 2 then inserts a record in table 3 with data from both table 1 and table 2 like in your picture. I added a $WS_Price which is the wholesale price multiplied by the .30 you wanted. In your picture you have the result of that as 7 which is wrong. 10 * .3 can't be 7 but it may be there just for show so don't worry about it. Here's the code.


$sql = "SELECT `Product_ID` FROM table1";
$result = mysql_query($sql)or die(mysql_error());
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
$product_id = "{$row['Product_Id']}";
$sql1 = "SELECT `STOCK_CODE`,`ItemName`,`ProductDetails`,`WHOLESALE_PRICE`,`RETAIL PRICE` FROM table2 WHERE `STOCK_CODE` = '$product_id' LIMIT 1";
$result1 = mysql_query($sql1)or die(mysql_error());
while($row1 = mysql_fetch_array($result1, MYSQL_ASSOC))
{
$stock_code = "{$row1['STOCK_CODE']}";
$item_name = "{$row1['ItemName']}";
$product_details = "{$row1['ProductDetails']}";
$wholesale_price = "{$row1['WHOLESALE_PRICE']}";
$retail_price = "{$row1['RETAIL PRICE']}";
$WS_Price = ($wholesale_price * .3);
}
mysql_query("INSERT INTO table3 (Product_ID,Product_CodeSKU,Product_Name,Product_Description,Price,Sale_Price) VALUES ('$product_id','stock_code','$item_name','product_details','$WS_Price','$retail_price')")or die(mysql_error());
}
0
 

Author Comment

by:Deacil
Comment Utility
Sorry for the delay in response.  I have been away and hope to be able to check out the code early next week.

Thank you again for all your help!  The code looks like exactly what I needed.
0
 
LVL 20

Expert Comment

by:Mark Brady
Comment Utility
Thanks for the points. Yes it should work just fine but let me know if you have any problems with it or don't get the desired results and I will adjust the code for you. Good luck.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
This video discusses moving either the default database or any database to a new volume.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now