Hi guys!

Could you tell me why I get a blank screen when I use the following code attempting to get only the tablenames?

PHP Code:

if ($_POST['submit']) {

//=========================== TABLE NAMES ==============================================
//Connect to the database
$conn = mysql_connect("localhost", "root") or die(mysql_error());

//choose the database to be used
$db = mysql_select_db("brc_dbase", $conn) or die(mysql_error());


//Get all tables that exist in the database
$result=mysql_list_tables("brc_dbase");

//Display the tablenames
for($i=0; i < mysql_list_tables($result); $i++) {

    echo "Table name: ".mysql_tablename($result, $i)."<br>\n";

    }
} 


Is there a better method than the one I'm using? If so, what would be the best approach?

Thanks for the help.

You are calling mysql_list_tables inside your for loop condition for some reason. You should get the number of rows and use that as your condition.

if ($_POST['submit'])
{

    //=========================== TABLE NAMES ==============================================
    //Connect to the database
    $conn = mysql_connect('localhost', 'root') or die(mysql_error());

    //choose the database to be used
    $db = mysql_select_db('brc_dbase', $conn) or die(mysql_error());


    //Get all tables that exist in the database
    $result = mysql_list_tables('brc_dbase');

    // Get the number of tables
    $num_tables = mysql_num_rows($result);

    //Display the tablenames
    for($i = 0; $i < $num_tables; $i++)
    {
        echo "Table name: " . mysql_tablename($result, $i) . "<br />\n";
    }

}

try this...

PHP Code:

if (!empty($_POST)) { 

    $conn = mysql_connect("localhost", "user", "pass") or die(mysql_error()); 
    $db = mysql_select_db("dbname", $conn) or die(mysql_error()); 
    
    $result = mysql_list_tables("dbname"); 
    
    while ($row = mysql_fetch_row($result)) {
        echo "Table name: {$row[0]<br />\n";
    }

} 


Thanks for the tip!

No problem. Let me know if that code works for you

Originally Posted by simptech

No problem. Let me know if that code works for you

The above code gets all tables in the specified database and places them in a drop-down menu. Now for the tricky question: How can I retrieve the fields pertaining to a selected table from the drop-down menu and nothing else. In other words, when I select the table, it creates a drop-down menu with its respective fieldnames.

Sorry if it causes you any inconvenience but any tip will be greatly appreciated! Thanks once more!

Why would you need to do that?

Sorry, but just curious.

And to answer your question, you define the search in your php. You cannot code php to find field names in an sql database.

Gaffer is not correct. You can get the field information from a MySQL database using mysql_list_fields() and mysql_fetch_field().

PHP Code:

$conn = mysql_connect("localhost", "user", "pass") or die(mysql_error());
$db = mysql_select_db("dbname", $conn) or die(mysql_error());
$result = mysql_query("SELECT * FROM tablename");
$i = 0;
while ($i < mysql_num_fields($result)) {
    $field = mysql_fetch_field($result);
    echo "Field name: {$field->name}<br />n";
} 


If you want this handled dynamically using the "onChange" event in JavaScript, use PHP to build associative or two-dimensional arrays to populate your drop-downs in JavaScript.

In other words, use PHP to dynamically build the JavaScript code that is output to the browser

If I have enough time, I will code you a quick example later

Originally Posted by simptech

If I have enough time, I will code you a quick example later

I can post what I have and you can direct me from there so that you don't go through the trouble of creating it from scratch! Also, I could tell you, while looking at the code, what it's doing or not doing, if its okay with you.

Go ahead and do that. I have started an example using a 3D array but have not been able to work on it much.

Okay, here is the code:

PHP Code:

if ($_POST['submit']) {

//================== CONNECT AND SELECT THE DATABASE =========================

//Connect to the database
$conn = mysql_connect("localhost", "root") or die(mysql_error());

//choose the database to be used
$db = mysql_select_db("brc_dbase", $conn) or die(mysql_error());



//=========================== TABLE NAMES ====================================

//Get all tables that exist in the database
$result=mysql_list_tables("brc_dbase");


//Assign the values

mysql_fetch_array($result);


//Get the tablenames and display them

echo "<select name =\"select_table\">";

for ($i=0; $i < mysql_fetch_array($result); $i++) {

    echo "<pre>     <option value=".mysql_tablename($result, $i).">".mysql_tablename($result, $i)."     </pre>";

    }
}

