Solved

Populating 2 different drop downs

Posted on 2015-02-06
69
45 Views
Last Modified: 2015-03-22
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
0
Comment
Question by:breeze351
  • 36
  • 32
69 Comments
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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

0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
0
 

Author Comment

by:breeze351
Comment Utility
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
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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

0
 

Author Comment

by:breeze351
Comment Utility
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
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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.
0
 

Author Comment

by:breeze351
Comment Utility
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
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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...
0
 

Author Comment

by:breeze351
Comment Utility
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
0
 

Author Comment

by:breeze351
Comment Utility
Sorry, must have hit the wrong button.
Thanks
Glenn
persons.php
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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.
0
 

Author Comment

by:breeze351
Comment Utility
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
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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.
0
 

Author Comment

by:breeze351
Comment Utility
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
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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.
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
You declare the doctype twice. Delete the first one and keep the second (<!DOCTYPE html>).
0
 

Author Comment

by:breeze351
Comment Utility
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?
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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 :-(
0
 

Author Comment

by:breeze351
Comment Utility
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
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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
0
 

Author Comment

by:breeze351
Comment Utility
I have done as requested.

The url is http://test.mrbreeze.net/index.php
index.php
persons.php
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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 :-)
0
 

Author Comment

by:breeze351
Comment Utility
No joy!
I'm in New Jersey.
0
 

Author Comment

by:breeze351
Comment Utility
What do I do?
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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.
0
 

Author Comment

by:breeze351
Comment Utility
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
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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 :-)
0
 

Author Comment

by:breeze351
Comment Utility
Ok, I've added the quotes around the url.  What's next
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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?
0
 

Author Comment

by:breeze351
Comment Utility
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
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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: http://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
0
 

Author Comment

by:breeze351
Comment Utility
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?
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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
0
 

Author Comment

by:breeze351
Comment Utility
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
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
Have you renamed persons.php to script.php?
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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
0
 

Author Comment

by:breeze351
Comment Utility
Marcos:
You are correct.  There is no reason so KMA.

I'll contact exchange for some one else.
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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 :-)
0
 

Author Comment

by:breeze351
Comment Utility
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
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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.
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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 :-)
0
 

Author Comment

by:breeze351
Comment Utility
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
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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
0
 

Author Comment

by:breeze351
Comment Utility
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
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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.
0
 

Author Comment

by:breeze351
Comment Utility
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);"
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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.
0
 

Author Comment

by:breeze351
Comment Utility
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"?
0
 

Author Comment

by:breeze351
Comment Utility
By the way, I did see what you were doing with the new code.
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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 :-)
0
 

Author Comment

by:breeze351
Comment Utility
I deleted the ")" and the error drops to another line.  

Shouldn't every "(" or "{" close with a ")" and "}"?
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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

0
 

Author Comment

by:breeze351
Comment Utility
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
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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
0
 

Author Comment

by:breeze351
Comment Utility
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
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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
0
 

Author Comment

by:breeze351
Comment Utility
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
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
Thank you very mutch, Glenn. I'm anxious to try :)
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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...
0
 

Author Comment

by:breeze351
Comment Utility
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
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
Ok, thank you. (I know the test site, I've played a lot in last weeks :-)
Cheers
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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

0
 

Author Comment

by:breeze351
Comment Utility
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?
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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
0
 

Author Comment

by:breeze351
Comment Utility
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.
0
 
LVL 30

Accepted Solution

by:
Marco Gasi earned 500 total points
Comment Utility
Then add 'Null' or 'None' instead of 'Choose'.
0
 

Author Closing Comment

by:breeze351
Comment Utility
Mario
Thank you again for all you time with this.  I wish I could give you an A+
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
I said we would succeess. Thanks for points and good luck, Glenn. On to the next.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

JavaScript can be used in a browser to change parts of a webpage dynamically. It begins with the following pattern: If condition W is true, do thing X to target Y after event Z. Below are some tips and tricks to help you get started with JavaScript …
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now