What is wrong with my function?

Question

I have reproduced this function:

function getTables()
  {
      global $db;

      $value = array();
      if (!($result = $db->query('SHOW TABLES'))) {
          return false;
      }
      while ($row = $db->fetchrow($result)) {
          if (empty($this->tables) or in_array($row[0], $this->tables)) {
              $value[] = $row[0];
          }
      }
      if (!sizeof($value)) {
          $db->error("No tables found in database");
          return false;
      }
      return $value;
  }

in this manner:

public function getTables() {

    $value = array();

    $tables = array();

    $sql = "SHOW TABLES";

    if($stmt = $this->connect->prepare($sql)) {
        $stmt->execute(); 
        while( $row = $stmt->fetch_row() ) {        
            if(empty($tables) or in_array($row[0], $tables)) {
                $value[0] = $row[0];
            }       
        }

        $stmt->close();

        if(!sizeof($value)) {
            echo 'The database has no tables';
        }

        return $value;

    } else {

        echo 'Couldn\t query the database';

    }

}

But the second method returns me The database has no tables which is not true because I have one table in the db.

What is it wrong with the second method ?

In case you wonder what connect does :

public $connect;
public function __construct() {
    // Define The Database Connection Or Die If Failed Connecting
    $this->connect = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME) or die(DB_CONNECTION_ERROR_MESSAGE);
}

It make a connection with the database. And prepare() it's a mysqli statement. I tried with query() too, same result.

Do some debugging. What does your query return? Are there any rows in the result set? — Pekka, Feb 01 '12 at 08:58
Let me check what the query returns. But the result is just `Array`. — Roland, Feb 01 '12 at 09:01
I see in your edit that now connect is included. Try to [check for errors](http://stackoverflow.com/questions/8927486/updating-records-with-prepared-statements-checking-if-update-worked). I delete my previuous answer. — dani herrera, Feb 01 '12 at 09:18

dfsq · Accepted Answer · 2012-02-01T09:15:28.953

1

Correct code. Use query instead of prepare:

public function getTables()
{
    $value = array();
    $tables = array();

    $sql = "SHOW TABLES";

    if ($res = $this->connect->query($sql))
    {
        while ($row = $res->fetch_row())
        {
            if (empty($tables) or in_array($row[0], $tables))
            {
                $value[] = $row[0];
            }
        }

        if (!sizeof($value))
        {
            echo 'The database has no tables';
        }

        return $value;
    }
    else
    {
        echo 'Could not query the database';
    }
}

If you still want to use prepare then you will also need $stmt->bind_result and $stmt->fetch() instead of fetch_row.

edited Feb 01 '12 at 09:15

answered Feb 01 '12 at 09:10

dfsq

191,768
25
236
258

Yes, but what would be the result I'm binding ? – Roland Feb 01 '12 at 09:26
This is what i'm talking. Use `query` and don't worry about that. For such queries `query` is ideal and more convinient. – dfsq Feb 01 '12 at 09:29
It works now, I get `Array ( [0] => users )`. This is what I needed, because now I will iterate through each table and get all the row names and values and output it in a sql file as you would have exported a database table from phpMyAddmin. – Roland Feb 01 '12 at 09:48

score 0 · Answer 2 · edited Feb 01 '12 at 09:24

0

I think this piece of code is broken $value[] = $row[0]; and probably you should change it to $value[0] = $row[0]; or array_push($value, $row[0])

edited Feb 01 '12 at 09:24

Nalaka526

11,278
21
82
116

answered Feb 01 '12 at 09:01

Mike

3,017
1
34
47

What is wrong with my function?

2 Answers2