sql-lover Posted February 2, 2012 Share Posted February 2, 2012 This is driving me crazy .... Need to use a variable for the database name on this code ... <!DOCTYPE html> <html> <head> <title>Table Definition's Tool</title> <style type="text/css"> th { font-size: 110%; border-bottom: 2px solid black; } td { padding: 3px; border-bottom: 1px solid #aaa } </style> </head> <body> <h1>ITG's table's definition</h1> <table> <?php error_reporting (E_ALL ^ E_NOTICE); //Variables $instance=$_REQUEST['instance']; $database_name= $_REQUEST['database_name']; $table=$_REQUEST['table']; require 'utils.php'; // Connect via Windows authentication $server = $instance; $connectionInfo = array( 'CharacterSet' => 'UTF-8' ); $db = sqlsrv_connect($server, $connectionInfo); if ($db === false) { exitWithSQLError('Database connection failed'); } /* Set up and execute the query. */ $query = "SELECT COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM $database_name.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='$table'"; // Run query $qresult = sqlsrv_query($db, $query); if ($qresult === false) { exitWithSQLError('Query of product data failed.'); } echo '<tr><th>COLUMN NAME</th><th>POSITION</th><th>DEFAULT</th><th>TYPE</th><th>LENGHT</th></tr>'; // Retrieve individual rows from the result while ($row = sqlsrv_fetch_array($qresult)) { echo '<tr><td>', htmlspecialchars($row['COLUMN_NAME']), '</td><td>', htmlspecialchars($row['ORDINAL_POSITION']), '</td><td>', htmlspecialchars($row['COLUMN_DEFAULT']), '</td><td>', htmlspecialchars($row['DATA_TYPE']), '</td><td>', htmlspecialchars($row['CHARACTER_MAXIMUM_LENGTH']), "</td></tr>\n"; } // null == no further rows, false == error if ($row === false) { exitWithSQLError('Retrieving schema failed.'); } // Share Release result liststatement resource and close connection sqlsrv_free_stmt($qresult); sqlsrv_close($db); ?> </table> </body> </html> Is a simple code where the main MS-SQL query is SELECT COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM $database_name.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='$table' The user should be able to introduce the database and table's name and above query will display the table's schema. However, does not work. I am able to use a variable for the table, but the program does nothing when I use $database_name inside the query. What am I doing wrong? Help, please .... Quote Link to comment Share on other sites More sharing options...
sql-lover Posted February 2, 2012 Author Share Posted February 2, 2012 Adding more info, so maybe someone can help ... I know the MS-SQL driver is a bit different than MySQL. But basically slqsrv_connect works with whatever parameters were provided before. Without using a variable for the database name, the same code works if I change the connection part to $connectionInfo = array( 'Database' => 'adventureworks', 'CharacterSet' => 'UTF-8' ); But of course, I want the user to be able to choose the database name or context. Quote Link to comment Share on other sites More sharing options...
Proletarian Posted February 2, 2012 Share Posted February 2, 2012 Check if $database_name= $_REQUEST['database_name'] is set or not. Quote Link to comment Share on other sites More sharing options...
sql-lover Posted February 2, 2012 Author Share Posted February 2, 2012 Check if $database_name= $_REQUEST['database_name'] is set or not. Thanks for reply! How do I change that? and why? The other two variables work perfectly. Why the one for the database is not getting the value. Here's the form's code <!DOCTYPE html> <html> <head> <title>Table Definition's Tool</title> <style type="text/css"> th { font-size: 110%; border-bottom: 2px solid black; } td { padding: 3px; border-bottom: 1px solid #aaa } </style> </head> <form action = "table_def.php" method="post"> <p> <b>SQL instance: </b><input type = "text" name = "instance" size ="40" maxlenght = "30" /> </p> <p> <b>Database: </b><input type = "text" name = "database" size ="40" maxlenght = "30" /> </p> <p> <b>Table's name: </b><input type = "text" name = "table" size ="20" maxlenght = "30" /> </p <p><input type = "submit" name="submit" value="Find table's schema" /></p> I just changed it to POST method, but still, no luck with the database's variable. Quote Link to comment Share on other sites More sharing options...
sql-lover Posted February 2, 2012 Author Share Posted February 2, 2012 Found the issue!!!! ... It was in the form, not the actual page that runs the query. I forgot to put the tag that closes the form: </form> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.