Link to home
Start Free TrialLog in
Avatar of breeze351
breeze351

asked on

Populating 2 different drop downs

I have 2 drop downs.  The values in the second drop down relate to those in the first .
How do I change the values in the second after the selection in the first?

Example
1:  Companies
2:  Salesmen that work for companies.

Thanks
Avatar of Marco Gasi
Marco Gasi
Flag of Spain image

You have to adapt to your script, but this code works in my cms:
$( document ).ready( function ()
{

$('#companies').on('change', function(){  //on change event of the select for Companies
  var company = $(this + 'option:selected').val(); //assume the valòue of each option is the company id got from the db
  $.ajax( {
		type: 'post',
		url: script.php,  //the script uses the company id to get from the db all salesmen of that company and return them as an array using json_encode(salesmen);
     data: {companyId: company},
		dataType: 'json',
		success: function ( result )
		{
     	$('#salesman').children().remove();
			$( result ).each( function ( a, b ) 
			{
				$( b ).each( function ( k, v ) 
				{
					$('#salesman').append( "<option value='" + v['salesmen_id'] + "'>" + v['salesmen_name'] + "</option>" );
				});
			});
		},
		error: function ( jqXHR, textStatus, errorThrown  )
		{
			console.log(textStatus + " " + errorThrown);
		}
	} );
});
});

Open in new window

This is often referred to as a "dependent dropdown."  jQuery has some resources that are useful.
http://forum.jquery.com/topic/multi-select-dependant-dropdown-list

Another way of describing it is "javascript dependent select lists"
http://myweb.tiscali.co.uk/cornford/example_scripts/dependent_select.html
Avatar of breeze351
breeze351

ASKER

Marco

I'm not that adept with java, but I can read it.  The solutions I found on the web were of really no help (had bugs in them).

Could you please post me a copy of your script.php?

Thanks
Glenn
script.php is a fake name :-) I can post some line of code, not the actual scriipt because now I'm working under CodeIgniter and something is different:
$options = array(
	PDO::ATTR_PERSISTENT => true,
	PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
	PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
	PDO::ATTR_EMULATE_PREPARES => true
);
$this->dsn = "mysql:host=your_host;dbname=database_name;charset=utf8";
$this->db = new PDO( $this->dsn, root, strongpassword, $options );
$company_id = $_POST['company_id'];
$sql = "SELECT * FROM salesmen WHERE company_id='$company_id'";
$stmt = $db->query($sql);
$result = $stmt->fetchAll(PDO::FECTH_ASSOC);
result json_encode($result);

Open in new window


Javascript should be the following:
$( document ).ready( function ()
{

	$('#companies').on('change', function(){  //on change event of the select for Companies
		var company = $(this + 'option:selected').val(); //assume the valòue of each option is the company id got from the db
		$.ajax( {
			type: 'post',
			url: script.php,  
						data: {companyId: company},
			dataType: 'json',
			success: function ( result )
			{
					$('#salesman').children().remove();
					$( result ).each( function ( k, v ) 
					{
				$('#salesman').append( "<option value='" + v['salesmen_id'] + "'>" + v['salesmen_name'] + "</option>" );
				 });
			},
			error: function ( jqXHR, textStatus, errorThrown  )
			{
				console.log(textStatus + " " + errorThrown);
			}
		} );
	});
});

Open in new window

Marco:
I've been sick for the last couple of days and I'm still recovering from knee surgery 2 weeks ago.

Could you please take a look at the following code.  I think that I'm damn close.

I know that there is an error in "persons.php".
index.php
persons.php
Quick look: you're right, in persons.php you have an error for sure :-)
You're passing companies value as TID variable from index.php. Then in persons.php you get that value
$company_id = $_POST['TID'];

Open in new window

But then you use a non existent variable
$sql = "SELECT * FROM pers WHERE TID ='$TID'";

Open in new window

you should write instead this:
$sql = "SELECT * FROM pers WHERE TID ='$company_id'";

Open in new window


Give it a try now.
I tried it but I'm still not getting anything on the return

$sql = "SELECT * FROM pers WHERE TID ='$company_id'";

I'm using DreamWeaver and it's showing an error on line 16.

12: $company_id = $_POST['TID'];
13: $sql = "SELECT * FROM pers WHERE TID ='$company_id'";
14: $stmt = $db->query($sql);
15: $result = $stmt->fetchAll(PDO::FECTH_ASSOC);
16: $result json_encode($result);

Code this have something to do that (PDO::FECTH_ASSOC) is not defined in the array?

Glenn
Your line 16 has no assignement

 $result json_encode($result);

should be

 $result = json_encode($result);

Is it a typo here or in your script?

PDO_FETCH_ASSOC  means that PDO will return only the associative array: I don't hink the issue be there...
No joy!
I'm attaching the code again.

