How to run a query from a drop down box.

[pobman]

Hi,

 

I am very new to php and mysql and want to write a little php app to display data from a DB.

 

with help I have managed to get a dropdown box working using the code below.

In the dropdown box I get a list of servers eg "merak"

I now want to use this varrible "merak" and run the following query and display it in the page, how do I make this happen.

 

The query was going to be: (straight from phpmyadmin)

$sql = 'SELECT `Name`, `Make`, `OS`, `Version` FROM `CES` WHERE `Name` = CONVERT(_utf8 \'merak\' USING latin1) COLLATE latin1_swedish_ci'; 

 

My current dropdown script:

<form class="table" action="check.php" method="post">
               <div class="inner-form">
                  <div class="msg msg-info">
                        <p>Please select which server you wish to edit.</p>
                        </div>
                  <?
mysql_connect(localhost, root, admin);
@mysql_select_db(servers_db) or die( "Unable to select database");

echo "<select name=form>n<option selected>Choose a Server</option>";
$query=mysql_query("SELECT * FROM `CES` WHERE `Name` IS NOT NULL");
while($row = mysql_fetch_array($query)) {
echo "<option value='{$row['Server']}'> {$row['Name']}</option>";

//echo "<option value=\"" . $row[server] . "\">" . $row[NAME] . "</option>";
}

    ?></div>
    </select>
<input type="submit" VALUE="Submit">
            </form>

 

 

Thanks

[nafetski]

If you want to have it do when an option is chose on the dropdown, you will need to use javascript.

 

That, or you can put a submit button and do a regular POST

[pobman]

Thanks for your reply, it would be the submit button I am after, javascript another day.

 

How do I change the code to do this.

[jacsdev]

Hi, what's up??? i think right now in a simple solution. there are many more solutions, i would like with ajax, but first the first

 

<?php
    // @jacsdev
// define your data connection
$host = '';
$user = '';
$pwd  = '';

$db = mysql_connect($host, $user, $pwd) or die("Sorry, it's not possible");
mysql_select_db($dbName);


if(isset($_GET['server']) && !empty($_GET['server']))
{
	$server = $_GET['server'];
	// now, as you see, we have in $server the value selected in dropdown list
	// so, we can do our query now:

	// this query is an example, you can modify
	$cn_server = mysql_query("select * from other_table_with_data where server_name='".$server."'");
	//..
}

?>

<form action="" onsubmit="return validateForm();">
Choose a Server:
<select name="myServers" id="myServers" onchange="selectMyServer(this.value);">
    	<option value="-">-</option>
        <?php
		$cn = mysql_query("select server_name, server_ip, server_type from server_list_table");
		while($rs = mysql_fetch_array($cn))
		{
			?>
                	<option value="<?php echo $rs['server_name']; ?>"><?php echo $rs['server_name']; ?></option>
                <?php
		}
	?>
    </select>
    <br />
    <br />
    <input type="submit" value="Send Data" />
    
</form>

<script language="javascript" type="text/javascript">
function selectMyServer(server)
{	
	if(server != '-')
	{
		// i get current page
		var myCurrentPage = location.href;

		// now, i setting my current page.  we must add selected server in new url
		location.href = myCurrentPage +'?server=' + server;  // redirecting to curret page
	}
}


function validateForm()
{
	if(document.getElementById('myServer').value !='-')
	{
		alert('please, choose a server');
		return false;
	}
}
</script>

 

when you choose a server in dropdown, the onChange event is launched. this event invoke to selectMyServer function, passing selected server as argument... so, the function redirect to same page with the name of selected server and then you can do a query with this value..

 

i hope help you friend

@jacsdev

 

 

[pobman]

Hi jacsdev,

 

Thanks for your post.

 

I have made the changes to your script as follows for the sql querys:

 

  
if(isset($_GET['server']) && !empty($_GET['server']))
   {
      $server = $_GET['server'];
      // now, as you see, we have in $server the value selected in dropdown list
      // so, we can do our query now:

      // this query is an example, you can modify
      $cn_server = mysql_query("SELECT * FROM CES WHERE Name ='".$server."'");
      //..
   }

?>

<form action="" onsubmit="return validateForm();">
   Choose a Server:
   <select name="myServers" id="myServers" onchange="selectMyServer(this.value);">
       <option value="-">-</option>
        <?php
         $cn = mysql_query("SELECT * FROM `CES` WHERE `Name` IS NOT NULL");
         while($rs = mysql_fetch_array($cn))
         {
            ?>
                   <option value="<?php echo $rs['server_name']; ?>"><?php echo $rs['server_name']; ?></option>
                <?php
         }
      ?>

 

Howver It is not returning a list of the servers in the dropdown box from the "Name" column in "CES"

 

Have I missed out some configuration?

 

Thanks

 

 

 

[pobman]

I have been going over the script and realised I had a few configuration errors.

 

I have now made the changes to what I think is all the correct places but the results in the drop down box is still empty except the "-" value.

 

Can you suggest some other changes I need to make.

 

thanks

 

   if(isset($_GET['server']) && !empty($_GET['server']))
   {
      $server = $_GET['server'];
      // now, as you see, we have in $server the value selected in dropdown list
      // so, we can do our query now:

      // this query is an example, you can modify
      $cn_server = mysql_query("SELECT * FROM CES WHERE Name ='".$server."'");
      //..
   }

?>

<form action="" onsubmit="return validateForm();">
   Choose a Server:
   <select name="myServers" id="myServers" onchange="selectMyServer(this.value);">
       <option value="-">-</option>
        <?php
         $cn = mysql_query("SELECT * FROM `CES` WHERE `Name` IS NOT NULL");
echo "SOME TEXT";
         while($rs = mysql_fetch_array($cn))
echo "<option value='{$rs['Name']}'> {$rs['Name']}</option>";

         {
            ?>
                   <option value="<?php echo $rs['Name']; ?>"><?php echo $rs['Name']; ?></option>
                <?php
         }
      ?>
    </select>
    <br />
    <br />
    <input type="submit" value="Send Data" />

</form>

 

[jacsdev]

what's up man!!! how are you?, maybe you must verify the mysql query string, check if it produces any data result.

you can try the following: copy and paste your query in sql option of phpmyadmin, and run it... then verify if your query works

 

now, when you choose a server and the page is loaded again, you must define the query that you'll use for extract new results of the other table.. also you must write the instruction that transforms query in a array type.. ex:

 

//below line where it say, we need write other instruction ..

$cn_server = mysql_query("SELECT * FROM CES WHERE Name ='".$server."'");

//here

while ($rs_server = mysql_fetch_array($cn_server))

{

  echo $rs_server['here i write name of field'];

}

 

 

//hey, a little fix,  inside validateForm function, instead  myServer, write myServers

 

by te way, i saw than you write ' echo "SOME TEXT" '  inside select tag, well, in that place you can't see output data, just it possible in the option tag value:  <option>here i am visible</option>

 

@jacsdev