Page 1 of 1

php mysql question

Posted: Tue Nov 09, 2004 11:29 am
by Hawkmannequin
I'm relatively new (okay, incredibly new) to php programming and have a question. I've been doing some searchs for my problem and thought I would use this as an additional outlet to get some help.

My situation....I'm designing a web page for the newspaper I work for. The site is done, but there is a login that we want to add to the page, for those subscribers that just get the paper online. We want to set up a user login that will connect to a MySQL database, and allow the users access to the content there in, while keeping out those who didn't subscribe to the online version (essentially online subscribers will have access to a pdf version of the paper, plus streaming video and such, we are not taking orders for subscriptions online using a credit card, so I'm just looking for something with database connectivity). Admittedly, I've done this before using VBScript connecting to an Access database, but am having problems with the script (I'm sick and tired of bashing my head against a wall). What I need is a layman's description of connecting to a MySQL database using php script (I'd do more searching on this, but I also am in charge of pagination and creation of display ads for said paper).

If any of you can point me in the right direction it would be extremely helpful.

Thanks in advance.

Posted: Tue Nov 09, 2004 11:36 am
by Weirdan
there's a lot of ready-made authentication scripts, most of them use MySQl as a backend. (you may find some of them at http://hotscripts.com or http://phpclasses.com). If you want to reinvent the wheel (there's nothing wrong with it :) ), google for [google]php mysql tutorial[/google]. All of those tutorials cover the conneting to the database.

Posted: Tue Nov 09, 2004 12:51 pm
by Hawkmannequin
Thanks for the heads up. This makes my search less painful, to say the least.

Posted: Tue Nov 09, 2004 2:02 pm
by cto1mac
Here is a login script that I have used.
Step 1: Create the accounts table.
Setp 2: Decide how you want to maintain login information, cookies or session variables,
Step 3.: Create the login form
Step 4: Test it.

Here is the whole shebang:
The first page: page1.php

Code: Select all

<?php
//create the initial db connection

$conn = mysql_connect("localhost","username","password") or die(mysql_error());

//select the appropriate db
mysql_select_db("dbname",$conn) or die(mysql_error());

//here is some trickery so that you can create the table from the
//script

$create_table_accounts = "CREATE TABLE IF NOT EXISTS accounts (
id int(11) primary key not null auto_increment,
username varchar(50),
password varchar(50), 
//continue adding what you might want, email addresses and such here
)";

mysql_query($create_table_accounts, $conn) or die(mysql_error());


?>
<form name="login" action="page2.php" method="POST">
<table><tr><td>Username: </td><td><input type="text" size="30" name="uname"></td></tr>
<tr><td>Password: </td><td><input type="password" size="30" name="pword"></td></tr>
<tr><td><input type="submit" value="Go!"></td><td><input type="reset" value="Clear"></td></tr>
</table>




?>
And the second page where we check the credentials and set session variables.

Code: Select all

<?php

// I personally like to use the MD5 function for passwords, so here is what I use.
$pword = md5($_POSTpword]);

$conn = mysql_connect("localhost","username","password") or die(mysql_error());

//select the appropriate db
mysql_select_db("dbname",$conn) or die(mysql_error());

$sql = "SELECT * FROM accounts WHERE username = '" . $_POST[uname] . "' AND password = '" . $pword . "'"; 

$result = mysql_query($sql, $conn) or die(mysql_error());

if(mysql_num_rows($result) == 1) {
   $id = mysql_result($result, 0, 'id');
   $_SESSION[user_id] = $id;
   header("Location: whateverpage.php");
} else {
   header("Location: backtopage1.php");
}


?>
Obviously there are alot of possible improvements to this script but, you ge the gist. It isn't really all that hard, but these forums are the best place I have found for help.

Posted: Tue Nov 09, 2004 2:36 pm
by Weirdan
cto1mac wrote: Obviously there are alot of possible improvements to this script...
First would be to use [php_man]mysql_real_escape_string[/php_man] on input parameters. Otherwise you would get compromised in a matter of minutes after putting your script on the host with magic_quotes_gpc turned off.

Posted: Tue Nov 09, 2004 3:00 pm
by cto1mac
I didn't claim to be an expert, just a helpful soul.

Posted: Tue Nov 09, 2004 3:07 pm
by Weirdan
cto1mac wrote:I didn't claim to be an expert
I don't either :wink: Otherwise I would run my own security research business :)