php - How can I make a dropdown box have multiple values? -
i have 2 tables ('players' , 'team'). user can select player name drop down list play in position, player name gets saved 'team' table under corresponding field name. code works fine if it's 1 player name getting saved, try save more (i need able save 15) gives me sql injection error. ik i'm trying save save value 15 columns, how can make dropdown list 15 different values?
here's code:
<?php //include database connection information. include('connect.php'); //start session. session_start(); //function display names in drop down list box populated players table. function get_name(){ $sql = "select player_id, name players"; $result = mysql_query($sql); //while loop echo out player names select box. echo "<select name='name'>"; while ($row = mysql_fetch_array($result)) { echo"<option value='" . $row['player_id'] . "'>" . $row['name'] . "</option>"; } echo '</select>'; } //declare variables. $fixture_id=$_get['fixture_id']; $select=$_get['update']; $submit=$_post['submit']; //because form method @ end post //check of select team has been clicked. if($select=='yes'){ //form ready edited. echo "<form method='post' action=''>"; echo "<table border='1' >"; echo"<tr><td><strong>fixture_id:</strong>'$fixture_id'</td></tr>"; echo "<td><label for='loose_head_prop'>loose head prop(1)</label></td><td>"; echo get_name(); echo '</td></tr>'; echo "<td><label for='hooker'>hooker(2)</label></td><td>"; echo get_name(); echo '</td></tr>'; echo "<tr><td><label for='tight_head_prop'>tight head prop(3)</label></td><td>"; echo get_name(); echo "</td></tr>"; echo "<tr><td><label for='lock1'>lock(4)</label></td><td>"; echo get_name(); echo "</td></tr>"; echo "<tr><td><label for='lock2'>lock(5)</label></td><td>"; echo get_name(); echo "</td></tr>"; echo "<tr><td><label for='flank1'>flank(6)</label></td><td>"; echo get_name(); echo "</td></tr>"; echo "<tr><td><label for='flank2'>flank(7)</label></td><td>"; echo get_name(); echo "</td></tr>"; echo "<tr><td><label for='eigth_man'>eigth man(8)</label></td><td>"; echo get_name(); echo "</td></tr>"; echo "<tr><td><label for='scrum_half'>scrum half(9)</label></td><td>"; echo get_name(); echo "</td></tr>"; echo "<tr><td><label for='fly_half'>fly half(10)</label></td><td>"; echo get_name(); echo "</td></tr>"; echo "<tr><td><label for='left_wing'>left_wing(11)</label></td><td>"; echo get_name(); echo "</td></tr>"; echo "<tr><td><label for='inside_centre'>inside centre(12)</label></td><td>"; echo get_name(); echo "</td></tr>"; echo "<tr><td><label for='outside_centre'>outside centre(13)</label></td><td>"; echo get_name(); echo "</td></tr>"; echo "<tr><td><label for='right_wing'>right wing(14)</label></td><td>"; echo get_name(); echo "</td></tr>"; echo "<tr><td><label for='full_back'>full back(15)</label></td><td>"; echo get_name(); echo "</td></tr>"; echo "<tr><td><input type='submit' name='submit' value='save team'/></td></tr>"; echo "</table>"; echo "</form>"; //check if submit has been clicked if(isset($_post['submit'])){ //grab grop downlist value. $name = $_post['name']; //savedata database. mysql_query("insert team (loose_head_prop, hooker, tight_head_prop, lock1, lock2, flank1, flank2, eigth_man, scrum_half, fly_half, left_wing, inside_centre, outside_centre, right_wing, full_back) select name players player_id= '" . mysql_real_escape_string($name) ."', '" . mysql_real_escape_string($name) ."', '" . mysql_real_escape_string($name) ."', '" . mysql_real_escape_string($name) ."', '" . mysql_real_escape_string($name) ."', '" . mysql_real_escape_string($name) ."', '" . mysql_real_escape_string($name) ."', '" . mysql_real_escape_string($name) ."', '" . mysql_real_escape_string($name) ."', '" . mysql_real_escape_string($name) ."', '" . mysql_real_escape_string($name) ."', '" . mysql_real_escape_string($name) ."', '" . mysql_real_escape_string($name) ."', '" . mysql_real_escape_string($name) ."', '" . mysql_real_escape_string($name) ."'") or die(mysql_error()); //once saved redirect list_players. header("location: team_selection.php"); } }else{ //display fixtures team can selected. //get fixtures database. $sql= mysql_query("select * fixtures") or die(mysql_error()); //table header. echo "<form method=post>"; echo "<table border='1'><tr bgcolor='#cccccc'><td>opponents</td><td>date</td><td>venue</td> <td>action</td></tr>"; //loop through results of database query, displaying them in table. while($row=mysql_fetch_array($sql)){ //while loop starts. $fixture_id=$row['fixture_id']; //fixture id use later. $opponents=$row['opponents']; $date=$row['date']; $venue=$row['venue']; //end loop. echo "<tr><td>$opponents</td><td>$date</td><td>$venue</td><td> <a href=team_selection.php?update=yes&fixture_id=$fixture_id>select team</a></td></tr>"; } echo "</table>"; //table ends outside loop. echo "</form>"; } ?>
normally have table 1 row per player, field giving positio. in such case (crudely) need put list of ids in clause in select. this:-
$name = $_post['name']; $name = array(); foreach($_post['name'] $aname) { $name[] = mysql_real_escape_string($aname); } //savedata database. if (count($name) > 0) { mysql_query("insert team (player) select name players player_id in ('".implode("','", $name)."')"; or die(mysql_error()); } however assuming have list of players in single row on table this:-
//grab grop downlist value. $name = $_post['name']; $name = array(); foreach($_post['name'] $aname) { $name[] = mysql_real_escape_string($aname); } //savedata database. if (count($name) == 15) { mysql_query("insert team (loose_head_prop, hooker, tight_head_prop, lock1, lock2, flank1, flank2, eigth_man, scrum_half, fly_half, left_wing, inside_centre, outside_centre, right_wing, full_back) select a.name, b.name, c.name, d.name, e.name, f.name, g.name, h.name, i.name, j.name, k.name, l.name, m.name, n.name, o.name, players inner join players b on b.player_id = $name[1] inner join players c on c.player_id = $name[2] inner join players d on d.player_id = $name[3] inner join players e on e.player_id = $name[4] inner join players f on f.player_id = $name[5] inner join players g on g.player_id = $name[6] inner join players h on h.player_id = $name[7] inner join players on i.player_id = $name[8] inner join players j on j.player_id = $name[9] inner join players k on k.player_id = $name[10] inner join players l on l.player_id = $name[11] inner join players m on m.player_id = $name[12] inner join players n on n.player_id = $name[13] inner join players o on o.player_id = $name[14] a.player_id = $name[0]"; or die(mysql_error()); } else { // not enough players! } edit
recoded script bit save using arrays of numbers.
<?php //include database connection information. include('connect.php'); //start session. session_start(); $positions = array('loose_head_prop'=>'loose head prop', 'hooker'=>'hooker', 'tight_head_prop'=>'tight head prop', 'lock1'=>'lock', 'lock2'=>'lock', 'flank1'=>'flank', 'flank2'=>'flank', 'eigth_man'=>'eigth man', 'scrum_half'=>'scrum half', 'fly_half'=>'fly half', 'left_wing'=>'left_wing', 'inside_centre'=>'inside centre', 'outside_centre'=>'outside centre', 'right_wing'=>'right wing', 'full_back'=>'full back'); //function display names in drop down list box populated players table. function get_name($fieldname, $currentselection = 0) { $sql = "select player_id, name players"; $result = mysql_query($sql); //while loop echo out player names select box. echo "<select name='$fieldname'>"; while ($row = mysql_fetch_array($result)) { echo"<option value='" . $row['player_id'] . "' ".(($currentselection == $row['player_id']) ? "selected='selected'" : '' )." >" . $row['name'] . "</option>"; } echo '</select>'; } //declare variables. $fixture_id = $_get['fixture_id']; $select = $_get['update']; $submit = $_post['submit']; //because form method @ end post //check of select team has been clicked. if($select=='yes') { //check if submit has been clicked if(isset($_post['submit'])) { $dataok = true; $fieldsescaped = array(); foreach($positions $positionfieldname=>$positiontextname) { if ($_post[$positionfieldname] == '') { $dataok = false; } $fieldsescaped[$positionfieldname] = intval($_post[$positionfieldname]); } if ($dataok) { mysql_query("insert team (loose_head_prop, hooker, tight_head_prop, lock1, lock2, flank1, flank2, eigth_man, scrum_half, fly_half, left_wing, inside_centre, outside_centre, right_wing, full_back) select a.name, b.name, c.name, d.name, e.name, f.name, g.name, h.name, i.name, j.name, k.name, l.name, m.name, n.name, o.name, players inner join players b on b.player_id = ".$fieldsescaped['hooker']." inner join players c on c.player_id = ".$fieldsescaped['tight_head_prop']." inner join players d on d.player_id = ".$fieldsescaped['lock1']." inner join players e on e.player_id = ".$fieldsescaped['lock2']." inner join players f on f.player_id = ".$fieldsescaped['flank1']." inner join players g on g.player_id = ".$fieldsescaped['flank2']." inner join players h on h.player_id = ".$fieldsescaped['eigth_man']." inner join players on i.player_id = ".$fieldsescaped['scrum_half']." inner join players j on j.player_id = ".$fieldsescaped['fly_half']." inner join players k on k.player_id = ".$fieldsescaped['left_wing']." inner join players l on l.player_id = ".$fieldsescaped['inside_centre']." inner join players m on m.player_id = ".$fieldsescaped['outside_centre']." inner join players n on n.player_id = ".$fieldsescaped['right_wing']." inner join players o on o.player_id = ".$fieldsescaped['full_back']." a.player_id = ".$fieldsescaped['loose_head_prop']) or die(mysql_error()); //once saved redirect list_players. header("location: team_selection.php"); } else { //form ready edited. echo "<form method='post' action=''>"; echo "<table border='1' >"; echo"<tr><td><strong>fixture_id:</strong>'$fixture_id'</td></tr>"; foreach($positions $positionfieldname=>$positiontextname) { echo "<td><label for='$positionfieldname'>$positiontextname</label></td><td>"; echo get_name($positionfieldname, $_post[$positionfieldname]); echo '</td></tr>'; } echo "<tr><td><input type='submit' name='submit' value='save team'/></td></tr>"; echo "</table>"; echo "</form>"; } } else { //display fixtures team can selected. //get fixtures database. $sql= mysql_query("select * fixtures") or die(mysql_error()); //table header. echo "<form method=post>"; echo "<table border='1'><tr bgcolor='#cccccc'><td>opponents</td><td>date</td><td>venue</td> <td>action</td></tr>"; //loop through results of database query, displaying them in table. while($row = mysql_fetch_array($sql)) { //while loop starts. $fixture_id = $row['fixture_id']; //fixture id use later. $opponents = $row['opponents']; $date = $row['date']; $venue = $row['venue']; //end loop. echo "<tr><td>$opponents</td><td>$date</td><td>$venue</td><td> <a href=team_selection.php?update=yes&fixture_id=$fixture_id>select team</a></td></tr>"; } echo "</table>"; //table ends outside loop. echo "</form>"; } } else { //form ready edited. echo "<form method='post' action=''>"; echo "<table border='1' >"; echo"<tr><td><strong>fixture_id:</strong>'$fixture_id'</td></tr>"; foreach($positions $positionfieldname=>$positiontextname) { echo "<td><label for='$positionfieldname'>$positiontextname</label></td><td>"; echo get_name($positionfieldname, $_post[$positionfieldname]); echo '</td></tr>'; } echo "<tr><td><input type='submit' name='submit' value='save team'/></td></tr>"; echo "</table>"; echo "</form>"; } ?> note above not tested, , have set tables, etc should of want.
you want bit more validation in script. had play around add this, , put class. rely on being pass database connection object, here give ideas.
<?php //include database connection information. include('connect.php'); //start session. session_start(); class produceselectionlist { private $positions = array('loose_head_prop'=>'loose head prop', 'hooker'=>'hooker', 'tight_head_prop'=>'tight head prop', 'lock1'=>'lock', 'lock2'=>'lock', 'flank1'=>'flank', 'flank2'=>'flank', 'eigth_man'=>'eigth man', 'scrum_half'=>'scrum half', 'fly_half'=>'fly half', 'left_wing'=>'left_wing', 'inside_centre'=>'inside centre', 'outside_centre'=>'outside centre', 'right_wing'=>'right wing', 'full_back'=>'full back'); private $db; private $fixture_id; private $fieldsescaped = array(); function __construct($db) { //declare variables. $this->db = $db; $this->fixture_id = $_request['fixture_id']; $this->mainprocessing(); } function mainprocessing() { if ($_request['saveteam'] , $this->unassignedfixture($this->fixture_id)) { if ($this->checkinput()) { $this->insertteam(); $this->fixturelist(); } else { $this->playerlist(); } } else { $this->fixturelist(); } } private function checkinput() { $dataok = true; $this->fieldsescaped = array(); foreach($this->positions $positionfieldname=>$positiontextname) { if ($_request[$positionfieldname] == '') { $dataok = false; } else { if (intval($_request[$positionfieldname]) > 0) { $this->fieldsescaped[$positionfieldname] = intval($_request[$positionfieldname]); } else { $dataok = false; } } } if ($dataok) { $sql = "select count(*) playercount players player_id in (".implode(',', $this->fieldsescaped).")"; if ($row = $this->db->fetch_array($sql)) { if ($row['playercount'] != 15) { $dataok = false; } } else { $$dataok = false; } } return $dataok; } private function insertteam() { $this->db->query("insert team (fixture_id, loose_head_prop, hooker, tight_head_prop, lock1, lock2, flank1, flank2, eigth_man, scrum_half, fly_half, left_wing, inside_centre, outside_centre, right_wing, full_back) select a.name, b.name, c.name, d.name, e.name, f.name, g.name, h.name, i.name, j.name, k.name, l.name, m.name, n.name, o.name, players inner join players b on b.player_id = ".$this->fieldsescaped['hooker']." inner join players c on c.player_id = ".$this->fieldsescaped['tight_head_prop']." inner join players d on d.player_id = ".$this->fieldsescaped['lock1']." inner join players e on e.player_id = ".$this->fieldsescaped['lock2']." inner join players f on f.player_id = ".$this->fieldsescaped['flank1']." inner join players g on g.player_id = ".$this->fieldsescaped['flank2']." inner join players h on h.player_id = ".$this->fieldsescaped['eigth_man']." inner join players on i.player_id = ".$this->fieldsescaped['scrum_half']." inner join players j on j.player_id = ".$this->fieldsescaped['fly_half']." inner join players k on k.player_id = ".$this->fieldsescaped['left_wing']." inner join players l on l.player_id = ".$this->fieldsescaped['inside_centre']." inner join players m on m.player_id = ".$this->fieldsescaped['outside_centre']." inner join players n on n.player_id = ".$this->fieldsescaped['right_wing']." inner join players o on o.player_id = ".$this->fieldsescaped['full_back']." a.player_id = ".$this->fieldsescaped['loose_head_prop']) or die($this->db->error()); } private function fixturelist() { //display fixtures team can selected. //get fixtures database. $sql= $this->db->query("select a.opponents, a.date, a.venue, a.fixture_id, count(b.fixture_id) teamcount fixtures left outer join teams b on a.fixture_id = b.fixture_id group a.opponents, a.date, a.venue, a.fixture_id") or die($this->db->error()); //table header. echo "<form method='post' action=''>"; echo "<table border='1'><tr bgcolor='#cccccc'><td>opponents</td><td>date</td><td>venue</td><td>action</td></tr>"; //loop through results of database query, displaying them in table. while($row = $this->db->fetch_array($sql)) { //while loop starts. echo "<tr><td>".htmlencode($row['opponents'])."</td><td>".htmlencode($row['date'])."</td><td>".htmlencode($row['venue'])."</td><td>".(($row['teamcount'] == 0 ) ? "<a href=team_selection.php?fixture_id=".$row['fixture_id'].">select team</a>" : "team assigned")."</td></tr>"; } echo "</table>"; //table ends outside loop. echo "</form>"; } private function playerlist() { //form ready edited. echo "<form method='post' action=''>"; echo "<table border='1' >"; echo"<tr><td><strong>fixture_id:</strong>'".$this->fixture_id."'</td></tr>"; foreach($this->positions $positionfieldname=>$positiontextname) { echo "<td><label for='$positionfieldname'>$positiontextname</label></td><td>"; echo $this->get_name($positionfieldname, $_post[$positionfieldname]); echo '</td></tr>'; } echo "<tr><td><input type='submit' name='saveteam' value='save team'/><input type='hidden' name='fixture_id' value='".$this->fixture_id."'/></td></tr>"; echo "</table>"; echo "</form>"; } private function unassignedfixture($fixture_id) { $sql= $this->db->query("select a.fixture_id, count(*) team_count fixtures left outer join team b on a.fixture_id = b.fixture_id a.fixture_id = ".intval($fixture_id)." group a.opponents, a.date, a.venue, a.fixture_id") or die($this->db->error()); if ($row = $this->db->fetch_array($sql)) { $retvar = (($row['team_count'] == 0) ? true : false); } else { $retvar = false; } return $retvar; } //function display names in drop down list box populated players table. private function get_name($fieldname, $currentselection = 0) { $sql = "select player_id, name players"; $result = $this->db->query($sql); //while loop echo out player names select box. echo "<select name='$fieldname'>"; while ($row =$this->db->fetch_array($result)) { echo"<option value='" . $row['player_id'] . "' ".(($currentselection == $row['player_id']) ? "selected='selected'" : '' )." >" . $row['name'] . "</option>"; } echo '</select>'; } } ?> <html> <head> </head> <body> <?php $processpage = new produceselectionlist($databaseobject); ?> </body> </html>
Comments
Post a Comment