query is affecting 4 rows instead of 2

$sql1 = "SELECT remark FROM log";

          $result1 = mysqli_query($db, $sql1);

          while($row1 = mysqli_fetch_assoc($result1))
          {
                $remark=($row1['remark']);

                $word = "success";
                if (stripos($remark,$word)!== false)
                {

                    $ins = mysqli_query($db,'insert into message_sent SELECT p.batch_id,p.phone,c.remark,p.date,user_id FROM to_numbers p INNER JOIN log c ON c.phone = p.phone');

                }
          }


here i'm executing this query when I execute only sql statement it will affect 2 rows it is correct, If i executed above code it is affecting 4 rows so where is going wrong how can i solve this.
shruti AAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Not clear to me. Could you please explain more what you are trying to achieve here?
0
Ryan ChongCommented:
$ins = mysqli_query($db,'insert into message_sent SELECT p.batch_id,p.phone,c.remark,p.date,user_id FROM to_numbers p INNER JOIN log c ON c.phone = p.phone');
you are doing an insertion of record (it could be more than 1 record affected) while it doesn't co-relate to your while loop, hence it could probably inserted more records (and duplicate records).

are you missing to put in "where" condition in your insert statement there?
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shruti AAuthor Commented:
@Pawan Kumar whe i executed

insert into message_sent SELECT p.batch_id,p.phone,c.remark,p.date,user_id FROM to_numbers p INNER JOIN log c ON c.phone = p.phone'
above code in sql on phpmyadmin  it works properly

when i executed with php code it not working properly affecting extra rows
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Ganesh GuruduSenior ConsultantCommented:
this could be the issue with While loop.
remove the insert code and just try to print numbers. you will come to know.

seems there is no issue with SQL statement.
1
Pawan KumarDatabase ExpertCommented:
As far as SQL is concerned this looks fine to me. I request you to check the PHP code, there might be an issue there.
1
shruti AAuthor Commented:
just tried for select statement shown 2 rows it is correct
0
Pawan KumarDatabase ExpertCommented:
If the rows are inserting twice then check your iterative approach.
1
Julian HansenCommented:
When you run this query what do you get

SELECT p.batch_id,p.phone,c.remark,p.date,user_id FROM to_numbers p INNER JOIN log c ON c.phone = p.phone

Open in new window


For your code to work as expected the above should return 2 rows - if it does not then there is your problem.
0
Chris StanyonWebDevCommented:
If this query returns 2 records containing 'success':

SELECT remark FROM log

And this one returns 2 records:

insert into message_sent SELECT p.batch_id,p.phone,c.remark,p.date,user_id FROM to_numbers p INNER JOIN log c ON c.phone = p.phone'

Then you will INSERT 4 records
1
Pawan KumarDatabase ExpertCommented:
Change this line and try again

$sql1 = "SELECT DISTINCT remark FROM log";
0
NerdsOfTechTechnology ScientistCommented:
just tried for select statement shown 2 rows it is correct

which SELECT are you referring to?

SELECT remark FROM log

Open in new window

or
SELECT p.batch_id,p.phone,c.remark,p.date,user_id FROM to_numbers p INNER JOIN log c ON c.phone = p.phone

Open in new window


If the latter, then every INSERT ran, will produce 2 INSERTED records. If there are 2 from the former, then you will end up with 2*2 (4) inserted records.

Based on this, I second Ryan Chong's logic that the SELECT statement within the INSERT statement should likely have a WHERE clause.
0
NerdsOfTechTechnology ScientistCommented:
Presumably, the SELECT within the INSERT has 2 records; thus each INSERT results in n*2 records; in this case, 2*2=4 records are being inserted. Without further information from OP, it is presumed that this logic error has been corrected by these answers.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.