PHP / MySQL - Creating a Database login

[stahlsta]

Hello everyone,
I am trying to create a database login but i am not having any luck. I am not sure what is wrong. I feel everything is in order but I am new and don't really know what to look for. If someone could help me get this up and running, i'd greatly appreciate it. I've spent over 20 hours. I know it isn't exteremely diffuclt but I am fustrated and about to give up . Some help would me great!
 
1st page: Login.html ( I left out the formatting, heres just the form) file:///C:/Users/Stahlsta/Desktop/PHP/Login.html

<form name="form 1"  method="post" action="KitchenDatabase.php">
 <Center><table  width="20%" border="0" cellspacing="0" bgcolor="blue"  frame="box" >

   <tr>
  <td><h3>Username:</h3></td>
  <td><input name="username" type="text" id="username" ></td></tr>
  <tr>
  <td><h3>Password:</h3></td>
  <td><input name="password" type="text" id="password" ></td></tr>
   <tr>
     <td colspan="2" align="center">
     <input type="submit" name="Submit" value="Login"/>
	 
     <input type="submit" value="Guest Log in"/></td></tr>

  </table> </Center>
</form>

When I click login: I want to access KitchenDatabase.php

 

2nd page:KitchenDatabase.php - This page should link to loginsuccess.php

<?php

$host="localhost:3306"; // Host name 
$Owner_fName="username"; // Mysql username 
$Owner_password="password"; // Mysql password 
$db_name="2013-wstahl"; // Database name 
$tbl_name="Owner"; // Table name 

