Dropdown List from Database, how to pass result to second PHP page

[JamesFlowers]

My Code (as below), connects to the DB , returns a list of customers from the table , how can I pass this to a second php page to read into a sql on that PHP to return the clients details?

Many thanks

James

<?php
$link = mysql_connect('*******') or die('Could not connect: ' . mysql_error());
mysql_select_db('*****') or die('Could not select database');
$query = "select customerName from customers order by customerName";
$result = mysql_query($query) or die(mysql_error());
$dropdown = "<select name='users'>";
while($row = mysql_fetch_assoc($result)) {$dropdown .= "\r\n<option value='{$row['customerName']}'>{$row['customerName']}</option>";}
$dropdown .= "\r\n</select>";
echo $dropdown;
?>

James Flowers
Crystal Consultant

 

[jpadie]

put the select in a form. point the form's action at the receiving page.

assuming the form's method is POST, the customer name will be in $_POST['users']. normally, however, you would pass the customer and not the customerName as the first is guaranteed unique.

<?php
$link = mysql_connect('*******') or die('Could not connect: ' . mysql_error());
mysql_select_db('*****') or die('Could not select database');
$query = "select customerID, customerName from customers order by customerName";
$result = mysql_query($query) or die(mysql_error());
$dropdown = "<select name='users'>";
while($row = mysql_fetch_assoc($result)) {$dropdown .= "\r\n<option value=\"{$row['customerID\"]}'>{$row['customerName']}</option>";}
$dropdown .= "\r\n</select>";
echo $dropdown;
?>

 

[JamesFlowers]

ok , I dont mean to be rude , but my customerNames are unique...they are known in this table by a code.

'put the select in a form'

What elements go here the SQL select or $dropdown?


<form name="input" action="cust_detail.php" method="POST">
<input type="text" name="$dropdown">
<input type="submit" value="Submit">
</form>


Im not sure what you mean ?

thanks

James Flowers
Crystal Consultant

 

[jpadie]

if customerName is a code and is unique then obviously no problem. your select generation code is fine. the form would look like this

