[RESOLVED] mysql_query is performing several queries instead of just one

[blue-eye-labs]

Hi all,

I have been writing my website and have ended up starting a sort of MVC (model-view-controller) framework, which has made lots of sense to write.
The problem I have is in my basic model. When I want it to insert a new record, instead of inserting one row, it inserts about five, all with the same properties (except the IDs, which is dealt with by MySQL itself). I checked the SQL query, and it is correct. I tested it by writing it directly into PHPMyAdmin and it only inserted one row.

The model's method looks like this:

function insert() {
        $sql = "INSERT INTO " . $this->table . "
        (";
        foreach($this->fields as $attr) {
            if(!$this->blacklisted($attr) && $attr != $this->primaryKey) {
                $sql .= $attr . ", ";
            }
        }
        $sql = substr_replace($sql, "", -2);
        $sql .= ") VALUES (";
        foreach($this->fields as $attr) {
            if(!$this->blacklisted($attr) && $attr != $this->primaryKey) {
                $sql .= "'" . $this->{$attr} . "', ";
            }
        }
        $sql = substr_replace($sql, "", -2);
        $sql .= ")";
        
        $this->con->query($sql);
    } 

The connector class for the database has a query() method which looks as follows:

function query($sql = null) {
        $sql = str_replace(array("\"", "\\"), array("'", ""), $sql);
        $this->result = mysql_query($sql) or $this->error("Unable to query database: " . mysql_error($this->con) . ".");
        return $this->result;
    } 

The SQL produced (using die($sql)) looks like this:

INSERT INTO artworks (name, date, modified, type, category, collection, comment, public, keywords) VALUES ('test1', '2009-08-12', '2009-08-12', '1', '', '', 'This is a test.', '', 'test, first')

This really is quite confusing, any help would be much appreciated.

[NogDog]

I don't see anything in the code provided, so my suspicion would be that the repetition is somewhere else, i.e. something calling the insert() function multiple times.

[NogDog]

PS: You could try adding some logging to that function to see where/when it's being called:

function insert()
{
   error_log(date('Y-m-d H:i:s').":\n".print_r(debug_backtrace(), 1));
   // rest of function...
} 

Don't forget to comment it out once you're finished debugging.

[blue-eye-labs]

@NogDog:

I've done a little bit of backtracing (but I didn't know of this function :S) and it didn't seem to suggest that the function was being called multiple times. The backtrace gave me this (I just used print_r(debug_backtrace())):

Array ( [0] => Array ( [file] => /Users/gid/Sites/bel/library/modules/php/models/Artwork.php [line] => 20 [function] => insert [class] => Model [type] => -> [args] => Array ( ) ) [1] => Array ( [file] => /Users/gid/Sites/bel/library/modules/php/models/Model.php [line] => 106 [function] => insert [class] => Artwork [type] => -> [args] => Array ( ) ) [2] => Array ( [file] => /Users/gid/Sites/bel/index.php [line] => 39 [function] => save [class] => Model [type] => -> [args] => Array ( ) ) )

which basically seems to be telling me that the function Artwork::save() was called in index.php, which called the Artwork::insert() function, which called the parent's insert() function:
Artwork::save() => Artwork::insert() => Model::insert() =>sql query

This doesn't suggest that the query was executed more than once, which was my finding before.

[NogDog]

When you ran the test that provided that backtrace output, did you still get multiple inserts? (I still don't see anything here that should cause that symptom. )

[blue-eye-labs]

Yes, 6 rows are inserted every time. I can't see anything either. All other queries seem to work: "SELECT...", "DELETE..." et cetera. Only INSERT seems to fail in this way. I've tried inputting the query into mysql using phpmyadmin but the error is not reproducible in this way.
I've had the system echo the sql before a query for each query and it shows that only one insert query is made, yet 5 rows result.

[NogDog]

Outside of some bizarre bug in PHP or MySQL, the only way I can see a single insert query inserting multiple rows is if you actually have multiple value sets in the query, e.g.:

INSERT INTO table (col1, col2, col3) VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9)

But I'm not seeing that in your code, so outside of a complete code walkthrough, I'm afraid I have no idea.

[blue-eye-labs]

The SQL I have echoed again just to double/triple/dodecatuple check:

INSERT INTO artworks (name, date, modified, type, category, collection, comment, public, keywords) VALUES ('Second Test', '2009-08-12', '2009-08-12', '1', 'fractal', '', 'This is a test for insert()', '', 'key, word')

... Only one value set...
I am thinking that maybe it could be to do with using ' instead of " (single quotation marks instead of double). I have had issues with this before and SELECT queries...

