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

[chr0n1k]

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 = "";

}


?>

[Ch0cu3r]

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>';
    }
}