Mssql and mysqli

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);
}
LVL 3
bigjdveAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
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
 
rockiroadsCommented:
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
 
bigjdveAuthor Commented:
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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
cconstable_workCommented:
Perhaps you have a page redirect that is running it again?
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
rockiroadsCommented:
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
 
bigjdveAuthor Commented:
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
 
rockiroadsCommented:
maybe its the form refresh? when do you run this code?
0
 
rockiroadsCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
Agree with rockiroads.  Also try what I indicated and use mysql_query instead of the mysqli extension.
0
 
bigjdveAuthor Commented:
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
 
rockiroadsCommented:
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
 
bigjdveAuthor Commented:
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
 
bigjdveAuthor Commented:
How is it reloading the page?  I only click the refresh button or F5 once.
0
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
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
 
rockiroadsConnect With a Mentor Commented:
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
 
cconstable_workCommented:
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
 
bigjdveAuthor Commented:
It was reposting to the same page so the redirecting to another page work.
0
All Courses

From novice to tech pro — start learning today.