Setting up a Testing Server

[ebanbury]

Many apologies if this is the wrong forum. I looked through all the different forums and wasn't sure. Please let me know and I'll transfer this to whichever the appropriate forum is.

I use PHP/MySQL for my website and have been using DW CS5 for Site Management/Connections.
I've been trying to set up a Testing Server on localhost using xampp. It seems to have all downloaded successfully and I can access my localhost PHPMyADMIN.

I have set up my testing server in the Site Management of my existing website, so that when I F12 it should just show in my localhost site.

I get the error message below:

Warning: mysql_pconnect() [function.mysql-pconnect]: Access denied for user 'dreamsin'@'localhost' (using password: YES) in C:\xampp\htdocs\dreamsin_testing\Connections\dreamsin_localhost.php on line 9

Fatal error: Access denied for user 'dreamsin'@'localhost' (using password: YES) in C:\xampp\htdocs\dreamsin_testing\Connections\dreamsin_localhost.php on line 9

I know why I get the error message. It is of course because my webpages refer to a Connections file, which contains the login information for an outside production server.

What I'm really hoping for is some information about how I can use my localhost test server best? i.e how should I be setting this up so that I can access any page without changing any of the connections info for the production site?

Many thanks for the help. I'm very grateful for any help in this.

[pritaeas]

You'll have to add a database with the same name, and a user/password with identical values to MySQL (with granted rights on that database).

[cereal]

As Pritaeas suggested here is an example code for MySQL shell:

mysql> select password('test');
+-------------------------------------------+
| password('test')                          |
+-------------------------------------------+
| *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> create user 'dreamsin'@'localhost' identified by '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';

mysql> grant select, insert, update, delete, create, create view, drop, alter on db_name.* to dreamsin@localhost;

here you setup a single user for reading, inserting, creating tables inside a database, but if you can, create separated users: readonly (only select), application (select, insert, update, delete) and administration (previous privileges plus create, create view, drop and alter) it will be better.

+ http://dev.mysql.com/doc/refman/5.5/en/grant.html
+ http://dev.mysql.com/doc/refman/5.5/en/create-user.html

bye

[pzuurveen]

I sometimes have used 2 config-files

if ($_SERVER['SERVER_ADDR'] == 'ip localhost here')
	{
	// testing
	require_once('testconfig.php'); 
	error_reporting(E_ALL);
	}
else	{
	// productie locatie
	require_once('dbconfig.php'); 
	error_reporting(0);
	}

I have a similar setup to Pz:

$server = $_SERVER['SERVER_ADDR']; //I use a different check to this, but you get the idea
switch($server){
  case ######:
     //local server - place connection details here
     break;
  case ######:
     //sandbox server - place connection details here 
     break;
  case ######:
     //remote server - place connection details here 
     break;
}

This file is kept above the public root - NEVER within/below public root e.g. includes folder.

[ebanbury]

Hi I've tried to set up a separate connection to my local PHPMyAdmin
In my DW 'MySQL Connection Setup' it is asking me the usual info i.e. username and password.
When I go to my local PHPMyAdmin in http://localhost/xampp/ to try and set up a password under 'Change Password' I get this error:

Error

SQL query:

SETPASSWORD= PASSWORD('***') 

MySQL said: 
#1133 - Can't find any matching row in the user table

I know this doens't really have anything to do with the username, but not sure how to set this up locally either. It should be the name attached to the .....@localhost but I can't tell if that is an issue or not as I don't have a password to put in.

How can I get a correct username/password in order to set up the Connection to the Testing Server in DW?

Many thanks for all the help and advice :-)

[ebanbury]

Hi All

OK I've managed to create the mysql connection to the testing server.

I've been trying the if, else format first in the top of my php page:

I was assuming it would be this:

if ($_SERVER['SERVER_ADDR'] == '127.0.0.1')

However I still get the error message:

Warning: mysql_pconnect() [function.mysql-pconnect]: Access denied for user 'dreamsin'@'localhost' (using password: YES) in C:\xampp\htdocs\dreamsin\Connections\dreamsin_localhost.php on line 9

In other words it is still automatically trying to connect to the remote server.

[ebanbury]

Hi...as you can see I am hopefully talking myself into an answer here:

Well the next piece of the puzzle is that if I just put this address into my browser:

http://localhost/dreamsin/

THen the usual error message appears :

Warning: require_once(Connections/dreamsin_localhost.php)

However when I created a totally new site and called it TESTIMAGE for example and put this in the browser:

http://localhost/TESTIMAGE/

Then the few files and directories I created in their show up.

It all comes back to this Connections issue. But why wouldn't I even be able to access the directory without having this error?

[ebanbury]

Hi after a bit more testing it appears everything has been set up as it should be. The only problem remaining is to set the code in each page so that when I F12 or click on 'Live View' it uses the testing server connection.php otherwise the remote server connection.php.

I've tried the following as above:

if ($_SERVER['SERVER_ADDR'] == 'ip localhost here')	{	// testing	require_once('testconfig.php'); 	error_reporting(E_ALL);	}else	{	// productie locatie	require_once('dbconfig.php'); 	error_reporting(0);	}if ($_SERVER['SERVER_ADDR'] == 'ip localhost here')
	{
	// testing
	require_once('testconfig.php'); 
	error_reporting(E_ALL);
	}
else	{
	// productie locatie
	require_once('dbconfig.php'); 
	error_reporting(0);
	}

and put in the 1st line as follows:

if ($_SERVER['SERVER_ADDR'] == '127.0.0.1')

But when I F12 or Live View - it always just tries to use the remote server config.

Do I need to add anything else to the IP address?

Many thanks

[AndreRet]

I have been using dw cs5 for the past year and a half without any problems. I remember having the same issues as you are having now, so, I'll start at the beginning and run through it with you to check each step and solve your problem. :)

First, have a look at my tutorial here. If you have followed the same steps, let me know, we can then start by setting up your servers in dreamweaver.

[ebanbury]

Hi Great. I'll go through the tutorial and get back to you.

[ebanbury]

Hi I've read through Part1. I can't find the zip file MyFirstDatabase.sql?
I actually think all my xampp and phpmyadmin is up and running successfully. I can also connect my DW pages to the testing server and see them when I click F12. But only if I set the require_once (testing_server.php) connections file in the page and comment out the connection to the remote_server.php.
In other words I think it is my coding of not being able to get the page to go to the testing_server connection if I F12...else....got to the remote_server connection.?

I'd really like to finish going through your tuturials though. Please could you let me know where the zip file is?

Many thanks for the help. I'm loving the process :-)

if(strpos($_SERVER['REMOTE_ADDR'],'127.0.0.1') === false){
	$use_server = 'remote'; 	
}else{
	$use_server = 'local'; 
}

//then use a switch{} to connect to the right server checking on $use_server

I tried in it Live and Browser (F12) - worked for me

[ebanbury]

Hi Thanks for the help!
If I use switch checking on $use_server

switch($use_server){

it tries to login to the dreamsin_localhost.php and of course fails.

Warning: mysql_pconnect() [function.mysql-pconnect]: Access denied for user 'dreamsin'@'localhost' (using password: YES) in C:\xampp\htdocs\DSP Testing\Connections\dreamsin_localhost.php on line 9

However if I use the following code:

if(strpos($_SERVER['REMOTE_ADDR'],'127.0.0.1') === false){
	$use_server = 'remote'; 	
	}else{
	$use_server = 'local'; 
	}
 
	switch($server){
		case 'remote':
		require_once('Connections/dreamsin_localhost.php');
		break;
		case 'local':
		require_once('Connections/Banbury01_localhost.php');
		break;
	}

strangely enough it works even though $server isn't defined! But I just get the to be expected notice:

Notice: Undefined variable: server in C:\xampp\htdocs\DSP Testing\terms&conditions.php on line 14

Why doesn't it like me using $use_server?

Thank you so much for the input...

[ebanbury]

OK All working! Thanks so much for all the help. Really appreciate it!

Final Code:

if(strpos($_SERVER['REMOTE_ADDR'],'127.0.0.1') === false){
	$use_server = 'remote'; 	
	}else{
	$use_server = 'local'; 
	}
 
	switch($use_server){
		case 1:
		require_once('Connections/dreamsin_localhost.php');
		break;
		case 2:
		require_once('Connections/Banbury01_localhost.php');
		break;
	}

[ebanbury]

However it seems to only be working for some pages....

For some of the pages, the html parts will show and then I will get error messages like:

Fatal error: Access denied for user 'dreamsin'@'localhost' (using password: YES) in C:\xampp\htdocs\DSP Testing\Connections\dreamsin_localhost.php on line 9

or

Object not found!

The requested URL was not found on this server. If you entered the URL manually please check your spelling and try again.

When I can see the file in the htdocs directory

??

[ebanbury]

OK Now it is all solved.
Just realised I have a bit of code-clearing to do :-)
thanks for the help

[ebanbury]

Hi Guys

Well the testing server seems to be working (albeit I have some work to do with mysql_select_db() connections for getting the pages to work where they require to be connected to a mysql db. i.e. Those pages still don't work.

A totally new side issue has sprung up though.
My connection to the remote server has broken, but only within DW and with mysql db connections and any recordset type stuff.

The password and username have never been changed
I can still login to cpanel & phpmyadmin on the remote server (it is all the same username and password)
The site setup still connects successfully and I can still ftp files across.
The error is:

MySQL Error#: 1045
Access denied for user 'dreamsin'@'localhost' (using password: YES)

This is my production server on a totally different outside server and I haven't changed any setting for this connection. This is a complete mystery to me and I'm feeling a bit out of my depth.
I have read up a pile of articles and conversations on google, which talk a lot about changing the 'root' password and having username and passwords identical on both the remote and local sites, but I thought I'd pass it by you before changing anything so high level.
The only username I have as root is currently my local testing setup via xampp.

Maybe the 2 connections are now getting a bit confused?
Both servers are called 'localhost' but that is the only part of the connection that is the same.

Any help would be great as I can't even continue developing now :-(

Many thanks liz

[ebanbury]

The other problem with pages which require connection to a mysql db on my local pc is this error message:

Object not found!

The requested URL was not found on this server. If you entered the URL manually please check your spelling and try again.

I can see the file in the browser by typing in the directory of where it is residing.
Maybe something to do with .htaccess file?
Maybe I do need to change root user? But dont' want to do anything here until above issue is fixed .....

I thought I had it all sorted about 6 hours ago :-)

The solution you posted (case 1: ... case 2:...) doesn't make sense - it's probably searching for a boolean (0 = false, 1 = true). So if $use_server is set at all, it'll probably return the case 1 option (which equates to true).

As for all your other problems - you've totally lost me. Try to connect problems to specific bits of code if possible.

See what you get when you load page:

if(strpos($_SERVER['REMOTE_ADDR'],'127.0.0.1') === false){
	$use_server = 'remote'; 	
}else{
	$use_server = 'local'; 
}
 
switch($use_server){
   case 'remote':
      require_once('Connections/dreamsin_localhost.php');
      echo 'remote conenction';
      break;
   case 'local':
      require_once('Connections/Banbury01_localhost.php');
      echo 'local conenction';
      break;
   default:
      echo 'default server selected'; 
      break;
}

If you find that local works when it should, but remote doesn't, you could try:

switch($use_server){
   case 'local':
      require_once('Connections/Banbury01_localhost.php');
      echo 'local conenction';
      break;
   default:
      require_once('Connections/dreamsin_localhost.php');
      echo 'remote conenction';
      break;
}

or vice-versa

[ebanbury]

Hi Many thanks for the help. I did have 'remote' and 'local' put in before but nothing happened. I'll try again.

As for the connection problems sorry about it being a bit all over the place, I am really struggling to explain it to myself.

Basically I set up the 2 x connections in DW MySQL DB Connect:
1 x dreamsin_localhost
1 x Banbury01_localhost

The dreamsin_localhost has always been the connection which goes to the remote server for nearly a year now. In my Site Manager set up it goes to www.dreamsingaporeproperty.com/public_html.

The Banbury01_localhost I just set up to go to the new testing server on my pc. Currently it has a different username and password to the remote server. Both go to localhost.

I successfully set up the testing server and added the server into the Site Manager. When I clicked 'Live View' or F12 the pages showed in my http://localhost URL.

However when I then tried to connect to the dreamsin_localhost connection it then failed with the above error:

MySQL Error#: 1045
Access denied for user 'dreamsin'@'localhost' (using password: YES)

If I take out the local server setup from the Site Manager and remove /public_html/ from the web URL the connection to the remote site then works, but the connection to the Banbury01_localhost then gets the above error message. If I keep the set up as I always had it with the URL www.dreamsingaporeproperty.com/public_html/ - then another error message about _mmServerScripts.

I'm clearly missing a piece in the whole set up but no idea what.
It seems I can't have both servers working so far.
I didn't set up a user in the mysql table of the local server with the same username and password as in my remote server yet either.

I know this has nothing to do with PHP now but would be really grateful for any insights...

Many thanks again! Liz

OK, now I get it. Yes it will. The reason for this is that the connection details for the remote site is host = localhost. So it will look on your machine for the DB. It obviously won't find it as it only exists on the remote machine. For your local php site, the connection file for the remote DB server MUST be able to reference the host as a url. Have a look at your cPanel (if you have one) to allow remote access. You should then get an IP address - use this instead of 'localhost'.

Doh! I feel like a prize idiot now.

[cereal]

Don't worry Ardav, you're not alone, I understood the same of you.. ;p

[ebanbury]

Hi
When I put in the above switch code all I get is this message:

Object not found!

The requested URL was not found on this server. If you entered the URL manually please check your spelling and try again.

?

[ebanbury]

Hi Guys sorry for the confusion. Yep it is kind of 2 issues going on but i feel will all be connected to the same solution.

Regarding the localhost host name being the same in both the testing server setup and remote server setup. I have asked my service provider for an IP address to use instead of localhost, but they won't give me one.
I have tried to use 127.0.0.1 in my testing server setup and this works, but obviously doesn't solve the problem of not being able to connect to the remote server.

Thank you sooooo much for being so patient with me. I feel this should not be complicated and kinda wish I never thought to have a proper testing server as everything was working great before :-)

