Help with PHP/MySql

Completed Posted Jan 20, 2010 Paid on delivery
Completed Paid on delivery

Need help with my PHP/MySql database I am trying to build.

I have it working fine, but need to improve on it to prevent one user

from seeing another users information.

Will require a decent knowledge of PHP/MySql

This is on a Linux system.

## Deliverables

I need some help with some PHP/MySql code.

I have a simple database that works fine. But not we need to allow others to use it.

We need to have a login to work with the userdetails table below.

After they log in we need to only allow them access to their records and not be able to see

any other users records and vice versa.

This is on Linux 2.4.22.

We have [url removed, login to view] and MySql [url removed, login to view], We will be upgrading in the very near future, but it

needs to be compatible with the above and up.

Here is what we have.

Directory tree

/user/homepage/database

/user/homepage/database/booking/

/user/homepage/database/contacts/

/user/homepage/database/expenses/

/user/homepage/database/mileage/

/user/homepage/database/payroll/

I have only put the first sub directory /booking below, and all the MySql tables.

All the sub directories all have the same files but different fields to fill

the database tables below.

In the

/user/homepage/database

We have the following that we did get to work with userdetails but we are not sure how

to get it to work with everything else.

Change the login any way you need to. Just remember the md5 is used.

----------------------------------------

#### [url removed, login to view]

<form action="[url removed, login to view]" method="post">

Username:

<input type="text" name="username" /><br />

Password:

<input type="password" name="password" /><br />

<input type="submit" value="Submit" />

</form>

-----------------------------------------

##### [url removed, login to view]

<?php

// Connect to the database

require('[url removed, login to view]');

// Set username and password variables for this script

$user = mysql_real_escape_string($_POST["username"]);

$pass = mysql_real_escape_string(md5($_POST["password"]));

// Make sure the username and password match, selecting all the client's

// data from the database if it does. Store the data into $clientdata

$clientdata = mysql_query("SELECT * FROM clients WHERE username='$user' and password='$pass'")

or die (mysql_error());

// Put the $clientdata query into an array we can work with

$data = mysql_fetch_array($clientdata, MYSQL_ASSOC);

// If the username and password matched, we should have 1 entry in our

// $clientdata array. The function mysql_num_rows() can count this.

// If not, we should have 0. So, we can use a simple if/else statement

// to determine if they matched up.

// If there is 1 row in the query, it is our user's row

if(mysql_num_rows($clientdata) == 1){

// Start a new blank session. This will assign the user's server

// with a session with an individual ID

session_start();

// With our session started, we can assign variables for a logged

// in user to use until they log out.

$_SESSION['username'] = $user;

//$_SESSION['email'] = $data['email'];

//$_SESSION['paypal'] = $data['paypal'];

// Then, redirect them to the profile page

header('Location: [url removed, login to view]');

// the username and password did not match.

}else{echo "<br><BR>The username and password don't match. Please go back and <a href=\"[url removed, login to view]\" > try again.</a>";}

-----------------------------------------

#### [url removed, login to view]

<form id="FormName" action="[url removed, login to view]" method="post" name="FormName">

<table width="448" border="0" cellspacing="2" cellpadding="0">

<BR><BR>

<tr><td width = "150"><div align="right"><label for="username">eMail Address</label></div></td>

<td><input id="username" name="username" type="text" size="50" value="" maxlength="100"></td></tr>

<tr><td width = "150"><div align="right"><label for="password">password</label></div></td>

<td><input id="password" name="password" type="text" size="50" value="" maxlength="50"></td></tr>

<tr><td width = "150"><div align="right"><label for="first_name">First Name</label></div></td>

<td><input id="first_name" name="first_name" type="text" size="50" value="" maxlength="50"></td></tr>

<tr><td width = "150"><div align="right"><label for="last_name">Last Name</label></div></td>

<td><input id="last_name" name="last_name" type="text" size="50" value="" maxlength="50"></td></tr>

<tr><td width = "150"><div align="right"><label for="address">Address</label></div></td>

<td><input id="address" name="address" type="text" size="50" value="" maxlength="50"></td></tr>

