1

Hello!
What I am trying to do is assign the values from a mysql query and store them in an array. I have some code that queries the DB by using SHOW COLUMNS. I then loop through dynamically assigning the name of the DB field to input fields of a form. That way I can build the form and give the text inputs a name without actually knowing the name up front. This depends on what the user selects from a link on a previous page. For example...I show DB's on the index page and then dynamically create links with the DB's names. They select the DB they want and then I take them to show the columns in that DB. From their they can select a link to insert into that DB. I then take them to this page and a form with text inputs that have been dynamically named from the query that loops and grabs the names of the DB fields by using SHOW COLUMNS.

Everything works now...it assigns the names as expected. What I am having trouble doing is grabbing the names of those text inputs so I can use them to do the insert. So I'm going to have to INSERT INTO dbname.dbtable and then dynamically assign the text input names to the columns for this INSERT INTO code and then grab the values from what the user entered into the form fields. I think I have to loop all of this because I do not know the text input names.

Anyways, I think I can figure it out if I can store the input names into an array, However, I have always been weak with using arrays. I included comments in my code below at the point where I want to do this and why.

Thanks to everyone who wants to help.

            // Get database and table names carried all the way over from the index page.
            $getdb = $_GET['db'];
            $getcols = $_GET['tables'];

            // Echo out DB and table name used for the breadcrumb trail.
            echo "<a href=\"index.php\"><b>Database-></b></a> " . "<a href=\"view.php?db=$getdb\">" . $getdb .
            "</a><b>-></b> " . $getcols;

            // connecting to local database mysql sever.
             $con = mysql_connect("localhost", "root", "") or die(mysql_error());

             // Select DB carried from the index page.
             $select_db = mysql_select_db($getdb, $con) or die(mysql_error());

            // Check if we are connected to DB, if so echo page heading. 
            if( $con )
               {
            echo "<h1><u>MySQL Local Server</u></h1>";
               }

            echo "<h4>Insert Record</h4>";
            echo "<br />";


            // Get column field names from database table.
            $sql = "SHOW COLUMNS FROM $getcols";

            // Run the query.
            $query = mysql_query($sql, $con) or die(mysql_error());



            // Set string to search for which dynamically grabs the name of the table field.
            $get_string = "Field";

            // Start html output and table.
            echo "<html>";
            echo "<head>";
            echo "<title>Insert data on Table " . strtoupper($getcols) . " on DB " . strtoupper($getdb) . "</title>";
            echo "</head>";
            echo "<body>";
            echo "<table width=\"250px\">";
            // Echo out form
            echo "<form method=\"post\" action=\"$_SERVER[PHP_SELF]\">";



            // While we have results
            while ($rows = mysql_fetch_assoc($query))
            {

            // Run through each result set.     
            foreach($rows as $cols => $columns)
            {

            // Grab the field name of the table so we can dynamically set the name for insertion into DB.
            // Checking for the text "Field" because SHOW COLUMNS will show as Field - FieldName for all the columns
            //So when it sees the text "Field" it then grabs the name of the field.

            // searching for the text "Field" in the query results
            if(substr($cols, 0, 5) == $get_string)
            {

            // trying to set an empty array here so I can assign the names of the DB columns below to it.
            $field_array = array();

            // Finish our table and echo the field name, dynamically naming it to the textbox.
            // This code here works to assign the name to each textbox correctly.
            // I can view source the page and see that the form is set up as expected with each text
            //input properly named matching the columns in the DB.
            // The first time that $column is echoed is just to print the name of the db field.
            // the second instance is to do the naming to the text box which Im sure you already know.
            echo "<tr>";
            echo "<td align=\"left\">";
            echo $columns;
            echo "</td>";
            echo "<td align=\"right\">";
            echo "<input type=\"text\" name=\"$columns\" />";
            echo "</td>";
            echo "</tr>";
            echo "\r\n";

            // here is where I try to store the name of the column in my array.
            $field_array[] = $columns;

            }
            // echo used for testing only and does not echo anything as is.

                echo $field_array[$columns];


            }       

            }
            echo "</table>";
            //was going to post to a new page with all the data but may be easier posting to self?
             //echo "<input type=\"hidden\" name=\"db\" value=$getdb />";
            //echo "<input type=\"hidden\" name=\"tables\" value=$getcols />";
                echo "<input type=\"submit\" value=\"INSERT\" />";
                echo "</form>";

            // here is where I want to assign the column name dynamically which I may have
            //to do for the actual value that the user entered also....not sure.
            // Guess Ill have to loop and assign it that way which I know isnt used yet.
            // The $getdb and $getcols are set earlier on in the script and these are set to the db name and table name.

            //$sql = "INSERT INTO `$getdb`.`$getcols` ($columns)
            //  VALUES ($columns)"; 

            echo "</body>";
            echo "</html>";
2
Contributors
2
Replies
3
Views
5 Years
Discussion Span
Last Post by jpadgett230
0

nicely described, the result returned by a mysql_fetch_assoc is actually an array so you should be more familiar with them than you think

<?php 
 while ($rows = mysql_fetch_assoc($query)){
    //loop through each row in the mysql result set
    //foreach($array as $key=>$value)
    foreach($rows as $column_name => $column_value){
        //loop through each column in the current row
        if(substr($column_name, 0, 5) == $get_string){
            $field_array = array();

            echo "<tr>";
            echo "<td align=\"left\">";
            echo $column_value;
            //prints out the value stored in this column
            echo "</td>";
            echo "<td align=\"right\">";
            echo "<input type=\"text\" name=\"$column_value\" />";
            echo "</td>";
            echo "</tr>";
            echo "\r\n";
            //$field_array[] = $columns; adds a value to the array with an incrementing index (0,1,2,3,4 etc)
            //$field_array[$column_value] = 'something'; adds an entry to the array with the key $column_value and value "something"
            $field_array[$column_value] = 'something';
        }
        echo $field_array[$column_value];
        //print out the value of $field_array[$column_value] (not the key)
    }
}
echo "<br/>\r\n";
foreach($field_array as $k => $v){
    echo $k.' => '.$v."<br/>\r\n";
    //$k will match your field name, value will be set to "something"
}
echo "<br/>\r\n";
foreach($field_array as $k => $v){
    echo "sql: INSERT INTO `{$getdb}`.`{$getcols}` (`{$k}`)  VALUES (`{$v}`)<br/>\r\n";
}
?>

Edited by Biiim: bit extra

0

Thank you for the fast reply....I will work on it tonite and reply back to you on the outcome.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.