Need assistance with a contacts database
07-15-2004, 07:17 PM
|
Need assistance with a contacts database
|
Posts: 1
|
I read the PSP tutorial by Dave Gowan. His example is very close to what I need, I have added a few more feilds but what Ireally need is the ability to define a new user to the database and have them login and only have acces to the contacts they enter and noone elses. Here is the code I have:
ADD.HTML
HTML Code:
<form action="insert.php" method="post">
Company: <input type="text" name="Company"><br>
Contact: <input type="text" name="Contact"><br>
Title: <input type="text" name="Title"><br>
Department: <input type="text" name="Department"><br>
Phone: <input type="text" name="Phone"><br>
Mobile: <input type="text" name="mobile"><br>
Fax: <input type="text" name="fax"><br>
Salutaion: <input type="text" name="Salutation"><br>
ID/Status: <input type="text" name="ID/Status"><br>
TickerSymbol: <input type="text" name="TickerSymbol"><br>
E-mail: <input type="text" name="email"><br>
Website: <input type="text" name="website"><br>
Address: <input type="text" name="Address"><br>
City: <input type="text" name="City"><br>
State: <input type="text" name="State"><br>
Zip: <input type="text" name="Zip"><br>
Country: <input type="text" name="Country"><br>
Last Result: <input type="text" name="LastResult"><br>
Notes: <input type="text" name="Notes"><br>
<input type="Submit">
</form>
dbinfo.inc.php
PHP Code:
<?
$username="Admin";
$password="Methu969";
$database="CHAcontacts";
?>
index.php
PHP Code:
<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM contacts";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
echo "<b><center>Database Output</center></b><br><br>";
?>
<table border="0" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Company</font></th>
<th><font face="Arial, Helvetica, sans-serif">Contact</font></th>
<th><font face="Arial, Helvetica, sans-serif">Title</font></th>
<th><font face="Arial, Helvetica, sans-serif">Department</font></th>
<th><font face="Arial, Helvetica, sans-serif">Phone</font></th>
<th><font face="Arial, Helvetica, sans-serif">Mobile</font></th>
<th><font face="Arial, Helvetica, sans-serif">Fax</font></th>
<th><font face="Arial, Helvetica, sans-serif">Salutation</font></th>
<th><font face="Arial, Helvetica, sans-serif">ID/Status</font></th>
<th><font face="Arial, Helvetica, sans-serif">TickerSymbol</font></th>
<th><font face="Arial, Helvetica, sans-serif">E-mail</font></th>
<th><font face="Arial, Helvetica, sans-serif">Website</font></th>
<th><font face="Arial, Helvetica, sans-serif">Address</font></th>
<th><font face="Arial, Helvetica, sans-serif">City</font></th>
<th><font face="Arial, Helvetica, sans-serif">State</font></th>
<th><font face="Arial, Helvetica, sans-serif">ZipCOde</font></th>
<th><font face="Arial, Helvetica, sans-serif">Country</font></th>
<th><font face="Arial, Helvetica, sans-serif">LastResult</font></th>
<th><font face="Arial, Helvetica, sans-serif">Notes</font></th>
</tr>
<?
$i=0;
while ($i < $num) {
$Company=mysql_result($result,$i,"Company");
$Contact=mysql_result($result,$i,"Contact");
$Title=mysql_result($result,$i,"Title");
$Department=mysql_result($result,$i,"Department");
$phone=mysql_result($result,$i,"phone");
$mobile=mysql_result($result,$i,"mobile");
$fax=mysql_result($result,$i,"fax");
$Salutation=mysql_result($result,$i,"Salutation");
$ID/Status=mysql_result($result,$i,"ID/Status");
$TickerSymbol=mysql_result($result,$i,"TickerSymbol");
$email=mysql_result($result,$i,"email");
$web=mysql_result($result,$i,"web");
$Address=mysql_result($result,$i,"Address");
$City=mysql_result($result,$i,"City");
$State=mysql_result($result,$i,"State");
$Zip Code=mysql_result($result,$i,"Zip Code");
$Country=mysql_result($result,$i,"Country");
$LastResult=mysql_result($result,$i,"LastResult");
$Notes=mysql_result($result,$i,"Notes");
?>
<tr>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$Company"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$Contact"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$Title"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$Department"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$Phone"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$Mobile"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$Fax"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$Salutation"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$ID/Status"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$TickerSymbol"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><a href="mailto:<? echo "$email"; ?>">E-mail</a></font></td>
<td><font face="Arial, Helvetica, sans-serif"><a href="<? echo "$web"; ?>">Website</a></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$Address"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$City"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$State"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$Zip Code"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$Country"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$LastResult"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$Notes"; ?></font></td>
</tr>
<?
++$i;
}
echo "</table>";
?>
insert.php
PHP Code:
<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($CHAContacts) or die( "Unable to select database");
$query = "INSERT INTO contacts VALUES ('','$Company','$Contact','$Title','$Department','$phone','$mobile','$fax','$Salutation','$ID/Status','$TickerSymbol','$email','$website','$Address','$City','$State','$ZipCode','$Country','$Last Result','$Notes')";
mysql_query($query);
mysql_close();
?>
setup.php
PHP Code:
<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($CHAContacts) or die( "Unable to select database");
$query="CREATE TABLE contacts (id int(6) NOT NULL auto_increment,Company varchar(25) NOT NULL,Contact varchar(20) NOT NULL,Title varchar(20) NOT NULL,Department varchar(20) NOT NULL,Phone varchar(20) NOT NULL,mobile varchar(20) NOT NULL,fax varchar(20) NOT NULL,Salutation varchar(25) NOT NULL,ID/Status varchar(25) NOT NULL,TickerSymbol varchar(10) NOT NULL,email varchar(25) NOT NULL,website varchar(25) NOT NULL,Address varchar(25) NOT NULL,City varchar(20) NOT NULL,State varchar(2) NOT NULL,ZipCode varchar(5) NOT NULL,Country varchar(20) NOT NULL,Last Result varchar(25) NOT NULL,Notes varchar(25) NOT NULL,PRIMARY KEY (id),UNIQUE id (id),KEY id_2 (id))";
mysql_query($query);
mysql_close();
echo "Database created";
?>
update.php
PHP Code:
<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($CHAContacts) or die( "Unable to select database");
$query="SELECT * FROM Company WHERE id='$id'";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
$i=0;
while ($i < $num) {
$Company=mysql_result($result,$i,"Company");
$Contact=mysql_result($result,$i,"Contact");
$Title=mysql_result($result,$i,"Title");
$Department=mysql_result($result,$i,"Department");
$phone=mysql_result($result,$i,"phone");
$mobile=mysql_result($result,$i,"mobile");
$fax=mysql_result($result,$i,"fax");
$Salutation=mysql_result($result,$i,"Salutation");
$ID/Status=mysql_result($result,$i,"ID/Status");
$TickerSymbol=mysql_result($result,$i,"TickerSymbol");
$email=mysql_result($result,$i,"email");
$web=mysql_result($result,$i,"web");
$Address=mysql_result($result,$i,"Address");
$City=mysql_result($result,$i,"City");
$State=mysql_result($result,$i,"State");
$Zip Code=mysql_result($result,$i,"Zip Code");
$Country=mysql_result($result,$i,"Country");
$LastResult=mysql_result($result,$i,"LastResult");
$Notes=mysql_result($result,$i,"Notes");
?>
<form action="updated.php">
<input type="hidden" name="ud_id" value="<? echo "$id"; ?>">
Company: <input type="text" name="ud_Company" value="<? echo "$Company"?>"><br>
Contact: <input type="text" name="ud_Contact" value="<? echo "$Contact"?>"><br>
Title: <input type="text" name="ud_Title" value="<? echo "$Title"?>"><br>
Department: <input type="text" name="ud_Department" value="<? echo "$Department"?>"><br>
Phone: <input type="text" name="ud_phone" value="<? echo "$phone"?>"><br>
Mobile Number: <input type="text" name="ud_mobile" value="<? echo "$mobile"?>"><br>
Fax Number: <input type="text" name="ud_fax" value="<? echo "$fax"?>"><br>
Salutation: <input type="text" name="ud_Salutation" value="<? echo "$Salutation"?>"><br>
ID/Status: <input type="text" name="ud_ID/Status" value="<? echo "$ID/Status"?>"><br>
Ticker Symbol: <input type="text" name="ud_TickerSymbol" value="<? echo "$TickerSymbol"?>"><br>
E-mail Address: <input type="text" name="ud_email" value="<? echo "$email"?>"><br>
Web Address: <input type="text" name="ud_web" value="<? echo "$web"?>"><br>
Address: <input type="text" name="ud_Address" value="<? echo "$Address"?>"><br>
City: <input type="text" name="ud_city" value="<? echo "$city"?>"><br>
State: <input type="text" name="ud_state" value="<? echo "$state"?>"><br>
Zip Code: <input type="text" name="ud_Zipcode" value="<? echo "$Zipcode"?>"><br>
Country: <input type="text" name="ud_Country" value="<? echo "$Country"?>"><br>
Last Result: <input type="text" name="ud_lastresult" value="<? echo "$lastresult"?>"><br>
Notes: <input type="text" name="ud_notes" value="<? echo "$notes"?>"><br>
<input type="Submit" value="Update">
</form>
<?
++$i;
}
?>
updated.php
PHP Code:
<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
$query="UPDATE contacts SET company='$ud_company', contact='$ud_contact', title='$ud_title',department='$ud_department', phone='$ud_phone',mobile='$ud_mobile',fax='$ud_fax',salutation='$ud_salutation',id/status='$ud_id/status',tickersymbol='$ud_tickersymbol',email='$ud_email',website='$ud_website', address='$ud_address', city='$ud_city', state='$ud_state',zipcode='$ud_zipcode',country='$ud_country',lastresult='$ud_lastresult',notes='$ud_notes', WHERE id='$ud_id'";
@mysql_select_db($CHAContacts) or die( "Unable to select database");
mysql_query($query);
echo "Record Updated";
mysql_close();
?>
Also I need to know some general info on making this work on a web page via my ISP that host a PHP/MySQL server.
Thanks alot!!! 
Last edited by Kyrnt : 07-15-2004 at 09:44 PM.
|
|
|
|
|
07-15-2004, 07:38 PM
|
|
Posts: 3,191
|
Could you please put that into bbcode php tags.
|
|
|
|
|
07-15-2004, 09:46 PM
|
|
Posts: 2,536
Location: Western Maryland
|
Quote:
|
Originally Posted by cptnwinky
Could you please put that into bbcode php tags.
|
Done. That is a lot of code.
Methu, I hope you don't mind, but if you're curious about how I got your code to appear like this, read this thread. Most members who want to help you here prefer getting the code like this.
http://www.webmaster-talk.com/showthread.php?t=11768
Regards,
Kyrnt
__________________
—Kyrnt
|
|
|
|
|
07-15-2004, 10:19 PM
|
|
Posts: 3,191
|
Thanks kyrnt...I will take a look at it more in depth tommorow while I'm at work (sad that I spend most of my time on this board while I'm at work).
|
|
|
|
|
07-16-2004, 08:59 AM
|
|
Posts: 3,191
|
Ok I will give you the general what to do first and if you have more questions after looking this up then I will answer those...
Look into sessions and/or cookies for authenticating the users. There are plenty of tutorials for this on hotscripts. Second, if you want to make sure that only contacts for a specific user show up give each user a unique id and assign this to them in a session var or cookie. Then when calling all the records to display from the db just add the clause:
$query="SELECT * FROM contacts WHERE unique_id=".$_SESSION['unique_id']."";
Or something along those lines. I would be careful about using cookies like this. Even sessions can be spoofed, although its less likely but I would do some checks before just putting the session var directly into the query.
|
|
|
|
|
07-16-2004, 11:45 AM
|
|
Posts: 1,314
Location: Glasgow, UK
|
I don't have the code for this but I would do as follows:
Create a table for users with the fields:
userid (int), username (varchar), password (varchar)
and use a simple SELECT to authenticate the user (WHERE username=x and password=y etc.)
then in the contacts database add a new field called owner (int).
Now, set up your system so that if someone is logged in and adds a contact it uses their unique id (from the users table) as the owner of the contact. Then for all your select queries add a WHERE owner=$userid so that only the logged in user's contacts are shown.
There are a few issues needed when dealing with this to ensure that only logged in users can access particular pages. One very simple login/password system I have used is as follows:
Create an authenticate.inc.php file which does the following:
gets username and password from $_POST variable
SELECTs from database to see if they match any user
if it fails, die() the script outputting a login form
otherwise, load the id of the user into a variable for use
Then include this at the top of every page in your system.
Finally, the time consuming bit. For every page transition you must use a form (this could take a bit of changing for things like the edit links, for this just make a form for each record and have a hidden field for the ID. Then get the value on the edit pages from $_POST instead of $_GET).
To all these forms add two hidden fields loaded with the username and password.
This is reasonably secure and should do everything you need.
|
|
|
|
|
« Reply to Need assistance with a contacts database
|
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
|