<tr><td width = "150"><div align="right"><label for="city_state">City, State</label></div></td>

<td><input id="city_state" name="city_state" type="text" size="50" value="" maxlength="50"></td></tr>

<tr><td width = "150"><div align="right"><label for="zipcode">Zipcode</label></div></td>

<td><input id="zipcode" name="zipcode" type="text" size="25" value="" maxlength="25"></td></tr>

<tr><td width = "150"><div align="right"><label for="phone">Phone</label></div></td>

<td><input id="phone" name="phone" type="text" size="25" value="" maxlength="25"></td></tr>

<tr><td width = "150"><div align="right"><label for="email">eMail</label></div></td>

<td><input id="email" name="email" type="text" size="50" value="" maxlength="50"></td></tr>

<tr><td width="150"></td><td>

<input type="submit" name="submitButtonName" value="Add"></td>

</tr></table></form>

-----------------------------------------

#### [url removed, login to view]

<?php

require('[url removed, login to view]');

// Create the variables, while encrypting the password and

// preventing SQL injection

$username = mysql_real_escape_string($_POST["username"]);

$password = mysql_real_escape_string($_POST["password"]);

$pw = md5($password);

$first_name = mysql_real_escape_string($_POST["first_name"]);

$last_name = mysql_real_escape_string($_POST["last_name"]);

$address = mysql_real_escape_string($_POST["address"]);

$city_state = mysql_real_escape_string($_POST["city_state"]);

$zipcode = mysql_real_escape_string($_POST["zipcode"]);

$phone = mysql_real_escape_string($_POST["phone"]);

$email = mysql_real_escape_string($_POST["email"]);

if(!preg_match('/^[a-zA-Z0-9]+$/', $username)) // If our username is invalid

{

echo "The username can only contain letters or numbers."; // Tell the user

}

if(!eregi("^[_a-z0-9-]+(.[_a-z0-9-]+)*@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,3})$", $email))

{

echo "The email address you entered is invalid."; // Tell the user

}

else if(!preg_match('/^[a-zA-Z0-9]+$/', $password)) // If our password is invalid

{

echo "The password can only contain letters or numbers."; // Tell the user

}

else{

// Inserts the data into the database

$result= MYSQL_QUERY(

"INSERT INTO clients (id, username, password, first_name, last_name, address, city_state, zipcode, phone)".

"VALUES ('', '$username', '$pw', '$first_name', '$last_name', '$address', '$city_state', '$zipcode', '$phone')"

);

echo "Thank you for signing up.";

}

?>

-----------------------------------------

#### [url removed, login to view]

<?php

#// DBPASS=****

#// DBUSER=****

#// DBHOST=localhost

#// mysql=/usr/bin/mysql

#// commands=/tmp/.commands.$$

$conn = mysql_connect("dbhost","dbuser","dbpass");

#// mysql_select_db is a predefined function in MySQL

#// It let's us call the database, so we can save it

#// in our variable $db

$db = mysql_select_db("dbase");

-------------------------------------------

/user/homepage/database/booking/

-------------------------------------------

############# [url removed, login to view]

<form id="FormName" action="[url removed, login to view]" method="post" name="FormName">

<table width="448" border="0" cellspacing="2" cellpadding="0">

<tr><td width = "150"><div align="right"><label for="title">Title</label></div></td>

<td><input id="title" name="title" type="text" size="25" value="" maxlength="50"></td></tr>

<tr><td width = "150"><div align="right"><label for="role">Role</label></div></td>

<td><input id="role" name="role" type="text" size="25" value="" maxlength="50"></td></tr>

<tr><td width = "150"><div align="right"><label for="date">Date</label></div></td>

<td><input id="date" name="date" type="text" size="25" value="" maxlength="15"></td></tr>

<tr><td width = "150"><div align="right"><label for="time_in">Time In</label></div></td>

<td><input id="time_in" name="time_in" type="text" size="25" value="" maxlength="15"></td></tr>

<tr><td width = "150"><div align="right"><label for="time_out">Time Out</label></div></td>

<td><input id="time_out" name="time_out" type="text" size="25" value="" maxlength="15"></td></tr>

