inserting unique data in the table by php file

Question

create table member(
member_id int not null auto_increment,
member_name varchar(50) not null,
look_contact_number int(4) default 5,
primary key(member_id)
)

create table contact_viewed(
contact_viewed_id int(11) not null auto_increment,
contacted_member_id int (11) not null,
member_id int(11) not null,
primary key(contact_viewed_id)
);

I want to insert data in the contact_viewed table where member_id can be same, it means one member can see multiple contacts but contacted_member_id should be unique.

I am using request param id by the url see below

/contact_viewed.php?id=1;

First I have taken condition if member_id=2 don't have data in the table member then insert the data in the table, then I have checked by rowCount() method if member_id=2 in member table have data then check contacted_member_id is available in database or not, if available then do nothing, if not available then insert the data into the database.

My problem is when foreach loop is executing then it is inserting data where id != contacted_member_id,if any one have idea so please help me.

<?php
    $stmt7 = $DB_con->prepare ( "SELECT * FROM contact_viewed WHERE member_id=:member_id" );
    $stmt7->bindParam ( ":member_id", $m_id );
    $stmt7->execute ();
    $row2 = $stmt7->fetchAll ();
    if ($stmt7->rowCount () > 0) {
    foreach ( $row2 as $data ) {
            if ($data ['member_id'] == $m_id) {
                if($data ['contacted_member_id'] == $_GET['id']){
                    echo "do nothing1";
                }
                else{
                    $stmt6 = $DB_con->prepare ( "INSERT INTO contact_viewed(contacted_member_id,member_id) VALUES(:contacted_member_id,:member_id)" );
                    $stmt6->bindParam ( ":contacted_member_id", $_GET ['id'] );
                    $stmt6->bindParam ( ":member_id", $m_id );
                    $stmt6->execute ();
                    echo "hello";
                }
            } 
        } 
    }
    else{
        $stmt6 = $DB_con->prepare ( "INSERT INTO contact_viewed(contacted_member_id,member_id) VALUES(:contacted_member_id,:member_id)" );
        $stmt6->bindParam ( ":contacted_member_id", $_GET ['id'] );
        $stmt6->bindParam ( ":member_id", $m_id );
        $stmt6->execute ();
        $available_contact = $userRow ['available_contact'];
        $available_contact = $available_contact - 1;
        $stmt5 = $DB_con->prepare ( "UPDATE member SET available_contact=:available_contact WHERE member_id=:member_id" );
        $stmt5->bindParam ( ":available_contact", $available_contact );
        $stmt5->bindParam ( ":member_id", $m_id );
        $stmt5->execute ();
        echo "ok ok";
    }
    ?>

Just an FYI (probably won't solve your issue), the PDO manual notes that rowCount() is not reliable on SELECT statements and suggests not to use it in that instance. Using a COUNT() statement is preferred for select. — Rasclatt
– Rasclatt, Commented Sep 9, 2015 at 5:21
@Rasclatt thanks for your response,it is very helpful for me,Sir If i will send my code can you please check it once,can I have your email id — Arif Khan
– Arif Khan, Commented Sep 9, 2015 at 5:31
You want me to give you my email so you can send me your code? Is that what you are proposing? — Rasclatt
– Rasclatt, Commented Sep 9, 2015 at 5:34
Sorry, no. I can not review your code. I don't know anything about you or how your hosting is set up. I don't know if you are going to spam me, or what-have-you. Sorry. I can only help you through SO for now. — Rasclatt
– Rasclatt, Commented Sep 9, 2015 at 5:38

Jevgenijs Šulins · Accepted Answer · 2015-09-09 06:12:29Z

1

Try this:

<?php
    $stmt7 = $DB_con->prepare ( "SELECT * FROM contact_viewed WHERE member_id=:member_id AND contacted_member_id=:contacted_member_id" );
    $stmt6->bindParam ( ":contacted_member_id", $_GET ['id'] );
    $stmt7->bindParam ( ":member_id", $m_id );
    $stmt7->execute ();
    $row2 = $stmt7->fetchAll ();
    if ($stmt7->rowCount () == 0) {
        $stmt6 = $DB_con->prepare ( "INSERT INTO contact_viewed(contacted_member_id,member_id) VALUES(:contacted_member_id,:member_id)" );
        $stmt6->bindParam ( ":contacted_member_id", $_GET ['id'] );
        $stmt6->bindParam ( ":member_id", $m_id );
        $stmt6->execute ();
        $available_contact = $userRow ['available_contact'];
        $available_contact = $available_contact - 1;
        $stmt5 = $DB_con->prepare ( "UPDATE member SET available_contact=:available_contact WHERE member_id=:member_id" );
        $stmt5->bindParam ( ":available_contact", $available_contact );
        $stmt5->bindParam ( ":member_id", $m_id );
        $stmt5->execute ();
        echo "ok ok";
    }

answered Sep 9, 2015 at 6:12

Jevgenijs Šulins

6446 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Arif Khan Over a year ago

@Jevgenjis Sulins,Thanks for your response,Thanks it is working as i want

Collectives™ on Stack Overflow

inserting unique data in the table by php file

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related