Mapear una base de datos PostgreSQL con PHP
Hola que tal a todos, en este articulo les voy a mostrar un script para mapear una base de datos postgresql y obtener las tablas, nombres de las columnas y tipos de las columnas usando PHP.
Este es un ejemplo muy útil, sobre todo si trabajamos en un entorno en el que no tenemos información de las tablas de la base de datos en PostgreSQL.
Codigo
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| $con = pg_connect("dbname=residencia1 host=localhost user=postgres password=123456"); | |
| $q = pg_query("SELECT * FROM pg_tables"); | |
| $not_display = array("pg_statistic","pg_type","pg_attribute","pg_proc","pg_class","pg_attrdef","pg_constraint","pg_index","pg_operator","pg_opfamily","pg_opclass","pg_am","pg_largeobject_metadata","pg_shdescription","pg_collation","pg_amop","pg_amproc","pg_language","pg_aggregate","pg_rewrite","pg_description","pg_cast","pg_conversion","pg_depend","pg_ts_config_map","pg_ts_dict","pg_ts_parser","pg_ts_template","pg_init_privs","pg_pltemplate","pg_ts_config","pg_range","pg_transform","sql_sizing_profiles","sql_sizing","sql_parts","sql_packages","sql_languages","sql_implementation_info","sql_features"); | |
| while($r=pg_fetch_object($q)){ | |
| $tabla = $r->tablename; | |
| if(!in_array($tabla, $not_display)){ | |
| echo "<h3>$tabla</h3>"; | |
| $query = 'select * from '.$tabla; | |
| $result = pg_query($query); | |
| $i = 0; | |
| echo '<table border=1><tr>'; | |
| while ($i < pg_num_fields($result)) | |
| { | |
| $fieldName = pg_field_name($result, $i); | |
| $fieldType = pg_field_type($result, $i); | |
| echo '<td><b>' . $fieldName . '</b> ('.$fieldType.')</td>'; | |
| $i = $i + 1; | |
| } | |
| echo '</tr>'; | |
| $i = 0; | |
| while ($row = pg_fetch_row($result)) | |
| { | |
| echo '<tr>'; | |
| $count = count($row); | |
| $y = 0; | |
| while ($y < $count) | |
| { | |
| $c_row = current($row); | |
| echo '<td>' . $c_row . '</td>'; | |
| next($row); | |
| $y = $y + 1; | |
| } | |
| echo '</tr>'; | |
| $i = $i + 1; | |
| } | |
| pg_free_result($result); | |
| echo '</table>'; | |
| } | |
| } | |
| ?> |
Explicación
En la linea 2 del archivo se encuentra la cadena de conexión, base de datos, host, usuario y password, ahí debemos colocar los valores correspondientes al servidor donde estemos trabajando.
En la linea 6 se encuentra un array, $not_display, este array o arreglo contiene nombres de tablas que no quiero que se muestren en el mapeo, debido a que son tablas generadas automáticamente por el motor de postgresql.