mysql while loop is completing tasks twice

[mrsnuff]

Hello,

 

Thanks for looking at my problem.

 

$vendorid = $_GET['cid'];
if (!empty($vendorid)){

include "dbconfig.php";
$query2 = "select * FROM dialler WHERE portalid = '$vendorid' LIMIT 1";
$result2 = mysql_query($query2);
$num2 = mysql_num_rows($result2);
while($row2 = mysql_fetch_array($result2))
{
$sql2  = " INSERT INTO dialler_visited";
$sql2 .= " (portalid, firstname, surnam, number) VALUES ";
$sql2 .= " ('".$row2['portalid']."', '".$row2['firstname']."', '".$row2['surnam']."', '".$row2['number']."') ";
$result2 = mysql_query($sql2);
}

}

 

When I run this code, it puts two entries into my dialler_visited table, even though there is only one entry in the dialler table which will have the $vendorid code.

 

Each entry in the dialler table has a unique $vendorid so essentially it should only input one record.

[Barand]

You are resetting $result2 in the last line of code instead of letting it return false at the end of the while loop

 

$result2 = mysql_query($sql2);

 

PS Why have a loop when only 1 row?

Edited January 16, 2013 by Barand

[mrsnuff]

Thank you for your response, Barand.

 

would removing the loop work?

 

$row2 = mysql_fetch_array($result2)
$sql2 = " INSERT INTO dialler_visited";
$sql2 .= " (portalid, firstname, surnam, number) VALUES ";
$sql2 .= " ('".$row2['portalid']."', '".$row2['firstname']."', '".$row2['surnam']."', '".$row2['number']."') ";

Edited January 16, 2013 by mrsnuff

[Barand]

You could do it in a single query instead of two

 

INSERT INTO dialler_visited
   (portalid, firstname, surnam, number)
SELECT portalid, firstname, surnam, number FROM dialler
WHERE portalid = '$vendorid'

[mrsnuff]

thanks, how could I turn whats stored in the database into variables without a while loop?

 

if i wanted to do something else with them for example.. like echo them.

 

so i've inserted them into the new dialler table, now i want to echo them on the page. how would i get them as variables?

[Barand]

What printed output do you want? There was no mention of any in your original post.

[mrsnuff]

I have put this code now, and it still adds a double record..

 

Surely it has to be some database setting?

 

$vendorid = $_GET['cid'];
if (!empty($vendorid)){

include_once('class.phpmailer.php');
include "dbconfig.php";
$sql1 ="INSERT INTO dialler_visited (portalid, firstname, surnam, number) SELECT portalid, firstname, surnam, number FROM dialler WHERE portalid = ".$vendorid;
if (!mysql_query($sql1))
 {
 die('Error: ' . mysql_error());
 }
echo "1 record added";
}

 

I know I didnt mention in the OP about printing the variables. however it has come to light that I need these variables to be sent in an email as well. So I need them.

[Barand]

How are the tables defined?

 

SHOW CREATE TABLE dialler

 

SHOW CREATE TABLE dialler_visited

[mrsnuff]

CREATE TABLE `dialler` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `portalid` int(100) NOT NULL, `firstname` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `surnam` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `number` varchar(100) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`ID`), KEY `ID` (`ID`)) ENGINE=MyISAM AUTO_INCREMENT=59518 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

 

 

CREATE TABLE `dialler_visited` ( `portalid` int(100) NOT NULL, `firstname` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `surnam` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `number` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1

 

Thanks in advance

[Barand]

Weird table your dialler_revisited! No primary key and all is does is duplicate data from the dialler table. What is its purpose?

 

Also there is no UNIQUE constraint on portalid in the dialler table so nothing to prevent more than one record with the same vendorid, which could account for your getting two records inserted.