8 Replies - 934 Views - Last Post: 20 April 2008 - 07:45 AM Rate Topic: -----

#1 didgy58  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 246
  • Joined: 23-October 07

another php database question

Post icon  Posted 19 April 2008 - 02:08 PM

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

Is This A Good Question/Topic? 0
  • +

Replies To: another php database question

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4306
  • View blog
  • Posts: 12,079
  • Joined: 18-April 07

Re: another php database question

Posted 19 April 2008 - 02:21 PM

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. :)
Was This Post Helpful? 0
  • +
  • -

#3 didgy58  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 246
  • Joined: 23-October 07

Re: another php database question

Posted 19 April 2008 - 02:29 PM

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
Was This Post Helpful? 0
  • +
  • -

#4 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4306
  • View blog
  • Posts: 12,079
  • Joined: 18-April 07

Re: another php database question

Posted 19 April 2008 - 02:34 PM

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

Was This Post Helpful? 0
  • +
  • -

#5 didgy58  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 246
  • Joined: 23-October 07

Re: another php database question

Posted 19 April 2008 - 03:02 PM

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??
Was This Post Helpful? 0
  • +
  • -

#6 didgy58  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 246
  • Joined: 23-October 07

Re: another php database question

Posted 19 April 2008 - 03:35 PM

well still no joy

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

Was This Post Helpful? 0
  • +
  • -

#7 didgy58  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 246
  • Joined: 23-October 07

Re: another php database question

Posted 19 April 2008 - 03:57 PM

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
Was This Post Helpful? 0
  • +
  • -

#8 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4306
  • View blog
  • Posts: 12,079
  • Joined: 18-April 07

Re: another php database question

Posted 19 April 2008 - 04:52 PM

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. :^:
Was This Post Helpful? 0
  • +
  • -

#9 didgy58  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 246
  • Joined: 23-October 07

Re: another php database question

Posted 20 April 2008 - 07:45 AM

Thanks very much for the help martyr2.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1