<tr><td width = "150"><div align="right"><label for="location">Location</label></div></td>

<td><input id="location" name="location" type="text" size="25" value="" maxlength="100"></td></tr>

<tr><td width = "150"><div align="right"><label for="check_in_with">Check In With</label></div></td>

<td><input id="check_in_with" name="check_in_with" type="text" size="25" value="" maxlength="50"></td></tr>

<tr><td width = "150"><div align="right"><label for="notes">Notes</label></div></td>

<td><textarea id="notes" name="notes" rows="4" cols="40"></textarea></td></tr>

<tr><td width = "150"><div align="right"><label for="parking">Parking</label></div></td>

<td><input id="parking" name="parking" type="text" size="25" value="" maxlength="15"></td></tr>

<tr><td width = "150"><div align="right"><label for="cab_bus">Cab bus</label></div></td>

<td><input id="cab_bus" name="cab_bus" type="text" size="25" value="" maxlength="15"></td></tr>

<tr><td width = "150"><div align="right"><label for="other">Other</label></div></td>

<td><input id="other" name="other" type="text" size="25" value="" maxlength="15"></td></tr>

<tr><td width = "150"><div align="right"><label for="description">Description</label></div></td>

<td><input id="description" name="description" type="text" size="25" value="" maxlength="50"></td></tr>

<tr><td width="150"></td><td>

<input type="submit" name="submitButtonName" value="Add"></td>

</tr></table></form>

-------------------------------------------------------------------------------------

########### [url removed, login to view]

<a href="[url removed, login to view]">Back to List</a>

<?php

include("[url removed, login to view]");

$title = $_POST['title'];

$role = $_POST['role'];

$date = $_POST['date'];

$time_in = $_POST['time_in'];

$time_out = $_POST['time_out'];

$location = $_POST['location'];

$check_in_with = $_POST['check_in_with'];

$notes = $_POST['notes'];

$parking = $_POST['parking'];

$cab_bus = $_POST['cab_bus'];

$other = $_POST['other'];

$description = $_POST['description'];

$query = "INSERT INTO booking (id, title, role, date, time_in, time_out, location, check_in_with, notes, parking,

cab_bus, other, description)

VALUES ('', '$title', '$role', '$date', '$time_in', '$time_out', '$location', '$check_in_with', '$notes', '$parking',

'$cab_bus', '$other', '$description')";

$results = mysql_query($query);

if ($results)

{

echo "Details added.";

}

mysql_close();

?>

-------------------------------------------------------------------------------------

############ [url removed, login to view]

<?php

/// For the following details,

/// please contact your server vendor

$hostname='localhost'; //// specify host, i.e. 'localhost'

$user='****'; //// specify username

$pass='****'; //// specify password

$dbase='dbase'; //// specify database name

$connection = mysql_connect("$hostname" , "$user" , "$pass")

or die ("Can't connect to MySQL");

$db = mysql_select_db($dbase , $connection) or die ("Can't select database.");

?>

---------------------------------------------------------------------------------------

################# [url removed, login to view]

<?php

$id = $_GET['id'];?>

<div align="center">

<h2>Are you sure?</h2>

<h2><a href="[url removed, login to view]<?php echo "$id" ?>">Yes</a> - <a href="[url removed, login to view]">No</a></h2>

</div>

---------------------------------------------------------------------------------------

################# [url removed, login to view]

<a href="[url removed, login to view]">Back to List</a><br>

<br>

<?php

include("[url removed, login to view]");

$id = $_GET['id'];

$delete = "DELETE FROM booking WHERE id='$id' ";

mysql_query($delete);

mysql_close();

echo "Entry deleted";

---------------------------------------------------------------------------------------

################# [url removed, login to view]

<a href="[url removed, login to view]">Add entry</a><br>

<br>

<?php

include("[url removed, login to view]");

$query="SELECT * FROM booking ";

$result=mysql_query($query);

$num = mysql_num_rows ($result);

mysql_close();

