Select Mysqlpopulated Drop Down Option And Use In A 2Nd Sql Query

[wright67uk]

In the code below I am populating a drop down list with results from the first MySQL query.

 

I would then like to select an option from the drop down, and use the selected option as a variable in a second

MySQL query.

 

If possible I would like to do his on the same page, posting to self.

 

I've given it a shot, but have run into some trouble. Can anyone help?

 

$sql = "SELECT location FROM snag_score_cards WHERE user_id = $user_id";
$result = mysql_query($sql);
$selected_id = isset($_POST['user_id']) ? intval($_POST['user_id']) : false;
$who = '';
$options = '';
while ($row=mysql_fetch_array($result)) { if($selected_id==$row['location'])
{
$selected = ' selected="selected"'; } else { $selected = ''; }
$options .= "<option value=\"{$row['location']}\"{$selected}>{$row['location']}</option>\n";
}
?>


<form method="post" action="<?php echo $PHP_SELF;?>">
<select name="user_id"><option value="">Select Scorecard</option><?php echo $options ?></select>
<input type="submit" value="Submit"></form>

<?php
$location = $_POST['location'];
$sql = "SELECT par1, par2, par3, par3, par5, par6, par7, par8, pa9 FROM snag_score_cards WHERE user_id = $user_id AND location = $location";
$_POST['par1']; $_POST['par2']; $_POST['par3']; $_POST['par4']; $_POST['par5']; $_POST['par6']; $_POST['par7']; $_POST['par8']; $_POST['par9'];
?>


<form name="form" action="addup5.php" method="post" >


<input type="text" autocomplete="off" name="Scores" id="Score" value="Score" readonly style="border:0px" />
<input type="text" autocomplete="off" name="Par" id="Par" value="Par" readonly style="border:0px" />
<input name="Par Score" id="Par Score" value="Par Score" readonly style="border:0px" /><br>


<input type="text" autocomplete="off" name="sum1" id="hole1A" value="" onblur="calc(this.value,'hole1B','hole1result')" />
<input type="text" autocomplete="off" name="sum2" readonly value="<?php echo $par1;?>" id="hole1B" onblur="calc(this.value,'hole1A','hole1result')" />
<input type="text" name="sum" value="" id="hole1result" readonly style=""> <br>

[TOA]

We need more info. What happens / doesn't happen? Do you have error reporting turned on?

[wright67uk]

I haven't had an errors, but after a bit of echoing I see that $location hasn't got a value...

How can I put the selection from the drop down list into a variable?

 

 

$sql = "SELECT location FROM snag_score_cards WHERE user_id = $user_id"; 
$result = mysql_query($sql);
$selected_id = isset($_POST['user_id']) ? intval($_POST['user_id']) : false; 
$who = ''; 
$options = ''; 
while ($row=mysql_fetch_array($result)) { if($selected_id==$row['location']) 
{
$selected = ' selected="selected"'; } else { $selected = ''; } 
$options .= "<option value=\"{$row['location']}\"{$selected}>{$row['location']}</option>\n"; 
} 
?>


<form method="post" action="<?php echo $PHP_SELF;?>">
<select name="user_id"><option value="">Select Scorecard</option><?php echo $options ?></select>
<input type="submit" value="Submit"></form>

<?php
$location = $row['location'];
$location2 = $_POST['location'];
echo "1". $location;
echo "2". $location2;
echo "3". $row['location'];
$sql = "SELECT par1, par2, par3, par3, par5, par6, par7, par8, pa9 FROM snag_score_cards WHERE user_id = $user_id";
$_POST['par1']; $_POST['par2']; $_POST['par3']; $_POST['par4']; $_POST['par5']; $_POST['par6']; $_POST['par7']; $_POST['par8']; $_POST['par9']; 
?>

[wright67uk]

http://www.treequoter.com/selected.php my example

[wright67uk]

oops, ive just seen I was using the wrong select name. I've now changed it.

$location now has a value, however nothing happens when I echo $par1 at the end of my script.

 

Any ideas why?

 

$sql = "SELECT location FROM snag_score_cards WHERE user_id = $user_id"; 
$result = mysql_query($sql);
$selected_id = isset($_POST['location']) ? intval($_POST['location']) : false; 
$who = ''; 
$options = ''; 
while ($row=mysql_fetch_array($result)) { if($selected_id==$row['location']) 
{
$selected = ' selected="selected"'; } else { $selected = ''; } 
$options .= "<option value=\"{$row['location']}\"{$selected}>{$row['location']}</option>\n"; 
} 
?>