// Connect to server and select databse.
mysql_connect("$host", "$Owner_fName", "$Owner_password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// username and password sent from form 
$Owner_fName=$_POST['username']; 
$Owner_password=$_POST['password']; 

// To protect MySQL injection (more detail about MySQL injection)
$Owner_fName = stripslashes($Owner_fName);
$Owner_password = stripslashes($Owner_password);
$Owner_fName = mysql_real_escape_string($Owner_fName);
$Owner_password = mysql_real_escape_string($Owner_password);
$sql="SELECT * FROM $tbl_name WHERE username='$Owner_fName' and password='$Owner_password'";
$result=mysql_query($sql);

// Mysql_num_row is counting table row
$count=mysql_num_rows($result);

// If result matched $username and $password, table row must be 1 row
if($count==1){

// Register $Owner_fName, $Owner_password and redirect to file "loginsuccess.php"
session_register("username");
session_register("password"); 
header("location:loginsuccess.php");
}
else {
echo "Wrong Username or Password";
}
?>

Page 3:loginsuccess.php - This page should link to KitchenDatabase.html

<?php
session_start();
if(!session_is_registered(username)){
header("KitchenDatabase.html");
}
?>

Page 4:I wont bore you witht he code, i dont think it's important for the login work.

(file:///C:/Users/Stahlsta/Desktop/PHP/KitchenDatabase.html)

 

Everything is based off this database.

KitchenDatabase.sql

#
# DUMP FILE
#
# Database is ported from MS Access
#------------------------------------------------------------------
# Created using "MS Access to MySQL" form http://www.bullzip.com
# Program Version 5.1.242
#
# OPTIONS:
#   sourcefilename=C:\Users\wstahl\Desktop\KBdatabase1.accdb
#   sourceusername=
#   sourcepassword=
#   sourcesystemdatabase=
#   destinationdatabase=2013-wstahl
#   storageengine=MyISAM
#   dropdatabase=0
#   createtables=1
#   unicode=1
#   autocommit=1
#   transferdefaultvalues=1
#   transferindexes=1
#   transferautonumbers=1
#   transferrecords=1
#   columnlist=0
#   tableprefix=
#   negativeboolean=0
#   ignorelargeblobs=0





#   memotype=LONGTEXT
#

CREATE DATABASE IF NOT EXISTS `2013-wstahl`;
USE `2013-wstahl`;

#
# Table structure for table 'Fridge'
#

DROP TABLE IF EXISTS `Fridge`;

CREATE TABLE `Fridge` (
  `Fridge_ID` INTEGER NOT NULL, 
  `Owner_ID` INTEGER NOT NULL, 
  `Room_Loc` VARCHAR(255), 
  INDEX (`Owner_ID`), 
  PRIMARY KEY (`Fridge_ID`),
  FOREIGN KEY (`Owner_ID`) REFERENCES `Owner`
) ENGINE=myisam DEFAULT CHARSET=utf8;

SET autocommit=1;

#
# Dumping data for table 'Fridge'
#

INSERT INTO `Fridge` VALUES (100, 100, 'Kitchen');
INSERT INTO `Fridge` VALUES (101, 101, 'Wills');
INSERT INTO `Fridge` VALUES (102, 102, 'Taylors');
INSERT INTO `Fridge` VALUES (103, 103, 'Matts');
INSERT INTO `Fridge` VALUES (104, 104, 'Felixs');
INSERT INTO `Fridge` VALUES (105, 105, 'Anthonys');
INSERT INTO `Fridge` VALUES (106, 106, 'Sams');
# 7 records

#
# Table structure for table 'Guest'
#

DROP TABLE IF EXISTS `Guest`;

CREATE TABLE `Guest` (
  `Guest_ID` INTEGER NOT NULL AUTO_INCREMENT, 
  `Guest_fName` VARCHAR(50), 
  `Guest_lName` VARCHAR(50), 
  `Over21` TINYINT(1) DEFAULT 0,
  `Owner_ID` INTEGER NOT NULL,
  INDEX (`Over21`), 
  PRIMARY KEY (`Guest_ID`),
  FOREIGN KEY (`Owner_ID`) REFERENCES `Owner`
) ENGINE=myisam DEFAULT CHARSET=utf8;

SET autocommit=1;

#
# Dumping data for table 'Guest'
#

INSERT INTO `Guest` VALUES (1, 'Harry', 'Potter', 1, 101);
INSERT INTO `Guest` VALUES (2, 'Jamie', 'Kurtis', 1, 102);
INSERT INTO `Guest` VALUES (3, 'Bucky', 'Smith', 0, 103);
INSERT INTO `Guest` VALUES (4, 'Nick', 'Crawl', 1, 101);
INSERT INTO `Guest` VALUES (5, 'Matt', 'Taylor', 0, 104);
INSERT INTO `Guest` VALUES (6, 'Martha', 'Stewart', 1,105);
INSERT INTO `Guest` VALUES (7, 'Kris', 'Durdon', 0, 105);
INSERT INTO `Guest` VALUES (8, 'Mike', 'Micheals', 1, 102);
# 8 records

#
# Table structure for table 'Item'
#

DROP TABLE IF EXISTS `Item`;

CREATE TABLE `Item` (
  `Item_ID` INTEGER NOT NULL AUTO_INCREMENT, 
  `Item_Name` VARCHAR(255), 
  `Item_Cost` DECIMAL(19,4), 
  `Exp_Date` DATETIME, 
  `Item_Qty` INTEGER, 
  `Owner_ID` INTEGER, 
  `Fridge_ID` INTEGER, 
  `Store_ID` INTEGER,  
  PRIMARY KEY (`Item_ID`),
  FOREIGN KEY (`Owner_ID`) REFERENCES `Owner`,
  FOREIGN KEY (`Fridge_ID`) REFERENCES `Fridge`,
  FOREIGN KEY (`Store_ID`) REFERENCES `Store`, 
  INDEX (`Fridge_ID`), 
  INDEX (`Owner_ID`),  
  INDEX (`Store_ID`)
) ENGINE=myisam DEFAULT CHARSET=utf8;

SET autocommit=1;

#
# Dumping data for table 'Item'
#

INSERT INTO `Item` VALUES (1, 'eggs', 2.09, '2013-11-11 00:00:00', 2, 100, 100, 200);
INSERT INTO `Item` VALUES (2, 'milk', 3.49, '2013-11-07 00:00:00', 2, 100, 100, 201);
INSERT INTO `Item` VALUES (3, 'Bread', 3.09, '2013-11-08 00:00:00', 1, 101, 101, 201);
INSERT INTO `Item` VALUES (4, 'cheese', 4.01, '2013-12-30 00:00:00', 2, 101, 100, 200);
INSERT INTO `Item` VALUES (5, 'hot dogs', .97, '2014-01-16 00:00:00', 3, 102, 102, 200);
INSERT INTO `Item` VALUES (6, 'rolls', 3.09, '2013-11-25 00:00:00', 6, 102, 102, 200);
INSERT INTO `Item` VALUES (7, 'noodles', .99, NULL, 4, 103, 103, 202);
INSERT INTO `Item` VALUES (8, 'sauce', 4.09, '2013-11-20 00:00:00', 2, 103, 103, 202);
INSERT INTO `Item` VALUES (9, 'rice', .98, NULL, 12, 104, 104, 200);
INSERT INTO `Item` VALUES (10, 'beans', 1.49, '2013-12-18 00:00:00', 2, 104, 100, 202);
INSERT INTO `Item` VALUES (11, 'hamburgers', 6.99, '2013-12-25 00:00:00', 8, 105, 100, 200);
INSERT INTO `Item` VALUES (12, 'buns', 3.09, '2013-12-19 00:00:00', 8, 105, 105, 200);
INSERT INTO `Item` VALUES (13, 'onions', .99, NULL, 3, 106, 106, 202);
INSERT INTO `Item` VALUES (14, 'soup', 1.99, '2014-04-16 00:00:00', 5, 106, 106, 200);
INSERT INTO `Item` VALUES (15, 'icream', 3.09, NULL, NULL, NULL, 101, NULL);
INSERT INTO `Item` VALUES (16, 'Bacon', 5.15, '2013-10-16 00:00:00', 1, 101, 101, 202);
INSERT INTO `Item` VALUES (17, 'Hot sauce', 2.79, '2013-11-22 00:00:00', 3, 101, 101, 200);
INSERT INTO `Item` VALUES (18, 'ketchup', 3.5, NULL, 1, 101, 101, 201);
INSERT INTO `Item` VALUES (19, 'crunch cereal', 3.49, '2014-01-22 00:00:00', 2, 101, 101, 201);
# 19 records

#
# Table structure for table 'Owner'
#

DROP TABLE IF EXISTS `Owner`;

CREATE TABLE `Owner` (
  `Owner_ID` INTEGER NOT NULL, 
  `Owner_fName` VARCHAR(255) NOT NULL, 
  `Owner_lname` VARCHAR(255), 
  `Owner_password` VARCHAR(50), 
  PRIMARY KEY (`Owner_ID`)
) ENGINE=myisam DEFAULT CHARSET=utf8;

SET autocommit=1;

#
# Dumping data for table 'Owner'
#

INSERT INTO `Owner` VALUES (100, 'All', 'NULL', 'NULL');
INSERT INTO `Owner` VALUES (101, 'Will', 'Stahl', password);
INSERT INTO `Owner` VALUES (102, 'Taylor', 'Ryzuk', NULL);
INSERT INTO `Owner` VALUES (103, 'Matt', 'Sheehan', NULL);
INSERT INTO `Owner` VALUES (104, 'Felix', 'Burgos', NULL);
INSERT INTO `Owner` VALUES (105, 'Anthony', 'Lombardi', NULL);
INSERT INTO `Owner` VALUES (106, 'Sam', 'Gutzmer', NULL);
# 7 records

#
# Table structure for table 'Store'
#

DROP TABLE IF EXISTS `Store`;

CREATE TABLE `Store` (
  `Store_ID` INTEGER NOT NULL, 
  `Store_Name` VARCHAR(255) NOT NULL, 
  `Store_City` VARCHAR(255), 
  PRIMARY KEY (`Store_ID`)
) ENGINE=myisam DEFAULT CHARSET=utf8;

SET autocommit=1;

#
# Dumping data for table 'Store'
#

INSERT INTO `Store` VALUES (200, 'Walmart', 'Oswego');
INSERT INTO `Store` VALUES (201, 'Bryne', 'Oswego');
INSERT INTO `Store` VALUES (202, 'Kinneys', 'Oswego');
INSERT INTO `Store` VALUES (203, 'Price Chopper', 'Oswego');
# 4 records

Am I even close? I've come to far to quit. Please help me get this working

Kudos,

Fridge.html

Fridge.php

KitchenDatabase.html

KitchenDatabase.php

Login.html

loginsuccess.php

[dalecosp]

Well, at least, this much is wrong:

 

header("KitchenDatabase.html");

 

header() requires more than that. Try this:

header("Location: KitchenDatabase.html");

 

(And technically, to match the HTTP Specification, it should be a fully-qualified URL (site.com/name) in the Header().)

Edited December 6, 2013 by dalecosp

[stahlsta]

Thanks Dale, I made the correction. I must have deleted that by mistake while I was trouble shooting my errors. I appreciate the help. 

 

Also, I plan on hosting it as an official site once i get it up and running. Good tip.

 

If anyone has the time to troubleshoot this with me, please do, I'd really appreciate it! I'm finding being a beginner is very hard. So frustrating! I'll be trying to get it to work all weekend while I have the time. I've done all I can i dont even know what else to try :/

Edited December 6, 2013 by stahlsta

[MDCode]

According to your SQL file, there is no username column in that table, but you are selecting it in your query.

[mac_gyver]

you need to start with the basics and learn some amount of the php/html/mysql language before you can use or write code that does something. by just trying things without knowing that meaning of each statement/line in it, leads to a lot of wasted time and frustration when trying to program.

 

a) the code you found or were given as a starting place is 11 years out of date. i won't waste the time mentioning what about it won't work at all under the latest php version, because php.net has documented what has changed in the language over time and this information can be found on their php web site.

 

b) that fact that you have posted the path to your files as file:///C:/Users/Stahlsta/Desktop/PHP/.... indicates two things.

 

