Problems with "show tables from $dbname" query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Problems with "show tables from $dbname" query

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Post your code.
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Post 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.

?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

How are you detecting that only one record is being given to you?
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Post 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.
Post Reply