<form method="post" action="<?php echo $PHP_SELF;?>">
<select name="location"><option value="">Select Scorecard</option><?php echo $options ?></select>
<input type="submit" value="Submit"></form>

<?php
$user_id = 7;
$location = $_POST['location'];
"<br/>"; echo "<h1>". $location . "<h1/>";

$sql = "SELECT par1, par2, par3, par3, par5, par6, par7, par8, pa9 FROM snag_score_cards WHERE location = $location";
$result = mysql_query($sql);

$_POST['par2']; $_POST['par3']; $_POST['par4']; $_POST['par5']; $_POST['par6']; $_POST['par7']; $_POST['par8']; $_POST['par9']; 

$par1 = $_POST['par1']; 
echo $par1;
?>

[wright67uk]

http://www.treequoter.com/selected2.php

[Muddy_Funster]

you can't have more than one option as selected="selected", no matter what is chosen the select bumps to Wimbeldon Park after the submit button is hit. The best way to do this is using AJAX set on the onchange of the Select Box. I see you already have some JS on the page, so just add another function to deal with the AJAX request to populate the form values. Jquery makes this a lot easier, so you probably want to look into getting that on your server, or linking to the google mini hosted version.

 

That said, looking at your query I'm not totaly convinced your tables are designed properly, and I don't think your query is correct, you go par1->par8 and then pa9 at the end....type-o on the site or in the query?

Another thing, you get the results out of the database, and don't seem to do anything with them, you have refference to $_POST['par1'] and such, but there are no form fields with that name. Another thing, looking at the source of the form, you have 9 groups of input where two fields have the same name for each group, every element should have a unique name, and if you are using id's they should all be unique as well. The only thing that should be attached to more than one element on a document is a class.

 

So:

you need to look at properly naming the form element values,

you need to look at the query and make sure there arn't any type-o's in it,

you need to think about, and change, what you are trying to do with the $_POST['parX'] list,

you need to create some way of getting the information from the database onto the screen,

you need to fix your selected="selected" on the dropdown,

you should probably look into using a JQuery AJAX call instead of the submit button to populate the values of the form that you are getting from the database.

 

That's just at a glance, but let us know how it goes.

 

Edt: for typing errors

Edited December 18, 2012 by Muddy_Funster

[wright67uk]

Muddy_Funster, thank you for your post,

 

Yes I did have some typo's, and my query was wrong.

I have fixed these...

 

I didn't include my whole file, which Is why some of the code I posted may of seemed irrelevant.

 

regarding the form names, I do need to sort these out, but at the moment I'm using a piece of Javascript that references these names, and I'm keeping it for now as a working example.

 

I could however do with a little help with the drop down.

How can I make it stay on the selected option?

 

the url is: http://www.treequoter.com/selected2.php

 

<html> <head> <title>Text Summation</title>


<script type="text/javascript">
function calc(A,B,SUM) { 
 var one = Number(A); 
 if (isNaN(one)) { alert('Invalid entry: '+A); one=0; } 
 var two = Number(document.getElementById(B).value); 
 if (isNaN(two)) { alert('Invalid entry: '+B); two=0; } 
 document.getElementById(SUM).value = one - two;
 addition()
} 
</script>


<script language="javascript" type="text/javascript">  
function addition(){  
 var t =  document.getElementById("hole1result").value * 1;  
     t += document.getElementById("hole2result").value * 1;  
     t += document.getElementById("hole3result").value * 1;  
     t += document.getElementById("hole4result").value * 1;   
     t += document.getElementById("hole5result").value * 1;  
     t += document.getElementById("hole6result").value * 1;  
     t += document.getElementById("hole7result").value * 1;  
     t += document.getElementById("hole8result").value * 1 ;  
     t += document.getElementById("hole9result").value * 1; 
 document.getElementById("totalparscore").value = t;   
 return false;  // or true after testing
}  
</script>


<style type="text/css">


input[type=text] {width: 40px};
input[value="0"] { color: red; } 
input[value="0"] { background-color: blue; } 
</style>


</head>
<body>


<?php


$user_id = 7;


### PASSWORD BLOCK ###


$connect_solning = mysql_connect($hostname_connect, $username_connect, $password_connect) or trigger_error(mysql_error(),E_USER_ERROR);
@mysql_select_db($database_connect) or die (mysql_error()); 


