Jump to content

How to insert & retrieve data from mysql table for a specific user


chr0n1k

Recommended Posts

I am not sure if the title is correct; I tried my best.


I'm a PHP/MySQL beginner and I really need some help.


I have a small script that I am using for sending SMS. I recently added a phonebook. The problem with the phonebook right now is that it's available to all users, i.e. they can all update and delete all rows. What I would like to do is make it so that each user can update and delete only their own contacts.


I have a table call contacts. Inside that table there is first name, last name, company and phonenumber.


How can I accomplish this with PHP & MySQL?



CREATE TABLE IF NOT EXISTS `contacts` (
`contact_id` int(10) NOT NULL AUTO_INCREMENT,
`firstname` varchar(255) NOT NULL,
`lastname` varchar(255) NOT NULL,
`company` varchar(255) NOT NULL,
`cell_no` text NOT NULL,
PRIMARY KEY (`contact_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;




CREATE TABLE IF NOT EXISTS `users` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`users_name` varchar(30) NOT NULL,
`uname` varchar(30) NOT NULL,
`u_pass` varchar(60) NOT NULL,
`utype` varchar(30) NOT NULL,
`timezone` varchar(30) NOT NULL,
`uapi_user` varchar(30) NOT NULL,
`uapi_pass` varchar(60) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;


<?php
if (isset($_POST['submit'])){
//form has been submitted1
$firstname = trim($_POST['firstname']);
$lastname = trim($_POST['lastname']);
$company = trim($_POST['company']);
$cellno = trim($_POST['cell_no']);

if($firstname == ''){
echo '<div class="alert alert-danger">First Name is not Valid!</div>';
exit;
}elseif($lastname == ''){
echo '<div class="alert alert-danger">Last Name is not Valid!</div>';
exit;
}elseif($company == ''){
echo '<div class="alert alert-danger">Company is not Valid!</div>';
exit;
}elseif($cellno == ''){
echo '<div class="alert alert-danger">Cellphone Number is not Valid!</div>';
exit;

}else{

$query = "Select cell_no from contacts where cell_no = '".$cellno."' ";
$result = mysql_query($query);
if (!mysql_num_rows($result)) {

$sql = "INSERT INTO contacts(firstname, lastname, company, cell_no)
values('{$firstname}','{$lastname}', '{$company}', '{$cellno}')";
$result = mysql_query($sql);
confirm_query($result);

//echo '<div class="alert alert-success">Successfully added.</div>';
//exit;
?>
<script type="text/javascript">
window.location = "contact_list.php";
</script>
<?php
}
else{

echo '<div class="alert alert-danger">Username. already exist!.</div>';
echo '<p><a href="new_contact.php" class="btn btn-success"> Back </a></p>';
exit;

}}

}else{
$firstname = "";
$lastname = "";
$company = "";
$cellno = "";
}

?>

Link to comment
Share on other sites

As a user can have multiple contacts, You will need to setup a one to many relationship between your users table and contacts table. To do so you'd add an extra column to your contacts table which be a foreign key for thee user_id field in your users table.

 

If you are unsure about this, then have read through this tutorial on database relationships

http://code.tutsplus.com/articles/sql-for-beginners-part-3-database-relationships--net-8561

To only retrieve the contacts belonging to the user you'll need to first authenticate the user. You should save the authenticated users user_id  into the session when they have logged in. Then you can run a query like this to retrieve their stored contacts

<?php
session_start();

$sql = 'SELECT * FROM contacts WHERE user_id = ' . $_SESSION['user_id']; // retrieve all the contacts belonging to the currently login in user
$result = mysql_query($sql) or trigger_error('DB Error: ' . mysql_error());

if($result && (mysql_num_rows() > 0))
{
    while($row = mysql_fetch_assoc($result))
    {
        // output contact information
        echo '<p>Contact Name: '. $row['firstname'] .', '.  $row['lastname'] .'<br />Company: '. $row['company'] .'Tell No: '. $row['cell_no'] . '</p>';
    }
}
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.