- Select and print all tables from database
- 2 Answers 2
- Turn on error reporting
- Printing all fields of a database
- My code
- What’s not working
- Debugging
- My question
- 2 Answers 2
- Displaying all tables in a database
- 3 Answers 3
- Displaying all table names in php from MySQL database
- 8 Answers 8
- How to print the whole table in MySQL
- 1 Answer 1
Select and print all tables from database
Any error message? PHP can have a lot of different things going wrong so you’ll need to activate warnings and debug info to find out what’s going on.
If you can, you should stop using mysql_* functions. These extensions have been removed in PHP 7. Learn about prepared statements for PDO and MySQLi and consider using PDO, it’s really not hard.
Add error reporting to the top of your file(s) right after your opening
They wouldn’t know what to do with that Sam, even if it came with an one-step instructional manual. — @JayBlanchard
2 Answers 2
A few things, as with the below changes, it works with data coming back. I faked the logged_in() function by always returning true.
Also, stay away from select * . Spell out the column names. And I suggest you use the column names and not ordinal values upon using them in the result set.
Also, move to mysqli or pdo . Jay gave you links to them above in comments.
$dbname = 'myDB'; $dbuser = 'GuySmiley'; $dbpass = 'anchovies'; $dbhost = 'localhost'; $link = mysql_connect($dbhost, $dbuser, $dbpass) or die("Unable to Connect to '$dbhost'"); mysql_select_db($dbname) or die("Could not open the db '$dbname'"); if(logged_in() === true) < $q=mysql_query("select * from `admin` order by `id` desc"); if($q === FALSE) < die(mysql_error()); // this is for non-production code >echo "Id User Password Permission delete edit "; > echo "
"; > else < echo "you are not logged in
"; > So it outputs your data (and by turning on error reporting, developing code is a lot easier).
Turn on error reporting
Develop with non-deprecated mysql libraries with code wrapped in exception handling
By the way, schema for above test is
create table admin ( id int auto_increment primary key, col1 int not null, col2 int not null, col3 int not null ); insert admin (col1,col2,col3) values (11,111,1111),(22,222,2222); Printing all fields of a database
Just like this one:
My code
'.$row['Tables_in_my_db'].''; $columns = mysqli_query($connessione,"SHOW COLUMNS FROM ".$row['Tables_in_my_db']); while($row2 = mysqli_fetch_array($columns))< echo ''.$row2['Field'].' '; > echo ' '; $query = "SELECT * FROM ".$row['Tables_in_my_db']; $fields = mysqli_query($connessione,$query); echo ''; while($row3 = mysqli_fetch_array($fields))< echo ''.$row3[$num].' '; $temp = $num+1; if($row3[$temp]=="") $num = $temp; > echo '
'; $num = 0; > ?> What’s not working
I get only the first two lines per table as output (the first containing the names of the columns, the second one with some values which aren’t in the same record of database).
Debugging
error_reporting(E_ALL) returns a bunch of Notice: Undefined offset on line 19
My question
2 Answers 2
$connessione = mysqli_connect(); $tables = mysqli_query( $connessione, "SHOW TABLES" ); while( $row = mysqli_fetch_array( $tables ) )< echo '' . $row[ 'Tables_in_my_db' ] . ''; // Show columns $columns = mysqli_query( $connessione, "SHOW COLUMNS FROM ".$row[ 'Tables_in_my_db' ] ); // Here we collect column names to output row values in the same order $columnNames = array(); echo ''; while( $row2 = mysqli_fetch_array( $columns ) )< $columnNames []= $row2[ 'Field' ]; echo '' . $row2[ 'Field' ] . ' '; > echo ' '; // Show table rows $query = "SELECT * FROM " . $row[ 'Tables_in_my_db' ]; $fields = mysqli_query( $connessione, $query ); $maxRows = 10; while( $row3 = mysqli_fetch_array( $fields ) )< echo ''; foreach( $columnNames as $columnName ) < echo '' . $row3[ $columnName ] . ' '; > echo ' '; $maxRows--; if( !$maxRows ) < break; >> echo '
'; > Displaying all tables in a database
How do I display out all the information in a database (All tables and records) using PHP? I read displaying tables as HTML table but how do I do it for all the tables? I tried the example here: http://davidwalsh.name/html-mysql-php But it shows the table names, how do I also display all the values?
3 Answers 3
query("SHOW TABLES"); while ($row = $result->fetch_row()) < $table = $row[0]; echo '', $table, '
'; $result1 = $mysqli->query("SELECT * FROM `$table`"); echo 'query("SHOW COLUMNS FROM `$table`"); echo ''; while ($row3 = $column->fetch_row()) < echo '' . $row3[0] . ' '; > echo ' '; while ($row2 = $result1->fetch_row()) < echo ''; foreach ($row2 as $key => $value) < echo '', $value, ' '; > echo ' '; > echo '
'; > $mysqli->close(); it could be that aandroidtest did not enable the following inside php.ini: extension=php_mysqli.dll and i also recommend enabling the pdo extension
you can use the recursive function to display the tree Structure of your database.
If you want to pull all tables with the values in mysql , you can use the following code:
echo $table[0]; $query = "SELECT * FROM ".$table[0]; //prints out tables name $result = mysql_query($query); PullValues($result); //call function to get all values > //function to pull all values from tables function PullValues($result) < if($result == 0) < echo "Error ".mysql_errno().": ".mysql_error().""; > elseif (@mysql_num_rows($result) == 0) < echo("Query completed. No results returned.
"); > else < echo " [Num] "; for($i = 0;$i < mysql_num_fields($result);$i++) < echo "" . $i . " - " . mysql_field_name($result, $i) . " "; > echo "[$i] "; $row = mysql_fetch_row($result); for($j = 0;$j < mysql_num_fields($result);$j++) < echo("" . $row[$j] . " "); > echo "
"; > //end else > ?> I am using this and works fine in mysql , for mysqli you need to tweak it a very little.
Displaying all table names in php from MySQL database
Alright, so I’m fairly new to PHP and SQL/MySQL so any help is appreciated. I feel like I took the right approach. I searched php.net for «MySQL show all table names», it returned a deprecated method and suggested using a MySQL query on SHOW TABLES [FROM db_name] [LIKE ‘pattern’] I’m not sure what «pattern» means but, I searched for «SQL Wildcard» and got the «%» symbol. According to everything I found, this should work and output the table names at the end, but it does not. Any suggestions? Thanks in advance.
'; echo 'These are your tables:'; echo '
'; $link = mysql_connect("sql2.njit.edu", "username", "password"); mysql_select_db("db_name") or die(mysql_error()); $result = mysql_query('SHOW TABLES [FROM db_name] [LIKE '%']'); echo $result; > else echo 'You did not provide the proper authentication'; ?> 8 Answers 8
The square brackets in your code are used in the mysql documentation to indicate groups of optional parameters. They should not be in the actual query.
The only command you actually need is:
If you want tables from a specific database, let’s say the database «books», then it would be
You only need the LIKE part if you want to find tables whose names match a certain pattern. e.g.,
show tables from books like '%book%'; would show you the names of tables that have «book» somewhere in the name.
Furthermore, just running the «show tables» query will not produce any output that you can see. SQL answers the query and then passes it to PHP, but you need to tell PHP to echo it to the page.
Since it sounds like you’re very new to SQL, I’d recommend running the mysql client from the command line (or using phpmyadmin, if it’s installed on your system). That way you can see the results of various queries without having to go through PHP’s functions for sending queries and receiving results.
If you have to use PHP, here’s a very simple demonstration. Try this code after connecting to your database:
$result = mysql_query("show tables"); // run the query and assign the result to $result while($table = mysql_fetch_array($result)) < // go through each row that was returned in $result echo($table[0] . ""); // print the table that was returned on that row. > How to print the whole table in MySQL
Currently I have this code that shows all the table in MySQL database but when trying to print it’s not printing the whole table but showing only one row please helpme anybody cause it’s in while loop
else < header("location:index"); >/* Do not edit */ ?>
student id student name kannada english hindi maths science social result exam added on
$sql = 'SELECT * FROM result'; mysql_select_db('sarvajna_users'); $retval = mysql_query( $sql, $conn ); if(! $retval ) < die('Could not get data: ' . mysql_error()); >while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) < ?>mysql_close($conn); ?> 1 Answer 1
You should move your table tag outside the while loop as Niranjan said.
This question is in a collective: a subcommunity defined by tags with relevant content and experts.
