Create MySQL Table

andersgrim

I have just browsed some tutorials on how to set up php log-in systems, and it told me to create a mySQL table..

What I wonder is where to fill in this text and how to apply it to the database..

CREATE TABLE member (
  id int NOT NULL auto_increment,
  username varchar(20) NOT NULL default '',
  password char(32) binary NOT NULL default '',
  cookie char(32) binary NOT NULL default '',
  session char(32) binary NOT NULL default '',
  ip varchar(15) binary NOT NULL default '',
  PRIMARY KEY  (id),
  UNIQUE KEY username (username)
);

And by the way -> Great forum!! 🙂

TheDefender

If you use PhPMyAdmin, then click on the SQL link, put it into that box, and click submit. You can also use the MySQL Shell, you can create a *.sql file, and load it in the MySQL shell, or phpmyadmin... There are a bunch of different ways to do it, depending on what you have to work with.

andersgrim

Thanks.

I now got access to the phpMyAdmin.
(Which was a bit hard as I did not have access to the cpanel)

Edit: And another thing...

When connecting to the DB via a PHP file, am I really required to type the password as open-source in the PHP file?

Or are there any ways to encrypt the password?

TheDefender

What I do is intput the variables into a separate include file.

// This is in my connect.inc.php file
$db_domain = "localhost";
$db_user = "username";
$db_pw = "password";
$db_name = "dbname";

// Then in my file, I would have something like this in my file that I want to create the connection in:
include('./path/to/connect.inc.php');
$connect = mysql_connect($db_domain, $db_user, $db_pw) or die("Could not connect: ".mysql_error());
mysql_select_db($db_name, $connect) or die(mysql_error());

That way it isn't hardcoded into your file. If someone tries to view the source of the include file, it will get parsed by the PHP engine before it is output to the browser anyhow, so it is fine, since the values aren't echoed. For added security, you can put the include file in a server-side password protected directory.

andersgrim

Thanks for excellent feedback.

Will it be possible to add a die() to block off visitors from viewing the content of the inc file?

Or will that prevent it from being required / included aswell?

As for example

// connect.inc.php

die('Not authorized');

$db_domain = "localhost"; 
$db_user = "username"; 
$db_pw = "password"; 
$db_name = "dbname";

TheDefender

You know, I have never really thought about it. I imagine you could use the predefined HTTP_REFERER variable to check to make sure the page calling it is the one you want to be the one calling it, and if it isn't, then echo some "unauthorized" error, and if it is, allow the variables...

This isn't really needed though, because since it is a PHP file, it gets parsed before it is sent to the client (this also includes anytime someone tries to save it locally), so if you don't physically echo the variables, you should be fine.

andersgrim

I have now set up a working login system, using sessions among others.

Now, will the session automatically timeout after 3 hours / 180 minutes? Or am i required to implent another pack of lines for the session to automatically expire?

EDIT: I have also heard about sessiontimeouts being defined somewhere on the server. Is it possible to change, or atleast view the current timeout settings?

TheDefender

I believe the default setting in the php.ini file is 180 minutes. You can adjust this by editing the session.cache_expire value in the php.ini file, and this will affect all sessions generated on the server.

You can affect individual session timeouts with the following function:
session_cache_expire()

ajdegans

maybe im copying TheDefender his post, but im using this method:

create_table.php

<?php
include ("dbconnect.php");

$install = "
CREATE TABLE `news` (
`id` TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
`title` TEXT NOT NULL, 
`date` TEXT NOT NULL, 
`news` TEXT NOT NULL);
";
mysql_query($install) or die(mysql_error());


?>

dbconnect.php

<?
mysql_connect("localhost","username","password"); 
mysql_select_db("weblog");
?>

Hope this might be helpfull 🙂
😃

andersgrim

Thanks. Will have a closer look tomorrow.
Currently replanning the site structure (as most professionals, right? 😉 )