[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MySQL Newbie Question

Posted on 2011-03-19
11
Medium Priority
?
408 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
ID: 35174682
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
ID: 35176924
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
ID: 35177183
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:Deacil
ID: 35177211
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
ID: 35177265
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
 

Author Comment

by:Deacil
ID: 35177373
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
ID: 35177470
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
ID: 35178023
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 2000 total points
ID: 35185787
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
ID: 35218338
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
ID: 35218728
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month20 days, 6 hours left to enroll

873 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