if ($num > 0 ) {

$i=0;

while ($i < $num) {

$title = mysql_result($result,$i,"title");

$role = mysql_result($result,$i,"role");

$date = mysql_result($result,$i,"date");

$time_in = mysql_result($result,$i,"time_in");

$time_out = mysql_result($result,$i,"time_out");

$location = mysql_result($result,$i,"location");

$check_in_with = mysql_result($result,$i,"check_in_with");

$notes = mysql_result($result,$i,"notes");

$parking = mysql_result($result,$i,"parking");

$cab_bus = mysql_result($result,$i,"cab_bus");

$other = mysql_result($result,$i,"other");

$description = mysql_result($result,$i,"description");

$id = mysql_result($result,$i,"id");

echo "<b>Title:</b> $title<br>";

echo "<b>Role:</b> $role<br>";

echo "<b>Date:</b> $date<br>";

echo "<b>Time In:</b> $time_in<br>";

echo "<b>Time Out:</b> $time_out<br>";

echo "<b>Location:</b> $location<br>";

echo "<b>Check In With:</b> $check_in_with<br>";

echo "<b>Notes:</b> $notes<br>";

echo "<b>Parking:</b> $parking<br>";

echo "<b>Cab bus:</b> $cab_bus<br>";

echo "<b>Other:</b> $other<br>";

echo "<b>Description:</b> $description<br>";

echo "<a href=\"[url removed, login to view]$id\">Update</a> - <a href=\"[url removed, login to view]$id\">Delete</a>";

echo "<br><br>";

++$i; } } else { echo "The database is empty"; }?>

---------------------------------------------------------------------------------------

################# [url removed, login to view]

<?php

include("[url removed, login to view]");

$id = $_GET['id'];

$qProfile = "SELECT * FROM booking WHERE id='$id' ";

$rsProfile = mysql_query($qProfile);

$row = mysql_fetch_array($rsProfile);

extract($row);

$title = stripslashes($title);

$role = stripslashes($role);

$date = stripslashes($date);

$time_in = stripslashes($time_in);

$time_out = stripslashes($time_out);

$location = stripslashes($location);

$check_in_with = stripslashes($check_in_with);

$notes = stripslashes($notes);

$parking = stripslashes($parking);

$cab_bus = stripslashes($cab_bus);

$other = stripslashes($other);

$description = stripslashes($description);

mysql_close();

?>

<form id="FormName" action="[url removed, login to view]" method="post" name="FormName">

<table width="448" border="0" cellspacing="2" cellpadding="0">

<tr><td width="150"><div align="right">

<label for="title">Title</label></div>

</td>

<td>

<input id="title" name="title" type="text" size="25" value="<?php echo $title ?>" maxlength="50"></td>

</tr>

<tr><td width="150"><div align="right">

<label for="role">Role</label></div>

</td>

<td>

<input id="role" name="role" type="text" size="25" value="<?php echo $role ?>" maxlength="50"></td>

</tr>

<tr><td width="150"><div align="right">

<label for="date">Date</label></div>

</td>

<td>

<input id="date" name="date" type="text" size="25" value="<?php echo $date ?>" maxlength="15"></td>

</tr>

<tr><td width="150"><div align="right">

<label for="time_in">Time In</label></div>

</td>

<td>

<input id="time_in" name="time_in" type="text" size="25" value="<?php echo $time_in ?>" maxlength="15"></td>

</tr>

<tr><td width="150"><div align="right">

<label for="time_out">Time Out</label></div>

</td>

<td>

<input id="time_out" name="time_out" type="text" size="25" value="<?php echo $time_out ?>" maxlength="15"></td>

</tr>

<tr><td width="150"><div align="right">

<label for="location">Location</label></div>

</td>

<td>

<input id="location" name="location" type="text" size="25" value="<?php echo $location ?>" maxlength="100"></td>

</tr>

<tr><td width="150"><div align="right">

<label for="check_in_with">Check In With</label></div>

</td>

<td>

<input id="check_in_with" name="check_in_with" type="text" size="25" value="<?php echo $check_in_with ?>" maxlength="50">

</td>

</tr>

<tr><td width="150"><div align="right">

<label for="notes">Notes</label></div>

</td>

<td>