Why can't I do the query in the java part of "index.php" and not have to go to "persons.php"?
index.php
Sorry, must have hit the wrong button.
Thanks
Glenn
persons.php
You're using a mysql database and php is the right tool to query.
The problem is still in your javascript:
var company = $(this + 'option:selected').val(); //assume the valòue of each option is the company id got from the db

Open in new window

but then you pass the wrong value:
data: {TID: companies},

Open in new window

The right way is
data: {TID: company},

Open in new window

because the value we want to pass persons.php is hold in company variable not in companies (which just doesn't exist).

I strongly suggest you to use Firebug and FirePhp to see in the console custom messages and errors. But this is another story.
Check this out and you will see what the problem is.

test.mrbreeze.net

The company is "Lansco Corp.".  This is the customer, so I know that there are agents.

How do I download "Firebug" or "FirePhp"?  I've been looking for some type of debugging software.

Glenn
Using Firebug the console says:
ReferenceError: $ is not defined
           $( document ).ready( function ()

Open in new window

This means you don't have linked jquery: I didn't specify supposing you knew it yet but evidently I was wrong: forgive me :-(
Just put this line as first javascript inclusion:

    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>

Open in new window

without this your browser doesn't kinow anything about jquery and $ function.
Marco:

I've tried this and I still don't get the "persons" drop down populated.  You have seen the code.  What am I missing?

In the mean time I've downloaded "Firebug" and don't see anything wrong with the code (It's probably me).

Thanks
Glenn
One thing per time :-)

Firebug:
open your test page;
open Firebug, click che small rectangle with an arrow you find on the left and then click the Console tab;
select Lansco Corp in the first select and look at the console: 2 errors; about the first I'm investigating: do it you too :-) The second caused by... me :-(

The error:
in this line i forgot to put a blank space
var company = $(this + 'option:selected').val();

Open in new window

replace it with this one:
var company = $(this + ' option:selected').val();

Open in new window


Without the space jQuery doesn't recognize the expression and raise the error. Try to fix this one first and see what happens. In the meanwhile I'll look for info about the first error.
You declare the doctype twice. Delete the first one and keep the second (<!DOCTYPE html>).
Marco
I deleted the first <!DOCTYPE> and added the space in "var company = $(this + ' option:selected').val();".

Still no joy?

I tried your instructions for FIrePHP. But "LangSystems" doesn't come up in the drop down.

Isn't there a way to do this on one page rather than having to use "URL:persons.php"?  Why can't all the code be in "index.php"?  I'm probably wrong, but isn't "persons.php" just taking data from "index.php" and returning it?
You can use only index.php but (AFAIK) changing the way to do it: you can use just php, post the new value of your dropdown to index.php page check if a value has been posted and then fill the second dropdown accordingly. But you can use javascript to intercept the dropdown change event and post it to index.php and nowdays it's far far widely used ajax for this kind of things.
Let me understand: have you updated the code in your testing page http://test.mrbreeze.net/? It gives me the same error...
What is LangSystem: it's the first time I hear about.
persons.php doesn't print anything, is a pure php script which get data from a atabase and returns them as an array

Since you're not using any framework, It's very surprisingly you can't get this trivial task work: there is some error somewhere and this is the moment to post here all your code involved, both index.php and persons.php so I can take a look. Without your code I can't try to help you further :-(
Marco:

Attached is "index.php" and "persons.php".  I have modified these according to your posts.

Here is a query that does return the correct results from "MyPHPAdmin".
SELECT c.TID, c.COMPANY, p.FNAME, p.LNAME, p.TITLE
                        FROM comp c
                        INNER JOIN pers p ON p.TID=c.TID
                        where c.TID = '00000619';

I have also attached a print screen of these results "Join_Results.doc".

Thanks
Glenn
index.php
persons.php
Join-Results
In persons.php there is a my error: last line has to be

return json_encode($result);

Open in new window


In index.php, you have an onchange() event which doesn't exist attached to the select companies: please, remove it.
econdly, the select for agent has id="agents" but in jquery code you use 'agent' so jquery code doe nothing because it doesn't find the element. Make 'agent' plural as below:

$('#agents').children().remove();
$( result ).each( function ( k, v ) 
{
	$('#agents').append( "<option value='" + v['TID'] + "'>" + v['FNAME'] + "</option>" );
 });

Open in new window


Please update the tet page so I can see it live.
Cheers
I have done as requested.

The url is http://test.mrbreeze.net/index.php
index.php
persons.php
Warning: you still have an agent instead of agents

$('#agent').append( "<option value='" + v['TID'] + "'>" + v['FNAME'] + "</option>" );

Once you have fixed that, please, try to replace
var company = $(this + ' option:selected').val();

Open in new window

with
var company = $('#companies option:selected').val();

Open in new window


btw, where are you? I'm in Tenerife and It looks like we have a lot of time zones of difference :-)
No joy!
I'm in New Jersey.
What do I do?
What I said in comment ID: 40633806:
first this line
$('#agent').append( "<option value='" + v['TID'] + "'>" + v['FNAME'] + "</option>" );

Open in new window

must become
$('#agents').append( "<option value='" + v['TID'] + "'>" + v['FNAME'] + "</option>" );

Open in new window


Second this line
var company = $(this + ' option:selected').val();

Open in new window

must become
var company = $('#companies option:selected').val();

Open in new window


The first fix is needed because it doesn't exist a div with id='agent', the correct id is 'agents'; the second fix should make jquery work.
Marco
I had made the changes you stated in ID 40633790.  I've included the code.  
If you didn't receive it, I'm attaching again.
index.php
persons.php
Wow, I must be blind! You have to use quotes around the url

url: 'persons.php',

Open in new window


We should be near. Eventually, we have to modify the loop through the result, but leave this for later :-)
Ok, I've added the quotes around the url.  What's next
Finally! Now, selecting Lasco Corp doesn't raise any jQuery error but in  the console I see a php error instead:

Notice:  Undefined variable: db in <b>/home/mrbreeze/public_html/test/persons.php on line 14
Fatal error:  Call to a member function query() on a non-object in <b>/home/mrbreeze/public_html/test/persons.php</b> on line 14

This means you don't have any database connection: in my comment ID 40599088 I inserted in my php script the code for database connection. When I saw your persons.php I assumed you had a global connection in your application but now it seems false, since the problem is that persons.php doesn't know anything about your connection: maybe you have to include a file?
Marco:

 Ok, you lost me.

In "persons.php" there is a line that states "include 'db_connect_inc.php'".  This is the connection to the database.  This is the same inclusion in "index.php" and every other page that I'm using for the site.

Here is the relent code:

//   Check connection to database
$conn = mysql_connect('localhost', 'mrbreeze_breeze', 'Zaq12wsX#');
mysql_select_db("mrbreeze_lansco",$conn);

Glenn
breeze351

Ok, you lost me.
What do you mean? I'm not english nor american, so I'm missing omething of this phrase...

In my example, I called my db connection 'db'  and I used PDO so I wrote $this->db->query(). If your connection is called $conn and you're still using mysql, then you have to adapt my example to your engine:

$result = mysql_query(...);
while(($resultArray[] = mysql_fetch_assoc($result)) || array_pop($resultArray)); 
return json_encode($result);

Open in new window


Let me ask a thing: are you following some philosophical thery which prevents to do more than one post per day at EE? :-) We risk to never end this task ;-)

Let me say that mysql is deprecated as Php 5.5 and soon it will not be supported further. So I reccommend to migrate to mysqli or to PDO (it's a lot of work but you can find useful help here: https://www.experts-exchange.com/Programming/Languages/Scripting/PHP/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html)
Also, be careful copying and pasting code from our help: this case illustrates clearly the issues you can get through if you don't adapt our snippets to your concrete environment.

So now we can try to see what hapens using your database connection: I'm very curious... :-)
Cheers
Marco:
Sorry that I didn't reply sooner.  I've been sick again and my wife's Mom has been in the hospital.

I've modified "persons.php" to reads as follows:

<?php
include 'Session_Start.php';
include 'db_connect_inc.php';
$options = array
      (
            PDO::ATTR_PERSISTENT => true,
            PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES => true
      );

$company_id = $_POST['TID'];
$sql = "SELECT * FROM pers WHERE TID ='$company_id'";
$result =  mysql_query($sql);
while      (
                  ($resultArray[] = mysql_fetch_assoc($result)
            )
            || array_pop($resultArray));

return json_encode($result);
?>

The java in "index.php" is:
<script language="JavaScript" type="text/JavaScript">
            $( document ).ready( function ()
{

      $('#companies').on('change', function(){  //on change event of the select for Companies
            var company = $('#companies option:selected').val();
            $.ajax( {
                  type: 'post',
                  url: 'persons.php',  
                  data: {TID: company},
                  dataType: 'json',
                  success: function ( resultArray )
                  {
                              $('#agents').children().remove();
                              $( result ).each( function ( k, v )
                              {
                        $('#agents').append( "<option value='" + v['TID'] + "'>" + v['FNAME'] + "</option>" );
                         });
                  },
                  error: function ( jqXHR, textStatus, errorThrown  )
                  {
                        console.log(textStatus + " " + errorThrown);
                  }
            } );
      });
});
      </script>    

The code still doesn't return the person name in "index.php".

Can't this code all be included in "index.php"?  In other words, when there is a change in "companies", call the java script to change the values in the person drop down?
Hi guy.
Forgive me, but you have to be more careful when replace variables' names: if you write

 success: function ( resultArray )

Open in new window


then you can't write

$( result ).each( function ( k, v ) 

Open in new window


because result is an undeclared variable and the code wont work.

Anyway, I prepared a working script using mysql.

index.html:
<!DOCTYPE html>
<html>
	<head>
		<title>test</title>
		<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
		<meta name="MSSmartTagsPreventParsing" content="TRUE" />
		<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
		<script type="text/javascript">
			$( document ).ready( function() {
			 $('#category').on('change', function(){  //on change event of the select for Companies
            var category_id = $('#category option:selected').val();
            $.ajax( {
                  type: 'post',
                  url: 'script.php',  
                  data: {cid: category_id},
                  dataType: 'json',
                  success: function ( resultArray )
                  {
                    $('#products').children().remove();
                    $( resultArray ).each( function ( k, v )
                    {
                      $('#products').append( "<option value='" + v['prod_code'] + "'>" + v['prod_name'] + "</option>" );
                    });
                  },
                  error: function ( jqXHR, textStatus, errorThrown  )
                  {
                        console.log(textStatus + " " + errorThrown);
                  }
            } );
      });			} );
		</script>
		<style type="text/css">
			/*<![CDATA[*/

			/*]]>*/
		</style>
	</head>

	<body>
		
		<select id='category'>
			<option valur='1'>1</option>
			<option valur='2'>2</option>
			<option valur='3'>3</option>
		</select>
		<select id='products'>
		</select>
		
	</body>
</html>

Open in new window


script.php:
<?php
$conn = mysql_connect("localhost", "root", "password") or die(mysql_error());
mysql_select_db("database_name") or die(mysql_error());

$category_id = $_POST['cid'];
$sql = "SELECT * FROM products WHERE category='$category_id'";
$result =  mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_array($result))
{
	$resultArray[] = $row;
}
echo json_encode($resultArray);

Open in new window


You can see this example running here: http://www.webintenerife.com/testing_script/index.php (I'll leave there until your next comment)
Don't care if values in the filled select  are repeated: this depends on the database values.
Hope this helps
I'm still not getting the values for the agents.

Attached is the code.  I've changed the reference "category" to "company" and the reference "product" to "agents".

I also added "agents2" and "agents3".  If "agents1" works so should "agents2" and "agents3".

Attached is the code.

The code looks like it should work.

Glenn
index.php
Have you renamed persons.php to script.php?
No you didn't: you just forgot to use your own script instead of mine...
I said once in a my comment above you have to be careful when you use copy-and-past programming tecnique.
And now, please, tell me why I should stay here to help you when it's evident you don't give any importance or relevance to this script?
Screenshot---12-03-2015---19-21-13.jpg
Marcos:
You are correct.  There is no reason so KMA.

I'll contact exchange for some one else.
You do errors which reveal superficiality and disinterest and instead of apologize do feel offended? Stop to copy and paste  pretending that others read the code to understand why it doesn't work. You did a lot of these errors just in this thread. And the last has been to call from your script my example script.php instead of your persons.php. Anyone read this thread will understand what is the point here :-)
I'm sorry about last nights post.  I'm in pain.  I just had knee surgery last month and I went to see the doctor today and they want to operate again!!!!

I just don't understand why I can't get a return on the 2nd drop downs.   The code has the correct db connection and the sql query is very simple.  

Can you tell me if there is an easy way with "firebug" to see what's going on?  When I've tried it, I don't see any results from "persons.php".

It almost looks to me in the ajax statement that there is a missing } or ).

