Solved

Mssql and mysqli

Posted on 2010-09-13
19
558 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

863 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

29 Experts available now in Live!

Get 1:1 Help Now