PDA

View Full Version : connectivity to MySQL database


rattlsnak
08-04-2008, 06:08 PM
I have built a site with a login .php page. I have exported/uploaded the MySQL database through MyAdminPhp to my host server.
The host server (earthlink) will not allow a connection to the database through DW, so I cannot use the applications window on the side panel to automatically find and connect to the database. Since I cant do that, I also cant set up the user authentication page behavior.
I can do it locally using WAMP and localhost and it works fine.
The host has provided a sample script to insert so i can connect when needed, but I dont know squat about SQL or php, so i have no idea where to insert it, or how, or if it will even work.

So, bottom line, I am unable to use the find database function on the application panel within DW, so how do I connect to my MySql Database on the server and use the login feature?

thanks!

davidj
08-04-2008, 06:21 PM
you have to do this manually i am afraid...

post the code which the hos has given you

omit any passwords and usernames and replace with astrix's

rattlsnak
08-04-2008, 06:56 PM
This is what they gave me as a sample. I do know to put my specific info into the variables, but what i dont get, is where to actually put this code? Do I attch it to my login page, or another page, or what?

Thanks!


<?php
// Connect to database
$hostname = "hostname";
$username = "usrname";
$password = "password";
$dbname = "databasename";
$usertable = "tablename";
$yourfield = "field1";
$yourfield2 = "field2";
mysql_connect($hostname, $username, $password) or DIE("Unable to connect to MySQL server $hostname");
print "Connected to MySQL server<br>";
$selected = mysql_select_db($dbname) or DIE("Could not select requested db $dbname");
print "Connected to database $dbname<br>";
$query = "SELECT * FROM $usertable";
$result = mysql_query($query) or DIE("Could not Execute Query on table $usertable");
if ($result) { print "Query successful on table $usertable<br><br>";
print "Your Query returned:<br>";
while ($row = mysql_fetch_array($result)) { print "".$row{$yourfield}." ".$row{$yourfield2}." <br>";
}
}
mysql_close;
?>

davidj
08-04-2008, 07:03 PM
does any of that make any sense to you at all??

rattlsnak
08-04-2008, 07:09 PM
Yes, i completey understand what the script is asking. I would remove the query section as well. What im missing is, where does that script go? Into my login page? or use some sort of .inc page? Or connected to the login post button, or? All of the other sites ive built, i was able to use the application feature in DW and it worked fine, but earthlink will not allow that for some reason. So im much better with the GUI side of things, and dont really know to much about the actual coding side of things.

davidj
08-04-2008, 07:13 PM
it replaces your current connection script or code in it

just compare both scripts and add the values to the variable which the host has supplied

rico1931
08-04-2008, 07:16 PM
Well if you want to keep it the same way DW does it they but the connections in a nice folder called /Connections/'filename.php' then add it to the top of your page as an include

Yes, i completey understand what the script is asking. I would remove the query section as well. What im missing is, where does that script go? Into my login page? or use some sort of .inc page? Or connected to the login post button, or?

rattlsnak
08-04-2008, 07:30 PM
ok, i see the folder in DW called connections, so i made a new php file with the above script in it. How do i include it to my login page? Does it need to be renamed .inc, or will .php work?
And next question would be, how do i set up user authetication using the database?

Geez, this is much easier with the gui tools in DW! ;)

rico1931
08-04-2008, 07:58 PM
ok, i see the folder in DW called connections, so i made a new php file with the above script in it. How do i include it to my login page? Does it need to be renamed .inc, or will .php work?
And next question would be, how do i set up user authetication using the database?

Geez, this is much easier with the gui tools in DW! ;)

then just add this to the top

<!-- #include virtual (or file)="'path to file in connections" -->


and then for the authentication to work you need to set up a record set that will check against the database to see if those users exist. Here you can use DWCS3's GUI to set up the authentication. Hope this helps! if not im sure david could help ya out better then I can!

rattlsnak
08-04-2008, 08:02 PM
i will try that here in a few moments, but the problem with the user auth, is that DW can only do that if it finds a database through the testing server, and mine cant! After talking with earthlink, the databases are kept on another server. i can setup a testing server just fine to the root where all my files are, but they will not let me connect to the DB server as a test server, which is how DW finds the DB's.

