COMBO BOX problem cant select multiple values from database

Question

0.00/5 (No votes)

See more:

I made the following

PHP

$dropdown = "<form action='get'><select name='Test' multiple='multiple'>";
while($row = mysql_fetch_assoc($result)) {
$dropdown .= "\r\n<option value='{$row['Test']}'>{$row['Test']}</option>";
}
$dropdown .= "\r\n</select><input type='submit' name='submit' value='submit' /></form>";

now I am using

PHP

$query2 = "SELECT * FROM tests WHERE id='".$_GET['Test']."'";

now what i have to use instead of $_GET['Test']for multiple selection of combo box
Help would be appreciated,

thanks.

Posted 12-Aug-12 19:46pm

project virus

Updated 10-Jan-18 19:22pm

JF2015

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

enhzflep · Accepted Answer · 2012-08-12T21:00:00

Solution 1

Sure, does this code help?

Note: The [] chars in the name of the select element ARE required.
Selecting all options gives the output of: (with the combo below that)

PHP

<?php
    if (isset( $_GET['test']))
    {
        foreach ($_GET['test'] as $selectedOption)
            echo $selectedOption."<br>";
    }
?>

<html>
<head>
</head>
<body>
    <form action='testCombo.php'>
        <select name='test[]' multiple='multiple'>
            <option value='1'>a</option>
            <option value='2'>b</option>
            <option value='3'>c</option>
            <option value='4'>d</option>
        </select>
        <input type='submit' value='submit'/>
    </form>
</body>

Posted 12-Aug-12 21:00pm

enhzflep

Comments

project virus 13-Aug-12 3:33am

huh, sorry dnt understand what u want say.
I have to retrieve values using that multiple options

enhzflep 13-Aug-12 3:54am

Save the thing as a file and run it. Surely you can see that all of the selected options are reported?

That code will extract the selected options.
You now have to formulate a new query using the info!

$sql = "select * from tests where id='1' or id='2' or id='3' or id='4'"

You need to work out how to construct the [id='1' or id='2' or id='3' or id='4'] part from the number of options that were selected.

If you put the results of $_GET['test'] into an array, you know how many elements you have and how many times you need to add OR after each id='xx' statement.

$i=0;
foreach $_GET['test] as $selectedOption
{
$options[$i++] = $selectedOption;
}

$sql = "select * from Tests where ";
$num = count($options);
for ($i=0; $i<$num-1; $i++)
{
$sql .= "id='".$options[$i]."' OR ";
}
$sql .= "id='".$options[$i]."'";

project virus 13-Aug-12 4:43am

what is $options??

enhzflep 13-Aug-12 4:51am

an array to hold each of the <option>s that were selected in the <select> element.
It's defined and used on the same line.

enhzflep 13-Aug-12 4:56am

Here's an update to the Solution:

<?php
if (isset( $_GET['test']))
{
$i = 0;
foreach ($_GET['test'] as $selectedOption)
{
$options[$i++] = $selectedOption;
}

$sql = "select * from Test where ";
$num = count($options);
for ($i=0; $i<$num-1; $i++)
{
$sql .= "id='".$options[$i]."' OR ";
}
$sql .= "id='".$options[$i]."'";

echo $sql . "<br>";
}
?>

<html>
<head>
</head>
<body>
<form action='testCombo.php'>
<select name='test[]' multiple='multiple'>
<option value='1'>a</option>
<option value='2'>b</option>
<option value='3'>c</option>
<option value='4'>d</option>
</select>
<input type='submit' value='submit'/>
</form>
</body>

Output: - All 4 options selected
select * from Test where id='1' OR id='2' OR id='3' OR id='4'

project virus 13-Aug-12 5:24am

no sql query not working

enhzflep 13-Aug-12 5:31am

Okay, I've manually run that same query on a database table that contains forum users. In that case, the id (userid) column is an int, not a string as yours appears to be.

When I type "select * from users where userid=1 or userid=2" I get users 1 and 2 returned.

When you say "sql query not working"? What exactly happens?
Does the query string get formed, but fails to return results?

Have you printed the computed string to the screen, before copying and pasting into MySql Admin's sql query page?

'no sql query not working' is an unsatisfactory answer my friend. More information thanks

project virus 13-Aug-12 5:41am

mysql_fetch_array() expects parameter 1 to be resource is the error

project virus 13-Aug-12 5:43am

but my id column is string not int

enhzflep 13-Aug-12 5:48am

Okay, Good. I could tell you used a string as your ID, so that's whay I wrapped the numbers (I guess you'll have text) inside ' ' chars.

Now that means that there's likely an error in the sql you've constructed.

The next step is to print the sql query string to the screen. Then, copy it into the SQL query window of the MySql Admin and try to execute it there. You should get better error message there than on your web-page.

The other trick is to formulate your query in that window, make sure it's correct and returns the expected results. Once this is done, write the PHP that will construct this query string.

Failing that, if you export the table and paste the result here, I'll have an even closer look at the problem.

project virus 13-Aug-12 5:55am

I have this one
$res1=mysql_query("SELECT DISTINCT time FROM import WHERE date1='$_POST[date]'");

while($row= mysql_fetch_array($res1))
{
echo "<option>".$row['time']."</option>";

}

echo "</select>";
?>
<input type="submit" name="submit" value="Find" />
$result=mysql_query("SELECT DISTINCT date,mobno,city,state,type,telecaller FROM import WHERE time IN ('$_POST[select2]]') OR date1='$_POST[date]'") or die(mysql_error());
//$result2=mysql_query("SELECT id,date,mobno,state,type,telecaller FROM temp1 WHERE date='$_POST[select]' OR telecaller='$_POST[select1]\r' ") or die(mysql_error());
print"<div id='print'>";
print"<center>List of Mobile Numbers for Date <font color="#FF00FF"><u> $_POST[date]</u> </font> <center> <br/>";
print"<table border='1' cellspacing='5' cellpadding='12'>
<tr bgcolor='#82CAFF'>
<th>Sr.No</th>
<th>Date</th>
<th>Mobile No</th>
<th>City</th>
<th>State</th>
<th>Type</th>
<th>CIExe.</th>
</tr>";

$srno1=0;

while($row=mysql_fetch_array($result))
{
$srno1=$srno1+1;
print"<tr>";
print"<td>" .$srno1. "</td>";
print"<td>" . $row['date'] . "</td>";
print"<td>" . $row['mobno'] . "</td>";
print"<td>" . $row['city'] . "</td>";
print"<td>" . $row['state'] . "</td>";
print"<td>" . $row['type'] . "</td>";
print"<td>" . $row['telecaller'] . "</td>";
print"</tr>";
}
print"</table>";
print"</div>";
echo"<br/>";
In above coding time has multiple values.

enhzflep 13-Aug-12 6:07am

It's really too hard to tell from that code you've pasted.
If you could, export the SQL table from PhpMyAdmin into a text file. Then paste that here.

That will allow me to create a table with the same column names/types. It will also give me some of the data to use.

Just export the 'import' table - I don't need much data - just the first 5 rows would be plenty.

1) open localhost/phpmyadmin
2) enter your MySQL username and password
3) On the left side of screen, select the database that contains the 'import' table
4) At the left of the screen, select the 'import' table
5) At top of screen choose 'Export' tab
6) At bottom of screen, set "Dump" to 10, leave "Starting at" on 0
7) Hit GO at bottom right of screen
8) Open exported file (import.sql?) in text editor.
9) Copy
10) Paste into this thread
:)

project virus 13-Aug-12 6:36am

(`id`, `srno`, `date`, `mobno`, `city`, `state`, `type`, `telecaller`, `date1`, `time`) VALUES
(0, 11, '27-Jul-2012', 9920050727, '', 'Maharashtra', 'ADMIN', 'Sitara\r', '7-Aug-2012', '12:49.PM'),
(0, 12, '27-Jul-2012', 9908606607, '', 'Andhra Pradesh', 'ADMIN', 'Sitara\r', '7-Aug-2012', '12:49.PM'),
(0, 13, '27-Jul-2012', 8891781257, '', 'Kerala', 'ADMIN', 'Snehal\r', '7-Aug-2012', '12:49.PM'),
(0, 14, '27-Jul-2012', 8291005163, '', 'Maharashtra', 'ADMIN', 'Sitara\r', '7-Aug-2012', '12:49.PM'),
(0, 15, '27-Jul-2012', 9722457600, '', 'Gujrat', 'ADMIN', 'Sitara\r', '7-Aug-2012', '12:49.PM'),
(0, 16, '27-Jul-2012', 8291495166, '', 'Maharashtra', 'ADMIN', 'Anuja\r', '7-Aug-2012', '12:49.PM'),
(0, 17, '27-Jul-2012', 9432014057, '', 'West Bengal', 'ADMIN', 'Anuja\r', '7-Aug-2012', '12:49.PM'),
(0, 18, '27-Jul-2012', 9421165840, '', 'Maharashtra', 'ADMIN', 'Anuja\r', '7-Aug-2012', '12:49.PM'),
(0, 19, '27-Jul-2012', 8904264089, '', 'Karnataka', 'ADMIN', 'Anuja\r', '7-Aug-2012', '12:49.PM'),
(0, 20, '27-Jul-2012', 8087863141, '', 'Maharashtra', 'ADMIN', 'Anuja\r', '7-Aug-2012', '12:49.PM');