Glenn
Hi, Glenn. I'm really sorry for your disease and I make you my best wishes. I apologize for my reaction: I was nervous me too, even if for less serious problems. I'm just trying to help as I can and I find incredible we can get it work since it's nothing really difficult! But sometimes, even the most simple things become hard.
Well, come back to the point: now I don't see any error: it just returns null

To use Firebug, just load the page, open Firebug, click Console tab and reload the page to be sure Console is active. Then run your script: yesterday it said script.php was not found in red, but today there is no error, just persons.php returns null.
I can't understand why.

Here is the way I think your persons.php should be written now:
include 'db_connect_inc.php';
$company_id = $_POST['TID'];
$sql = "SELECT * FROM pers WHERE TID ='$company_id'";
$result =  mysql_query($sql);
while ($row = mysql_fetch_array($result))
{
	$resultArray[] = $row;
}
echo json_encode($resultArray);

Open in new window

I tried to write it in a way it should work. You can try it. Please, post every time both scripts here as they are in the server at http://test.mrbreeze.net/index.php so we can be sure we are speaking of the same code and that the code is the one gives certain result on the server. This thread is now a so  long story and we risk to make confusion, ok?
Best wishes again, Glenn. Indeed.
Oh, you often asked if you can have all code in just one file (index.php). If you want to use ajax (the more modern and efficient way) you can't. But if you just use php, then you can do it:

