Listbox option value info pulled from MySQL 1

[rogerzebra]

Hi all,

I definately need someone to help me I'm pretty new to this so please have patience. I'm trying to find out on how to retrive information from a database depending of what selected option you have made in a listform.

I have a form_layout.html page including the listbox with several options in it and a submit button and a select_it.php page with the database connection and a query on what to select. When i submit the button on the html page it shows me the whole table information wich was not a supprise but that is not what I want it to do. I want it to show me the information based on what option you've made in the listbox showing me the result for that specific row (producer info) pulled out from the database. I know that my basic script is far from what I want, so please can anyone help me with this.

This is my code

<form name="form1" method="post" action="select_it.php">
<p align="center">
<select name="Producers" size="1" id="Producers">

<option value="Alcott Insurance Services">Alcott Insurance Services</option>
<option value="Owen-Dunn Insurance">Owen-Dunn Insurance</option>
<option value="Pehaim &amp; Snipper Ins Svc Inc">Pehaim &amp; Snipper
Ins Svc Inc</option>
<option value="Dwight Gove Agency Inc">Dwight Gove Agency Inc</option>
etc..
</select>
<br>
<br>
<br>
<input type="submit" name="Submit" value="Submit">
<br>
</p>
<p>&nbsp;</p>
</form>

select_it.php page

<?php

// connect to server, database, table.
include 'db_intranet.php';

// retrieve the new information from the database

$result = mysql_query("SELECT * FROM producers");


while ($row = mysql_fetch_array($result)) {
print $row['producer_code'].','.$row['company_name'].','.$row['contacts_1'].'<br>';
}

?>

I hope my explaination is clear and understandable.
Thank you in advance
/rz

 

[kenrbnsn]

You have to add a "where" clause to your select:

$result = mysql_query("SELECT * FROM producers where company_name='" . $_POST['Producers'] . "'");

This is just an example. I've done no checking for correctness of the input.

Ken

 

[rogerzebra]

Hi Ken,
Thanks for your respond I really appreciate it. Your addition almost did it. Now it actually shows me just one row but it only works for the first option in the listbox. The other options shows me a blank page.
Any ideas?

<?php

// connect to server, database, table.
include 'db_intranet.php';

// retrieve the new information from the database

$result = mysql_query("SELECT * FROM producers WHERE company_name='" .
$_POST['Producers'] . "'");


while ($row = mysql_fetch_array($result)) {
print $row['producer_code'].','.$row['company_name'].','.$row['contacts_1'].'<br>';
}

?>

 

[HMarcBower]

That is strange, because with the addition that Ken gave you it should work just fine. So the problem that is happening now, if I understand it correctly, is that your dropdown list has the four companies in it, but if you select any of the bottom three it doesn't actually show you the record it presumably gets from the database?

Something to try for troubleshooting purposes is to add the following lines right above your $result= line:

echo("POST variable: ".$_POST['Producers']."<BR>");
echo("sql query: ".$result."<BR><BR>");

This will tell you what the values are that your query has to work with, so you can see if everything is getting passed along correctly... but it is strange indeed that it would work with the top item in the select and no other ones. Let us know the results of the lines above when you select one of the non-working ones and we can go from there.

Marc

 

[rogerzebra]

Hi HMarcBower and thank you.

When I tried a another option in the listbox it shows the clients name but still not anyone of the other columns in my database.

POST variable: G.S. Levine Insurance Services
sql query:

Is it something I need to change maybe in my formpage?

 

[rogerzebra]

Hi HMarcBower and thank you.

When I tried another option in the listbox it shows the clients name but still not any of the information in the other columns from my database. This is what i get with your code:

POST variable: G.S. Levine Insurance Services
sql query:

Is it something I need to change maybe in my formpage?

 

[rogerzebra]

Marc and Ken,
I wasn't sure if you understood my last post. Yes it's correct that it only shows the result from the very top one in the listbox. So now I start to wonder that something need to be changed on the formpage. If any of you have a clue of how to solve this, please let me know. Thank you anyway for your input so far.
/rz

 

[HMarcBower]

Something you could try is to make the values numeric, which means you'd have to add an index field to your table in the db. Functionally it shouldn't make a lot of difference, but it would eliminate the possibility of string-matching problems.

For example:

<select name="Producers" size="1" id="Producers">

<option value="1">Alcott Insurance Services
<option value="2">Owen-Dunn Insurance
<option value="3">Pehaim &amp; Snipper Ins Svc Inc
<option value="4">Dwight Gove Agency Inc
etc.. 
</select>

Something else that occurred to me while I was editing the above code... I don't think you need to end the <option> tag. Try removing all of the </option>s and see if that helps before you restructure the db to include a numeric index field. I've never used </option> and it's possible that this tag is interfering since after its very first occurrance you're experiencing the issue.

Marc

 

[Vragabond]

</option> tag is neccessary for xhtml, because all elements must be closed. Therefore I suggest you continue using it. It should not interfere with the code at all.

 

[HMarcBower]

OK, there ya go.

Thanks for clearing that up, Vragabond.

Marc

 

[Bastien]

i wrote a function to do this:

http://www.weberdev.com/get_example-3852.html

Bastien

Cat, the other other white meat

 

[rogerzebra]

Wow,
thank you guys, that was a quick reply. I'll try it out and let you how is goes.
thanks guys.
/rz

 

[rogerzebra]

ok, I tried your code Marc my producer_code works as an index already and company_name field is my primary key, so I just added the option value numbers on the form page and changed my producer_code to 1,2 etc. after the changes I just get a blank page not even the data from the first row this time.


<select name="Producers" size="1" id="Producers">
<option value="1">Alcott Insurance Services</option>
<option value="2" selected>G.S. Levine Insurance Services</option>


..I haven't tried Bastiens code just yet, it seems to advanced for me, but I'll give it a try.
what next to do?

 

[HMarcBower]

If you modified the values of the select items, did you go and modify your query appropriately as well?

$result = mysql_query("SELECT * FROM producers WHERE producer_code='".$_POST['Producers']."'");

Marc

 

[HMarcBower]

Something else to try is putting a different one at the top of the list to see if it will pull up that information. Just a long-shot, but it will tell you that the data and the query are OK. Maybe post again your php pages and an output of the table.

Marc

 

[rogerzebra]

Marc,
you did it. thanks man it needed to be producer_code in hthe where clause and the correct index number for the producer_code.
thanks a lot.