first, you are probably directly opening the files using those paths in your browser. this won't work because php is a server side scripting language and requires that the URL used when requesting the files (or submitting a form) involve php on the web server.

 

second, since there is no mention of a htdocs or document root folder in those paths, you likely don't have a web server with php installed on your local development system or if you do, you haven't placed your files into the htdocs folder under that web server.

 

the URL you would use in your browser for any of your pages on a local development system would be similar to http://localhost/file_name.ext

 

c) the loginsuccess.php code, in addition to not working at all in the latest php version, is not secure as you need an exit; statement after the header() redirect to prevent the protected code on the rest of the page from running. it's the browser that is performing the redirect. all anyone needs to do to stay on that page is ignore the redirect.

 

d) that whoever wrote this code named the files and database tables like - KitchenDatabase.php, `Fridge`, ... rather than general names indicating the purpose of the file, means that this code is at best leftover (pun intended) from a classroom assignment and shouldn't be the basis for your login code. file names/table names should indicate their general usage (i.e. user, product...), not actual categories of the data within them.

 

e) the script you found is missing in some basic functionally for even a minimal login script - the passwords need to be hashed (preferably with a random salt for each user) and the form processing code needs to start with at least some logic to test if a form has been submitted.

 

f) lastly, the mysql_ functions are depreciated starting in php5.5 and should not be used when writing new code (or learning php) as they will need to all be replaced in the future. see this link - http://www.php.net/manual/en/mysqlinfo.api.choosing.php

Edited December 7, 2013 by mac_gyver

[stahlsta]

Okay thank you for saving me my time. Can't believe How off I was. Back to the drawing board. Thank you everyone