Solved

mysql database layout from php

Posted on 2013-06-05
3
391 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 250 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 110

Accepted Solution

by:
Ray Paseur earned 250 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

Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

Question has a verified solution.

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

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…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

688 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