[SOLVED] Is this even possible in PHP?

Seema · May 14, 2008

Hello all

I guess I need someone to point me in the right direction. Here's the story (I'm sure a familiar one to many of you, but please read on): I am pulling info from a database, first to populate a dropdown menu, then to populate an html table below, based on the option selected. The table column headers are

- Promotion (an ENUM yes / no value)

- Course name

- Location

- Price in local currency for <select>these dates</select>

- Price in <select>other currencies</select>

The database info then prints in rows below.

I have got the form to self-submit, using the javascript onchange event handler, and even keep the selected choice selected (thanks to all who went before me!)

But I've done many, many searches and I can't find anything on the final thing I want to do: somehow get the table rows populated on first page load, with the data from the first (default) option in each select. This will display the available courses for the current dates in local currency and US$ as default.

Can this even be done with PHP, or do I have to use Javascript (am hoping not as I am even more clueless about JS than php)? If it can be done, what operators will do the trick and what do I need to do to make them compatible with my current code (below, apologies for messiness)?

Any advice will be greatly appreciated.

<table>
<tr>
<td>Promotion</td>
<td>Course Type</td>
<td>Location</td>
<td>Prices in THB for: 
<form name="form1" method="post" action="<?php echo basename($_SERVER['PHP_SELF']); ?>">
    <select name="date" onchange="document.form1.submit()">

<?php 
//PULL DATE SELECTION OPTIONS FROM DB
//1. Connect to database
mysql_connect ("localhost" , "databaseadmin" , "password") or die ('Error: ' . mysqlerror());
mysql_select_db (databasename);

//2. Pull date info from coursetable
$query="SELECT DISTINCT StartDate, EndDate FROM coursetable WHERE EndDate > now() ORDER by StartDate ASC";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close ();

//3. Display info in dropdown menu
$i=0;
while ($i < $num) {

$startdate=mysql_result($result,$i,"StartDate");
$enddate=mysql_result($result,$i,"EndDate");

//create variables to format db dates for display
$display_startdate = date('j M Y', strtotime($startdate));
$display_enddate = date('j M Y', strtotime($enddate));
$selected = $_POST ['date'];
echo "<option value=\"$enddate\"";

   if($enddate == $selected){
      echo " selected=\"selected\">";
   } else{
      echo ">";
   }
echo "$display_startdate to $display_enddate</option>";

$i++;
};

?>
</select>
</td>

<?php 
//CREATE 2nd DROPDOWN FOR CURRENCY CONVERSION
	$exchangerates = array    (    
                            'USD' => 31,
                            'EUR' => 48,
                            'GBP' => 62,
		    'SGD' => 21,
	            'MYR' => 9,
                            'AUD' => 28,
                            'HKD' => 15,
                    'SEK' => 6							            
                        );
?> 
<td>
Approx price in 
<select name="currency" onchange="document.form1.submit()">
<option value="USD" <?php if (isset($_POST['currency']) && $_POST['currency'] == USD) { echo 'selected="selected"';} ?>>US $</option>
<option value="EUR" <?php if (isset($_POST['currency']) && $_POST['currency'] == EUR) { echo 'selected="selected"';} ?>>Euro</option>
<option value="GBP" <?php if (isset($_POST['currency']) && $_POST['currency'] == GBP) { echo 'selected="selected"';} ?>>GB £</option>
<option value="SGD" <?php if (isset($_POST['currency']) && $_POST['currency'] == SGD) { echo 'selected="selected"';} ?>>SGD</option>
<option value="MYR" <?php if (isset($_POST['currency']) && $_POST['currency'] == MYR) { echo 'selected="selected"';} ?>>Ringgit</option>
<option value="HKD" <?php if (isset($_POST['currency']) && $_POST['currency'] == HKD) { echo 'selected="selected"';} ?>>HK $</option>
<option value="AUD" <?php if (isset($_POST['currency']) && $_POST['currency'] == AUD) { echo 'selected="selected"';} ?>>AUS $</option>
<option value="SEK" <?php if (isset($_POST['currency']) && $_POST['currency'] == SEK) { echo 'selected="selected"';} ?>>SEK</option>
</select></form></td></tr>

