[SOLVED] Create dropdown list by querying database?

Moron · December 2, 2008

Okay, I'm working on a project for employee paystubs. It's working great, but...... when they click the "Paystubs" button, I want it to drop down a list of entries for their name in the database so they can pick and click one. The table I'm using is brand new and only contains the latest and greatest data (thus, one record per person), but soon there will be an archive. I want to display a list and let them pick.

Any guidance is appreciated as always.

gevans · December 2, 2008

build a query to select everything you want from the database. output it into a select field in an html form

when that form is submitted build another query using the chosen value from the drop down list.

Moron · December 2, 2008

build a query to select everything you want from the database. output it into a select field in an html form

when that form is submitted build another query using the chosen value from the drop down list.

Sounds good to me. I just plugged in a dynamic javascript dropdown to list the entries when they click the button. I can write the query in question. Any good starting point on plugging into the select fields?

Brian W · December 2, 2008

<?php do { ?>

<?php } while ($result = mysql_fetch_assoc($querry)); ?>

</select>

gevans · December 2, 2008

it wont work with a do, you'll need to use a while loop or you'll miss the first value

<?php
echo '<select name="your_menu" id="your_menu">';
echo '<option value="">YOUR LIST</option>';
while($result = mysql_fetch_assoc($querry)){
echo "<option value=\"{$result['val']}\">{$result['Name'];}</option>";
}
echo '</select>';
?>

Brian W · December 2, 2008

???

Really?

I never miss the first one... so what makes you think it will miss it?

Moron · December 2, 2008

Okay, I now have it pulling the records of available paystubs for each employee and populating a list. So if you all don't mind, help me with my thinking here....

Next I need to make each item in the list linkable (duh). Then I need to pass the date they're clicking as a variable into the query on the results page right? Or am I missing something?

gevans · December 2, 2008

???

Really?

I never miss the first one... so what makes you think it will miss it?

//wont work
<select name="your_menu" id="your_menu">
<?php do { ?>
<option value="<?php $result['val']; ?>"><?php $result['Name']; ?></option>
<?php } while ($result = mysql_fetch_assoc($querry)); ?>
</select>

//will work
<?php $result = mysql_fetch_array($query); ?>
<select name="your_menu" id="your_menu">
<?php do { ?>
<option value="<?php $result['val']; ?>"><?php $result['Name']; ?></option>
<?php } while ($result = mysql_fetch_assoc($querry)); ?>
</select>

gevans · December 2, 2008

Okay, I now have it pulling the records of available paystubs for each employee and populating a list. So if you all don't mind, help me with my thinking here....

Next I need to make each item in the list linkable (duh). Then I need to pass the date they're clicking as a variable into the query on the results page right? Or am I missing something?

You can use javascript to submit the form once a drop down value is chosen, taht will then take you to your script

Moron · December 2, 2008

You can use javascript to submit the form once a drop down value is chosen, taht will then take you to your script

Can I not just make the selected item a "post" value in php and pass it that way?

gevans · December 2, 2008

That will be done using a form, so yea...

Brian W · December 2, 2008

???

Really?

I never miss the first one... so what makes you think it will miss it?

//wont work
<select name="your_menu" id="your_menu">
<?php do { ?>
<option value="<?php $result['val']; ?>"><?php $result['Name']; ?></option>
<?php } while ($result = mysql_fetch_assoc($querry)); ?>
</select>

//will work
<?php $result = mysql_fetch_array($query); ?>
<select name="your_menu" id="your_menu">
<?php do { ?>
<option value="<?php $result['val']; ?>"><?php $result['Name']; ?></option>
<?php } while ($result = mysql_fetch_assoc($querry)); ?>
</select>

Oh yes, now I see what you mean. I didn't think to put the "<?php $result = mysql_fetch_array($query); ?>" first like I know you do need.

I make it a habit to put that under my query.

Can I not just make the selected item a "post" value in php and pass it that way?

The value="" part is what is passed as a post value. The name of the post value is the menu's name (in example: my_menu). So you'd use $_POST['my_menu'].

Moron · December 2, 2008

value=""[/b] part is what is passed as a post value. The name of the post value is the menu's name (in example: my_menu). So you'd use $_POST['my_menu'].

So using this code:

while ($RESULT = mssql_fetch_assoc($RESULTDS))   {

$endingdate = $RESULT['PSTUB5'];
$month = substr("$endingdate", -8, 2);
$day = substr("$endingdate", -6, 2);
$year = substr("$endingdate", -4, 4);

echo "<CENTER>";

echo "<font size=2 color=#0000ff face=arial>";

echo $month; 
echo "/";
echo $day;
echo "/";
echo $year;

echo "</font>";

echo "</CENTER>";

...how do I make "$RESULT['PSTUB5']" a "post" value and also make it linkable?

Brian W · December 2, 2008

I think this may be what you are asking about:

<?php $RESULT = mssql_fetch_array($RESULTDS); ?>
<select name="endingdate" id="endingdate">
<?php do { ?>
<option value="<?php echo $RESULT['PSTUB5']; ?>"><?php echo $RESULT['PSTUB5']; ?></option>
<?php } while ($RESULT = mssql_fetch_assoc($RESULTDS)); ?>
</select>

(as long as mssql_fetch_assoc does the same as the mysql version. I've never used it.)

that should do the trick to make it a POST value. Note that the menu I made above is named "endingdate", so the POST value it will make will be $_POST['endingdate'].

Moron · December 2, 2008

I think this may be what you are asking about:
<?php $RESULT = mssql_fetch_array($RESULTDS); ?>
<select name="endingdate" id="endingdate">
<?php do { ?>
<option value="<?php echo $RESULT['PSTUB5']; ?>"><?php echo $RESULT['PSTUB5']; ?></option>
<?php } while ($RESULT = mssql_fetch_assoc($RESULTDS)); ?>
</select>
(as long as mssql_fetch_assoc does the same as the mysql version. I've never used it.)

that should do the trick to make it a POST value. Note that the menu I made above is named "endingdate", so the POST value it will make will be $_POST['endingdate'].

For some reason, that isn't making it a $_POST value. I can echo other post or session values on the page, but this gives me nothing.

Isn't there a way to make ANY value a $_SESSION variable?

Brian W · December 2, 2008

You have to have the variable picked out before you could make it a session value which if you are using a form to pick it out, you'll need to post it first. :-\

Can I see your code for the form and also where you are trying to use the POST variable?

Moron · December 2, 2008

You have to have the variable picked out before you could make it a session value which if you are using a form to pick it out, you'll need to post it first. :-\

Can I see your code for the form and also where you are trying to use the POST variable?

Here it is:

<?php

$RESULTDS=mssql_query("SELECT DISTINCT M2.[EMPNO], PS.[PSTUB5] 

FROM MASTERL2 M2 

LEFT JOIN PAYSTUBS PS
ON PS.[PSTUB2]=M2.EMPNO 

WHERE M2.[EMPNO] = '".$_SESSION['empcode']."'           

ORDER BY PS.[PSTUB5]");

$RESULT=mssql_fetch_assoc($RESULTDS);


mssql_data_seek($RESULTDS,0);

echo "<CENTER>";
echo "<font size=2 color=#000000 face=arial>";
echo "Please select a stub to view:";
echo "</font>";
echo "</CENTER>";
echo "<BR>";


echo "<form action=\"paystubs.php\" name=\"paystubs\" value=\"ppeenddate\" method=\"post\">";

while ($RESULT = mssql_fetch_assoc($RESULTDS))   {

$endingdate = $RESULT['PSTUB5'];
$month = substr("$endingdate", -8, 2);
$day = substr("$endingdate", -6, 2);
$year = substr("$endingdate", -4, 4);

echo "<CENTER>";

echo "<font size=2 color=#0000ff face=arial>";


echo "<a href=\"paystubs.php\">";

echo $month; 
echo "/";
echo $day;
echo "/";
echo $year;

echo "</a>";

echo "</form>";

echo "</font>";

echo "</CENTER>";

echo "<BR>";

}
?>

The above does what I want; it lists the available Pay Period Ending dates ($RESULT['PSTUB5']) from the database table and also makes them clickable. I just can't seem to pass $RESULT['PSTUB5'] as a variable to the next page. This variable will tell it what date to pull the record for.

Brian W · December 2, 2008

That is not a form dude. Your form won't use links like that. So, forget the form if you wan't, but you'll need to stop trying to use the like they are Inputs. Lets do this with $_GET then.

<?php

$RESULTDS=mssql_query("SELECT DISTINCT M2.[EMPNO], PS.[PSTUB5] 

FROM MASTERL2 M2 

LEFT JOIN PAYSTUBS PS
ON PS.[PSTUB2]=M2.EMPNO 

WHERE M2.[EMPNO] = '".$_SESSION['empcode']."' ORDER BY PS.[PSTUB5]");
   
$RESULT=mssql_fetch_assoc($RESULTDS);

mssql_data_seek($RESULTDS,0);

echo "<CENTER>";
echo "<font size=2 color=#000000 face=arial>";
echo "Please select a stub to view:";
echo "</font>";
echo "</CENTER>";
echo "<BR>";

echo "<CENTER><font size=2 color=#0000ff face=arial>";

while ($RESULT = mssql_fetch_assoc($RESULTDS))   {

$endingdate = $RESULT['PSTUB5'];
$month = substr("$endingdate", -8, 2);
$day = substr("$endingdate", -6, 2);
$year = substr("$endingdate", -4, 4);




$Date = $month."-".$day."-".$year; 

echo "<a href=\"paystubs.php?Date=".$Date."\">";

echo $Date;

echo "</a>";

}

echo "</font> </CENTER>";

echo "<BR>";
?>

Sorry, changed your date format to use "-" instead of "/" because you shouldn't use / in the URL unless you are using it correctly (for directories). To use the variable as a $_GET variable: once its been clicked on and the variable is added to the URL query string, use echo $_GET['Date'].

I'm not sure how you are using advanced SQL and even some pretty non novice PHP functions and not be using forms correctly.

If you want to still use a drop down menu, you (or I) can do so by using some javascript.

BTW, why are you even bothering breaking the date up if your just going to put it back together?

Brian W · December 2, 2008

oh yes, and to make it into a $_SESSION var, add this to the top of your pages that will use the $_SESSION var.

session_start();

and add this to that page

$_SESSION['the_name_of_the_session_you_want'] = $_GET['Date'];

Its hard for me to know what you want at this point because you'r a little all over the place. LOL

Moron · December 2, 2008

oh yes, and to make it into a $_SESSION var, add this to the top of your pages that will use the $_SESSION var.
session_start();
and add this to that page
$_SESSION['the_name_of_the_session_you_want'] = $_GET['Date'];
Its hard for me to know what you want at this point because you'r a little all over the place. LOL

Thanks for your help, Brian. Yes, this has been confusing.

Let me try to express myself a bit better. I understand that the above code isn't a proper HTML "form." What I want is the following:

1. Get the available pay period ending dates through a query (already working).

2. List them and make them links (working).

NOTE: The reason I'm breaking up the date is for display purposes only ("12/15/2008" looks a bit more human-readable than "12152008"). But it's the actual date value ($RESULT['PSTUB5']) that I'm trying to pass to the next page.

3. Pass the selected variable ($RESULT['PSTUB5']) into the query on the next page by using sessions or whatever technique I can (WHERE $RESULT['PSTUB5'] = $_SESSION['enddate']) .

That's what I'm really getting at; when a user clicks a Pay Period Ending Date, I want to pass that selected Pay Period Ending Date into the query on the next page, thereby pulling the record for that date.

As for forms, I don't really want an HTML "select" with a dropdown; I want a list to appear instead, which is what I have now.

Sorry for the confusion. Does this make a bit more sense?

Thanks again.

Brian W · December 2, 2008

THANK YOU FOR CLARIFYING!!! lol jk, but that does help.

on this line i gave you:

echo "<a href=\"paystubs.php?Date=".$Date."\">";

instead say

echo "<a href=\"paystubs.php?Date=".$endingdate."\">";

Then, in your query, say

...WHERE `date_column`=".$_GET['Date']."...

that should pass your block date (12152008) to the next page specified by the link above and make it usable for the query.

Additional comment: use

if(!is_numeric($_GET['Date'])){ die('Bad Date Input'); }

That will prevent SQL injection (type of hacking if you didn't know)...

Moron · December 2, 2008

THANK YOU FOR CLARIFYING!!! lol jk, but that does help.

on this line i gave you:
echo "<a href=\"paystubs.php?Date=".$Date."\">";
instead say
echo "<a href=\"paystubs.php?Date=".$endingdate."\">";
Then, in your query, say
...WHERE `date_column`=".$_GET['Date']."...
that should pass your block date (12152008) to the next page specified by the link above and make it usable for the query.

Additional comment: use
if(!is_numeric($_GET['Date'])){ die('Bad Date Input'); }
That will prevent SQL injection (type of hacking if you didn't know)...

Thanks, Brian. I'll try it in a bit.

This is on our internal Intranet, so if we have a hacker I think we'll catch him pretty quickly!

Brian W · December 2, 2008

yeah, that would be a sure sign that someone needs to be fired. lol

Hope it does the job for ya. I'll tag this thread to notify, so let me know if it works else I'll help you.

Moron · December 3, 2008

yeah, that would be a sure sign that someone needs to be fired. lol

Hope it does the job for ya. I'll tag this thread to notify, so let me know if it works else I'll help you.

It works!

Thanks again. Currently, there is only one Pay Period Ending date in the table, but I see no reason it won't work fine once there are more.

Sign In

[SOLVED] Create dropdown list by querying database?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information