Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

mysql database layout from php

Posted on 2013-06-05
3
Medium Priority
?
394 Views
Last Modified: 2013-06-07
given the shared server remote acess allowed mysql hostname, username, password
could I get the table structure
0
Comment
Question by:rgb192
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 27

Assisted Solution

by:Cornelia Yoder
Cornelia Yoder earned 1000 total points
ID: 39224601
mysql_list_fields()     (retrieves a list of fields from a table)

mysql_list_tables()    (retrieves a list of tables from a database)

are two php functions to retrieve this information from a database.


http://php.net/manual/en/function.mysql-list-fields.php
http://php.net/manual/en/function.mysql-list-tables.php
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 1000 total points
ID: 39225456
For obvious reasons I do not leave this script on my server, but if you install it with your credentials (near line 15) you should be able to run it and see the output from the various exercises.

<?php // RAY_mysql_SHOW_TABLES.php
error_reporting(E_ALL);
echo "<pre>\n"; // READABILITY FOR VAR-DUMPS


// DEMONSTRATE HOW TO GET INFORMATION ABOUT MySQL TABLES AND COLUMNS


// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";


// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
if (!$dbcx = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $dbcx))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    trigger_error('NO DATA BASE', E_USER_ERROR);
}
// IF WE GOT THIS FAR WE CAN DO QUERIES




// FINDING THE NAMES OF OUR TABLES
$sql = "SHOW TABLES";

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
// MAN PAGE:http://php.net/manual/en/function.mysql-query.php
if (!$res = mysql_query($sql, $dbcx))
{
    // MAN PAGE: http://php.net/manual/en/function.mysql-error.php
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}

// GET THE LIST OF TABLE NAMES
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-array.php
while ($show_tables = mysql_fetch_array($res))
{
    $my_tables[] = $show_tables[0];
}
if (empty($my_tables)) die('NO TABLES');
var_dump($my_tables);





// CHOOSE A TABLE AND GET THE COLUMN NAMES
$table_name = $my_tables[0];

$sql = "SHOW COLUMNS FROM $table_name";
if (!$res = mysql_query($sql, $dbcx))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
if (mysql_num_rows($res) == 0)
{
    echo "<br/>$table_name HAS NO COLUMNS";
}
else
{
    // MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
    while ($show_columns = mysql_fetch_assoc($res))
    {
        $my_columns[] = $show_columns["Field"];
        var_dump($show_columns);
    }
    var_dump($my_columns);
}





// CHOOSE A TABLE AND GET THE CREATE TABLE STATEMENT FOR IT
$table_name = $my_tables[0];

$sql = "SHOW CREATE TABLE $table_name";
if (!$res = mysql_query($sql, $dbcx))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
while ($show_creates = mysql_fetch_assoc($res))
{
    $my_creates[] = $show_creates["Create Table"];
}
var_dump($my_creates);






// GET A LIST OF ALL THE DATA BASES ON THIS CONNECTION
// MAN PAGE: http://php.net/manual/en/function.mysql-list-dbs.php
if (!$db_list = mysql_list_dbs($dbcx))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB LIST: ";
   echo "<br/> $errmsg <br/>";
   die();
}
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-object.php
while ($row = mysql_fetch_object($db_list))
{
    $db_names[] = $row->Database ;
}
echo "<br/>LIST OF DB NAMES: ";
var_dump($db_names);

Open in new window

HTH, ~Ray
0
 

Author Closing Comment

by:rgb192
ID: 39229419
thanks for mysql commands and code example
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to implement server side field validation and display customized error messages to the client.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question