mysql query executing multiple times

[h4rrison.james]

Hey, basically my problem is that the mysql query at the end is posting a multiple number of the rows it should be posting, often 3 or 4 times as many as it should. The extra rows inserted are just repeats of the first group. The weird thing is I am echoing the query to the page just before it is executed, and the query comes out fine, and is only echoed once. Also if I copy/paste the query into phpmyadmin the correct number of rows are added.

 

mysql_select_db("comics", $con);

//Delete all existing entries
$delete = "DELETE FROM lookup_comics WHERE UID = '$session->username'";
mysql_query($delete);

if (!mysql_query($delete,$con))
  {
  die('Error: ' . mysql_error());
  }

//Insert all new entries into table
$sql = "INSERT INTO lookup_comics (UID, CID) VALUES ";
$comics = $_POST["comics"];
foreach ($comics as $cid) {
$sql .= "('$session->username', '$cid'),";
echo "Record Added: ".$session->username.", ".$cid."<br>"; }
$sql = substr($sql, 0, -1);
echo $sql."<br>";
mysql_query($sql);

 

In one example, the echo produced:

INSERT INTO lookup_comics (UID, CID) VALUES ('admin', '1'),('admin', '4'),('admin', '8'),('admin', '9'),('admin', '10')

 

Thanks for your help, I'm thoroughly stumped on this one... :S

[Philip]

So, this query:

INSERT INTO lookup_comics (UID, CID) VALUES ('admin', '1'),('admin', '4'),('admin', '8'),('admin', '9'),('admin', '10')

Is inserting something like this in the database? :

'admin' '1'
'admin' '1'
'admin' '1'
'admin' '4'
'admin' '4'
'admin' '4'
'admin' '8'
'admin' '8'
'admin' '8'
'admin' '9'
'admin' '9'
'admin' '9'
'admin' '10'
'admin' '10'
'admin' '10'

[h4rrison.james]

Only when run from the php file.

If I run that from phpmyadmin it produces just one entry of each number, which is what I want.

I initially thought that the query statement must just be being looped over somewhere, but I can't find anywhere that it is :S

[Philip]

Try running this and see what you get:

mysql_select_db("comics", $con);

//Delete all existing entries
$delete = "DELETE FROM lookup_comics WHERE UID = '$session->username'";
mysql_query($delete);

if (!mysql_query($delete,$con))
  {
  die('Error: ' . mysql_error());
  }

//Insert all new entries into table
$sql = "INSERT INTO lookup_comics (UID, CID) VALUES ";
$comics = $_POST["comics"];
$count = 0; // for debugging
foreach ($comics as $cid) {
$count++; // for debugging
$sql .= "('$session->username', '$cid'),";
echo "Record Added: ".$session->username.", ".$cid."<br>"; 
}
$sql = substr($sql, 0, -1);
echo $sql."<br>";
mysql_query($sql);
echo 'Rows we should have: ',$count,' actual rows inserted: ',mysql_affected_rows(),' and any errors: ',mysql_error(),'<br>';

[h4rrison.james]

"Rows we should have: 5 actual rows inserted: 5 and any errors: "

 

Weird.....It's still posting double entries to the database...

[PFMaBiSmAd]

Temporarily prevent the INSERT query from being executed by commenting out the mysql_query() statement for it. Then submit to that page and then check directly in your database to see if all the rows have been deleted.

 

Either you have existing data that 'looks' like it has the same UID but it does not (has some non-printing character so that it does not match the WHERE clause in the DELETE query) or you have some other code that is also inserting data for the same UID on that page or on a page that is including the posted code.

 

Posting all the code on that page would get the quickest solution. My guess is that you have an additional mysql_query($sql); statement later in that same code.