rico1931
08-04-2008, 08:08 PM
i will try that here in a few moments, but the problem with the user auth, is that DW can only do that if it finds a database through the testing server, and mine cant!

well this is just a shot in the dark but once you set up this connections and test it and it works then it shouldn't have a problem. Because if that is the case then you will have trouble getting all of your recordsets and info from the database... well not trouble but you are going to have to hand code all that stuff in and it personally doesn't sound like fun

davidj
08-04-2008, 08:09 PM
not sure that include will work in PHP

i use the standard include syntax for PHP


include("path/file.php");

rico1931
08-04-2008, 08:12 PM
not sure that include will work in PHP

i use the standard include syntax for PHP


include("path/file.php");


Thanks for the correction I forgot it was php and not asp!

davidj
08-04-2008, 08:14 PM
well not trouble but you are going to have to hand code all that stuff in and it personally doesn't sound like fun


your wrong

its a blast

its like everything else which you don't understand but if someone takes the time to teach you then you soon start to understand and then start enjoying it. It opens a whole new world of possibilities

fancy learning ?

rico1931
08-04-2008, 08:23 PM
haha ok a blast but I remember my class last semester in php and it was annoying typing all those statements into the sql manager to just see the table that you created! and the worst part was my teacher was mean and turned off the debugging that would show up on pages so we couldn't google anything and had to search by hand where we went wrong! thats why i'm not too enthusiastic about php but maybe i will learn from someone whos not mean!

your wrong

its a blast

its like everything else which you don't understand but if someone takes the time to teach you then you soon start to understand and then start enjoying it. It opens a whole new world of possibilities

fancy learning ?

davidj
08-04-2008, 08:29 PM
im not mean

hehehehe well actually i am but only to Bull Shiters and there isnt any of those in this thread so your ok

as long as you want to learn i will teach and you will enjoy

rattlsnak
08-04-2008, 08:30 PM
include("path/file.php");



ok so, 'connect' being what i named the file,


include("path/connect.php");


what exactly is the path? i mean all of my pages are on the same root folder on the server.

davidj
08-04-2008, 08:32 PM
do you understand paths and directory structures

path = the path to your connection file

rattlsnak
08-04-2008, 08:37 PM
yes, but all of my files are under the same directory and this connect file is under a folder in that directory called connections. So the path would be:

root directory/connections/connect.php ? or do i not need the root portion and just use "connections/connect.php"

davidj
08-04-2008, 08:38 PM
"connections/connect.php"

rico1931
08-04-2008, 08:39 PM
Connections/connect.php and DW puts a / before Connections but it shouldn't matter.


yes, but all of my files are under the same directory and this connect file is under a folder in that directory called connections. So the path would be:

root directory/connections/connect.php ? or do i not need the root portion and just use "connections/connect.php"

rico1931
08-04-2008, 08:40 PM
you beat me to it Davidj!

rattlsnak
08-04-2008, 09:09 PM
First of all THANK YOU so much for helping!

So, where in my login page do I put this code? Is there a specific spot?