$sql = "SELECT location FROM snag_score_cards WHERE user_id = $user_id"; 
$result = mysql_query($sql);
$selected_id = isset($_POST['location']) ? intval($_POST['location']) : false; 
$who = ''; 
$options = ''; 
while ($row=mysql_fetch_array($result)) { if($selected_id==$row['location']) 
{
$selected = ' selected="selected"'; } else { $selected = ''; } 
$options .= "<option value=\"{$row['location']}\"{$selected}>{$row['location']}</option>\n"; 
} 
?>


<form method="post" action="<?php echo $PHP_SELF;?>">
<select name="location"><option value="">Select Scorecard</option><?php echo $options ?></select>
<input type="submit" value="Submit"></form>

<?php
$user_id = 7;
$location = $_POST['location'];  
"<br>"; echo "<h1>". $location . "<h1/>";
$sql = "SELECT par1, par2, par3, par4, par5, par6, par7, par8, par9 FROM snag_score_cards WHERE location = '$location'";
$result = mysql_query($sql);


while($row = mysql_fetch_array($result)) 
{  
$par1 = $row['par1']; $par2 = $row['par2']; $par3 = $row['par3']; $par4 = $row['par4'];
$par5 = $row['par5']; $par6 = $row['par6']; $par7 = $row['par7']; $par8 = $row['par8']; 
$par9 = $row['par9'];  
} 
?>


<form name="form" action="addup5.php" method="post" >  


<input type="text" autocomplete="off" name="Scores"    id="Score"     value="Score"     readonly style="border:0px" />
<input type="text" autocomplete="off" name="Par"       id="Par"       value="Par"       readonly style="border:0px" />
<input                                name="Par Score" id="Par Score" value="Par Score" readonly style="border:0px" /><br>


<input type="text" autocomplete="off"  name="sum1" id="hole1A" value="" onblur="calc(this.value,'hole1B','hole1result')" /> 
<input type="text" autocomplete="off" name="sum2" readonly value="<?php echo $par1;?>"  id="hole1B" onblur="calc(this.value,'hole1A','hole1result')" /> 
<input type="text" name="sum" value="" id="hole1result" readonly style=""> <br>


<input type="text" autocomplete="off" name="sum1" id="hole2A" value="" onblur="calc(this.value,'hole2B','hole2result')" /> 
<input type="text" autocomplete="off" name="sum2" readonly value="<?php echo $par2;?>" id="hole2B" onblur="calc(this.value,'hole2A','hole2result')" /> 
<input type="text" name="sum2T" value="" id="hole2result" readonly style=""> <br>


<input type="text" autocomplete="off" name="sum1" id="hole3A" value="" onblur="calc(this.value,'hole3B','hole3result')" /> 
<input type="text" autocomplete="off" name="sum2" readonly value="<?php echo $par3;?>" id="hole3B" onblur="calc(this.value,'hole3A','hole2result')" /> 
<input type="text" name="sum3" value="" id="hole3result" readonly style=""> <br>


<input type="text" autocomplete="off" name="sum1" id="hole4A" value="" onblur="calc(this.value,'hole4B','hole4result')" /> 
<input type="text" autocomplete="off" name="sum2" readonly value="<?php echo $par4;?>" id="hole4B" onblur="calc(this.value,'hole4A','hole4result')" /> 
<input type="text" name="sum4" value="" id="hole4result" readonly style=""> <br>


<input type="text" autocomplete="off" name="sum1" id="hole5A" value="" onblur="calc(this.value,'hole5B','hole5result')" /> 
<input type="text" autocomplete="off" name="sum2" readonly value="<?php echo $par5;?>" id="hole5B" onblur="calc(this.value,'hole5A','hole5result')" /> 
<input type="text" name="sum5" value="" id="hole5result" readonly style=""> <br>



<input type="text" autocomplete="off" name="sum1" id="hole6A" value="" onblur="calc(this.value,'hole6B','hole6result')" /> 
<input type="text" autocomplete="off" name="sum2" readonly value="<?php echo $par6;?>" id="hole6B" onblur="calc(this.value,'hole6A','hole6result')" /> 
<input type="text" name="sum6" value="" id="hole6result" readonly style=""> <br>