enhzflep 13-Aug-12 7:54am

I'll see what I can do. Usually the file will contain far more than that.
That is to say, there's no indication of the table structure - you've chopper off the first couple of lines.
Also, I'd assume that id should have an AUTO_INCREMENT flag on it - they should all be unique.
I'll have a look.
As for the "\r" - an't possibly see, without seeing the insert sql statement. It's been inserted into the sql table when each entry has been added.

project virus 13-Aug-12 6:37am

one more que.why \r is coming before name of telecaller like Sitara\r'

enhzflep · Accepted Answer · 2012-08-13T03:02:00

Okay then. Let's see what we have here.
I think you should be able to solve it from here.

Here's the import.sql file I have exported

SQL

-- phpMyAdmin SQL Dump
-- version 3.2.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 13, 2012 at 10:43 PM
-- Server version: 5.1.41
-- PHP Version: 5.3.1

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `test`
--

-- --------------------------------------------------------

--
-- Table structure for table `import`
--

CREATE TABLE IF NOT EXISTS `import` (
  `id` int(11) NOT NULL,
  `srno` int(11) NOT NULL,
  `date` varchar(64) NOT NULL,
  `mobno` int(11) NOT NULL,
  `city` varchar(64) NOT NULL,
  `state` varchar(64) NOT NULL,
  `type` varchar(64) NOT NULL,
  `telecaller` varchar(64) NOT NULL,
  `date1` varchar(64) NOT NULL,
  `time` varchar(64) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `import`
--

INSERT INTO `import` (`id`, `srno`, `date`, `mobno`, `city`, `state`, `type`, `telecaller`, `date1`, `time`) VALUES
(0, 11, '27-Jul-2012', 2147483647, '', 'Maharashtra', 'ADMIN', 'Sitara', '7-Aug-2012', '12:48.PM'),
(0, 12, '27-Jul-2012', 2147483647, '', 'Andhra Pradesh', 'ADMIN', 'Sitara\r', '7-Aug-2012', '12:49.PM'),
(0, 13, '27-Jul-2012', 2147483647, '', 'Kerala', 'ADMIN', 'Snehal\r', '7-Aug-2012', '12:49.PM'),
(0, 14, '27-Jul-2012', 2147483647, '', 'Maharashtra', 'ADMIN', 'Sitara\r', '7-Aug-2012', '12:49.PM'),
(0, 15, '27-Jul-2012', 2147483647, '', 'Gujrat', 'ADMIN', 'Sitara', '7-Aug-2012', '12:40.PM'),
(0, 16, '27-Jul-2012', 2147483647, '', 'Maharashtra', 'ADMIN', 'Anuja\r', '7-Aug-2012', '12:49.PM'),
(0, 17, '27-Jul-2012', 2147483647, '', 'West Bengal', 'ADMIN', 'Anuja', '7-Aug-2012', '12:39.PM'),
(0, 18, '27-Jul-2012', 2147483647, '', 'Maharashtra', 'ADMIN', 'Anuja\r', '7-Aug-2012', '12:49.PM'),
(0, 19, '27-Jul-2012', 2147483647, '', 'Karnataka', 'ADMIN', 'Anuja\r', '7-Aug-2012', '12:49.PM'),
(0, 20, '27-Jul-2012', 2147483647, '', 'Maharashtra', 'ADMIN', 'Anuja\r', '7-Aug-2012', '12:49.PM');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Here's the test php file I've been using (sqlTest.php):

PHP

<?php

	// step 1 - establish connection to MySql
	$server = 'localhost';
	$user = '** YOUR USERNAME HERE **';
	$password = '** YOUR PASSWORD HERE **';
	$dbLink = mysql_connect($server, $user, $password);  
	if (!$dbLink) 
		die('Can\'t establish a connection to the database: ' . mysql_error());  
  
	$dbSelected = mysql_select_db('test', $dbLink);  
		if (!$dbSelected) die ('We\'re connected, but can\'t use the table: ' . mysql_error());  


	// step 2 - output a <select> list containing all of the distinct times of day
	echo "<form action='sqlTest.php'>";
	echo "<select name='test[]' multiple='multiple'>";
		$sql = "SELECT DISTINCT time FROM import ORDER BY time asc";
		$res1=mysql_query($sql);
		while($row=  mysql_fetch_array($res1))
		{
			echo "<option value='". $row['time'] . "'>".$row['time']."</option>";
		}
		echo "</select>";
		echo"<input type='submit' value='submit'/>";
	echo"</form>";


	// step 3 - if the form has been submitted, extract the selected times (times come from table just above on line 13)
	if (isset($_GET['test']))
	{
		// step 3 - create sql statement to include the times that were selected.
		
		$i = 0;
		foreach ($_GET['test'] as $selectedOption)
			{
				$options[$i++] = $selectedOption;
			}

		$sql = "select DISTINCT date,mobno,city,state,type,telecaller,time FROM import WHERE time IN(";
		$num = count($options);
		for ($i=0; $i<$num-1; $i++)
		{
			$sql .= "'".$options[$i]."', ";
		}
		$sql .= "'".$options[$i]."')";
		$sql .= "OR date1='01/01/0000'";
		echo $sql . "<br>";		


		$result = mysql_query($sql);

		print "<div id='print'>";
		print "<center>List of Mobile Numbers for Date <font color='#FF00FF'>";

		// TODO:
		// need to add the selected dates here
		print "<u>01/01/000</u></font> or Time <font color='#FF00FF'>";
		
		// TODO:
		// need to add the selected times here
		print "<u>12:49.PM</u></font> <center> <br/>";
		
		print "<table border='1'  cellspacing='5' cellpadding='12'>";
		print "<tr  bgcolor='#82CAFF'>";
		print "<th>Sr.No</th>";
		print "<th>Date</th>";
		print "<th>Mobile No</th>";
		print "<th>City</th>";
		print "<th>State</th>";
		print "<th>Type</th>";
		print "<th>CIExe.</th>";
		print "<th>Time</th>";
		print "</tr>";
		
		$srno1=0;
		while($row=mysql_fetch_array($result))
		{
			$srno1=$srno1+1;
			print"<tr>";
				print"<td>" .$srno1. "</td>";
				print"<td>" . $row['date'] . "</td>";
				print"<td>" . $row['mobno'] . "</td>";
				print"<td>" . $row['city'] . "</td>";
				print"<td>" . $row['state'] . "</td>";
				print"<td>" . $row['type'] . "</td>";
				print"<td>" . $row['telecaller'] . "</td>";
				print"<td>" . $row['time'] . "</td>";
			print"</tr>";
		}
		print"</table>";
		print"</div>";
		echo"<br/>";
	}
?>

Here's the output when opened for the first time

HTML

<html>
<head>
</head>
<body>
    <form action="sqlTest.php">
    <select name="test[]" multiple="multiple">
        <option value="12:39.PM">12:39.PM</option>
        <option value="12:40.PM">12:40.PM</option>
        <option value="12:48.PM">12:48.PM</option>
        <option value="12:49.PM">12:49.PM</option>
        </select>
    <input type="submit" value="submit">
    </form>
    </body>
</html>

Here's the output when the first 3 options are selected:

select DISTINCT date,mobno,city,state,type,telecaller,time FROM import WHERE time IN('12:39.PM', '12:40.PM', '12:48.PM')OR date1='01/01/0000'
List of Mobile Numbers for Date 01/01/000 or Time 12:49.PM

Sr.No	Date	Mobile No	State	Type	CIExe.	Time
1	27-Jul-2012	2147483647	Maharashtra	ADMIN	Sitara	12:48.PM
2	27-Jul-2012	2147483647	Gujrat	ADMIN	Sitara	12:40.PM
3	27-Jul-2012	2147483647	West Bengal	ADMIN	Anuja	12:39.PM