Solved

Mssql and mysqli

Posted on 2010-09-13
19
546 Views
Last Modified: 2013-12-13
I am trying to connect to ms sql server to retrieve data and then insert into mysql.
However, for some reason, I am getting double the amount of insert into mysql.  Instead of 37 records, there are 74 records.  It seem like it is going through the while loop twice.  What is going on?  

//connect to mssql
$link = mssql_connect('localhost','user','password');
mssql_select_db('mssql_develop');

$sql = "select * from table1";
$query = mssql_query($sql);

$mysqli = new mysqli('testjhost', 'tester', 'password!', 'customer');

while ($result = mssql_fetch_array($query))
{
   $insert = "insert into table10 (...) value (...)";
   $mysqli->query($insert);
}
0
Comment
Question by:bigjdve
  • 6
  • 6
  • 5
  • +1
19 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 33664333
just to make sure you did start with an empty table?
have you confirmed this was not run twice (maybe add some diagnostics to say what record is being inserted as well as a diagnostic to say you have entered and exited the method)
perhaps add indexes on so that dup records do not get created
0
 
LVL 3

Author Comment

by:bigjdve
ID: 33664402
rockiroad:

yes, i double and triple check that the table has been emptied.
I even did a count so if that when it reach the number of record (38), it is suppose to die("stop!").
But it never get there.  Yet, when I check the database, there are 74 records.  
I even try to unset the result:

$sqli_result = $mysqli->query($insert);
unset($sqli_result);

0
 
LVL 3

Expert Comment

by:cconstable_work
ID: 33664457
Perhaps you have a page redirect that is running it again?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33664823
I think you want mssql_fetch_row here:
while ($result = mssql_fetch_row($query))

{

   $insert = "insert into table10 (...) value (...)";

   $mysqli->query($insert);

}

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33664865
Never mind on my last comment.  I was use to seeing the fetch_array syntax with a second parameter that I forget it is optional and does the same thing really as fetch_row just with a little more benefit.

The issue could be a trigger or something in your tables that is causing the rows to be inserted twice.

Something else to try is the using mysql_query and see if same results:
http://php.net/manual/en/function.mysql-query.php

Can also do a standard insert from MySQL Workbench or command line and see if you get double rows then also.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33664988
just a thought if you run your loop without doing the insert, how many times does it iterate?

echo "In Populate Method"'
$counter = 0;
while ($result = mssql_fetch_array($query))
{
   $insert = "insert into table10 (...) value (...)";
   $counter = $counter + 1
   echo $counter . $insert
}

echo "Out Populate Method";
echo "Total Count " . $counter



might need to correct the syntax a little. This is just a diagnostic test so run your code and check the output. This is to confirm if its called twice or not
0
 
LVL 3

Author Comment

by:bigjdve
ID: 33665162
I have not create a trigger for this at all.  Does anyone know if there is a setting on the server or configure file that will cause it to insert twice.

I create a test page in which i connect to mysql.
Create and execute multiple queries.  Instead of 2 records, it is four.

See code below:

$mysqli = @new mysqli('localhost','usr','pass','dbname');

$sql = "insert into table (...) values (...)";
$sql .= "insert into table (...) values (...)";

$mysqli->multi_query($sql);

However, in phpmyadmin, i run these 2 queries, it is only 2 records.

What is going on in my php (web) page that is causing me to double the amount of insert???
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33665283
maybe its the form refresh? when do you run this code?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33665302
check how that code is running.

what was the result of running the diagnostics? you need to see the statements rather than what is in the db.

0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33665526
Agree with rockiroads.  Also try what I indicated and use mysql_query instead of the mysqli extension.
0
 
LVL 3

Author Comment

by:bigjdve
ID: 33665969
How do I test how the code is running?
I did perform the diagnostic test in echoing out the insert statement with a counter.  There are only 27 statements.

I perform other tests (mysql, mysqli) with only 1 query inserting one record.  However, the database will show 2 records.  I am at a lost!!  My web server is using IIS 6 / PHP 5.3 and MySQL.  

See my test.php entire code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Lab Page</title>
</head>

<body>
<?php

$con = mysql_connect('localhost', 'user', 'pass!');      

if (!$con) {
    die('Could not connect: ' . mysql_error());
}
//echo 'Connected successfully';

mysql_select_db('dbname', $con);
$sql = "insert into table (...) values (...)";

$result = mysql_query($sql);

mysql_close($con);


echo "done!";
?>
</body>
</html>
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33666347
strange there is only 27, thought you expected 37 (unless that was a typo)

definitely not a form refresh issue then, wonder if its better to add logging with date/time to a file to ensure anything on the screen doesnt get overwritten so definitely know if its twice or not.

the fact that you ran it with phpadmin and only produced one row leads me to think there is an issue with the web page.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 200 total points
ID: 33666497
Maybe have the page redirect to another page to say "done!"
In other words, process the insert and then redirect to another page -- maybe this will stop reload of page which might be running the insert again.  Going along with rockiroads thoughts here as I agree.
0
 
LVL 3

Author Comment

by:bigjdve
ID: 33666591
Yes, 27 is a typo.

But as you can see from the web page (test.php), there is nothing there that would insert twice.

And my table:

CREATE TABLE `database`.`test` (
`state_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`state_name` VARCHAR( 30 ) NOT NULL
) ENGINE = InnoDB;

$sql = "insert into test (state_name) values ('California')";
mysql_query($sql);

This will insert 2 records of California, with state_id as 1 and 2.
0
 
LVL 3

Author Comment

by:bigjdve
ID: 33666608
How is it reloading the page?  I only click the refresh button or F5 once.
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 200 total points
ID: 33666703
It may be reposting the original data and then processing the page again since the same page that is displaying the content is the one that handled the db action, so I am saying after you submit to db send the user away from the page.  That way if you want to hit the page again it is deliberate.  

By the way, you can test if you click on the address in the browser and hit enter after clearing the table if you only get one set of rows.

Hitting the F5 or refresh on page that was already submitted may be the culprit.
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 50 total points
ID: 33666782
the refresh I was talking abiout, mwvisa1 has given you the explanation. I cant think of anything else that would be causing this.

how does your code get triggered, is it on some form submit?
0
 
LVL 3

Expert Comment

by:cconstable_work
ID: 33667739
Yeah I would make sure you have something that triggers the code such as submitting a form, if the page is hosted with a web hosting provider they could have a number of things you are unaware of that are running.
The test.php you have executes as soon as the page is accessed by anyone or anything, my guess is some feature of the hosting provider is accessing that page when you access it also.
0
 
LVL 3

Author Closing Comment

by:bigjdve
ID: 33672461
It was reposting to the same page so the redirecting to another page work.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now