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
458 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
8 Comments
 
LVL 13

Accepted Solution

by:
F Igor earned 500 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
 
LVL 108

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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 108

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

759 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

19 Experts available now in Live!

Get 1:1 Help Now