Ad:

PHP Discussion Thread
Unsolved
Views: 3432

Oct 15th, 2008

Populate dropdown using mysql date range

Expand Post »

Hello everyone, I'm having trouble populating a dropdown box with mysql data. I have a table with columns makeid, yearstart, yearend and makename. Sample data would be 1, 1936, 1941, American Bantam. I want the dropdown to list the years a certain brand of car was offered. For instance, a user selects American Bantam and the years 1936, 1937... 1940, 1941 are listed. I've tried a bunch of different things but I can't seem to get the dropdown to populate with anything more than just the start or end dates. I'm relatively new to PHP so I'm sure there is a simple solution.

Any help would be appreciated!

Thanks,
Arthur

forwardlookguy

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

23 posts
since Jun 2008

Permalink

Oct 15th, 2008

Re: Populate dropdown using mysql date range

can you post your code for us to know what you want it would be?

im a newbie too .. perhaps i could help too

enim

enim213

Reputation Points: 11

Solved Threads: 2

Light Poster

Offline

40 posts
since Jun 2008

Permalink

Oct 15th, 2008

Re: Populate dropdown using mysql date range

Click to Expand / Collapse Quote originally posted by forwardlookguy ...

For instance, a user selects American Bantam and the years 1936, 1937... 1940, 1941 are listed.

If user selects name, then based on that id you can get two dates from your database table, then make a dropdown list by incrementing the first date, put if condition greater than end date...
try it...
or post appropriate code...then we can see it for validate....

Shanti C

Reputation Points: 137

Solved Threads: 162

Posting Virtuoso

Offline

1,641 posts
since Jul 2008

Permalink

Oct 16th, 2008

Re: Populate dropdown using mysql date range

Well, I've actually got two pieces of code. The first one I'll post was given to me by someone else but I've managed to get it to work for my application. Here it is:

PHP Syntax (Toggle Plain Text)
<?php
 
require "includes/dbconnect.php";
 
 
$query = "";
if(isset($_GET['make']))
{
	if(trim($query2) == "")
	{
		$query2 .= " where makename = '" . mysql_real_escape_string(stripslashes($_GET['make'])) . "'";
	}
	else
	{
		$query2 .= " and makename = '" . mysql_real_escape_string(stripslashes($_GET['make'])) . "'";
	}
}
 
$query2 = "select * from makes" . $query2 . " order by makename";
$searchresult = mysql_query($query2) or die(mysql_error());;
 
$query3 = "SELECT yearstart, yearend FROM makes";
$yearresult = mysql_query($query3) or die(mysql_error());;
 
 
$yeararray = array();
while($row = mysql_fetch_assoc($yearresult))
{
	$yeararray[$row["yearstart"]][] = $row["yearend"];
}
?>
 
	<table cellpadding="0" cellspacing="0">
	<?
		if(mysql_num_rows($searchresult) == 0 || !is_numeric(mysql_num_rows($searchresult)))
		{
		?>
			<tr>
				<td colspan="3">No vehicles found under that search criteria.</td>
			</tr>
		<?
		}
		else
		{
			for($i = 0; $i < mysql_num_rows($searchresult); $i++)
			{
			?>
				<tr>
					<td><a href="<? echo basename($_SERVER['PHP_SELF']); ?>?make=<? echo urlencode(mysql_result($searchresult, $i, "makename")); ?>"><? echo mysql_result($searchresult, $i, "makename"); ?></a></td>
					<td>
						<form action="<? echo basename($_SERVER['PHP_SELF']); ?>" method="get">
						<input type="hidden" name="make" value="<? echo mysql_result($searchresult, $i, "makename"); ?>" />
						<select name="year" id="year<? echo mysql_result($yearresult, $i, "yearstart"); ?>">
							<option value="Choose">Choose</option>
							<?
							foreach($yeararray[mysql_result($yearresult, $i, "yearstart")] as $value)
							{
							?>
								<option value="<? echo $value; ?>"><? echo $value; ?></option>
							<?
							}
						?>
							</select>
<?php

require "includes/dbconnect.php";


$query = "";
if(isset($_GET['make']))
{
	if(trim($query2) == "")
	{
		$query2 .= " where makename = '" . mysql_real_escape_string(stripslashes($_GET['make'])) . "'";
	}
	else
	{
		$query2 .= " and makename = '" . mysql_real_escape_string(stripslashes($_GET['make'])) . "'";
	}
}

$query2 = "select * from makes" . $query2 . " order by makename";
$searchresult = mysql_query($query2) or die(mysql_error());;

$query3 = "SELECT yearstart, yearend FROM makes";
$yearresult = mysql_query($query3) or die(mysql_error());;


$yeararray = array();
while($row = mysql_fetch_assoc($yearresult))
{
	$yeararray[$row["yearstart"]][] = $row["yearend"];
}
?>

	<table cellpadding="0" cellspacing="0">
	<?
		if(mysql_num_rows($searchresult) == 0 || !is_numeric(mysql_num_rows($searchresult)))
		{
		?>
			<tr>
				<td colspan="3">No vehicles found under that search criteria.</td>
			</tr>
		<?
		}
		else
		{
			for($i = 0; $i < mysql_num_rows($searchresult); $i++)
			{
			?>
				<tr>
					<td><a href="<? echo basename($_SERVER['PHP_SELF']); ?>?make=<? echo urlencode(mysql_result($searchresult, $i, "makename")); ?>"><? echo mysql_result($searchresult, $i, "makename"); ?></a></td>
					<td>
						<form action="<? echo basename($_SERVER['PHP_SELF']); ?>" method="get">
						<input type="hidden" name="make" value="<? echo mysql_result($searchresult, $i, "makename"); ?>" />
						<select name="year" id="year<? echo mysql_result($yearresult, $i, "yearstart"); ?>">
							<option value="Choose">Choose</option>
							<?
							foreach($yeararray[mysql_result($yearresult, $i, "yearstart")] as $value)
							{
							?>
								<option value="<? echo $value; ?>"><? echo $value; ?></option>
							<?
							}
						?>
							</select>

The next code is a simple drop down I've been working on. I've tried a lot of different things with it but to no avail.

PHP Syntax (Toggle Plain Text)
 
<?php 
 
$query3 = "SELECT yearstart, yearend FROM makes";
 
$result = mysql_query($query3) or die(mysql_error());;
 
$options="";
 
while ($row=mysql_fetch_array($result)) {
 
$yearstart=$row["yearstart"];
$yearend=$row["yearend"];
$options.="<OPTION VALUE=\"$yearstart\">".$yearstart.$yearend; /// concatenates your option tags with the value and option at that returned row 
}
?>
<?php 

$query3 = "SELECT yearstart, yearend FROM makes";

$result = mysql_query($query3) or die(mysql_error());;

$options="";

while ($row=mysql_fetch_array($result)) {

$yearstart=$row["yearstart"];
$yearend=$row["yearend"];
$options.="<OPTION VALUE=\"$yearstart\">".$yearstart.$yearend; /// concatenates your option tags with the value and option at that returned row 
}
?>

Thanks,
Arthur

forwardlookguy

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

23 posts
since Jun 2008

Permalink

Oct 17th, 2008

Re: Populate dropdown using mysql date range

Any help?

forwardlookguy

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

23 posts
since Jun 2008

Permalink

Oct 17th, 2008

Re: Populate dropdown using mysql date range

Possible modification for Choice Box

<?php

$query3 = "SELECT yearstart, yearend FROM makes";

$result = mysql_query($query3) or die(mysql_error());;

$options="";

while ($row=mysql_fetch_array($result)) {
$yearstart=$row["yearstart"];
$yearend=$row["yearend"];
for ( $i=$yearstart;$i<=$yearend;$i++) {
$options.="<OPTION VALUE=\"$i\">". $i . "</OPTION>";
}
}
?>

Quadar

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since May 2008

Permalink

Oct 18th, 2008

Re: Populate dropdown using mysql date range

Well, that's kinda working. The dropdown now populates with all the years for all the Makes. I only want it to display the relevent years for each car the user clicks on. Any ideas?

forwardlookguy

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

23 posts
since Jun 2008

Permalink

Oct 18th, 2008

Re: Populate dropdown using mysql date range

It seems to me that the reason you are getting all the makes for the year range you choossis because you are not explicitly choosing a make in that query. You need to have WHERE makename = 'something' in your SQL query.

petrov50

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Jun 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: All my php files has .tpl extension is there any editor to work on that

Next Thread in PHP Forum Timeline: code for inviting a friend and adding the request similar to orkut

DaniWeb Message

Cancel Changes

User Name
Password