[RESOLVED] mysql_query is performing several queries instead of just one
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.
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())):
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.
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.
Last edited by blue-eye-labs; 08-12-2009 at 10:05 AM.
Reason: 6 rows, not 5, are created.
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();
"comment"=>"This is a test for insert()",
echo Html::write("br") . Html::write("br");
I can confirm that the query is only executed once, because I put a global counter in which is increased and then echoed every time insert() is called. I tried the same with query() and got the same result. Now I'm totally confused.
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.
"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation
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)));
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>
[name] => Test
[type] => 1
[category] => fractal
[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.