Closed

Help with PHP/MySql

This project was awarded to trustyd for $20.41 USD.

Get free quotes for a project like this
Employer working
Awarded to:
Project Budget
$30 - $5000 USD
Total Bids
21
Project Description

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 ;

Looking to make some money?

  • Set your budget and the timeframe
  • Outline your proposal
  • Get paid for your work

Hire Freelancers who also bid on this project

    • Forbes
    • The New York Times
    • Time
    • Wall Street Journal
    • Times Online