<input type="text" autocomplete="off" name="sum1" id="hole7A" value="" onblur="calc(this.value,'hole7B','hole7result')" /> 
<input type="text" autocomplete="off" name="sum2" readonly value="<?php echo $par7;?>" id="hole7B" onblur="calc(this.value,'hole7A','hole7result')" /> 
<input type="text" name="sum7" value="" id="hole7result" readonly style=""> <br>


<input type="text" autocomplete="off" name="sum1" id="hole8A" value="" onblur="calc(this.value,'hole8B','hole8result')" /> 
<input type="text" autocomplete="off" name="sum2" readonly value="<?php echo $par8;?>" id="hole8B" onblur="calc(this.value,'hole8A','hole8result')" /> 
<input type="text" name="sum8" value="" id="hole8result" readonly style=""> <br>


<input type="text" autocomplete="off" name="sum1" id="hole9A" value="" onblur="calc(this.value,'hole9B','hole9result')" /> 
<input type="text" autocomplete="off" name="sum2" readonly value="<?php echo $par9;?>" id="hole9B" onblur="calc(this.value,'hole9A','hole9result')" /> 
<input type="text" name="sum9" value="" id="hole9result" readonly style=""> <br>


<input type="text" name="Total Scores"    id="Score"         value=""       readonly style="border:0px" />
<input type="text" name="Par Total"       id="Par"           value="Total"  readonly style="border:0px" />
<input type="text" name="totalparscore"   id="totalparscore" value=""       readonly style="" />


<input type="submit" name="submit"/>


</form>
</body> </html>

 

<html> <head> <title>Text Summation</title>


<script type="text/javascript">
function calc(A,B,SUM) { 
 var one = Number(A); 
 if (isNaN(one)) { alert('Invalid entry: '+A); one=0; } 
 var two = Number(document.getElementById(B).value); 
 if (isNaN(two)) { alert('Invalid entry: '+B); two=0; } 
 document.getElementById(SUM).value = one - two;
 addition()
} 
</script>


<script language="javascript" type="text/javascript">  
function addition(){  
 var t =  document.getElementById("hole1result").value * 1;  
     t += document.getElementById("hole2result").value * 1;  
     t += document.getElementById("hole3result").value * 1;  
     t += document.getElementById("hole4result").value * 1;   
     t += document.getElementById("hole5result").value * 1;  
     t += document.getElementById("hole6result").value * 1;  
     t += document.getElementById("hole7result").value * 1;  
     t += document.getElementById("hole8result").value * 1 ;  
     t += document.getElementById("hole9result").value * 1; 
 document.getElementById("totalparscore").value = t;   
 return false;  // or true after testing
}  
</script>


<style type="text/css">


input[type=text] {width: 40px};
input[value="0"] { color: red; } 
input[value="0"] { background-color: blue; } 
</style>


</head>
<body>


<?php


$user_id = 7;


### PASSWORD BLOCK ###


$connect_solning = mysql_connect($hostname_connect, $username_connect, $password_connect) or trigger_error(mysql_error(),E_USER_ERROR);
@mysql_select_db($database_connect) or die (mysql_error()); 


$sql = "SELECT location FROM snag_score_cards WHERE user_id = $user_id"; 
$result = mysql_query($sql);
$selected_id = isset($_POST['location']) ? intval($_POST['location']) : false; 
$who = ''; 
$options = ''; 
while ($row=mysql_fetch_array($result)) { if($selected_id==$row['location']) 
{
$selected = ' selected="selected"'; } else { $selected = ''; } 
$options .= "<option value=\"{$row['location']}\"{$selected}>{$row['location']}</option>\n"; 
} 
?>


<form method="post" action="<?php echo $PHP_SELF;?>">
<select name="location"><option value="">Select Scorecard</option><?php echo $options ?></select>
<input type="submit" value="Submit"></form>

<?php
$user_id = 7;
$location = $_POST['location'];  
"<br>"; echo "<h1>". $location . "<h1/>";
$sql = "SELECT par1, par2, par3, par4, par5, par6, par7, par8, par9 FROM snag_score_cards WHERE location = '$location'";
$result = mysql_query($sql);


while($row = mysql_fetch_array($result)) 
{  
$par1 = $row['par1']; $par2 = $row['par2']; $par3 = $row['par3']; $par4 = $row['par4'];
$par5 = $row['par5']; $par6 = $row['par6']; $par7 = $row['par7']; $par8 = $row['par8']; 
$par9 = $row['par9'];  
} 
?>


