Welcome Guest, Not a member yet? Register   Sign In
Simple Method to Set Up a Databse on a Server
#1

[eluser]crispinatari[/eluser]
Alright so i'm having a little trouble understanding how i set up my SQL dump to work on a proper server, i can get it working using my localhost easily but thats all automated using directory (/bin/data/database/)...which uses (.frm), (.MYD), (.MYI) as opposed to an sql Dump text file. these are all easily configured using WAMP and a program like sqlYog.

on a school server i log in and place my code igniter folder into the public directory so it can be viewed and the SQL dump also lives in that directory ....

also i have 5 tables in the one SQL dump, should they be seperate instead??

the way i've been accessing my database on the localhost is like this:

controller
Code:
function index()
    {
    
        $data['res']=$this->empsmodel->get_all_emps();
          $this->load->view('empslistview', $data);

model
Code:
function get_all_emps() {
     // $query = $this->db->get('tblemployee');
       return $query->result();
    }

database:

Code:
$db['default']['hostname'] = "localhost";
$db['default']['username'] = "mattv";
$db['default']['password'] = "kdZRhg";
$db['default']['database'] = "mattv";
$db['default']['dbdriver'] = "mysql";
$db['default']['dbprefix'] = "";
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = "";
$db['default']['char_set'] = "InnoDB";
$db['default']['dbcollat'] = "utf8_general_ci";


so i'm assuming that the sql file has to be accessed differently once on the school server, so i have to use something like:

Code:
$conn = mysqli_connect($host, $user, $password) or die("Cannot make connection");
mysqli_select_db($conn, $database);
$sql='select * from emps';
//loop through the result set
$result = mysqli_query($conn,$sql);
while ($data = $result->fetch_assoc()) {

yeh so i'm really confused because there seems to be many ways to go about doing this as opposed to a simple direct way of accessing an sql file on a server...

yes im confused, any tips? i'm very new to sql and php as well.

sql file is this:

Code:
DROP TABLE IF EXISTS `tblcourse`;

CREATE TABLE `tblcourse` (
  `courseCode` varchar(20) NOT NULL,
  `courseName` varchar(250) NOT NULL default '',
  `courseID` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`courseCode`),
  UNIQUE KEY `courseID` (`courseID`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;

/*Data for the table `tblcourse` */

insert  into `tblcourse`(`courseCode`,`courseName`,`courseID`) values ('70-228','Ins Cnfg, Admn Mcrsft SQL Servr 2000 Entpris Edtn',2),('70-229','Dsgn,Implmnt Dtbses,SQL Srvr 2000 Ent Edtn',3),('70-290','Manging, Maintn a Micrsft Win Serv 03 Envrnmnt',4),('70-528','Mcrsft .NET Frame 2.0 - Web-Clnt Dvlpmnt',5),('70-529','Mcrsft .NET Frame 2.0 - Dstrbtd App Dvlpmnt',6),('CX-310-019','SunCrtfd AsocJava Pltfrm',7),('CX-310-027','SunCrtfd Devlp Java',8),('CX-310-065','SunCrtfd Prgrmr Java ,Edtn 6',9),('CX-310-083','SunCrtfd Web Cmpnnt Dvlpr Java Pltfrm Entrprse Edtn 5',10),('CX-310-252','SunCrtfd Dvlpr Stndrd Edtn: Asgnmnt ',11),('FX-1007','Intrdctn Accountng post GST',12),('FX-1178','Using MYOB in 2008 for the beginner',13),('FX-1179','MYOB fundamentals for Windows Vista',14),('FX-2000','Introduction to Quicken 2008 ',15),('FX-2001','Interm Quicken 2008',16),('SWIN5770','Intrdctn Marktng for the busns envrnmnt',17),('SWIN5771','Interm Markting  business envrnmnt',18),('SWIN5772  ','Advc Marktng business envrnmnt',19);

/*Table structure for table `tbldepartment` */

DROP TABLE IF EXISTS `tbldepartment`;

CREATE TABLE `tbldepartment` (
  `departmentID` int(4) NOT NULL,
  `departmentName` varchar(80) NOT NULL,
  `depLevel` varchar(20) NOT NULL,
  PRIMARY KEY  (`departmentID`)
) ENGINE=InnoDB AUTO_INCREMENT=1235 DEFAULT CHARSET=latin1;

/*Data for the table `tbldepartment` */

insert  into `tbldepartment`(`departmentID`,`departmentName`,`depLevel`) values (1234,'Accounting','Level 2'),(5551,'Marketing','Level 4'),(7070,'Training','Level 3'),(8765,'IT','Level 1');

/*Table structure for table `tblemployee` */

DROP TABLE IF EXISTS `tblemployee`;

CREATE TABLE `tblemployee` (
  `employeeID` varchar(6) NOT NULL,
  `departmentID` int(4) NOT NULL,
  `first_name` varchar(20) NOT NULL,
  `last_name` varchar(20) NOT NULL,
  PRIMARY KEY  (`employeeID`),
  KEY `FK_employeeID_departmentID` (`departmentID`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

/*Data for the table `tblemployee` */

insert  into `tblemployee`(`employeeID`,`departmentID`,`first_name`,`last_name`) values ('davlis',8765,'David','Lister'),('edwelg',5551,'Edward','Elgar'),('forpre',8765,'Ford','Prefect'),('frahol',7070,'Frank','Hollister'),('krikoc',7070,'Kristine','Kochanski'),('olapet',7070,'Olaf','Peterson'),('projel',8765,'Prostetnic','Jeltz'),('samvim',7070,'Sam','Vimes');

/*Table structure for table `tblresult` */
#2

[eluser]kgill[/eluser]
You're misusing some terms so maybe I'm reading things wrong but it looks like you're confused on the concept of a dump file, your database isn't accessed from the dump file, the database only resides in the MySQL server. There is no setting up involved in a dump file, all a dump file is - is the statements you'd have to type from a mysql command line to create a copy of the dumped database somewhere else. To use it you execute the script in mysql which then runs all those commands in the file and you end up with the tables and data re-created. I'm guessing you have command line access to the school server instead of a GUI and that's what's throwing you off, you need to log into your account on the server, from the same directory as your dump file you then log in to mysql, once you're sitting at the mysql command line type: source yourdumpfilename.sql;

That will run the script, after that's done running quit out of mysql and delete the dump file (no sense in keeping it once the data's been loaded into the DB server). Things to note here would be that your school server is probably shared with other classmates so you need to find out how and where you're supposed to put things because if you all share one DB and someone has the same table names as you, you'll wipe out their data and replace it with yours.

- K




Theme © iAndrew 2016 - Forum software by © MyBB