[SOLVED] PHP Dynamic Drop Down - the best kept secret on the internet...

[webmaster1]

Hi All,

 

I've built a modest mailing CRM and so far I'm outputting the my entire mySQL table into a web page as a crude report of sorts.

 

I want to filter the results by date but I'm not sure how to go about it since I'm using datetime which means there all unique. I'd prefer a range (lets say a day) rather than populating every single date and time in the dropdown.

 

Can anyone help me in A) creating a php dynamic dropdown and B) how to limit the results to a range?

 

I can only get as far as populating the results in the dropdown and thats it. I can't find one tutorial that properly explains the mechanics of the code. Is there a conspiracy against newbs unlocking the secrets of the mighty php dropdown?

[amclean]

echo '<select>';
$query="SELECT table.column1, table.column2 FROM column";
$result=$mysqli->query($query);

while ($row=$result->fetch_object())
	{
		echo '<option value="'.$row->column1.'">'.$row->column2."</option>\n";
	}
echo '</select>';

 

The query part is obvious, the while loop turns the whole result set into an array that's accessible by the table column name.

 

Hope that helps

 

[Mchl]

Is there a conspiracy against newbs unlocking the secrets of the mighty php dropdown?

 

There is no such conspiracy, as there is no such thing as php dropdown

 

Creating dropdowns filled with data from database can be split into two steps, and you should make sure you undestand them.

 

These steps are roughly

1. Get data to populate dropdown from mySQL and process it

2. Create a dropdown using HTML

 

In your case it seems you have problem with getting data from mySQL. You want to get all distinct dates from a filed that stores date and time... How about you take a look at this:

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date

 

[webmaster1]

@amclean

 

Thanks for that. Its a neater starting point than I've come across so far.

 

I don't understand the query. Is that a typo? How can I select a column from a column?

 

Might this work just as well SELECT column1, column2 FROM table?

 

I also can't grasp why I need to pull in two columns when I'm only displaying one.

 

<?php

include("dbinfo.php");

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

echo '<select>';
$query="SELECT urn, recipientname FROM zevasce";
$result=$mysqli->query($query);

while ($row=$result->fetch_object())
	{
		echo '<option value="'.$row->urn.'">'.$row->recipientname."</option>\n";
	}
echo '</select>';

?>

 

I end up with an empty dropdown 

 

@Mchl  Thanks for the resource. It looks nasty  . I was hoping I could group by date based on the date of the month. E.g. Display all records submitted on the 17th Jan without discriminating the results by time. One step at a time though. I need to master the ancient ways of the dropdown first.

[amclean]

the table.column1, table.column2 were firstly examples of how you would bring in relational tables, and secondly the table.column isn't strictly necessary but can be helpful to avoid confusing code.  In my example "table" would merely be the name of the table and "column1" would obviously be the column. 

 

I think your query didn't work because you mixed your mysql query methods.  I referenced the OOP one "mysqli" as opposed to mysql_connect.

 

i use the following:

 

$mysqli = new mysqli("localhost", "username", "password", "dbname");

 

you can put that into an include file in that format and it'll meet all your db connection needs.

 

Edit:  I can see how my query would be confusing since I removed some necessary info.  Usually you would only bring in values from multiple tables in a relational database, and usually that would require adding a "where" clause to the query, such as "select this, this from here WHERE that=that".

[amclean]

I also can't grasp why I need to pull in two columns when I'm only displaying one.

 

Could you clarify this?

[Mchl]

@Mchl  Thanks for the resource. It looks nasty  . I was hoping I could group by date based on the date of the month. E.g. Display all records submitted on the 17th Jan without discriminating the results by time. One step at a time though. I need to master the ancient ways of the dropdown first.

 

Using DATE() function you can

 

SELECT DATE(dateTimeField) AS date FROM table GROUP BY date

[webmaster1]

I also can't grasp why I need to pull in two columns when I'm only displaying one.

 

Could you clarify this?

 

The end user only see's one column populated in the dropdown. I can't seem to work out why we're calling on two as per the query. I know this is the correct way but I just don't understand it.

 

@Mchl: Ah, I see. Very neat indeed. Still at the dropdown junction though 

[premiso]

I also can't grasp why I need to pull in two columns when I'm only displaying one.

 

Could you clarify this?

 

The end user only see's one column populated in the dropdown. I can't seem to work out why we're calling on two as per the query. I know this is the correct way but I just don't understand it.

 

@Mchl: Ah, I see. Very neat indeed. Still at the dropdown junction though 

 

You pull 2, the display value then the passed value which is usually an id. This is common practice.

[Mchl]

If the dropdown is to be used as a date picker, then pulling just one value (dates) would be enough.

[webmaster1]

That makes sense. I was more confused at what it was doing but from both of your explanations its more of a convention than an action.

 

Any ideas where I went wrong editing your code? The dropdown is empty.

 

<?php

include("dbinfo.php");

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

echo '<select>';
$query="SELECT urn, date FROM zevasce";
$result=$mysqli->query($query);

while ($row=$result->fetch_object())
	{
		echo '<option value="'.$row->urn.'">'.$row->date."</option>\n";
	}
echo '</select>';

?>

 

[Mchl]

Use either mysql or mysqli (preferably), but don't mix both.

[webmaster1]

Adjusted accordingly. Still stuck with an empty drop down though 

 

<?php

include("dbinfo.php");

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

echo '<select>';
$query="SELECT urn, date FROM zevasce";
//$result=$mysqli->query($query);
$result=mysql_query($query);


while ($row=$result->fetch_object())
	{
		echo '<option value="'.$row->urn.'">'.$row->date."</option>\n";
	}
echo '</select>';

?>

[Mchl]

$result->fetch_object()

 

This is still myslqi way

[webmaster1]

$result->fetch_object()

 

This is still myslqi way

 

Goddamn you amcleam and your misleading post! Just kidding  . Back to google it is then. I'll crack this, don't worry. Those ninja-like dropdowns can't elude me forever.

[amclean]

<?php

$mysqli = new mysqli("localhost", "username", "password", "dbname");

echo '<select>';
$query="SELECT urn, date FROM zevasce";
$result=$mysqli->query($query);

while ($row=$result->fetch_object())
	{
		echo '<option value="'.$row->urn.'">'.$row->date."</option>\n";
	}
echo '</select>';

?>

 

Is this any more clear?

[webmaster1]

That works great, thanks amclean.

 

I've researched the mysqli extension since.