here is my page: (minus a few titles and such)


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Admin Login</title>
<style type="text/css">
<!--
body,td,th {
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 18pt;
color: #4a639c;
}
body {
background-color: #c5cde0;
}
#Layer1 {
position:absolute;
left:128px;
top:235px;
width:140px;
height:42px;
z-index:1;
}
#Layer2 {
position:absolute;
left:227px;
top:101px;
width:145px;
height:338px;
z-index:1;
background-color: #00FFFF;
}
.style4 {
font-size: 24pt;
font-weight: bold;
}
h1,h2,h3,h4,h5,h6 {
font-weight: bold;
}
h1 {
font-size: 14px;
}
.style6 {font-size: 14px; }
.style7 {font-size: 16px; }
.style8 {font-family: "Times New Roman", Times, serif}
-->
</style>
<script type="text/JavaScript">
<!--
function MM_preloadImages() { //v3.0
var d=document; if(d.images){ if(!d.MM_p) d.MM_p=new Array();
var i,j=d.MM_p.length,a=MM_preloadImages.arguments; for(i=0; i<a.length; i++)
if (a[i].indexOf("#")!=0){ d.MM_p[j]=new Image; d.MM_p[j++].src=a[i];}}
}
function MM_findObj(n, d) { //v4.01
var p,i,x; if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) {
d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);}
if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<d.forms.length;i++) x=d.forms[i][n];
for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=MM_findObj(n,d.layers[i].document);
if(!x && d.getElementById) x=d.getElementById(n); return x;
}
function MM_nbGroup(event, grpName) { //v6.0
var i,img,nbArr,args=MM_nbGroup.arguments;
if (event == "init" && args.length > 2) {
if ((img = MM_findObj(args[2])) != null && !img.MM_init) {
img.MM_init = true; img.MM_up = args[3]; img.MM_dn = img.src;
if ((nbArr = document[grpName]) == null) nbArr = document[grpName] = new Array();
nbArr[nbArr.length] = img;
for (i=4; i < args.length-1; i+=2) if ((img = MM_findObj(args[i])) != null) {
if (!img.MM_up) img.MM_up = img.src;
img.src = img.MM_dn = args[i+1];
nbArr[nbArr.length] = img;
} }
} else if (event == "over") {
document.MM_nbOver = nbArr = new Array();
for (i=1; i < args.length-1; i+=3) if ((img = MM_findObj(args[i])) != null) {
if (!img.MM_up) img.MM_up = img.src;
img.src = (img.MM_dn && args[i+2]) ? args[i+2] : ((args[i+1])? args[i+1] : img.MM_up);
nbArr[nbArr.length] = img;
}
} else if (event == "out" ) {
for (i=0; i < document.MM_nbOver.length; i++) {
img = document.MM_nbOver[i]; img.src = (img.MM_dn) ? img.MM_dn : img.MM_up; }
} else if (event == "down") {
nbArr = document[grpName];
if (nbArr)
for (i=0; i < nbArr.length; i++) { img=nbArr[i]; img.src = img.MM_up; img.MM_dn = 0; }
document[grpName] = nbArr = new Array();
for (i=2; i < args.length-1; i+=2) if ((img = MM_findObj(args[i])) != null) {
if (!img.MM_up) img.MM_up = img.src;
img.src = img.MM_dn = (args[i+1])? args[i+1] : img.MM_up;
nbArr[nbArr.length] = img;
} }
}
//-->
</script>
</head>
<body onload="MM_preloadImages('homenava.png')">
<table width="858" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#4A639C">
<tr bordercolor="#4A639C">
<td width="142" height="112"><div align="left"><img src="cornerlogoa.png" width="140" height="110" /></div></td>
<td width="710"><div align="center" class="style4">AAHL</div></td>
</tr>
</table>
<table width="858" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#4A639C">
<tr bordercolor="#000060">
<td width="854" height="35" valign="top"><div align="center"><em>Admin Login </em></div></td>
</tr>
</table>
<table width="858" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#000060">
<tr valign="top" bordercolor="#4A639C">
<td width="160" height="495" align="center" valign="top" bordercolor="#4A639C"><div align="center">
<h1>&nbsp;</h1>
<p> <a href="index.html" target="_top" onclick="MM_nbGroup('down','group1','home','',1)" onmouseover="MM_nbGroup('over','home','homenava.png','',1)" onmouseout="MM_nbGroup('out')"><img src="homenav.png" alt="" name="home" width="160" height="40" border="0" id="home" onload="" /></a><br />
</p>
</div></td>
<td width="692" bordercolor="#4A639C"><p align="center" class="style6">&nbsp;</p>
<p align="center" class="style6">&nbsp;</p>
<p align="center" class="style6">&nbsp;</p>
<form id="form1" name="form1" method="POST">
<div align="center"><span class="style8">Username </span>
<input type="text" name="textfield" />
</div>
</form>
<form id="form2" name="form2" method="post" action="">
<div align="center"><span class="style8">Password </span>
<input type="password" name="textfield2" />
</div>
</form>
<form id="form3" name="form3" method="post" action="">
<div align="center">
<input name="Submit" type="submit" id="Submit" value="Login" />
</div>
</form> <p align="center" class="style7">&nbsp;</p>
</td>
</tr>
</table>
<p align="left">&nbsp;</p>
<p align="left">&nbsp;</p>
</body>


</html>

rico1931
08-04-2008, 09:12 PM
Put the include above everything else on your page so above where you have

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">


