#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Posts
    10
    Rep Power
    0
    I'm looking for a way to display the table names and field names of a mySQL database. I'm trying to write an database admin page. But I want to scale it back so normal users can't mess things up too much.

    Thanks
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Posts
    2
    Rep Power
    0
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    20
    Rep Power
    0
    Check out http://www.php.net/quickref.php
    look under the list of Mysql functions - especially 'mysql_field_name' and 'mysql_tablename'
    also 'mysql_list_fields' and 'mysql_list_tables'
  6. #4
  7. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2000
    Location
    Perth West Australia
    Posts
    757
    Rep Power
    15
    This lists the table names in a select box,and then lists the fields from the selected table in a HTML table,

    You just need to add in the connection and specify $database.

    <?
    mysql_select_db("$database");
    ?>
    <html>
    <body bgcolor="#ffffff">
    <? $result = mysql_list_tables ("$database");$i=0;
    while ($i < mysql_num_rows ($result)) {
    $tb_names[$i] = mysql_tablename ($result, $i);$i++;
    }
    ?>
    <? if(!$submit){$table="<i>not selected</i>";$xnum=0;}?>
    <center><form name=ff method=post action="sql.php3">
    <b>Select table</b>:<select name="table">
    <? for($x=0;$x<$i;$x++){?>
    <option value="<? echo $tb_names[$x];?>"><? echo $tb_names[$x];?></option>
    <? }?>
    </select>
    <input type="submit" name="submit" value="submit">
    </form>
    </center>

    <? if($submit){
    $SQL="SELECT * FROM $table";
    $result = mysql_query($SQL);
    $xnum = mysql_num_fields($result);

    }
    echo "<b><center>$table contains $xnum fields.<br><br></b></center>";?>

    <center>
    <table bgcolor=black><tr>
    <td bgcolor="#e3e3e3">Offset</td><td bgcolor="#e3e3e3">Field Name</td><td bgcolor="#e3e3e3">Field type</td></tr>

    <? for($x=0;$x<$xnum;$x++){$name[$x]=mysql_field_name($result,$x);$type[$x]=mysql_field_type($result,$x);?>
    <tr>
    <td bgcolor="white"><? echo $x;?></td>
    <td bgcolor="white"><? echo $name[$x];?></td>
    <td bgcolor="white"><? echo $type[$x];?></td>
    </tr>
    <? }?>
    </table></center>

    <?
    for($x=0;$x<count($name);$x++){
    $str .=$name[$x];$str .=",";
    $str2 .="'$"; $str2 .=$name[$x]; $str2 .="',";
    $sups .=$name[$x]; $sups .=" ='$"; $sups .=$name[$x]; $sups .="' , ";
    }
    $del=""DELETE FROM " .$table ." WHERE xxx = '$xxx'";

    $ins="$SQL="INSERT into " .$table;
    $ins .=" (" .$str .")" ." VALUES " ." (" .$str2 .")";" ;
    $ins=str_replace(",)",")",$ins);

    $ups="$SQL="UPDATE " .$table ." SET ";
    $ups .=$sups ."Z6Z " ." WHERE xxx ='$xxx'";";
    $ups=str_replace(", Z6Z ","",$ups);
    ?>
    <br><br>
    <center><table width=750 bgcolor=black wrap=virtual>
    <tr><td bgcolor="#e3e3e3">Insert</td><td bgcolor="white" width=700><font size=2 family=arial><? echo $ins;?></font></td></tr>
    <tr><td bgcolor="#e3e3e3">Update</td><td bgcolor="white" width=700><font size=2 family=arial><? echo $ups;?></font></td></tr>
    <tr><td bgcolor="#e3e3e3">Delete</td><td bgcolor="white" width=700><font size=2 family=arial><? echo $del;?></font></td></tr>
    </table></center>
    </body>
    </html>

    Note: it also generates SQL queries (SELECT/UPDATE/DELETE) that you can cut and paste for each table - I hate writing long UPDATE's etc which is why I did this script - for the life of me I can not work out why I bothered with the DELETE sql ! (ok I get lazy sometimes)

    ------------------
    Simon Wheeler
    FirePages -DHTML/PHP/MySQL

Similar Threads

  1. Replies: 13
    Last Post: June 4th, 2004, 05:49 PM
  2. need help in sorting&printing MySQL results
    By lowdog in forum MySQL Help
    Replies: 0
    Last Post: February 16th, 2004, 11:23 AM
  3. Need advice designing a usage tracking database (MySQL)
    By Randolpho in forum Database Management
    Replies: 0
    Last Post: February 13th, 2004, 05:11 PM
  4. dynamic form field names
    By harwoodspike in forum PHP Development
    Replies: 1
    Last Post: February 1st, 2004, 11:43 AM

IMN logo majestic logo threadwatch logo seochat tools logo