<?php
include 'Session_Start.php';
include 'db_connect_inc.php';
if (isset($_POST['TID']))
{
	$company_id = $_POST['TID'];
	$sql = "SELECT * FROM pers WHERE TID ='$company_id'";
	$result =  mysql_query($sql);
	while ($row = mysql_fetch_array($result))
	{
		$resultArrayAgents[] = $row;
	}
}
?>

<!DOCTYPE html>
<html>
	<head>
		<title>test</title>
		<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
		<meta name="MSSmartTagsPreventParsing" content="TRUE" />
	</head>

	<body>
		Company:
		<select id='company'>
        <?php
			$SqlString = "select * from comp order by COMPANY";
			$result =  mysql_query($SqlString) or die(mysql_error());
			while ($row = mysql_fetch_array($result))
			{
				$resultArray[] = $row;
				echo "<option value=\"{$row['TID']}\">{$row['COMPANY']}</option>";
			}
		?>			
		</select>
        <br>
        Agent 1:
			<select id='agents1'>
				<?php
				foreach($resultArrayAgents as $raa){
				?>
				<option value="<?php echo $raa['PNID'] ?>"><?php echo $raa['FNAME'] ?></option>
				<?php } ?>
			</select>
            
		<br>
                 
		Agent 2:
			<select id='agents2'>
			</select>
            
		<br>                   
        
        Agent 3:
			<select id='agents3'>
			</select>
            
		<br> 
		
	</body>