<?php
$currency = $_POST ['currency'];
$exrate = $exchangerates [$currency];

//CREATE TABLE ROWS BASED ON SELECTION FROM DROPDOWNS
//1. Connect to database again

mysql_connect ("localhost" , "databaseuser" , "password") or die ('Error: ' . mysqlerror());
mysql_select_db (databasename);

//2. Pull info based on selected date from the table
$query="SELECT CourseType, Price, Location, Promotion FROM coursetable WHERE EndDate = '$selected' ORDER by Price ASC";
$courseinfo=mysql_query($query);  
$num=mysql_numrows($courseinfo);
mysql_close ();   

$i=0;
while ($i < $num) {
$coursetype=mysql_result($courseinfo,$i,"CourseType");
$price=mysql_result($courseinfo,$i,"Price");
$location=mysql_result($courseinfo,$i,"Location");
$promotion=mysql_result($courseinfo,$i,"Promotion");
$newprice=$price/$exrate;
echo "<tr><td>$promotion</td><td>$coursetype</td><td>$location</td><td>$price</td><td>".round($newprice);
echo "</td></tr>";
$i++;
};
?>

</table>

benphp · May 14, 2008

Perhaps this? I could only see one var in your main SQL - $selected

<table>
<tr>
<td>Promotion</td>
<td>Course Type</td>
<td>Location</td>
<td>Prices in THB for: 
<form name="form1" method="post" action="<?php echo basename($_SERVER['PHP_SELF']); ?>">
    <select name="date" onchange="document.form1.submit()">

<?php 
//PULL DATE SELECTION OPTIONS FROM DB
//1. Connect to database
mysql_connect ("localhost" , "databaseadmin" , "password") or die ('Error: ' . mysqlerror());
mysql_select_db (databasename);

//2. Pull date info from coursetable
$query="SELECT DISTINCT StartDate, EndDate FROM coursetable WHERE EndDate > now() ORDER by StartDate ASC";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close ();

//3. Display info in dropdown menu
$i=0;
while ($i < $num) {

$startdate=mysql_result($result,$i,"StartDate");
$enddate=mysql_result($result,$i,"EndDate");

//create variables to format db dates for display
$display_startdate = date('j M Y', strtotime($startdate));
$display_enddate = date('j M Y', strtotime($enddate));
$selected = $_POST ['date'];
echo "<option value=\"$enddate\"";

   if($enddate == $selected){
      echo " selected=\"selected\">";
   } else{
      echo ">";
   }
echo "$display_startdate to $display_enddate</option>";

$i++;
};

?>
</select>
</td>

<?php 
//CREATE 2nd DROPDOWN FOR CURRENCY CONVERSION
	$exchangerates = array    (    
                            'USD' => 31,
                            'EUR' => 48,
                            'GBP' => 62,
		    'SGD' => 21,
	            'MYR' => 9,
                            'AUD' => 28,
                            'HKD' => 15,
                    'SEK' => 6							            
                        );
?> 
<td>
Approx price in 
<select name="currency" onchange="document.form1.submit()">
<option value="USD" <?php if (isset($_POST['currency']) && $_POST['currency'] == USD) { echo 'selected="selected"';} ?>>US $</option>
<option value="EUR" <?php if (isset($_POST['currency']) && $_POST['currency'] == EUR) { echo 'selected="selected"';} ?>>Euro</option>
<option value="GBP" <?php if (isset($_POST['currency']) && $_POST['currency'] == GBP) { echo 'selected="selected"';} ?>>GB £</option>
<option value="SGD" <?php if (isset($_POST['currency']) && $_POST['currency'] == SGD) { echo 'selected="selected"';} ?>>SGD</option>
<option value="MYR" <?php if (isset($_POST['currency']) && $_POST['currency'] == MYR) { echo 'selected="selected"';} ?>>Ringgit</option>
<option value="HKD" <?php if (isset($_POST['currency']) && $_POST['currency'] == HKD) { echo 'selected="selected"';} ?>>HK $</option>
<option value="AUD" <?php if (isset($_POST['currency']) && $_POST['currency'] == AUD) { echo 'selected="selected"';} ?>>AUS $</option>
<option value="SEK" <?php if (isset($_POST['currency']) && $_POST['currency'] == SEK) { echo 'selected="selected"';} ?>>SEK</option>
</select></form></td></tr>

<?php
$currency = $_POST ['currency'];
$exrate = $exchangerates [$currency];

if (!isset($_POST ['date'])) {
$selected = $enddate[0];
}

//CREATE TABLE ROWS BASED ON SELECTION FROM DROPDOWNS
//1. Connect to database again

mysql_connect ("localhost" , "databaseuser" , "password") or die ('Error: ' . mysqlerror());
mysql_select_db (databasename);

//2. Pull info based on selected date from the table
$query="SELECT CourseType, Price, Location, Promotion FROM coursetable WHERE EndDate = '$selected' ORDER by Price ASC";
$courseinfo=mysql_query($query);  
$num=mysql_numrows($courseinfo);
mysql_close ();   

$i=0;
while ($i < $num) {
$coursetype=mysql_result($courseinfo,$i,"CourseType");
$price=mysql_result($courseinfo,$i,"Price");
$location=mysql_result($courseinfo,$i,"Location");
$promotion=mysql_result($courseinfo,$i,"Promotion");
$newprice=$price/$exrate;
echo "<tr><td>$promotion</td><td>$coursetype</td><td>$location</td><td>$price</td><td>".round($newprice);
echo "</td></tr>";
$i++;
};
?>

</table>

I added this just above your table sql:

if (!isset($_POST ['date'])) {

$selected = $enddate[0];

}

And if that doesn't work, there is another way.

Seema · May 14, 2008

Thanks for the quick response! Unfortunately, that didn't work though

It seems that this extra code you provided will only make the first option in the dropdown display as selected (which I have already implemented).

I actually want the value from this first option POSTED so that the table is not empty when the page loads. At the moment the table rows are only filled on an "onchange" event in the dropdown menu, which posts the form values back to the second myqsl query.

And I'm still not sure that's possible in PHP. Would love to hear your second idea...

haku · May 14, 2008

I'm not going to write your code, but basically you want to add

selected="selected"

to whichever option you want to be selected on page load. You can do this in PHP - you just have to figure out the logic that will let you know which option to add the above code to.

Seema · May 14, 2008

Thanks for your advice, but I have to admit, I'm getting confused here.

I thought I HAD already added the selected="selected" to each option, here in the while loop:

$i=0;
while ($i < $num) {

$startdate=mysql_result($result,$i,"StartDate");
$enddate=mysql_result($result,$i,"EndDate");

//create variables to format db dates for display
$display_startdate = date('j M Y', strtotime($startdate));
$display_enddate = date('j M Y', strtotime($enddate));
$selected = $_POST ['date'];
echo "<option value=\"$enddate\"";

   if($enddate == $selected){
      echo " selected=\"selected\">";
   } else{
      echo ">";
   }
echo "$display_startdate to $display_enddate</option>";

$i++;
};

And it works! I mean, the option I want IS being selected in the dropdown menu when the page loads.

Only the value of that option is not being submitted on page load so the table below (the results) is empty. The option value will only submit when the user changes the selection in the dropdown, thanks to the "onchange" javascript in the <select> tag.

I would like to know how I can get this first pre-selected dropdown option to submit on page load, so there are already some default results showing on the page when the user arrives.