[COLOR=#990000]<[/color]form method[COLOR=#990000]=[/color][COLOR=#FF0000]"post"[/color] action[COLOR=#990000]=[/color][COLOR=#FF0000]"somepage.php"[/color][COLOR=#990000]>[/color]
[COLOR=#990000]<[/color]fieldset[COLOR=#990000]>[/color]
[COLOR=#990000]<?php[/color] [b][COLOR=#0000FF]echo[/color][/b] [COLOR=#009900]$dropdown[/color][COLOR=#990000];[/color] [COLOR=#990000]?>[/color]
[COLOR=#990000]<[/color]br[COLOR=#990000]/>[/color]
[COLOR=#990000]<[/color]input type[COLOR=#990000]=[/color][COLOR=#FF0000]"submit"[/color] name[COLOR=#990000]=[/color][COLOR=#FF0000]"xsubmit"[/color] value[COLOR=#990000]=[/color][COLOR=#FF0000]"Submit"[/color] [COLOR=#990000]/>[/color]
[COLOR=#990000]</[/color]fieldset[COLOR=#990000]>[/color]
[COLOR=#990000]</[/color]form[COLOR=#990000]>[/color]

some asides:
1. there is no requirement for the containing element to be a fieldset, but to be compliant with the html spec the controls do need to be contained in a block level element (such as fieldset, div etc)
2. didn't think that form elements had a name attribute. but I may be wrong.

then in your receiving script

<?php
[b][COLOR=#0000FF]if[/color][/b][COLOR=#990000]([/color][b][COLOR=#0000FF]isset[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$_POST[/color][COLOR=#990000][[/color][COLOR=#FF0000]'user'[/color][COLOR=#990000]])):[/color]
  [COLOR=#009900]$link[/color] [COLOR=#990000]=[/color] [b][COLOR=#000000]mysql_connect[/color][/b][COLOR=#990000]([/color][COLOR=#FF0000]'*******'[/color][COLOR=#990000])[/color] [b][COLOR=#0000FF]or[/color][/b] [b][COLOR=#0000FF]die[/color][/b][COLOR=#990000]([/color][COLOR=#FF0000]'Could not connect: '[/color] [COLOR=#990000].[/color] [b][COLOR=#000000]mysql_error[/color][/b][COLOR=#990000]());[/color]
  [b][COLOR=#000000]mysql_select_db[/color][/b][COLOR=#990000]([/color][COLOR=#FF0000]'*****'[/color][COLOR=#990000])[/color] [b][COLOR=#0000FF]or[/color][/b] [b][COLOR=#0000FF]die[/color][/b][COLOR=#990000]([/color][COLOR=#FF0000]'Could not select database'[/color][COLOR=#990000]);[/color]
  [COLOR=#009900]$sql[/color] [COLOR=#990000]=[/color] [COLOR=#FF0000]"Select * from customers where customerName='%s'"[/color][COLOR=#990000];[/color]
  [COLOR=#009900]$query[/color] [COLOR=#990000]=[/color] [b][COLOR=#000000]sprintf[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$sql[/color][COLOR=#990000],[/color] [b][COLOR=#000000]mysql_real_escape_string[/color][/b][COLOR=#990000]([/color] [COLOR=#009900]$_POST[/color][COLOR=#990000][[/color][COLOR=#FF0000]'user'[/color][COLOR=#990000]][/color] [COLOR=#990000])[/color] [COLOR=#990000]);[/color]
  [COLOR=#009900]$result[/color] [COLOR=#990000]=[/color] [b][COLOR=#000000]mysql_query[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$query[/color][COLOR=#990000])[/color] [b][COLOR=#0000FF]or[/color][/b] [b][COLOR=#0000FF]die[/color][/b] [COLOR=#990000]([/color][b][COLOR=#000000]mysql_error[/color][/b][COLOR=#990000]());[/color]
  [COLOR=#009900]$row[/color] [COLOR=#990000]=[/color] [b][COLOR=#000000]mysql_fetch_assoc[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$result[/color][COLOR=#990000]);[/color]
  [b][COLOR=#0000FF]echo[/color][/b] [COLOR=#FF0000]'<pre>'[/color][COLOR=#990000].[/color] [b][COLOR=#000000]print_r[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$row[/color][COLOR=#990000],[/color] true[COLOR=#990000])[/color] [COLOR=#990000].[/color] [COLOR=#FF0000]'</pre>'[/color][COLOR=#990000];[/color]
[b][COLOR=#0000FF]else[/color][/b][COLOR=#990000]:[/color]
  [b][COLOR=#0000FF]echo[/color][/b] [COLOR=#FF0000]"No customerName supplied"[/color][COLOR=#990000];[/color]
[b][COLOR=#0000FF]endif[/color][/b][COLOR=#990000];[/color]
?>

 

[jpadie]

please ensure that you always post code between [ignore]

[/ignore] tags.

 

[JamesFlowers]

In the above somepage.php the ($_POST['user'])): is declared


where is 'user' defined in the <form method> php file ?

how is this variable passed to the sql statement in the somepage.php

thanks

James Flowers
Crystal Consultant

 

[vacunita]

In jpadie's PHP code the variable name should be users as that's the name of your dropdown. I suspect its a simple typo:
$_POST['users'] instead of $_POST['user']

how is this variable passed to the sql statement in the somepage.php

Using the sprintf function, its value replaces the %s placeholder in the $sql variable that holds the query.

The query is then executed using the mysql_query method.

[COLOR=#009900]$sql[/color] [COLOR=#990000]=[/color] [COLOR=#FF0000]"Select * from customers where customerName='[COLOR=#204A87][b]%s[/b][/color]'"[/color][COLOR=#990000];[/color]
  [COLOR=#009900]$query[/color] [COLOR=#990000]=[/color] [b][COLOR=#000000]sprintf[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$sql[/color][COLOR=#990000],[/color] [b][COLOR=#000000]mysql_real_escape_string[/color][/b][COLOR=#990000]([/color] [COLOR=#009900]$_POST[/color][COLOR=#990000][[/color][COLOR=#FF0000]'[COLOR=#440099]users[/color]'[/color][COLOR=#990000]][/color] [COLOR=#990000])[/color] [COLOR=#990000]);[/color]

The mysql_real_escape_string function merely cleanses the variable for use in the query.


----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[JamesFlowers]

and so what is %s then please?



James Flowers
Crystal Consultant

 

[jpadie]

A placeholder.

 

[vacunita]

its value replaces the %s placeholder.

Yup.





----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[JamesFlowers]

Cool thanks that worked

it is printing as

Array ( [customername] => 293 )

how can this be utilised further , can you point to any examples on the web please?

James

James Flowers
Crystal Consultant

 

[jpadie]

How further do you want to use it?

 

[JamesFlowers]

This passes all customer details and format them using css and div tags as a display screen , and then use the customer number (which I have now been told there is one) to pull up there orders below that


name xxxx
address xxxx
details xxxx

order date product value
xxxx xxxx xxxxxx xxxx

etc


James Flowers
Crystal Consultant

 

[jpadie]

so construct a SQL statement using the customerName/number to retrieve those details and display them.
we can't help with that without knowing
1. what detail you want,
2. how you want them displayed
3. your table schemas.

 

[JamesFlowers]

select c.cust_id,c.customername , c.address, c.details , o.order, o.date, o.product, o.value from
customer c , order o
where c.cust_id =o.cust_id

so the array that is created above Array ( [customername] => 293
is to be passed to the c.cust_id as a parameter

which I can then place in a table on my browser and position with div tags

thanks

James Flowers
Crystal Consultant

 

[jpadie]

here is an example with a simple table. swap out the table references if you want to use divs and floats

[COLOR=#990000]<?php[/color]
[b][COLOR=#0000FF]if[/color][/b][COLOR=#990000]([/color][b][COLOR=#0000FF]isset[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$_POST[/color][COLOR=#990000][[/color][COLOR=#FF0000]'users'[/color][COLOR=#990000]])):[/color]
  [COLOR=#009900]$link[/color] [COLOR=#990000]=[/color] [b][COLOR=#000000]mysql_connect[/color][/b][COLOR=#990000]([/color][COLOR=#FF0000]'*******'[/color][COLOR=#990000])[/color] [b][COLOR=#0000FF]or[/color][/b] [b][COLOR=#0000FF]die[/color][/b][COLOR=#990000]([/color][COLOR=#FF0000]'Could not connect: '[/color] [COLOR=#990000].[/color] [b][COLOR=#000000]mysql_error[/color][/b][COLOR=#990000]());[/color]
  [b][COLOR=#000000]mysql_select_db[/color][/b][COLOR=#990000]([/color][COLOR=#FF0000]'*****'[/color][COLOR=#990000])[/color] [b][COLOR=#0000FF]or[/color][/b] [b][COLOR=#0000FF]die[/color][/b][COLOR=#990000]([/color][COLOR=#FF0000]'Could not select database'[/color][COLOR=#990000]);[/color]
  [COLOR=#009900]$sql[/color] [COLOR=#990000]=[/color] [COLOR=#990000]<<<[/color]SQL
SELECT c[COLOR=#990000].[/color]cust_id[COLOR=#990000],[/color]c[COLOR=#990000].[/color]customername [COLOR=#990000],[/color] c[COLOR=#990000].[/color]address[COLOR=#990000],[/color] c[COLOR=#990000].[/color]details [COLOR=#990000],[/color] o[COLOR=#990000].[/color]order[COLOR=#990000],[/color] o[COLOR=#990000].[/color]date[COLOR=#990000],[/color] o[COLOR=#990000].[/color]product[COLOR=#990000],[/color] o[COLOR=#990000].[/color]value 
FROM customer c 
JOIN order o on c[COLOR=#990000].[/color]cust_id [COLOR=#990000]=[/color] o[COLOR=#990000].[/color]cust_id
WHERE c[COLOR=#990000].[/color]cust_id [COLOR=#990000]=[/color] [COLOR=#FF0000]'%s'[/color]
SQL[COLOR=#990000];[/color]
  [COLOR=#009900]$query[/color] [COLOR=#990000]=[/color] [b][COLOR=#000000]sprintf[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$sql[/color][COLOR=#990000],[/color] [b][COLOR=#000000]mysql_real_escape_string[/color][/b][COLOR=#990000]([/color] [COLOR=#009900]$_POST[/color][COLOR=#990000][[/color][COLOR=#FF0000]'users'[/color][COLOR=#990000]][/color] [COLOR=#990000])[/color] [COLOR=#990000]);[/color]
  [COLOR=#009900]$result[/color] [COLOR=#990000]=[/color] [b][COLOR=#000000]mysql_query[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$query[/color][COLOR=#990000])[/color] [b][COLOR=#0000FF]or[/color][/b] [b][COLOR=#0000FF]die[/color][/b] [COLOR=#990000]([/color][b][COLOR=#000000]mysql_error[/color][/b][COLOR=#990000]());[/color]
[COLOR=#990000]?>[/color]
[COLOR=#990000]<[/color]table[COLOR=#990000]>[/color]
 [COLOR=#990000]<[/color]thead[COLOR=#990000]>[/color]
  [COLOR=#990000]<[/color]tr[COLOR=#990000]><[/color]th[COLOR=#990000]>[/color]Customer ID[COLOR=#990000]</[/color]th[COLOR=#990000]><[/color]th[COLOR=#990000]>[/color]Customer Name[COLOR=#990000]</[/color]th[COLOR=#990000]><[/color]th[COLOR=#990000]>[/color]Address[COLOR=#990000]</[/color]th[COLOR=#990000]><[/color]th[COLOR=#990000]>[/color]Details[COLOR=#990000]</[/color]th[COLOR=#990000]><[/color]th[COLOR=#990000]>[/color]Order[COLOR=#990000]</[/color]th[COLOR=#990000]><[/color]th[COLOR=#990000]>[/color]Date[COLOR=#990000]</[/color]th[COLOR=#990000]><[/color]th[COLOR=#990000]>[/color]Product[COLOR=#990000]</[/color]th[COLOR=#990000]><[/color]th[COLOR=#990000]>[/color]Value[COLOR=#990000]</[/color]th[COLOR=#990000]></[/color]tr[COLOR=#990000]>[/color]
 [COLOR=#990000]</[/color]thead[COLOR=#990000]>[/color]
[COLOR=#990000]<?php[/color] [b][COLOR=#0000FF]while[/color][/b] [COLOR=#990000]([/color][COLOR=#009900]$row[/color] [COLOR=#990000]=[/color] [b][COLOR=#000000]mysql_fetch_assoc[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$result[/color][COLOR=#990000])):?>[/color]
  [COLOR=#990000]<[/color]tbody[COLOR=#990000]>[/color]
   [COLOR=#990000]<[/color]tr[COLOR=#990000]><[/color]td[COLOR=#990000]><?php[/color] [b][COLOR=#0000FF]echo[/color][/b] [b][COLOR=#000000]implode[/color][/b][COLOR=#990000]([/color][COLOR=#FF0000]'</td><td>'[/color][COLOR=#990000],[/color] [b][COLOR=#000000]array_map[/color][/b][COLOR=#990000]([/color][COLOR=#FF0000]'htmlspecialchars'[/color][COLOR=#990000],[/color] [COLOR=#009900]$row[/color][COLOR=#990000]));?></[/color]td[COLOR=#990000]></[/color]tr[COLOR=#990000]>[/color]
  [COLOR=#990000]</[/color]tbody[COLOR=#990000]>[/color]
[COLOR=#990000]<?php[/color] [b][COLOR=#0000FF]endwhile[/color][/b][COLOR=#990000];[/color] [COLOR=#990000]?>[/color]
[COLOR=#990000]</[/color]table[COLOR=#990000]>[/color]
[COLOR=#990000]<?php[/color]
[b][COLOR=#0000FF]else[/color][/b][COLOR=#990000]:[/color]
  [b][COLOR=#0000FF]echo[/color][/b] [COLOR=#FF0000]"No customerName supplied"[/color][COLOR=#990000];[/color]
[b][COLOR=#0000FF]endif[/color][/b][COLOR=#990000];[/color]
[COLOR=#990000]?>[/color]

 

[JamesFlowers]

cheers

James Flowers
Crystal Consultant