Then test to see if it worked or not. I found a good way to test connections is by trying to insert a record set to see if it gets all the data from the table

First of all THANK YOU so much for helping!

So, where in my login page do I put this code? Is there a specific spot?

here is my page: (minus a few titles and such)


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Admin Login</title>
<style type="text/css">
<!--
body,td,th {
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 18pt;
color: #4a639c;
}
body {
background-color: #c5cde0;
}
#Layer1 {
position:absolute;
left:128px;
top:235px;
width:140px;
height:42px;
z-index:1;
}
#Layer2 {
position:absolute;
left:227px;
top:101px;
width:145px;
height:338px;
z-index:1;
background-color: #00FFFF;
}
.style4 {
font-size: 24pt;
font-weight: bold;
}
h1,h2,h3,h4,h5,h6 {
font-weight: bold;
}
h1 {
font-size: 14px;
}
.style6 {font-size: 14px; }
.style7 {font-size: 16px; }
.style8 {font-family: "Times New Roman", Times, serif}
-->
</style>
<script type="text/JavaScript">
<!--
function MM_preloadImages() { //v3.0
var d=document; if(d.images){ if(!d.MM_p) d.MM_p=new Array();
var i,j=d.MM_p.length,a=MM_preloadImages.arguments; for(i=0; i<a.length; i++)
if (a[i].indexOf("#")!=0){ d.MM_p[j]=new Image; d.MM_p[j++].src=a[i];}}
}
function MM_findObj(n, d) { //v4.01
var p,i,x; if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) {
d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);}
if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<d.forms.length;i++) x=d.forms[i][n];
for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=MM_findObj(n,d.layers[i].document);
if(!x && d.getElementById) x=d.getElementById(n); return x;
}
function MM_nbGroup(event, grpName) { //v6.0
var i,img,nbArr,args=MM_nbGroup.arguments;
if (event == "init" && args.length > 2) {
if ((img = MM_findObj(args[2])) != null && !img.MM_init) {
img.MM_init = true; img.MM_up = args[3]; img.MM_dn = img.src;
if ((nbArr = document[grpName]) == null) nbArr = document[grpName] = new Array();
nbArr[nbArr.length] = img;
for (i=4; i < args.length-1; i+=2) if ((img = MM_findObj(args[i])) != null) {
if (!img.MM_up) img.MM_up = img.src;
img.src = img.MM_dn = args[i+1];
nbArr[nbArr.length] = img;
} }
} else if (event == "over") {
document.MM_nbOver = nbArr = new Array();
for (i=1; i < args.length-1; i+=3) if ((img = MM_findObj(args[i])) != null) {
if (!img.MM_up) img.MM_up = img.src;
img.src = (img.MM_dn && args[i+2]) ? args[i+2] : ((args[i+1])? args[i+1] : img.MM_up);
nbArr[nbArr.length] = img;
}
} else if (event == "out" ) {
for (i=0; i < document.MM_nbOver.length; i++) {
img = document.MM_nbOver[i]; img.src = (img.MM_dn) ? img.MM_dn : img.MM_up; }
} else if (event == "down") {
nbArr = document[grpName];
if (nbArr)
for (i=0; i < nbArr.length; i++) { img=nbArr[i]; img.src = img.MM_up; img.MM_dn = 0; }
document[grpName] = nbArr = new Array();
for (i=2; i < args.length-1; i+=2) if ((img = MM_findObj(args[i])) != null) {
if (!img.MM_up) img.MM_up = img.src;
img.src = img.MM_dn = (args[i+1])? args[i+1] : img.MM_up;
nbArr[nbArr.length] = img;
} }
}
//-->
</script>
</head>
<body onload="MM_preloadImages('homenava.png')">
<table width="858" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#4A639C">
<tr bordercolor="#4A639C">
<td width="142" height="112"><div align="left"><img src="cornerlogoa.png" width="140" height="110" /></div></td>
<td width="710"><div align="center" class="style4">AAHL</div></td>
</tr>
</table>
<table width="858" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#4A639C">
<tr bordercolor="#000060">
<td width="854" height="35" valign="top"><div align="center"><em>Admin Login </em></div></td>
</tr>
</table>
<table width="858" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#000060">
<tr valign="top" bordercolor="#4A639C">
<td width="160" height="495" align="center" valign="top" bordercolor="#4A639C"><div align="center">
<h1>&nbsp;</h1>
<p> <a href="index.html" target="_top" onclick="MM_nbGroup('down','group1','home','',1)" onmouseover="MM_nbGroup('over','home','homenava.png','',1)" onmouseout="MM_nbGroup('out')"><img src="homenav.png" alt="" name="home" width="160" height="40" border="0" id="home" onload="" /></a><br />
</p>
</div></td>
<td width="692" bordercolor="#4A639C"><p align="center" class="style6">&nbsp;</p>
<p align="center" class="style6">&nbsp;</p>
<p align="center" class="style6">&nbsp;</p>
<form id="form1" name="form1" method="POST">
<div align="center"><span class="style8">Username </span>
<input type="text" name="textfield" />
</div>
</form>
<form id="form2" name="form2" method="post" action="">
<div align="center"><span class="style8">Password </span>
<input type="password" name="textfield2" />
</div>
</form>
<form id="form3" name="form3" method="post" action="">
<div align="center">
<input name="Submit" type="submit" id="Submit" value="Login" />
</div>
</form> <p align="center" class="style7">&nbsp;</p>
</td>
</tr>
</table>
<p align="left">&nbsp;</p>
<p align="left">&nbsp;</p>
</body>


