www.webdeveloper.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 17

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

  1. #1
    Join Date
    Aug 2007
    Location
    London
    Posts
    410

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

    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:
    PHP Code:
    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:
    PHP Code:
    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:

    Code:
    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.
    Last edited by blue-eye-labs; 08-11-2009 at 07:25 PM. Reason: little typo

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    18,918
    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.
    "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

    eBookworm.us

  3. #3
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    18,918
    PS: You could try adding some logging to that function to see where/when it's being called:
    PHP Code:
    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.
    "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

    eBookworm.us

  4. #4
    Join Date
    Aug 2007
    Location
    London
    Posts
    410

    Question

    @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())):

    Code:
    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.

  5. #5
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    18,918
    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. )
    "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

    eBookworm.us

  6. #6
    Join Date
    Aug 2007
    Location
    London
    Posts
    410
    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.

  7. #7
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    18,918
    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.:
    Code:
    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.
    "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

    eBookworm.us

  8. #8
    Join Date
    Aug 2007
    Location
    London
    Posts
    410
    The SQL I have echoed again just to double/triple/dodecatuple check:
    Code:
    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...

  9. #9
    Join Date
    Aug 2007
    Location
    London
    Posts
    410
    That made no difference. I'm so confused.

  10. #10
    Join Date
    Aug 2007
    Location
    London
    Posts
    410
    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)

  11. #11
    Join Date
    Aug 2007
    Location
    London
    Posts
    410
    In case any of this helps I've attached some of the parts. The code calling these functions in index.php is as follows:

    PHP Code:
    $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:
    Code:
    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
    Attached Files Attached Files

  12. #12
    Join Date
    Aug 2007
    Location
    London
    Posts
    410
    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.

  13. #13
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    18,918
    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

    eBookworm.us

  14. #14
    Join Date
    Aug 2009
    Posts
    593
    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?

  15. #15
    Join Date
    Aug 2007
    Location
    London
    Posts
    410
    @ NogDog:
    I tried an isolated test using the following code:
    PHP 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:
    HTML Code:
    <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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles