[SOLVED] Problems with MySQL INSERT, UPDATE, and DELETE

[jayred]

I'm working on a bunch of new components for my website and many require using MySQL INSERT, UPDATE, and DELETE commands. (I'm on Joomla 1.0.13 BTW). Right now, any queries that are SELECT work just fine. However, whenever I go to any of the above types, the query doesn't seem to execute, but there are no errors generated. Additionally, when I echo out the query statement and copy and paste it directly into my PHPMyAdmin, it executes just fine.

Any ideas what the problem might be? Let me know if you need code examples.

[pe7er]

Joomla's default components do work okay?
e.g. Can you create (Insert record), Modify (Update record) or Delete (Delete record) an article?

Could you compare your code with code used in Joomla core components?

[jayred]

Yes, all of the normal Joomla components work just fine. I can add, edit, and delete articles.

I also took a look at the code for the users core component, since I'm inserting / updating / deleting things in the users, core_acl, and contact_details tables, and double-checked that my code was the same syntax as the core code. And it definitely is.

Any ideas where to go from here?

[pe7er]

I also took a look at the code for the users core component, since I'm inserting / updating / deleting things in the users, core_acl, and contact_details tables, and double-checked that my code was the same syntax as the core code.

Are you trying to insert/update/delete from multiple tables at the same time?

You can use select + join to retrieve info from multiple tables at the same time,
but I am not sure that you can do that with insert/update/delete.
(Note: maybe phpMyAdmin handles the requests differently, so there it might be possible)

[ircmaxell]

How are you executing the query? 

Code: Select all

$sql = "DELETE blah FROM blah";
$database->setQuery($sql);
if(!$database->query()) echo $database->getErrorMsg();

[jayred]

@pe7er:

No, I'm just trying to do one table.

@ircmaxwell:

Yep, that's how I'm executing the query and it's not throwing any errors.

Here's some of my code, in case it helps.

Code: Select all

global $database;

      $counter = 0;

      for( $i = 0; $i<count($positions); $i++ ){

         if( $positions[$i] != '' ){
            $query = "UPDATE #__contact_details"
            . "\n SET con_position = '{$positions[$i]}'"
            . "\n WHERE id = '{$cid[$counter]}'";
            $database->setQuery($query);
            if(!$database->query()) echo $database->getErrorMsg() . "<br/>";
            echo "$query <br/>";
            $counter++;
         }
      }

Where $cid is an array of contactIDs and $positions is an array of positions (duh). The echo produces the following for one example:

Code: Select all

UPDATE #__contact_details SET con_position = 'Please work' WHERE id = '194'

[jayred]

I found the problem. Prior to ircmaxwell's comment with the code sample that included the if(!$database->query()), I did not have that line in my code. I was under the impression that the $database->setQuery($query) command performed the query since it did for "SELECT" statements (this is supposed to happen, right?). Well unfortunately, even after reviewing the documentation for the $database class in the Joomla docs, it still wasn't clear that I had to execute the query using $database->query().

Apparently, when I added that if(...) statement before making my last post with code sample, it worked, but somehow I missed that in fact it had and I thought it still didn't work.

So, to conclude, here's what I determined.

1. If performing a SELECT query, simply use $database->setQuery().

2. If performing an INSERT, UPDATE, or DELETE query, use $database->setQuery() followed by some form of $database->query()

Thank you both for your help. 

[pe7er]

Great, thanks for your follow-up with your solution!

Could you please mark your first message in this thread as "solved" ? (Modify the first message and choose the Message Icon [solved] ).
This way the list of messages shows that the question has been solved, and other users can benefit from it.
Thanks!

[ircmaxell]

Actually, the way it works is that when you exectue a Select statement, one of the methods to load the data (like $database->loadObjectList()) actually executes $database->query()...  So if you're not loading the results, you need to exectue it manually...

[jayred]

Thanks for that explanation ircmaxwell. That's very clear and helpful. Thanks again for all of your help.