Let me try and put the issues down briefly. (I have screen shots of all the setups but dont' think I can add here)
1. Local Connection is working great if I F12 or Live View - but only for html pages. For pages requiring connection to mysql.
2. I can access my local mysql tables and create recordsets. (obviously I see nothing when I ftp to the remote server)
2. I can ftp my pages to the remote server - the site setup is working fine.
3. I can't make a connection within the DW Database MySQL Connect setup - to the remote server only. Therefore I can't dynamically create or edit my pages which require access to the remote servers mysql tables.

There is a mismatch somewhere between:
Connection setups
Local and Remote mysql database access.

When I use your '$use_server' switch code - this works fine in html pages but still having issues with pages needing to use mysql - this will probably be because of above connection problems.

Maybe I should just have the remote and testing sites on separate Site Manager setups? But then I have 2 maintain 2 separate sets of my website - which kind of defeats the object.

Many many thanks for all the help. I am really appreciating it!!

[ebanbury]

Hello
OK I have both my connections within the DW Database MySQL Connect setup Working!! yippeee.

Now my only remaining issue is to work out how to F12 or Live View any pages which have connections to mysql databases. I know why they don't. It is because they were originally created from only the remote source. So they all refer to dreamsin_localhost.

I have done the following in my local testing phpmyadmin:
1. Set up the database with the same name as in the remote phpmyadmin
2. Set up all the same tables and imported the required data.
3. Set up a user 'dreamsin' and the same password as in the remote - but currently I'm not using this as my local connection.

Here is an example page:

Inside I now set my connection to :
<?php require_once('myaccess/dbc_switch.php');

dbc_switch.php - using $use_server switch case you helped me with.

if(strpos($_SERVER['REMOTE_ADDR'],'127.0.0.1') === false){
	$use_server = 'remote'; 	
}else{
	$use_server = 'local'; 
}
 
switch($use_server){
   case 'remote':
      require_once('Connections/dreamsin_localhost.php');
      echo 'remote connection';
      break;
   case 'local':
      require_once('Connections/Banbury01_localhost.php');
      echo 'local connection';
      break;
   default:
      echo 'default server selected'; 
      break;
}

The problem is changing my code elsewhere in the pages so that when it is doing the recordsets or any other mysql_query() it uses this dbc_switch.php

For Example - further down in renew_property.php - I have the piece below -so I've tried to set up a variable to put my dbc_switch.php in.
But when it comes to the mysql_query() - it doesn't know what $dreamsin_localhost is.

$connect = mysql_connect('myaccess/dbc_switch.php');
mysql_select_db($connect);
$query_rs_viewprop = sprintf("SELECT DEV_property_details.prop_id, DEV_property_details.add_date, DEV_property_details.prop_cat, DEV_property_details.prop_renew, DEV_property_details.prop_type, DEV_property_details.prop_saletype, DEV_property_details.screenpath, DEV_property_details.image_main FROM DEV_property_details WHERE add_date < DATE_ADD(current_date, INTERVAL -45 DAY) AND prop_id = %s", GetSQLValueString($colname_rs_viewprop, "int"));
$rs_viewprop = mysql_query($query_rs_viewprop, $dreamsin_localhost) or die(mysql_error());
$row_rs_viewprop = mysql_fetch_assoc($rs_viewprop);
$totalRows_rs_viewprop = mysql_num_rows($rs_viewprop);

$prop_id = $row_rs_viewprop['prop_id'];

I'm also not sure if my local mysql user & pw should be the same as the remote one????

change

mysql_query($query_rs_viewprop, $dreamsin_localhost)

to

mysql_query($query_rs_viewprop)

[ebanbury]

Hi Ardav
I finally have it all sorted.

I have changed the dbc_switch.php page so that my 2 different servers have the same information inside:

<?php
  // Open the $dbc connection
 

if(strpos($_SERVER['REMOTE_ADDR'],'127.0.0.1') === false){
	$use_server = 'remote';
    $hostname_dreamsin_localhost = "ip address";
    $database_dreamsin_localhost = "dreamsin_property";
    $username_dreamsin_localhost = "xxxxxxxx";
    $password_dreamsin_localhost = "xxxxxxxx";
}
else
{
	$use_server = 'local'; 
    $hostname_dreamsin_localhost = "127.0.0.1";
    $database_dreamsin_localhost = "dreamsin_property";
	$username_dreamsin_localhost = "xxxxxxxx";
	$password_dreamsin_localhost = "xxxxxxxx";
}

$dreamsin_localhost = mysql_pconnect($hostname_dreamsin_localhost, $username_dreamsin_localhost, $password_dreamsin_localhost) or trigger_error(mysql_error(),E_USER_ERROR);

It works fine now.

Thanks you so much for all the help. Really appreciated it.

Many thanks