Has that got to do with where I am putting the select=selected code? ??? Am I putting it in the wrong place?

benphp · May 14, 2008

I'm not seeing any if statements that would prevent the SQL from running on page load. From what I see, this:

//CREATE TABLE ROWS BASED ON SELECTION FROM DROPDOWNS
//1. Connect to database again

mysql_connect ("localhost" , "databaseuser" , "password") or die ('Error: ' . mysqlerror());
mysql_select_db (databasename);

//2. Pull info based on selected date from the table
$query="SELECT CourseType, Price, Location, Promotion FROM coursetable WHERE EndDate = '$selected' ORDER by Price ASC";
$courseinfo=mysql_query($query);  
$num=mysql_numrows($courseinfo);
mysql_close ();   

$i=0;
while ($i < $num) {
$coursetype=mysql_result($courseinfo,$i,"CourseType");
$price=mysql_result($courseinfo,$i,"Price");
$location=mysql_result($courseinfo,$i,"Location");
$promotion=mysql_result($courseinfo,$i,"Promotion");
$newprice=$price/$exrate;
echo "<tr><td>$promotion</td><td>$coursetype</td><td>$location</td><td>$price</td><td>".round($newprice);
echo "</td></tr>";
$i++;
};

isn't waiting for a form submit. It's going to run unless it's inside an if/while statement. The only var you need is $selected, which comes from the previous statement, so $selected = $enddate[0] should pull the first end date from the db and show the results in the second table.

Seema · May 15, 2008

Yes, I see what you mean - it should work on that basis, but it's just not! All I get on page load are the table headers with the dropdowns populated fine, but no info below.

I looked at the page source on first load and the problem is that the first option in the dropdown is not definitely not displaying selected="selected" in the html, even with your extra code (I tried it in several different places to be sure). It writes it correctly after the form is submitted, though.

Is there something in my code that could be preventing it from working?

BIG thanks if you can look through it again / suggest that other way you mentioned...

benphp · May 15, 2008

It sounds like your query is executing - just not finding anything.

Print you SQL and see what it looks like.

$query="SELECT CourseType, Price, Location, Promotion FROM coursetable WHERE EndDate = '$selected' ORDER by Price ASC";

print $query;

That will give you some clues as to where to look. If $selected is blank, you need to populate it with the first EndDate from the first select statement - which is why I suggested $enddate[0].

Seema · May 16, 2008

Thanks again for your help. The print SQL does indeed give a blank $selected on first page load.

As soon as the form is submitted (by changing the option in the dropdown), $selected gets a value.

If you then change it back to the original option, $selected also has a value, and the table rows print, so there is info in the db to be found. That's why I thought the query was not executing until the form was submitted. \

But then I have even tried a straight out $selected = $enddate[0] and no joy. I feel like I must be doing something very stupid for this not to work - but the code is all there for you to see.

Does anyone have any other ideas, please?

blueman378 · May 16, 2008

so put in if $selected = "";

$selected = "whatever";

Seema · May 17, 2008

Thanks for your patience, people. I have added the following code, above the second sql, which checks if $selected is empty, then assigns a value of $enddate[0] if true:

if (empty($selected)) {
$selected = $enddate[0];
}

It's still not working, but now, when I print $query I get

SELECT CourseType, Price, Location, Promotion FROM coursetable WHERE EndDate = '2' ORDER by Price ASC

WTF???

How can EndDate = '2'? Where is this value coming from? I know for a fact $enddate[0] = 2008-10-31, as it displays it correctly in the dropdown options.

I continue to greatly appreciate any help you can give.

Seema · May 17, 2008

Ok, I'm guessing it is returning the row number in the database, instead of the actual value of the field.

I've been trying to reformat my array and while loop so that $enddate [0] displays the field value, not the row number (thus far with not much luck). If anyone can help me to rewrite, that would be great. If not, maybe you could just tell me if this is even what I should be doing, or is there another way?

