Page 1 of 1

Problems with "show tables from $dbname" query

Posted: Sun Feb 18, 2007 2:05 pm
by mjseaden
Hi,

I'm trying to return a list of all tables from my database by using the "show tables from <dbname>" query, and fetching the returned rows from the result handle.

However, although the first table is correctly returned, that's it, it doesn't return any of the others.

I'm read in some places that this particular query will only return tables with data in them, however more of the tables in my database have rows in them, and these are not being returned. I don't know if this query returns the tables in alphabetical order, however the table returned by the query is the first table alphabetically.

Bizarrely, if I check the number of rows returned using mysql_num_rows, it returns the correct number of tables in the selected database, however the row fetch returns false after only the first table.

I've spent a while trying to work this out, but it sort of eludes me, as if its going on at a lower level that you don't have access to in PHP.

Any ideas please.

Many thanks

Posted: Sun Feb 18, 2007 4:33 pm
by feyd
Post your code.

Posted: Sun Feb 18, 2007 4:39 pm
by mjseaden

Code: Select all

// Query to obtain all tables
if(!$this->m_database->SQLQuery("show tables from ".LS_DATABASE_NAME))
{
      return false;
}

while( $row = $this->m_database->FetchRow() )
{
       // cycle through tables
}
The functions are taken from my own database class, which i have tested countlessly with other queries that I know works, but here's the functions:

Code: Select all

public function SQLQuery( $query )
	{
		// Obtain the SQL query resultset
		if(!($this->m_result = mysql_query( $query )))
		{
			return false;
		}
		
		return true;
	}

	public function FetchRow()
	{
		// Obtain the next mysql row
		$row = mysql_fetch_row( $this->m_result );
	
		return $row;
	}
Any ideas what's going on? A return of the rows gives 25, which is correctly the number of tables. However, $row returns false after the first row return, and the second table in alphabetical order has data in it.

?

Posted: Sun Feb 18, 2007 4:46 pm
by feyd
How are you detecting that only one record is being given to you?

Posted: Sun Feb 18, 2007 4:51 pm
by mjseaden
Sorry, my mistake. What was happening was I was calling a function in my database class to export each table as a CSV file. This function itself was calling the SQLQuery function, which was messing up the FetchRow function that was cycling through the table names.

I have to get used to using classes in PHP as opposed to C++.

Sorry for the mixup,

Many thanks.