dropdown list - help pls

[microsoftboy]

Hi,

Using the following selection criteria, I am able to list the data from mysql db, but I would like to provide the following options for users: Select All Data, Select None. I am not sure if this is possiible. Any help would be greatly appreciated.


Here's my code

<h4>SEARCH</h4>

<form action="<?php echo $_SERVER['PHP_SELF'] ?>" method = "post" target = "right">

//1st DROPDOWN

Date<br>
<select size="1" name="search">
<?php

$con = mysql_connect("localhost","****","****");
if (!$con) {die('Database is down: ' . mysql_error());}
mysql_select_db("test",$con);

$query = ("select distinct date from **** order by date desc");
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
echo "<option value=\"$row[date]\">$row[date]</option>";
}
?>
</select>


//2nd DROPDOWN

<br><br>
Product<br>
<select size="1" name="search1">
<?php
$con = mysql_connect("localhost","****","****");
if (!$con) {die('Database is down: ' . mysql_error());}
mysql_select_db("test",$con);

$query = ("select distinct productname from *** order by productname desc");
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
//echo "<option value=\"$row[date]\">.$row[date].'</option>'";
echo "<option value=\"$row[productname]\">$row[productname]</option>";
}
?>
</select>

<input type="Submit" value="Submit" name="Submit">
</form>



//ASSINGING THE SELECTED FIELDS TO A VARIABLE

$search = empty($_POST['search'])? die ("ERROR: Enter Search Criteria") : mysql_escape_string($_POST['search']); 

$search1 = empty($_POST['search1'])? die ("ERROR: Enter Search Criteria") : mysql_escape_string($_POST['search1']); 



//QUERY
$query = "SELECT * FROM ***  where date = '$search' and productname = '$search1'  or die (mysql_error()); 
$result = mysql_query($query) or die (mysql_error()); 
$num = mysql_numrows($result); 

mysql_close($connect);

//OUTPUT
***

[Dormilich]

of course this is possible. you can use for instance a dropdown field (or radios) to select a fixed SQL statement.

ex.

// HTML
<input type="radio" name="sql" value="1" checked>
<input type="radio" name="sql" value="2">
<input type="radio" name="sql" value="3">

// PHP
$sql_type = (int) $_POST['sql'];
if (1 == $sql_type) // that's only one possibility
{
    $sql = "SELECT * FROM mytable";
}
elseif (2 == $sql_type)
{
    $sql = "SELECT `id` FROM mytable";
}
// etc.

[microsoftboy]

Thanks a lot.

I am rewriting my code, I will post the results soon..

[microsoftboy]

imm..I think I am making some mistakes..

I've modified my code as mentioned below--

...

<input type="radio" name="product[]" value="1" /> Select All <br>
<input type="radio" name="product[]" value="2" /> ProductName1 <br>
<input type="radio" name="product[]"  value="3" /> ProductName2<br>

<input type="Submit" value="Submit" name="Submit">
</form>
..
..

$connect = mysql_connect($host,$user,$password) or die ("Unable to connect to host"); 
mysql_select_db($db) or die ("Unable to connect to database"); 



$sql_type = (int) $_POST['product']; 

if (1 == $sql_type) // that's only one possibility 
{ 
    $prodcut = "SELECT distinct productname FROM mytable"; 
} 
elseif (2 == $sql_type) 
{ 
    $sql = "SELECT productname FROM environment_performance WHERE productname='ProductName1'"; 
} 
elseif (3 == $sql_type) 
{ 
    $sql = "SELECT productname FROM mytable WHERE productname='ProductName2'"; 
}


$query = "SELECT * FROM mytable where productname ='$product'" or die (mysql_error()); 
$result = mysql_query($query) or die (mysql_error()); 
$num = mysql_numrows($result);  

mysql_close($connect); 

//OUTPUT GOES HERE

Note: I can see only the value 2 so am not sure you know.


When users select - select all then the output should list all the column values where productname = ProductName1 and ProductName2

Any help would be really appriciated..Th anks agian!

[Dormilich]

well, why don't you use the selected SQL? you set the query string $sql via form and in your query you use $query instead of $sql. and you should not name the radio boxes "product[]" (there is no need to use an array)

tip: in the if-elseif conditions, use an else block in case the input does not match any previous condition (this may be another SQL statement as well as an Exception (Error message))

if (1 == $sql_type)
{ ... }
elseif (2 == $sql_type)
{ ... }
else
{
    throw new Exception("Incorrect choice of the SQL query.");
}

note for the HTML: the text that belongs to an input element should be put into a label

<input type="radio" value="2" name="product" id="product2">
<label for="product2">ProductName 2</label>

[microsoftboy]

Ok sure, Investigating.. .

Thanks again!

[microsoftboy]

Hi,

I was able to select all or none by using the checkboxes as mentioned below.

<tr>
<td>
Product
</td>
<td>
<input type="checkbox" name="product[]" value="AP" />AP
<input type="checkbox" name="product[]" value="AR" />AR
<input type="checkbox" name="product[]" value="GL" />GL
<input type="checkbox" name="product[]" value="COM" />COM<br />
</td>
</tr>



$product_array = $_POST['product'];
//echo $product_array;
foreach ($product_array as $one_product) {
$source .= $one_product.", ";
}
$product = substr($source, 0, 100);



Thanks a lot.

[microsoftboy]

I was able to get it working by your idea as well..

i.e

$sql_type = (int) $_POST['sql']; 
if (1 == $sql_type) // that's only one possibility 
{ 
    $sql = "SELECT * FROM mytable"; 
} 
elseif (2 == $sql_type) 
{ 
    $sql = "SELECT `id` FROM mytable"; 
}

Thanks again!