echo "    <tr>
            <td><input type=submit name=\"go\" value=\"   GO     \"><td><strong>    SET     </strong></td>
            </td>
        </tr>";


//=========================== FIELD NAMES =========================================


//=======================  FIELD NAMES FOR MAINLOGIN  =============================

if($submit == $mainlogin) {

//List the fields from the table and assign it to $result

$result=mysql_list_fields("brc_dbase", "mainlogin");


//Get the fields

mysql_fetch_field($result);


//Format the heading

echo "<th><pre><strong><b>SELECT FIELD:</b></strong></pre></th>";


//Get the fieldnames and display them

echo "<select name =\"select_field\">";

    for ($i=0; $i < mysql_fetch_field($result); $i++) {

    echo "<option value=".mysql_field_name($result, $i).">".mysql_field_name($result, $i)."";

    }
  }


//======================================  FIELD NAMES FOR COMPANY_INFO  =============================

if($submit == $company_info) {

//List the fields from the table and assign it to $result

$result=mysql_list_fields("brc_dbase", "company_info");


//Get the fields

mysql_fetch_field($result);


//Format the heading

echo "<th><pre><strong><b>SELECT FIELD:</b></strong></pre></th>";


//Get the fieldnames and display them

echo "<select name =\"select_field\">";

    for ($i=0; $i < mysql_fetch_field($result); $i++) {

    echo "<option value=".mysql_field_name($result, $i).">".mysql_field_name($result, $i)."";

    }
  }


//======================================  FIELD NAMES FOR FINISHING  =============================

if($go == $finishing) {

//List the fields from the table and assign it to $result

$result=mysql_list_fields("brc_dbase", "finishing");


//Get the fields

mysql_fetch_field($result);


//Format the heading

echo "<th><pre><strong><b>SELECT FIELD:</b></strong></pre></th>";


//Get the fieldnames and display them

echo "<select name =\"select_field\">";

    for ($i=0; $i < mysql_fetch_field($result); $i++) {

    echo "<option value=".mysql_field_name($result, $i).">".mysql_field_name($result, $i)."";

    }
  }


//======================================  FIELD NAMES FOR ORDER_DESC  =============================

if($go == $order_desc) {

//List the fields from the table and assign it to $result

$result=mysql_list_fields("brc_dbase", "order_desc");


//Get the fields

mysql_fetch_field($result);


//Format the heading

echo "<th><pre><strong><b>SELECT FIELD:</b></strong></pre></th>";


//Get the fieldnames and display them

echo "<select name =\"select_field\">";

    for ($i=0; $i < mysql_fetch_field($result); $i++) {

    echo "<option value=".mysql_field_name($result, $i).">".mysql_field_name($result, $i)."";

    }
  }


//======================================  FIELD NAMES FOR ORDER_INFO  =============================

if($go == $order_info) {

//List the fields from the table and assign it to $result

$result=mysql_list_fields("brc_dbase", "order_info");


//Get the fields

mysql_fetch_field($result);


//Format the heading

echo "<th><pre><strong><b>SELECT FIELD:</b></strong></pre></th>";


//Get the fieldnames and display them

echo "<select name =\"select_field\">";

    for ($i=0; $i < mysql_fetch_field($result); $i++) {

    echo "<option value=".mysql_field_name($result, $i).">".mysql_field_name($result, $i)."";

    }
  }


//======================================  FIELD NAMES FOR PAPER NEEDED  =============================

if($go == $paper_needed) {

//List the fields from the table and assign it to $result

$result=mysql_list_fields("brc_dbase", "paper_needed");


//Get the fields

mysql_fetch_field($result);


//Format the heading

echo "<th><pre><strong><b>SELECT FIELD:</b></strong></pre></th>";


//Get the fieldnames and display them

echo "<select name =\"select_field\">";

    for ($i=0; $i < mysql_fetch_field($result); $i++) {

    echo "<option value=".mysql_field_name($result, $i).">".mysql_field_name($result, $i)."";

    }
  }


//======================================  FIELD NAMES FOR PRESSMAN  =============================