<textarea id="notes" name="notes" rows="4" cols="40"><?php echo $notes ?></textarea></td>

</tr>

<tr><td width="150"><div align="right">

<label for="parking">Parking</label></div>

</td>

<td>

<input id="parking" name="parking" type="text" size="25" value="<?php echo $parking ?>" maxlength="15"></td>

</tr>

<tr><td width="150"><div align="right">

<label for="cab_bus">Cab bus</label></div>

</td>

<td>

<input id="cab_bus" name="cab_bus" type="text" size="25" value="<?php echo $cab_bus ?>" maxlength="15"></td>

</tr>

<tr><td width="150"><div align="right">

<label for="other">Other</label></div>

</td>

<td>

<input id="other" name="other" type="text" size="25" value="<?php echo $other ?>" maxlength="15"></td>

</tr>

<tr><td width="150"><div align="right">

<label for="description">Description</label></div>

</td>

<td>

<input id="description" name="description" type="text" size="25" value="<?php echo $description ?>" maxlength="50"></td>

</tr>

<tr>

<td width="150"></td>

<td><input type="submit" name="submitButtonName" value="Update"><input type="hidden" name="id" value="<?php echo $id ?>">

</td>

</tr>

</table>

</form>

---------------------------------------------------------------------------------------

################# [url removed, login to view]

<a href="[url removed, login to view]">Back to List</a><br>

<br>

<?php

include("[url removed, login to view]");

$id = $_POST['id'];

$title = $_POST['title'];

$role = $_POST['role'];

$date = $_POST['date'];

$time_in = $_POST['time_in'];

$time_out = $_POST['time_out'];

$location = $_POST['location'];

$check_in_with = $_POST['check_in_with'];

$notes = $_POST['notes'];

$parking = $_POST['parking'];

$cab_bus = $_POST['cab_bus'];

$other = $_POST['other'];

$description = $_POST['description'];

$update = "UPDATE booking SET title = '$title', role = '$role', date = '$date', time_in

= '$time_in', time_out = '$time_out', location = '$location', check_in_with = '$check_in_with', notes

= '$notes', parking = '$parking', cab_bus = '$cab_bus', other = '$other', description = '$description'

WHERE id='$id' ";

$rsUpdate = mysql_query($update);

if ($rsUpdate)

{

echo "Update successful.";

} mysql_close();

?>

---------------------------------------------------------------------------------------

####### MySql Database

CREATE TABLE `booking` (

`id` int(6) NOT NULL auto_increment,

`title` varchar(50) NOT NULL default '',

`role` varchar(50) NOT NULL default '',

`date` varchar(15) NOT NULL default '',

`time_in` varchar(15) NOT NULL default '',

`time_out` varchar(15) NOT NULL default '',

`location` varchar(100) NOT NULL default '',

`check_in_with` varchar(50) NOT NULL default '',

`notes` text NOT NULL default '',

`parking` varchar(15) NOT NULL default '',

`cab_bus` varchar(15) NOT NULL default '',

`other` varchar(15) NOT NULL default '',

`description` varchar(50) NOT NULL default '',

PRIMARY KEY (`id`),

UNIQUE KEY `id` (`id`)

) TYPE=MyISAM AUTO_INCREMENT=1 ;

CREATE TABLE `contacts` (

`id` int(6) NOT NULL auto_increment,

`work_date` varchar(15) NOT NULL default '',

`project` varchar(50) NOT NULL default '',

`name` varchar(50) NOT NULL default '',

`their_position` varchar(50) NOT NULL default '',

`photo_upload` varchar(100) NOT NULL default '',

`notes` text NOT NULL default '',

PRIMARY KEY (`id`),

UNIQUE KEY `id` (`id`)

) TYPE=MyISAM AUTO_INCREMENT=1 ;

CREATE TABLE `expenses` (

`id` int(6) NOT NULL auto_increment,

`date` varchar(15) NOT NULL default '',

`description` varchar(50) NOT NULL default '',

`type` varchar(25) NOT NULL default '',

`amount` varchar(15) NOT NULL default '',

`notes` text NOT NULL default '',

PRIMARY KEY (`id`),

UNIQUE KEY `id` (`id`)

) TYPE=MyISAM AUTO_INCREMENT=1 ;