Once again, thanks much for your help.

phorman · May 17, 2008

Ok, I'm guessing it is returning the row number in the database, instead of the actual value of the field.

I've been trying to reformat my array and while loop so that $enddate [0] displays the field value, not the row number (thus far with not much luck). If anyone can help me to rewrite, that would be great. If not, maybe you could just tell me if this is even what I should be doing, or is there another way?

Once again, thanks much for your help.

Your variable enddate is not the array you think it is. It is an array, but it is string. Soo, when you ask for $enddate[0] you are infact getting the 2 from 2008, the index 0 of the string array. If you want the enddates stored in array so that you can call the $enddate[0] and get the desired full date, change the following:

<?php
$enddate=mysql_result($result,$i,"EndDate");
?>

to

<?php
$enddate[$i]=mysql_result($result,$i,"EndDate");
?>

Seema · May 19, 2008

Ummm... thanks, but that gets me an enddate value in the dropdown menu of 31 Dec 1969 (on all 3 options found). ???

And the print $query is now showing a blank for EndDate.

I will keep trying today. I don't want to give up on this, so if anyone can shed some light on what I should do, please chip in!

Seema · May 19, 2008

Just to summarise as we've been trying many different things here, the problem is essentially that on page load, the first option in the dropdown is not echoing selected="selected" in the html, as it should, so the table below does not populate.

It echoes fine once the dropdown option has been manually changed (and therefore re-submitted).

1. All dropdown options are displaying correctly, with the correct values echoed in the html.

2. The selected EndDate values being passed to the database query - for all options, including the first - are all correct (according to print $query) and the info in the table below displays correctly, but only once the dropdown value is manually changed / changed back.

Therefore the problem surely lies with the while loop, creating the dropdown options, which is copied again below:

<?php
$query="SELECT DISTINCT StartDate, EndDate FROM coursetable WHERE EndDate > now() ORDER by StartDate ASC";
$result=mysql_query($query);
$num=mysql_numrows($result);

mysql_close ();

//Display info in dropdown menu

$i=0;
$selected = $_POST ['date'];
while ($i < $num) {

$startdate=mysql_result($result,$i,"StartDate");
$enddate=mysql_result($result,$i,"EndDate");

//create variables to format db dates for display
$display_startdate = date('j M Y', strtotime($startdate));
$display_enddate = date('j M Y', strtotime($enddate));

echo "<option value=\"$enddate\"";
   if($enddate == $selected){
      echo " selected=\"selected\">";
   } else{
      echo ">";
   }
echo "$display_startdate to $display_enddate</option>";
$i++;
};
?>

This is driving me crazy now, can anyone put me out of my misery (no, not that way!!)

sasa · May 19, 2008

try

<table>
<tr>
<td>Promotion</td>
<td>Course Type</td>
<td>Location</td>
<td>Prices in THB for: 
<form name="form1" method="post" action="<?php echo basename($_SERVER['PHP_SELF']); ?>">
    <select name="date" onchange="document.form1.submit()">

<?php 
//PULL DATE SELECTION OPTIONS FROM DB
//1. Connect to database
mysql_connect ("localhost" , "databaseadmin" , "password") or die ('Error: ' . mysqlerror());
mysql_select_db (databasename);

//2. Pull date info from coursetable
$query="SELECT DISTINCT StartDate, EndDate FROM coursetable WHERE EndDate > now() ORDER by StartDate ASC";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close ();

//3. Display info in dropdown menu
$i=0;
$selected = isset($_POST ['date']) ? $_POST ['date'] : mysql_result($result,0,"EndDate"); // add this line
while ($i < $num) {

$startdate=mysql_result($result,$i,"StartDate");
$enddate=mysql_result($result,$i,"EndDate");

//create variables to format db dates for display
$display_startdate = date('j M Y', strtotime($startdate));
$display_enddate = date('j M Y', strtotime($enddate));
//$selected = $_POST ['date']; // remove this line
echo "<option value=\"$enddate\"";

   if($enddate == $selected){
      echo " selected=\"selected\">";
   } else{
      echo ">";
   }
echo "$display_startdate to $display_enddate</option>";

$i++;
};