if($go == $pressman) {

//List the fields from the table and assign it to $result

$result=mysql_list_fields("brc_dbase", "pressman");


//Get the fields

mysql_fetch_field($result);


//Format the heading

echo "<th><pre><strong><b>SELECT FIELD:</b></strong></pre></th>";


//Get the fieldnames and display them

echo "<select name =\"select_field\">";

    for ($i=0; $i < mysql_fetch_field($result); $i++) {

    echo "<option value=".mysql_field_name($result, $i).">".mysql_field_name($result, $i)."";

    }
  } 


What I'm trying to do:

1. The main purpose is to create a form where the user will be able to UPDATE a MySql row easily.

2. Get the tables for the database ---> DONE!

3. Select TABLENAME from drop-down menu and get its respective fields ---> PROBLEM: Gets fields for ***all*** tables. Irrespective of which table I choose, it should get the fields for only that table and none other.

4. The whole process should work as follows:

TABLENAME --> SET --> FIELDNAME --> = --> NEW VALUE --> WHERE --> $FIELDNAME = (Enter new value to be changed)

There are programs that exist that already do this but I want this to be my creation (with some help)?????

For your assistance, thanks!

the code we developed as an example is as follows...

PHP Code:

$link = mysql_connect("localhost", "username", "password") or die(mysql_error());
$dbnames = mysql_list_dbs($link);
$output = "<script type=\"text/javascript\">\n<!--\n";
$output .= "var arr = new array();\n";
while ($curdb = @mysql_fetch_object($dbnames)) {
$tables = @mysql_list_tables($curdb->Database, $link);
$success = 0;
$tmp = "";
while ($curtable = @mysql_fetch_row($tables)) {
$success = 1;
$tmp .= "arr[{$curdb->Database}][{$curtable[0]}] = new Array();\n";
$fields = @mysql_list_fields($curdb->Database, $curtable[0], $link);
$columns = @mysql_num_fields($fields);
for ($i = 0; $i < $columns; $i++) {
$name = @mysql_field_name($fields, $i);
$tmp .= "arr[{$curdb->Database}][{$curtable[0]}][] = \"{$name}\";\n";
}
}
if ($success) {
$output .= "arr[{$curdb->Database}] = new Array();\n";
$output .= $tmp;
}
}
$output .= "//-->\n</script>\n";
echo $output; 


output returned is a 3D javascript array that can be disected to use with your dorp-down menus.

example:

<script type="text/javascript">
<!--
var arr = new array();
arr[dbprefix_-_forum] = new Array();
arr[dbprefix_-_forum][cart] = new Array();
arr[dbprefix_-_forum][cart][] = "cartId";
arr[dbprefix_-_forum][cart][] = "cookieId";
arr[dbprefix_-_forum][cart][] = "itemId";
arr[dbprefix_-_forum][cart][] = "qty";
arr[dbprefix_-_forum][client_items] = new Array();
arr[dbprefix_-_forum][client_items][] = "rid";
arr[dbprefix_-_forum][client_items][] = "name";
arr[dbprefix_-_forum][client_items][] = "description";
arr[dbprefix_-_forum][client_items][] = "services";
arr[dbprefix_-_forum][client_items][] = "site_url";
arr[dbprefix_-_forum][client_items][] = "active";
arr[dbprefix_-_forum][client_items][] = "feature";
arr[dbprefix_-_forum][items] = new Array();
arr[dbprefix_-_forum][items][] = "itemId";
arr[dbprefix_-_forum][items][] = "itemName";
arr[dbprefix_-_forum][items][] = "itemDesc";
arr[dbprefix_-_forum][items][] = "itemPrice";
arr[dbprefix_-_forum][tpl_cats] = new Array();
arr[dbprefix_-_forum][tpl_cats][] = "rid";
arr[dbprefix_-_forum][tpl_cats][] = "pid";
arr[dbprefix_-_forum][tpl_cats][] = "name";
arr[dbprefix_-_forum][tpl_cats][] = "description";
arr[dbprefix_-_forum][tpl_items] = new Array();
arr[dbprefix_-_forum][tpl_items][] = "rid";
arr[dbprefix_-_forum][tpl_items][] = "cid";
arr[dbprefix_-_forum][tpl_items][] = "price";
arr[dbprefix_-_forum][tpl_items][] = "name";
arr[dbprefix_-_forum][tpl_items][] = "color";
arr[dbprefix_-_forum][tpl_items][] = "description";
arr[dbprefix_-_forum][tpl_items][] = "dhtml";
arr[dbprefix_-_forum][tpl_items][] = "exclusive";
arr[dbprefix_-_forum][tpl_items][] = "expanding";
arr[dbprefix_-_forum][tpl_items][] = "frames";
arr[dbprefix_-_forum][tpl_items][] = "limited";
arr[dbprefix_-_forum][tpl_items][] = "multi";
arr[dbprefix_-_forum][tpl_items][] = "psd";
arr[dbprefix_-_forum][tpl_items][] = "rollover";
arr[dbprefix_-_main] = new Array();
arr[dbprefix_-_main][config] = new Array();
arr[dbprefix_-_main][config][] = "config_name";
arr[dbprefix_-_main][config][] = "config_value";
arr[dbprefix_-_main][config][] = "config_title";
arr[dbprefix_-_main][requests] = new Array();
arr[dbprefix_-_main][requests][] = "request_id";
arr[dbprefix_-_main][requests][] = "request_from";
arr[dbprefix_-_main][requests][] = "request_email";
arr[dbprefix_-_main][requests][] = "request_phone";
arr[dbprefix_-_main][requests][] = "request_comments";
arr[dbprefix_-_main][requests][] = "request_datetime";
arr[dbprefix_-_main][requests][] = "request_followup_date";
arr[dbprefix_-_main][requests][] = "request_followup_notes";
arr[dbprefix_-_rvc_link_trade] = new Array();
arr[dbprefix_-_rvc_link_trade][admin] = new Array();
arr[dbprefix_-_rvc_link_trade][admin][] = "admin_id";
arr[dbprefix_-_rvc_link_trade][admin][] = "username";
arr[dbprefix_-_rvc_link_trade][admin][] = "password";
arr[dbprefix_-_rvc_link_trade][link_trade] = new Array();
arr[dbprefix_-_rvc_link_trade][link_trade][] = "link_id";
arr[dbprefix_-_rvc_link_trade][link_trade][] = "company";
arr[dbprefix_-_rvc_link_trade][link_trade][] = "description";
arr[dbprefix_-_rvc_link_trade][link_trade][] = "website";
arr[dbprefix_-_rvc_link_trade][link_trade][] = "email";
arr[dbprefix_-_rvc_link_trade][link_trade][] = "phone";
arr[dbprefix_-_rvc_link_trade][link_trade][] = "ext";
arr[dbprefix_-_rvc_link_trade][link_trade][] = "rec_link";
arr[dbprefix_-_rvc_link_trade][link_trade][] = "disabled";
//-->
</script>

you should find someone who knows how to get keynames from javascript arrays so you can loop through the array to populate select #1 and select #2 accordingly based on the selection(s) from 1 and 2

I was trying to run from Javascript as I'm at zero when it comes to it. But thanks for the help!

With what you showed me, I'll try it my own way. If I can't get the results I want, then I'll have to look at the Javascript option.

Someone is not going to sleep tonight!

You can see where (and hopefully why) we append to the $output variable. Instead of the mass of queries you were using, this method would probably be less intensive on the system resources.

I am pretty sure this is the fastest way to get the info you are looking for. Just snatch the code, modify it to output whatever format you want to work with, and go from there.

We were going to use an Object() to hold the values but did not have enough time to convert the output and test it properly. When using an Object() you can call "foreach (item in collection)" similar to how you would using foreach() when iterating over an array in PHP

maybe this type of output would make it easier to understand/work with

<script type="text/javascript">
<!--
var items = new Array();
items[0] = "dbprefix_-_shop";
items[0][0] = "cart";
items[0][0][0] = "cartID";
items[0][0][1] = "cookieID";
items[0][0][2] = "itemID";
items[0][0][3] = "qty";
items[0][1] = "items";
items[0][1][0] = "itemId";
items[0][1][1] = "itemName";
items[0][1][2] = "itemDesc";
items[0][1][3] = "itemPrice";
items[1] = "dbprefix_-_main";
items[1][0] = "config";
items[1][0][0] = "config_name";
items[1][0][1] = "config_value";
items[1][0][2] = "config_title";
 
//-->
</script>

items[i] = db i
items[i][j] = table j, db i
items[i][j][k] = field k, table j, db i

basically, when the user changes the selection in drop-down1, the value from that selection determines which tables to load in drop-down2 and likewise for drop-down3 onchange from drop-down2 which populates field names

that make sense on your end?

I'm analyzing the code, will use the weekend to see how far I get. Will keep you posted.