CREATE TABLE `mileage` (

`id` int(6) NOT NULL auto_increment,

`work_date` varchar(15) NOT NULL default '',

`project` varchar(50) NOT NULL default '',

`mileage_start` varchar(15) NOT NULL default '',

`mileage_stop` varchar(15) NOT NULL default '',

`total_miles_driven` varchar(15) NOT NULL default '',

`notes` text NOT NULL default '',

PRIMARY KEY (`id`),

UNIQUE KEY `id` (`id`)

) TYPE=MyISAM AUTO_INCREMENT=1 ;

CREATE TABLE `payroll` (

`id` int(6) NOT NULL auto_increment,

`date_worked` varchar(15) NOT NULL default '',

`project` varchar(50) NOT NULL default '',

`payroll_company` varchar(15) NOT NULL default '',

`date_paid` varchar(15) NOT NULL default '',

`gross_paid` varchar(15) NOT NULL default '',

`net_paid` varchar(15) NOT NULL default '',

PRIMARY KEY (`id`),

UNIQUE KEY `id` (`id`)

) TYPE=MyISAM AUTO_INCREMENT=1 ;

CREATE TABLE `userdetails` (

`id` int(6) NOT NULL auto_increment,

`username` varchar(100) NOT NULL default '',

`password` varchar(50) NOT NULL default '',

`first_name` varchar(50) NOT NULL default '',

`last_name` varchar(50) NOT NULL default '',

`address` varchar(100) NOT NULL default '',

`city_state` varchar(50) NOT NULL default'',

`zipcode` varchar(25) NOT NULL default '',

`phone` varchar(25) NOT NULL default '',

`email` varchar(50) NOT NULL default '',

PRIMARY KEY (`id`),

UNIQUE KEY `id` (`id`)

) TYPE=MyISAM AUTO_INCREMENT=1 ;

Data Entry Engineering MySQL PHP Project Management Software Architecture Software Testing

Project ID: #3107625

About the project

21 proposals Remote project Active Jan 21, 2010

Awarded to:

trustyd

See private message.

$20.41 USD in 14 days
(51 Reviews)
4.0

21 freelancers are bidding on average $74 for this job

fleetways

See private message.

$255 USD in 14 days
(68 Reviews)
7.6
rabbul

See private message.

$15.3 USD in 14 days
(293 Reviews)
7.5
newgroup4u

See private message.

$106.25 USD in 14 days
(120 Reviews)
7.1
stevebryant

See private message.

$55.25 USD in 14 days
(483 Reviews)
6.6
ROWS

See private message.

$65.45 USD in 14 days
(170 Reviews)
6.6
dvfabia

See private message.

$85 USD in 14 days
(255 Reviews)
6.3
shobhitsingh1

See private message.

$42.5 USD in 14 days
(23 Reviews)
4.5
smackcodersvw

See private message.

$17 USD in 14 days
(8 Reviews)
4.3
marysson

See private message.

$42.5 USD in 14 days
(15 Reviews)
4.0
shahramjaved0075

See private message.

$76.5 USD in 14 days
(12 Reviews)
3.5
ricardassl

See private message.

$5.95 USD in 14 days
(29 Reviews)
3.2
d4rjuss

See private message.

$17 USD in 14 days
(5 Reviews)
3.0
kanhashriinc

See private message.

$65.45 USD in 14 days
(8 Reviews)
2.8
burpicane

See private message.

$12.75 USD in 14 days
(13 Reviews)
2.4
craftmansl

See private message.

$42.5 USD in 14 days
(3 Reviews)
2.1
krlosvw

See private message.

$17 USD in 14 days
(10 Reviews)
2.0
RobertsMrtn

See private message.

$255 USD in 14 days
(0 Reviews)
0.0
aravindnc

See private message.

$17 USD in 14 days
(0 Reviews)
0.0
samiaafshin

See private message.

$85 USD in 14 days
(0 Reviews)
0.0
aldocastillo

See private message.

$255 USD in 14 days
(0 Reviews)
0.0