[blue-eye-labs]

That made no difference. I'm so confused.

[blue-eye-labs]

mysql_affected_rows shows that only one row is affected, which presumably means that query is being repeated over and over again (well, 6 times)

[blue-eye-labs]

In case any of this helps I've attached some of the parts. The code calling these functions in index.php is as follows:

$ars = new Artwork();
$ars->setup(array(
    "name"=>"Second Test", 
    "comment"=>"This is a test for insert()",
    "keywords"=>"key, word",
    "type"=>1,
    "category"=>"fractal"
));
echo Html::write("br") . Html::write("br");
var_dump($ars->get_values());
echo Html::write("br");

$ars->save();
echo mysql_affected_rows($ars->con->con); 

The output is as follows:

array(10) { ["name"]=> string(11) "Second Test" ["id"]=> NULL ["date"]=> NULL ["modified"]=> NULL ["type"]=> int(1) ["category"]=> string(7) "fractal" ["collection"]=> NULL ["comment"]=> string(27) "This is a test for insert()" ["public"]=> NULL ["keywords"]=> string(9) "key, word" } 
1

[NogDog]

How about making a separate script that does nothing except connect to the DB and do an insert? This way we can at least either eliminate or else narrow it down to something screwy in the PHP mysql_query() or MySQL itself, as opposed to your application code.

I'm thinking that an insert is the only query that, if run multiple times, would easily be noticeable on the DB side. It actually adds records, whereas a update would simply update the same record[s] with the same data, and selects do not change the data at all. So if for some reason there is some gremlin-induced problem in MySQL or the API programs that PHP uses, it would best be detected by a simple, single-file script that does nothing except one insert.

[thraddash]

Just a thought, have you got any stylesheets, script files or images generated through your code that could somehow trigger Inserts to happen in the background?

[blue-eye-labs]

@ NogDog:
I tried an isolated test using the following code:

<?php
# Blue Eye Labs
# Isolated Script Testing Environment
# @/blank_test.php

require_once($_SERVER['DOCUMENT_ROOT'] . "/admin/configuration.php");

Gideon::load_model("Artwork");

?>

<p>Configuration Loaded</p>
<p>Model: Artwork Loaded</p>
<p>Creating new Artwork with properties:</p>
<ul>
    <li>Name: Test</li>
    <li>Category: fractal</li>
    <li>Type: 1</li>
    <li>Keywords: test, test, test</li>
</ul>

<?php

$con = new Connector();
$con->query("SELECT * FROM artworks");
Html::write("p", array("html"=>"Number of records currently in table: " . mysql_num_rows($con->result)));

$ars = new Artwork();
$ars->setup(array(
    "name"=>"Test",
    "category"=>"fractal",
    "type"=>1,
    "keywords"=>"test, test, test"
));

Html::write("p", array("html"=>print_r($ars->get_values())));

?>

<p>Saving Artwork to Database</p>

<?php

$ars->save();

Html::write("p", array("html"=>"Number of rows affected:" . mysql_affected_rows($ars->con->con)));
$con->query("SELECT * FROM artworks");
Html::write("p", array("html"=>"Number of records currently in table: " . mysql_num_rows($con->result)));
?>

<p>Testing Complete</p>

The output was given as this:

<p>Configuration Loaded</p>
<p>Model: Artwork Loaded</p>
<p>Creating new Artwork with properties:</p>
<ul>
	<li>Name: Test</li>
	<li>Category: fractal</li>
	<li>Type: 1</li>
	<li>Keywords: test, test, test</li>
</ul>

Array
(
    [name] => Test
    [id] => 
    [date] => 
    [modified] => 
    [type] => 1
    [category] => fractal
    [collection] => 
    [comment] => 
    [public] => 
    [keywords] => test, test, test
)

<p>Saving Artwork to Database</p>


<p>Testing Complete</p>

(I need to fix something in my Html::write() method but that is currently irrelevant)

Happily, only one row was inserted, which is interesting, and very good.

@ thraddash:
In the code of index.php, I don't think there is anything that could cause this (but there obviously is!) The stylesheets are not dynamic, merely loaded through the Gideon class. Some includes are dynamic (the header one, specifically). No images are generated with PHP in this page, and the javascript isn't currently loaded I don't think, but when it gets loaded it uses PHP to gather my own library together.

[blue-eye-labs]

Okay... basically, I re-ran the script in index.php just to check, and only one row was inserted. This is after a computer reboot since yesterday, so it must have been a PHP/MySQL glitch. That's so infuriating since I spent ages trying to debug it.
Thanks anyway folks.