?>
</select>
</td>

<?php 
//CREATE 2nd DROPDOWN FOR CURRENCY CONVERSION
	$exchangerates = array    (    
                            'USD' => 31,
                            'EUR' => 48,
                            'GBP' => 62,
		    'SGD' => 21,
	            'MYR' => 9,
                            'AUD' => 28,
                            'HKD' => 15,
                    'SEK' => 6							            
                        );
?> 
<td>
<?php 
if(!isset($_POST['currency'])) $_POST['currency'] = 'USD'; //add this line
?> 
Approx price in 
<select name="currency" onchange="document.form1.submit()">
<option value="USD" <?php if ($_POST['currency'] == 'USD') { echo 'selected="selected"';} ?>>US $</option>
<option value="EUR" <?php if ($_POST['currency'] == 'EUR') { echo 'selected="selected"';} ?>>Euro</option>
<option value="GBP" <?php if ($_POST['currency'] == 'GBP') { echo 'selected="selected"';} ?>>GB £</option>
<option value="SGD" <?php if ($_POST['currency'] == 'SGD') { echo 'selected="selected"';} ?>>SGD</option>
<option value="MYR" <?php if ($_POST['currency'] == 'MYR') { echo 'selected="selected"';} ?>>Ringgit</option>
<option value="HKD" <?php if ($_POST['currency'] == 'HKD') { echo 'selected="selected"';} ?>>HK $</option>
<option value="AUD" <?php if ($_POST['currency'] == 'AUD') { echo 'selected="selected"';} ?>>AUS $</option>
<option value="SEK" <?php if ($_POST['currency'] == 'SEK') { echo 'selected="selected"';} ?>>SEK</option>
</select></form></td></tr>

<?php
$currency = $_POST ['currency'];
$exrate = $exchangerates [$currency];

//CREATE TABLE ROWS BASED ON SELECTION FROM DROPDOWNS
//1. Connect to database again

mysql_connect ("localhost" , "databaseuser" , "password") or die ('Error: ' . mysqlerror());
mysql_select_db (databasename);

//2. Pull info based on selected date from the table
$query="SELECT CourseType, Price, Location, Promotion FROM coursetable WHERE EndDate = '$selected' ORDER by Price ASC";
$courseinfo=mysql_query($query);  
$num=mysql_numrows($courseinfo);
mysql_close ();   

$i=0;
while ($i < $num) {
$coursetype=mysql_result($courseinfo,$i,"CourseType");
$price=mysql_result($courseinfo,$i,"Price");
$location=mysql_result($courseinfo,$i,"Location");
$promotion=mysql_result($courseinfo,$i,"Promotion");
$newprice=$price/$exrate;
echo "<tr><td>$promotion</td><td>$coursetype</td><td>$location</td><td>$price</td><td>".round($newprice);
echo "</td></tr>";
$i++;
};
?>

</table>

Seema · May 19, 2008

HALLELUJAH

There is a (php) God and his/ her name is sasa!

Thank you so much. This works perfectly, just as it's supposed to. You have made my week.

Now I just have to go and figure out WHY it works so I can understand this stuff better...

sasa · May 19, 2008

$selected = isset($_POST ['date']) ? $_POST ['date'] : mysql_result($result,0,"EndDate"); // add this line

check is posted some value and setup $selected variable (if it not set $selected is result of 1s row)

if(!isset($_POST['currency'])) $_POST['currency'] = 'USD'; //add this line

same for another posted variable, btw. you don't need to check isset variable $_POST['currency'] is set for shure

that is it

Sign In

[SOLVED] Is this even possible in PHP?

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

Join the conversation

Important Information