<form name="form" action="addup5.php" method="post" >  


<input type="text" autocomplete="off" name="Scores"    id="Score"     value="Score"     readonly style="border:0px" />
<input type="text" autocomplete="off" name="Par"       id="Par"       value="Par"       readonly style="border:0px" />
<input                                name="Par Score" id="Par Score" value="Par Score" readonly style="border:0px" /><br>


<input type="text" autocomplete="off"  name="sum1" id="hole1A" value="" onblur="calc(this.value,'hole1B','hole1result')" /> 
<input type="text" autocomplete="off" name="sum2" readonly value="<?php echo $par1;?>"  id="hole1B" onblur="calc(this.value,'hole1A','hole1result')" /> 
<input type="text" name="sum" value="" id="hole1result" readonly style=""> <br>


<input type="text" autocomplete="off" name="sum1" id="hole2A" value="" onblur="calc(this.value,'hole2B','hole2result')" /> 
<input type="text" autocomplete="off" name="sum2" readonly value="<?php echo $par2;?>" id="hole2B" onblur="calc(this.value,'hole2A','hole2result')" /> 
<input type="text" name="sum2T" value="" id="hole2result" readonly style=""> <br>


<input type="text" autocomplete="off" name="sum1" id="hole3A" value="" onblur="calc(this.value,'hole3B','hole3result')" /> 
<input type="text" autocomplete="off" name="sum2" readonly value="<?php echo $par3;?>" id="hole3B" onblur="calc(this.value,'hole3A','hole2result')" /> 
<input type="text" name="sum3" value="" id="hole3result" readonly style=""> <br>


<input type="text" autocomplete="off" name="sum1" id="hole4A" value="" onblur="calc(this.value,'hole4B','hole4result')" /> 
<input type="text" autocomplete="off" name="sum2" readonly value="<?php echo $par4;?>" id="hole4B" onblur="calc(this.value,'hole4A','hole4result')" /> 
<input type="text" name="sum4" value="" id="hole4result" readonly style=""> <br>


<input type="text" autocomplete="off" name="sum1" id="hole5A" value="" onblur="calc(this.value,'hole5B','hole5result')" /> 
<input type="text" autocomplete="off" name="sum2" readonly value="<?php echo $par5;?>" id="hole5B" onblur="calc(this.value,'hole5A','hole5result')" /> 
<input type="text" name="sum5" value="" id="hole5result" readonly style=""> <br>



<input type="text" autocomplete="off" name="sum1" id="hole6A" value="" onblur="calc(this.value,'hole6B','hole6result')" /> 
<input type="text" autocomplete="off" name="sum2" readonly value="<?php echo $par6;?>" id="hole6B" onblur="calc(this.value,'hole6A','hole6result')" /> 
<input type="text" name="sum6" value="" id="hole6result" readonly style=""> <br>


<input type="text" autocomplete="off" name="sum1" id="hole7A" value="" onblur="calc(this.value,'hole7B','hole7result')" /> 
<input type="text" autocomplete="off" name="sum2" readonly value="<?php echo $par7;?>" id="hole7B" onblur="calc(this.value,'hole7A','hole7result')" /> 
<input type="text" name="sum7" value="" id="hole7result" readonly style=""> <br>


<input type="text" autocomplete="off" name="sum1" id="hole8A" value="" onblur="calc(this.value,'hole8B','hole8result')" /> 
<input type="text" autocomplete="off" name="sum2" readonly value="<?php echo $par8;?>" id="hole8B" onblur="calc(this.value,'hole8A','hole8result')" /> 
<input type="text" name="sum8" value="" id="hole8result" readonly style=""> <br>


<input type="text" autocomplete="off" name="sum1" id="hole9A" value="" onblur="calc(this.value,'hole9B','hole9result')" /> 
<input type="text" autocomplete="off" name="sum2" readonly value="<?php echo $par9;?>" id="hole9B" onblur="calc(this.value,'hole9A','hole9result')" /> 
<input type="text" name="sum9" value="" id="hole9result" readonly style=""> <br>


<input type="text" name="Total Scores"    id="Score"         value=""       readonly style="border:0px" />
<input type="text" name="Par Total"       id="Par"           value="Total"  readonly style="border:0px" />
<input type="text" name="totalparscore"   id="totalparscore" value=""       readonly style="" />


<input type="submit" name="submit"/>


</form>
</body> </html>