</html>

rico1931
08-04-2008, 09:14 PM
by the way i'm not sure if its still good web practice to have your css inline instead of external but correct me if i'm wrong and plus having an external css makes the code look a bit better if someone else was going to be working on it .

rattlsnak
08-04-2008, 09:31 PM
and just to verify, the statement should look like this:

<?php
include("connections/connect.php");
?>

with the ( ) and the double quotes and no spaces??

Ive seen references without the ( ) and some with single quotes, so i want to make sure!

rattlsnak
08-05-2008, 01:19 AM
ok really weird stuff here. I have made the connect page and the include statement and it connects to the database and everything looks good at this point, except as soon as I added the <php include statement my design view in DW goes blank with a php icon displayed. The page still shows up properly online, but what is happening?

edbr
08-05-2008, 02:39 AM
the DW wizard has gone into the west :)

rattlsnak
08-05-2008, 03:42 AM
ok, since that wasnt working for some reason, i made another page called logincheck.php that has the mysql_connect as well as the validating script on it, and it works! I simply pointed the action of the $post (login button) to that page and it works great. I still have to add session variables and restirction scripts on the pages insude the login, but hey i'm getting there! Thanks for the help guys! It pointed me in the right direction.

But curious, anyone know why the php include script trashed my page?

davidj
08-05-2008, 06:35 AM
yes

dreamweaver has to write code in its own way else it wont recognise it and all your panels will fail to work

you will be better off learning how to code

rattlsnak
08-05-2008, 09:41 PM
David, i just went through some of your php login script tutorials (Awesome B.T.W.! ) and had a question. I see you use the require_once statement for the database connect functons but you use an include for the security or sessions, what is the difference between those two statement? Don't they accomplish the same thing?

davidj
08-05-2008, 09:43 PM
yes they do

require_once is ignored after its loaded once where include is run time and time again

they do the same thing and i tend to use the include option

davidj
08-05-2008, 09:44 PM
respect to you for wanting to learn

rattlsnak
08-05-2008, 10:04 PM
i have a site that has 20+ pages and a member section within of 5 pages or so. i have built a logon page for the member section in which the submit button points to a php page called check login that if successful points to the member home page where they can go on from there and if not returns them to the login page. The only thing im using the database for is for login at this point.

Question #1, does the sql connect statement (whether by include, require or written out) have to be on every member page?

Question #2, I cant quite understand why in your lesson #2 you did the second variable to assign the session a user id. Was it simply so you could write the script welcoming that person by name?

Yes I have more, but thats a start! ;)

davidj
08-05-2008, 10:10 PM
1. yes when accessing the db on that page. You have to connect then execute query and then close connection. The latter is handled automatically

2. its been a while since i did those tuts but if i assigned a sting to a session its normally to output throughout the users session of the application so I'm guessing it was to show the name of the user

davidj
08-05-2008, 10:11 PM
post seperate questions in their own posts please

rattlsnak
08-05-2008, 10:14 PM
ok, sure will..

thanks so much..