</html>

Open in new window

Be careful: I had to change the $resulArray variable name for agents to avoid conflict with the $resultArray you use for companies. In addition I found errors in your markup:
echo "<option valur='$row[TID]'>$row[COMPANY]</option>";

there is valur instead of value TID and COMPANY are without quotes so Php intyerprets them as constant. I changed this line to:

echo "<option value=\"{$row['TID']}\">{$row['COMPANY']}</option>";

If you want to test this script also, keep it separate from the other two, otherwise we really wont understand nothing :-)
Ok. Damn close.  I'm getting names back from the person file and they are populating the drop down.  The only problem is that I'm getting every person from the file.  

The query should only return those records where the "TID" in the company file is the same as the "TID" in the person file.

Look at the company "Lansco Corp".  

I know the data has to be cleaned up (blanks at the top of every drop down).

At least the doctor yesterday gave me pain killers.
Glenn
index.php
SO we have to check the value of TID: I suggest to make some edit in order to do this check. First will add an alert to the javascript immediately after the creation of company_id variable so you'll can check its value. Then we'll place another alert to display the result of persons.php and we'll edit persons.php to make it return just the value of TID

index_test.php
<?php
include 'Session_Start.php';
include 'db_connect_inc.php';
?>

<!DOCTYPE html>
<html>
	<head>
		<title>test</title>
		<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
		<meta name="MSSmartTagsPreventParsing" content="TRUE" />
		<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
		<script type="text/javascript">
			$( document ).ready( function() 
			{
				$('#company').on('change', function()
				{  //on change event of the select for Companies
          var company_id = $('#company option:selected').val();
					alert(company_id);
 		    $.ajax
				(
					{
						type: 'post',
						url: 'persons.php',  
						data: '{TID: company_id}',
						dataType: 'json',
						success: function (resultArray)
						{
							alert(resultArray); //I kept the name even if this now is not an array
            },
            error: function ( jqXHR, textStatus, errorThrown  )
            {
              console.log(textStatus + " " + errorThrown);
            }
					} );
      });	
		} );
		</script>
		<style type="text/css">
			/*<![CDATA[*/

			/*]]>*/
		</style>
	</head>

	<body>
		Company:
		<select id='company'>
        <?php
			$SqlString = "select * from comp order by COMPANY";
			$result =  mysql_query($SqlString) or die(mysql_error());
			while ($row = mysql_fetch_array($result))
			{
				$resultArray[] = $row;
				
				echo "<option valur='$row[TID]'>$row[COMPANY]</option>";
			}
		?>			
		</select>
        <br>
        Agent 1:
			<select id='agents1'>
			</select>
            
		<br>
                 
		Agent 2:
			<select id='agents2'>
			</select>
            
		<br>                   
        
        Agent 3:
			<select id='agents3'>
			</select>
            
		<br> 
		
	</body>
</html>

Open in new window


and this the persons_test.php
<?php
include 'db_connect_inc.php';
$company_id = $_POST['TID'];
echo $company_id;

Open in new window


This way we'll can be sure the company id is correct: if it isn't, then we'll know the problem, otherwise we'll know where to look (the query, probably).
Ok. Go on with this small test.
Cheers
The TID is correct.  

alert(resultArray); is returning:

[object Object],[object Object],[object Object],[object Object]................

This looks like it is for every name in the "pers" table.
index.php
Ooops, I forgot to change dataType:
						type: 'post',
						url: 'persons.php',  
						data: '{TID: company_id}',
						dataType: 'html', //instead of json
						success: function (resultArray)

