[didgy58]

ok ive made a script now that i can copy information from my one table to another table this is what i would like to do, and then ive given the admin the facility to view the tables on the database so they can select and view the info in each one, the problem is the database im using has loads of tables that are relevant to things that im doing elsewhere so i wouldnt really want them to see all the tables only ones that are relevant to the university registration system.
so i asked my host streamline to setup another database as i dont have the privilege under phpmyadmin,,, sucks i know.. so they have done i have this new database thing is the username and password are different to my original connection, the server is still the same. so how would i go about this?? would i need to ask them to change the username and password to the same as my original one and move the new database to the same place, or can i make a multiple connection in php and copy information from table 1 in database 1 to table 1 in database 2, even thought the connection parameters are different (only username and password) not really sure if i can do this.

or should i maybe run a sql statement that copies that table to a text file and then close that connection run the new connection and then run the text file again to create the table and insert into it all the information needed??? has anybody every tried doing this, im not sure if im in the right area but this doesnt seem like a total database question to me.

ive tried the following

CREATE TABLE IF NOT EXISTS $datbase.$newtable SELECT * FROM $database1.userlogin

where $database and $database 1 hold the database names.

i get CREATE COMMAND denied, so im guessing as they wont let me create tables??

is there anyway around this one guys and gals??

thanks
DAN

[Martyr2]

Well you can certainly create two connections, one to one database and another to the other one. Then pull out the records of one table and put it into the other. Now if your host won't even let you create your own databases, they may not be letting you create your own tables either. You will have to check with them as to your general permissions for your database.

But in the event that they are very restrictive, you can have them create the necessary tables on the second database and then through PHP connect to both databases and move records in between.

If this is MySQL you are talking about you could use the Show Grants SQL statement to see what access you do have. To do this, check out the following page for the SQL query syntax... just have PHP issue this query and it should tell you if you have access to create tables and such.

MySQL 6.0 Reference Manual - SHOW GRANTS

Hope it helps.

[didgy58]

sorry i forgot to say that, its a mysql database and i do have permissions to create tables as i have just tested that, so i can open up one database connection to my one database, and then another to the other database. how would i go about changing the information would it just be as simple as the line i did above?? as both connections are open??

thanks for your help martyr2

[Martyr2]

Well you know how to setup your one connection to the database right with mysql_connect() where you supply it with the server, username, and password and it returns a resource. You just do that twice and store the resources to different variables.

Now you could do that or simply keep switching back and forth on one connection using mysql_select_db. The choice is yours. Check out both mysql_connect and mysql_select_db and I am sure you can get what you have to do.

Be sure to read the user comments for mysql_select_db... they have more info on multiple connections in case you have problems.

This post has been edited by Martyr2: 19 April 2008 - 02:37 PM

[didgy58]

i understand that and have made my connections, i cant just switch databases though as the connections have different usernames, but not different hosts and passwords. so thats where it gets confusion, im trying to copy from one connection to a completely different one, all this things ive seen so far show how to swap between databases using the same connection parameters mine are slightly different.

$dbc=mysql_connect('$host','$name','$password');
mysql_select_db('$database1');
$dbc2=mysql_connect('$host','$name','$password');
mysql_select_db('$database2');

im trying different things here just gettin errors as im trying to access the database with the wrong connection.. ive taken a look on php.net but no joy on how to get around this??

[didgy58]

well still no joy

This post has been edited by didgy58: 19 April 2008 - 03:38 PM

[didgy58]

WOOOP done it

$dbc=mysql_connect('$host','$user','$pass);
@mysql_select_db('$db1');

$res_db1=mysql_query("select * from userlogin",$dbc);
$dbc2=mysql_connect('$host',$user1','$pass1');
@mysql_select_db('$db2');

$new=date("F");
$year=date("Y");

$newtable=$year."_Backup";

$sql="CREATE TABLE IF NOT EXISTS $newtable  (
  `enrolno` varchar(10) NOT NULL,
  `coursid` varchar(10) NOT NULL,
  `modid` varchar(10) NOT NULL,
  `date` date NOT NULL,
  `lesson` varchar(10) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ";

$result=mysql_query($sql,$dbc2) or die (mysql_error());
if ($result){
$res_db1=mysql_query("select * from userlogin",$dbc);
while ($row=mysql_fetch_array($res_db1)){
$sql2="insert into $newtable (enrolno,coursid,modid,date,lesson) Values ('$row[enrolno]','$row[coursid]','$row[modid]','$row[date]','$row[lesson]')";
$result=mysql_query($sql2,$dbc2) or die (mysql_error());
}//end of while
if (mysql_affected_rows()>0){
$sql="TRUNCATE userlogin";
$result=mysql_query($sql,$dbc) or die(mysql_error());
$done='true';
}//end of if rows
else {
$done='false';
}//end of else

}//end ofif
else {
$done='false';
}

seems to work nicely i know its a little crude but it does the job for me, if anybody can see anyway i could improve it let me know
thanks

Dan

[Martyr2]

I knew if I sat on the question long enough you would get it working yourself. I have faith in your abilities even if you don't.

Good work.

[didgy58]

Thanks very much for the help martyr2.