?
Solved

copy a table from 2 databases on the same server update a table with data from another, each table in different databases.

Posted on 2011-09-09
8
Medium Priority
?
465 Views
Last Modified: 2013-12-13
Dear Experts,

I have the Database A with the tables a1 , a2 , a3   and the Database B with the tables b1,b2, b3
in the same server.  I need update the information of a3 with the information of the b3.  (update or store a new data in b3 for a3) .   I use Joomla 1.5.23 and Jumi component in order to write PHP code on Joomla.
My code is included and it not works, =(  

any idea? thanks in advanced!.
cheers,
<?php
defined('_JEXEC') OR defined('_VALID_MOS') OR die( "Direct Access Is Not Allowed" );

$link = mysql_connect('site1.com', 'root', 'sqladminx');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';

$db_selected_joomla = mysql_select_db('A', $link);
$db_selected_sia = mysql_select_db('B', $link);

SELECT *  INTO B.sia_users FROM A.jos_users

mysql_close($link);
?>

Open in new window

0
Comment
Question by:viertelstrasse
[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
8 Comments
 
LVL 13

Accepted Solution

by:
F Igor earned 2000 total points
ID: 36515271
Change

SELECT *  INTO B.sia_users FROM A.jos_users

Open in new window



to


$result=mysql_query("SELECT *  INTO B.sia_users FROM A.jos_users",$link);

Open in new window


Putting the SELECT statement directly in a PHP code is a syntax error and can't be executed.

And you don't need to call to mysql_select_db twice, only for one database.
All references to the selected database doesn't need the DATABASE.table syntax, and only the external databases need it.


$db_selected_joomla = mysql_select_db('A', $link);
$db_selected_sia = mysql_select_db('B', $link);

Open in new window


to

$db_selected_joomla = mysql_select_db('A', $link);

Open in new window


Thinking the A database is the main database


0
 

Author Comment

by:viertelstrasse
ID: 36515414
thanks!  Fraigor,

the suggestion was used,  but the table table sia.users  don't have any information ! after the code runs, I should see the information of the joomla.users showed by sia.users.

what can  I do? thanks in advanced.
0
 
LVL 1

Expert Comment

by:Aviv23
ID: 36516251
Hi there,

If you include your code to joomla you can use JFactory::getDBO()->Execute($query);

as for the query, when you change the database to B you lose connection to database A,
but you don't really need a connection to a database here.

the code:
<?php
defined('_JEXEC') OR defined('_VALID_MOS') OR die( "Direct Access Is Not Allowed" );

$link = mysql_connect('site1.com', 'root', 'sqladminx');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
//$db_selected_sia = mysql_select_db('B', $link);

mysql_query("INSERT INTO B.sia_users SELECT * FROM  A.jos_users");
mysql_close($link);
?>

Open in new window

0
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.

 
LVL 111

Expert Comment

by:Ray Paseur
ID: 36516339
You probably want to enable some kind of error reporting or error logging.  The code posted with the original question will throw a parse error on line 13.  Joomla, by default, may suppress these messages so you will have no idea what went wrong.  Maybe you can add something like this to the top of your script:

ini_set('display_errors', TRUE);
error_reporting(E_ALL);

In MySQL syntax, you do not have to select any data base.  You can just write the information into your query something like this:

SELECT column FROM database.table WHERE...

A really good book that will help you get a foundation in PHP and MySQL is available here.   Very readable with great examples.
http://www.sitepoint.com/books/phpmysql4/

Best of luck with it, ~Ray
0
 

Author Comment

by:viertelstrasse
ID: 36527151
Thanks aviv23  , but the JFactory::getDBO()->Execute($query)  is the same  that  $result=mysql_query($querey)  both do not work!!  the table  jos_users  (from database A)  isn't  copied to table sia_users (from database B).

Ray_paseur,  also thanks I use your suggestion and I dont have any error msg  with ni_set('display_errors', TRUE);
error_reporting(E_ALL);
 and I want all the information of the table jos_user to sia_users , the structure is the same for boths.
mysql_query("INSERT INTO B.sia_users SELECT * FROM  A.jos_users");  do not work!

I don´t know why not !....what can I do? what is wrong?

cheers,

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 36529081
Regarding this: I dont have any error msg

That suggests that you may not be running the code at all.  Maybe the updates went into the wrong directory or something like that.  I installed the exact script posted with the question on my server here.  You can see the output.
http://www.laprbass.com/RAY_temp_viertelstrasse.php

At this point I would suggest hiring a programmer who has a depth of experience in PHP, MySQL and Joomla.  There are complex interactions between these parts of the system, and when the question is "what is wrong?" the answer is usually only found through exhaustive testing.  If you find a programmer with a lot of experience in those things, you may have found someone who has experienced this sort of thing before, gone through the exhaustive testing and knows the answer already.  I make this recommendation because I believe that time is money, and the fastest solution is the best.

If you do not want to hire a programmer, get the SitePoint book and start there.  You will need a strong foundation in the underlying technologies before you make educated guesses about what to try next.

Best of luck with it, ~Ray
0
 

Author Comment

by:viertelstrasse
ID: 36529671
Thanks Ray Paseur,

Well,  of course that "may not be running the code at all" ,  and something else is wrong .   Some tests, ej. Display the information of the tables in the form, etc. was carried out,  in order to test connection of tables ..etc.      Your suggestion that I Need a programmer is not accept, I NEED SOLVE THIS PROBLEM,   Im now reading the SitePoint Book and I Need found the solutions, also for this reason  I have a subscription in EE in order to Know "tips for the way to follow" .
Maybe the mySQL sentence is not correct,  maybe I Need use MySQLi Extension not MYSQL ....      I need found the solution, solve the problem.

cheers,
0
 
LVL 2

Expert Comment

by:eZov
ID: 36935063
This is just the issue of SQL command:

For insert new rows from table B into A:
SELECT *  INTO B.sia_users FROM A.jos_users WHERE B.pr_key_field not in (SELECT pr_key_field from A.sia_users)

pr_key_field is primary key filed of tables.
This will insert just new rows from one table to other. I suppose that db schemes of these two tables are the same. If not, you just need to list field in select command.
0

Featured Post

WordPress Tutorial 1: Installation & Setup

WordPress is a very popular option for running your web site and can be used to get your content online quickly for the world to see. This guide will walk you through installing the WordPress server software and the initial setup process.

Question has a verified solution.

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

This article discusses four methods for overlaying images in a container on a web page
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

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