Open in new window

Forgive me, I made a stupid mistake.
Ok,
I've changed it to 'html'.  

Now I can't read the alert from "alert(resultArray);", and nothing is showing up under agents.

Shouldn't persons.php be changed to something else?  Currently it reads as: "echo json_encode($resultArray);"
Yes, it must be changed to persons_test.php until we come back to index.php and persons.php with the old code. In my last post I forgot to change this... This way you should get the company id even in the second alert.

But I think we can just come back to our original scripts (index.php and persons.php) and change the javascript this way:

index.php
<!DOCTYPE html>
<html>
	<head>
		<title>test</title>
		<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
		<meta name="MSSmartTagsPreventParsing" content="TRUE" />
		<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
		<script type="text/javascript">
			$( document ).ready( function() {
			 $('#category').on('change', function(){  //on change event of the select for Companies
            var category_id = $('#category option:selected').val();
            $.ajax( {
                  type: 'post',
                  url: 'script.php',  
                  data: {cid: category_id},
                  dataType: 'json',
                  success: function ( resultArray )
                  {
                    $('#products').children().remove();
                    $( resultArray ).each( function ( k, v )
                    {
											$(v).each(function(a,b))
											{
												$('#products').append( "<option value='" + b['prod_code'] + "'>" + b['prod_name'] + "</option>" );
											});
                    });
                  },
                  error: function ( jqXHR, textStatus, errorThrown  )
                  {
                        console.log(textStatus + " " + errorThrown);
                  }
            } );
      });			} );
		</script>
		<style type="text/css">
			/*<![CDATA[*/

			/*]]>*/
		</style>
	</head>

	<body>
		
		<select id='category'>
			<option valur='1'>1</option>
			<option valur='2'>2</option>
			<option valur='3'>3</option>
		</select>
		<select id='products'>
		</select>
		
	</body>
</html>

Open in new window

Do you see what I changed? It looks like you get a multiple array of object, so we have to iterate once through the array of objects and then through each object: this is the reason why I added a nested each loop to our javascript.
There is syntax error on the line that states:
$(v).each(function(a,b))

This is the code I'm using:
$.ajax
                        (
                              {
                                    type: 'post',
                                    url: 'persons.php',  
                                    data: '{TID: company_id}',
                                    dataType: 'html',
                                    success: function (resultArray)
                                    {
                                          alert(resultArray);
                                          $('#agents1').children().remove();
                                          $('#agents2').children().remove();
                                          $('#agents3').children().remove();
                                          $(resultArray).each(function(k,v)
                                          {
                                                $(v).each(function(a,b))
                                                {
                                                      $work = v['FNAME'] + " " + v['LNAME']
                                                      $('#agents1').append( "<option value='" + v['PNID'] + "'>" + $work + "</option>" );
                                                      $('#agents2').append( "<option value='" + v['PNID'] + "'>" + $work + "</option>" );
                                                      $('#agents3').append( "<option value='" + v['PNID'] + "'>" + $work + "</option>" );
                                                }
                                }
                              );
                  },
                  error: function ( jqXHR, textStatus, errorThrown  )
                  {
                        console.log(textStatus + " " + errorThrown);
                  }
            } );
      });                  } );

I even copied your code to the same index.php and it still shows up with the same syntax error.

Do I have to change "jpos" back to "html"?
By the way, I did see what you were doing with the new code.
Sorry, replace

$(v).each(function(a,b))

Open in new window


with

$(v).each(function(a,b)

Open in new window


Just delete the last rounded parenthesis. If you get some other error and if you can provide the dump files of the two tables involved, I'll can create a database on my server and do my own tests and then provide you the working solution. Maybe this can be less frustrating for you than this daily exchange of posts :-)
I deleted the ")" and the error drops to another line.  

Shouldn't every "(" or "{" close with a ")" and "}"?
Yes, of course.

Is it the script this way?

<script type="text/javascript">
$( document ).ready( function() {
     $('#category').on('change', function(){  //on change event of the select for Companies
     var category_id = $('#category option:selected').val();
     $.ajax( {
         type: 'post',
         url: 'script.php',  
         data: {cid: category_id},
         dataType: 'json',
         success: function ( resultArray )
         {
              $('#products').children().remove();
              $( resultArray ).each( function ( k, v )
              {
		    $(v).each(function(a,b)
		    {
			$('#products').append( "<option value='" + b['prod_code'] + "'>" + b['prod_name'] + "</option>" );
		    });
               });
         },
         error: function ( jqXHR, textStatus, errorThrown  )
         {
             console.log(textStatus + " " + errorThrown);
         }
     } );
   } );			
} );
</script>

Open in new window

Ok, we're losing each other.

Three questions:
1:  I'm still not getting the "agents..." drop downs populated.

2:  I can't read what is coming back from persons.php in  index.php that reads alert(resultArray);  (line # 27).  Something is being returned, but I'm not sure what the data is.

3:  Since I've changed the data type in index.php to be "html", shouldn't there be a change in persons.php to reflect this?

I've gone over your code snippet again and everything that I'm doing seems to confirm with it.

We're so damn close on this it is frustrating.  Hope you feel the same. ;)

I've attached the code for index.php and persons.php
index.php
persons.php
Well this is damned frustrating for sure! ;-)
Mmmhhh... When I suggested to do some test I meant to create two new files called index_test.php and persons_test.php to avoid confusion and in persons_test.php the returned value was just $company_id, not an array, and you had to use test files for this test and leave the original as they were... but you have merged the things and this is not nice for us :-)
In addition I see now that in persons.php you're checking $_POST['company'] but we're passing company_id as 'TID', and so we have to check for this:

