Why can't my form create a new db table? [SOLVED]

Hi. Is there a reason why a new db table won't be created?

I have the following code within a form:

	$db =& JFactory::getDBO();
	$query = "
		CREATE TABLE IF NOT EXISTS #__data_compresion_".$anual."_".$mes."
		LIKE #__data_matriz;
		INSERT #__data_compresion_".$anual."_".$mes."
		SELECT * FROM #__data_matriz;
	";
	$db->setQuery($query);
	$db_dup = $db->query();

When I try it, the table is not created. However, if I create the table manually and then just leave the insert part of the code in the form, that part will work fine. Is there any limitation that is preventing CF or Joomla from creating a new db table?

I am using Joomla 2.5.6 and CF 4

David

Hi David,

Do $anual and $mes have values?

What do you see if you echo out the $query string?

Bob

Thanks for replying, GreyHead.

Yes, $anual and $mes have the right values. When I echo $query, I get:

CREATE TABLE IF NOT EXISTS #__data_compresion_2012_12 LIKE #__data_matriz;
INSERT #__data_compresion_2012_12 SELECT * FROM #__data_matriz;

Any ideas?

Hi David,

Nope, nothing obvious :-(

Do you get any error message back in $db_dup?

Ah but possibly the $db->query() method will only run a single query. Try breaking it into two separate queries.

Bob

Nothing new. I tried splitting up the query but no table is created. I get nothing when I echo $db_dup. Is that the right way to see if there is a MySQL error? Not really sure.

Got it to work. For some reason, I had to establish a new db connection [ $db =& JFactory::getDBO(); ]. I added the line before the first query and both queries work now. I'll try merging them into one single query again but it works anyway, so thanks.

For the record. I did need to keep the instructions in two separate queries. I'll say it here in case it can help someone in the future.