bigjdve
asked on
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',
mssql_select_db('mssql_dev
$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);
}
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',
mssql_select_db('mssql_dev
$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);
}
ASKER
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);
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);
Perhaps you have a page redirect that is running it again?
I think you want mssql_fetch_row here:
while ($result = mssql_fetch_row($query))
{
$insert = "insert into table10 (...) value (...)";
$mysqli->query($insert);
}
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.
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.
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
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
ASKER
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','
$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???
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','
$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???
maybe its the form refresh? when do you run this code?
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.
what was the result of running the diagnostics? you need to see the statements rather than what is in the db.
Agree with rockiroads. Also try what I indicated and use mysql_query instead of the mysqli extension.
ASKER
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',
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>
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',
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>
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
How is it reloading the page? I only click the refresh button or F5 once.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
It was reposting to the same page so the redirecting to another page work.
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