$company_id = $_POST['TID'];

Open in new window


I recreated the two test files: please, download them and upload them to your test server and see what happens.
If this damned company_id is correct, I'll post here other two files which in my opinion should work: if they wont work, then we have a bad result from the sql query.

I feel like you: now I can't be happy until this question is solved. I know it's an easy thing and it doesn't make sense we can get it working!

We'll succeed!
persons-test.php
index-test.php
No joy :(

I changed the following 2 lines in index-test.php

From:
url: 'script.php',  
data: {cid: category_id},

To:
url: 'persons-test.php',  
data: {TID: company_id},

The alert on company_id works and shows the correct id, but that's it.  The alert on result does nothing and neither does the echo statement in persons-test.php
Ok, I'll use the response array to build a table and do tests myself: if I can play directly with the code, maybe I can understand what's going wrong here. I'll come back when I have done, ok?
Cheers
Sorry, Glenn, I have to ask you to give me the dump of your table. It's too long to rebuild it using data to understand the structure and it's too long prepare a script to put data in the table. If you can give the dump, I'll can work on this damn piece of code :-) Just open PhpMyAdmin select the table choose export and post here the file. For the companies I can put just two with the right values to trigger the event and do the test, but I need pers table to work.
Thank you
Hope this helps

Let me know if you want  csv files or a different format.

The company_id (TID) I was using is "00000619".  It has agents in the pers file.
mrbreeze-lansco.sql
Thank you very mutch, Glenn. I'm anxious to try :)
Glenn, the dump file you gave me holds only two table, avenue_grid and bld1. Table pers is mising and there's no TID column in tables I have...
I exported from MySql and selected the 2 files.  Evidentally, it started at the top and didn't use the selected files.  

The attached zip has the 2 files you need as both sql and csv.  I had to rename the files as ".txt" to get them to load in the zip to Experts.  For some reason it didn't like the extensions.

The company's that you are looking for with agents attached are:

00000619 - Lansco Corp
00002601 - CBRE

The data from my customer stinks.  You can see that there are allot of blank records and duplicates.  It's either they clean it up or I can write something and charge them (I'm hoping for the second).

Hope this works better for you.

If you want to take a look at the site, it's at "test.mrbreeze.net".  I'm not worried about it since all the data is only for testing and not live.
mrbreeze.zip
Ok, thank you. (I know the test site, I've played a lot in last weeks :-)
Cheers
Hi Glenn.

Following code seems to work: it gives 86 matches and the same gives my code. You can see the running script at http://www.webintenerife.com/testing_script/index.php

So, just copyng and pasting this code should give you the same results in your server: the only difference here is that I've not included Session_Start.php and obviously my connection data are different ;-). Please, take a look at my running script above, then copy and paste the following code without changing nothing. I'll wait to hear the results.

Cheers

index.php
<!DOCTYPE html>
<html>
	<head>
		<title>test</title>
		<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
		<meta name="MSSmartTagsPreventParsing" content="TRUE" />
		<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
		<script type="text/javascript">
			$( document ).ready( function ()
			{
				$( '#company' ).on( 'change', function () {  //on change event of the select for Companies
					var company_id = $( '#company option:selected' ).val();
					var count = 0;
					$.ajax(
					{
						type: 'post',
						url: 'persons.php',
						data: { TID: company_id },
						dataType: 'json',
						success: function ( resultArray )
						{
							$('#agents1').children().remove();
							$('#agents2').children().remove();
							$('#agents3').children().remove();
							$( resultArray ).each( function ( k, v )
							{
								$( v ).each( function ( a, b )
								{
									$work = b['FNAME'] + " " + b['LNAME']
									$( '#agents1' ).append( "<option value='" + b['PNID'] + "'>" + $work + "</option>" );
									$( '#agents2' ).append( "<option value='" + b['PNID'] + "'>" + $work + "</option>" );
									$( '#agents3' ).append( "<option value='" + b['PNID'] + "'>" + $work + "</option>" );
									count++;
								} );
							} );
							$('#queryResult').text(count);
						},
						error: function ( jqXHR, textStatus, errorThrown )
						{
							console.log( textStatus + " " + errorThrown );
						}
					} );
				} );
			} );
		</script>
		<style type="text/css">
			/*<![CDATA[*/
			/*]]>*/
		</style>
	</head>

	<body>
		Company:
		<select id='company'>
			<?php
			include 'db_connect_inc.php';
			$SqlString = "select * from comp order by COMPANY";
			$result = mysql_query( $SqlString ) or die( mysql_error() );
			$resultArray = array();
			while ( $row = mysql_fetch_array( $result ) )
			{
				$resultArray[] = $row;
				echo "<option value='$row[TID]'>$row[COMPANY]</option>";
			}
			?>			
		</select>
		<br>
		<h3>Records returned by query: <span id="queryResult"></span></h3>
		Agent 1:
		<select id='agents1'>
		</select>

		<br>

		Agent 2:
		<select id='agents2'>
		</select>

		<br>                   

		Agent 3:
		<select id='agents3'>
		</select>

		<br> 
	</body>
</html>

Open in new window


persons.php
<?php
include 'db_connect_inc.php';
$company_id = $_POST['TID'];
$sql = "SELECT * FROM pers WHERE TID ='$company_id'";
$result =  mysql_query($sql);
while ($row = mysql_fetch_array($result))
{
	$resultArray[] = $row;
}
echo json_encode($resultArray);
?>

Open in new window

Thank you! Thank you!

I would do a happy dance but my knees are screwed up!

One more question on this.  It has to do with data.  I have 3 agents that can be selected.  Would you create a "Null" agent so that if there is only 1 selected, the other 2 default to the null ID?
You could jut add an option to the three selects:

<!DOCTYPE html>
<html>
	<head>
		<title>test</title>
		<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
		<meta name="MSSmartTagsPreventParsing" content="TRUE" />
		<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
		<script type="text/javascript">
			$( document ).ready( function ()
			{
				$( '#company' ).on( 'change', function () {  //on change event of the select for Companies
					var company_id = $( '#company option:selected' ).val();
					var count = 0;
					$.ajax(
					{
						type: 'post',
						url: 'persons.php',
						data: { TID: company_id },
						dataType: 'json',
						success: function ( resultArray )
						{
							$('#agents1').children().remove();
							$('#agents2').children().remove();
							$('#agents3').children().remove();
							$('#agents1').append('<option>--Choose--</option>')
							$('#agents2').append('<option>--Choose--</option>')
							$('#agents3').append('<option>--Choose--</option>')
							$( resultArray ).each( function ( k, v )
							{
								$( v ).each( function ( a, b )
								{
									$work = b['FNAME'] + " " + b['LNAME']
									$( '#agents1' ).append( "<option value='" + b['PNID'] + "'>" + $work + "</option>" );
									$( '#agents2' ).append( "<option value='" + b['PNID'] + "'>" + $work + "</option>" );
									$( '#agents3' ).append( "<option value='" + b['PNID'] + "'>" + $work + "</option>" );
									count++;
								} );
							} );
							$('#queryResult').text(count);
						},
						error: function ( jqXHR, textStatus, errorThrown )
						{
							console.log( textStatus + " " + errorThrown );
						}
					} );
				} );
			} );
		</script>
		<style type="text/css">
			/*<![CDATA[*/
			/*]]>*/
		</style>
	</head>

	<body>
		Company:
		<select id='company'>
			<?php
			include 'db_connect_inc.php';
			$SqlString = "select * from comp order by COMPANY";
			$result = mysql_query( $SqlString ) or die( mysql_error() );
			$resultArray = array();
			while ( $row = mysql_fetch_array( $result ) )
			{
				$resultArray[] = $row;
				echo "<option value='$row[TID]'>$row[COMPANY]</option>";
			}
			?>			
		</select>
		<br>
		<h3>Records returned by query: <span id="queryResult"></span></h3>
		Agent 1:
		<select id='agents1'>
		</select>

		<br>

		Agent 2:
		<select id='agents2'>
		</select>

		<br>                   

		Agent 3:
		<select id='agents3'>
		</select>

		<br> 
	</body>
</html>

Open in new window


Then, in the code which processes data you'll can do something like:
if ($_POST['agents1'] != '--Choose--')
{
...

Open in new window

and the same for other selects
That works on entry.  But what if this in an update.  There are 3 agents and they want to drop agent #3.  I was thinking that if they would select the "Null" agent.
ASKER CERTIFIED SOLUTION
Avatar of Marco Gasi
Marco Gasi
Flag of Spain image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Mario
Thank you again for all you time with this.  I wish I could give you an A+
I said we would succeess. Thanks for points and good luck, Glenn. On to the next.