Inserting New sql Records Thru PHP form & drop down menus from db column data

mtnmchgrl · May 4, 2010

Hello again,

I just realized this didn't make sense. On this form, I am adding statistics for each game and player.

The name of the tournament and the player is already in the db (b/c you can't add stats on games that don't exist).

So all the person sees is 2 drop downs and then the stat input boxes.

The first drop down should be the name of the tournament.

The second drop down is the players first and last names (which is a concatenation of the playerFN, playerLN columns).

While it looks like alot I am certain the input fields are set up correctly considering they are the same from the other form. I am unsure about the <option select> drop down fields and how i have that set up.

I did have the drop downs working before but the adding of new records would not work. That was before i reworked the data from that previous thread.

This is the last one I am working on so if anyone could help again I would be VERY appreciative.......

The drop downs are toward the bottom of the page w/ all the input field code.

I tried to run this and it gave me this error:

Parse error: syntax error, unexpected T_STRING, expecting ']' in L:\addStats.php on line 183

Line 183 is this:

<?php include_once('includes/admin_nav.inc.php'); ?>

So that makes no sense....

<?php
// START SESSION
require_once('includes/session.inc.php');

// CONNECT TO DATABASE
require_once('includes/connect.inc.php');

// PROCESS FORM ON SUBMIT
$response = '';
if (isset($_POST['submit']))
{
    //Create array var to capture errors
    $errors = array();

    //VALIDATE THE SUBMITTED DATA
    $tournID = trim($_POST['tourneyID']);
    $playID = trim($_POST['playerID']);
    $AB = trim($_POST['atBats']);
    $H = trim($_POST['hits']);
    $W = trim($_POST['walks']);
    $RBI = trim($_POST['RBI']);
    $SO = trim($_POST['strkOuts']);
    $S = trim($_POST['singles']);
    $D = trim($_POST['doubles']);
    $T = trim($_POST['triples']);
    $hR = trim($_POST['homeRuns']);                             
    $rS = trim($_POST['runsScrd']);
    $fCh = trim($_POST['fieldersCh']);
     
    
    //IF STATEMENTS TO CHECK FOR EMPTY FIELDS

        if(empty($tournID))
    {
        $errors[] = "Tournament ID is required.";
    }
        if(empty($playID))
    {
        $errors[] = "Player ID is required.";
    }
        if(empty($AB))
    {
        $errors[] = "At Bats are required.";
    }
        if(empty($H))
    {
        $errors[] = "Hits are required.";
    }
        if(empty($W))
    {
        $errors[] = "Walks are required.";
    }
        if(empty($RBI))
    {
        $errors[] = "RBI is required.";
    }
        if(empty($SO))
    {
        $errors[] = "Strike Outs are required.";
    }
        if(empty($S))
    {
        $errors[] = "Singles are required.";
    }
        if(empty($D))
    {
        $errors[] = "Doubles are required.";
    }
        if(empty($T))
    {
        $errors[] = "Triples are required.";
    }
        if(empty($hR))
    {
        $errors[] = "Home Runs are required.";
    }
        if(empty($rS))
    {
        $errors[] = "Runs Scored are required.";
    }
        if(empty($fCh))
    {
        $errors[] = "Fielders Choice is required.";
    }
    
    //IF THE THE NUMBER OF ERRORS IS GREATER THAN ZERO, THEN THE MESSAGES WILL DISPLAY IN THIS FASHION...
    if(count($errors)>0)
    {
        //There were validation errors, prepare error message
        $response = "<span style=\"color:red;\">";
        $response .= "The following errors occured:\n";
        $response .= "<ul>";
        $response .= '<li>' . implode('</li><li>', $errors) . '</li>';
        $response .= "</ul>\n";
        $response .= "</span>\n";
    }
    else
    {
     //IF NO VALIDATION ERRORS, SAVE INPUT DATA TO DATABASE
        $tournID = mysql_real_escape_string($tournID);
        $playID = mysql_real_escape_string($playID);
        $AB = mysql_real_escape_string($AB);
        $H = mysql_real_escape_string($H);
        $W = mysql_real_escape_string($W);
        $RBI = mysql_real_escape_string($RBI);
        $SO = mysql_real_escape_string($SO);
        $S = mysql_real_escape_string($S);
        $D = mysql_real_escape_string($D);
        $T = mysql_real_escape_string($T);
        $hR = mysql_real_escape_string($hR);
        $rS = mysql_real_escape_string($rS);
        $fCh = mysql_real_escape_string($fCh);


              
        // QUERY TO RETRIEVE ENTRY RESULTS
        $query="INSERT INTO `stats` (`tourneyID`, `playerID`, `atbats`, `hits`, `walks`, `RBI`, `strkOuts`, `singles`, `doubles`, `triples`, `homeRuns`, `runsScrd`, `fieldersCh`)
                 VALUES ('{$tournID}', '{$playID}', '{$AB}', '{$H}', '{$W}', '{$RBI}', '{$SO}', '{$S}', '{$T}', '{$hR}', '{$rS}', '{$fCh}')";
        $result = mysql_query($query);


        if (!$result)
        {
            $response = "No record was added. Please try again.";
            $response .= "<br />\n$query<br />".mysql_error();
        }
        else
        {
            $response = "The record has been added.";
        }
    }
}

//GET CURRENT RECORDS TO DISPLAY - THIS WILL SHOW ME THE LAST ROW I ENTERED
$query = "SELECT playerID, playerFN, playerLN, tourneyID, tourneyName, atbats, hits, walks, RBI, strkOuts, singles, doubles, triples, homeRuns, runsScrd, fieldersCh FROM `player`, `tournaments`, `stats` WHERE player.playerID=stats.FK_playerID AND tournaments.tourneyID=stats.FK_tourneyID ORDER BY playerID DESC LIMIT 1";
$result = mysql_query($query);

//VALIDATE THERE WERE RECORDS
if(mysql_num_rows($result) == 0)
{
    $records = "Sorry, no records were found!";
}
else
{
    // START OF TABLE, HEADERS, ETC
    $records  = "<table border=\"1\">\n";
    $records .= "<tr><th>TourneyID</th><th>PlayerID</th><th>AB</th><th>H</th><th>W</th><th>RBI</th><th>SO</th><th>S</th><th>D</th><th>T</th><th>HR</th><th>RS</th><th>FC</th></tr>\n";
    //LOOP THE DATABASE TO DISPLAY THE RECORDS
    while ($myrow = mysql_fetch_array($result))
    {
        //PRINT RESULTS
        $records .= "<tr>";
        $records .= "<td>{$myrow['tourneyID']}</td>";
        $records .= "<td>{$myrow['playerID']}</td>";
        $records .= "<td>{$myrow['atBats']}</td>";
        $records .= "<td>{$myrow['hits']}</td>";
        $records .= "<td>{$myrow['walks']}</td>";
        $records .= "<td>{$myrow['RBI']}</td>";
        $records .= "<td>{$myrow['strkOuts']}</td>";
        $records .= "<td>{$myrow['singles']}</td>";
        $records .= "<td>{$myrow['doubles']}</td>";
        $records .= "<td>{$myrow['triples']}</td>";
        $records .= "<td>{$myrow['homeRuns']}</td>";
        $records .= "<td>{$myrow['runsScrd']}</td>";
        $records .= "<td>{$myrow['fieldersCh]}</td>";
        $records .= "</tr>\n";
    } 
    $records .= "</table>\n";
}

?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html lang="en">
<head>
<title>Admin MySQL Database</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<link href="css/admin.css" rel="stylesheet" type="text/css">
</head>

<body>
<div id="navigation">
<?php include_once('includes/admin_nav.inc.php'); ?>
</div> <!--END NAVIGATION DIV-->

<div id="content">
<h1>Add Statistics</h1>
<br />
<div><?php echo $response; ?></div>

<!--HTML FORM FOR ENTRY OF THE STATS!-->
<form method="post" action=""><!--addStats.php-->

        <p>Select Tournament: <select name="tourneyID">

		<?php
		// CONNECT TO DATABASE
		require_once('includes/connect.inc.php');

		//THIS QUERY GETS tourneyID, tourneyName and gameDate from tournaments table.
		$result = mysql_query("SELECT * FROM tournaments");

		while ($myrow = mysql_fetch_array($result)) {
		//THIS IS MY DROP DOWN WHERE THE PERSON SHOULD SELECT THE NAME OF THE EXISTING TOURNAMENT.	
		echo'<option value="'.$myrow['tourneyID'].'">'.$myrow['tourneyName'].'</option>';	
		}
		?>
		</select>
		</p>


	<p>Select Player: <select name="playerID">
		<?php
		//THIS QUERY GETS FIRST THREE FIELDS OF PLAYER TABLE
		$result = mysql_query("SELECT playerID, playerFN, playerLN FROM player");

		while ($myrow = mysql_fetch_array($result)) {
		//THIS IS MY DROP DOWN SHOWING THE CONCATENATED PLAYERS FIRST AND LAST NAME	
		echo'<option value="'.$myrow['playerID'].'">'.$myrow['playerFN'].' '.$myrow['playerLN'].'</option>';
		}
		?>
		</select></p>
        
           <!--HERE IS WHERE THE INPUT FIELDS FOR EACH STATISTIC WILL GO - DATA THAT IS TO BE ENTERED AND IS NOT ALREADY IN db-->   
        
            <table border=1 RULES=NONE FRAME=BOX>
        <tr>
		<td>At Bats:</td>
		<td><input type="text" name="atBats" size="20" maxlength="20"></td>
	</tr>
	<tr>
		<td>Hits:</td>
		<td><input type="text" name="hits" size="20" maxlength="20"></td>
	</tr>
	<tr>
		<td>Walks:</td>
		<td><input type="text" name="walks" size="20" maxlength="20"></td>
	</tr>
	<tr>
		<td>RBI:</td>
		<td><input type="text" name="RBI" size="20" maxlength="20"></td>
	</tr>
	<tr>
		<td>Strike Outs:</td>
		<td><input type="text" name="strkOuts" size="20" maxlength="20"></td>
	</tr>
	<tr>
		<td>Singles:</td>
		<td><input type="text" name="singles" size="20" maxlength="20"></td>
	</tr>
	<tr>
		<td>Doubles:</td>
		<td><input type="text" name="doubles" size="20" maxlength="20"></td>
		</tr>
	<tr>
		<td>Triples:</td>
		<td><input type="text" name="triples" size="20" maxlength="20"></td>
		</tr> 
	<tr>
		<td>Home Runs:</td>
		<td><input type="text" name="homeRuns" size="20" maxlength="20"></td>
		</tr> 
	<tr>
		<td>Runs Scored:</td>
		<td><input type="text" name="runsScrd" size="20" maxlength="20"></td>
		</tr> 
	<tr>
		<td>Fielders Choice:</td>
		<td><input type="text" name="fieldersCh" size="20" maxlength="20">
	</td>
	</tr>     
        
    </table>
    <br />
    <input type="submit" value="Submit" name="submit">
    <input type="reset" value="Reset Values">
</form>

<br />

<?php echo $records; ?>

</div> <!--END CONTENT DIV-->
</body>
</html>

[attachment deleted by admin]

Pikachu2000 · May 4, 2010

You're missing a closing single-quote in this line: $records .= "<td>{$myrow['fieldersCh]}</td>";

EDIT: If you don't have an editor with syntax highlighting, you should get one. It would make it much harder to miss this type of error . . .

mtnmchgrl · May 4, 2010

You're missing a closing single-quote in this line: $records .= "<td>{$myrow['fieldersCh]}</td>";

EDIT: If you don't have an editor with syntax highlighting, you should get one. It would make it much harder to miss this type of error . . .

Ok, caught that and a few other little things. Form is showing up w/out the parse/syntax error page but its still not submitting properly. Nothing gets submitted and I get these errors:

The following errors occured:

    * Home Runs are required.
    * Runs Scored are required.
    * Fielders Choice is required.

Think there is something wonky w/ my query too b/c its not filling up right at the bottom. It should show the last entered record.....

Getting closer.........

What syntax editor do you use? I use Crimson Editor and Dreamweaver. DW sucks for this kind of stuff. What do you recommend?

Re attaching new .php

[attachment deleted by admin]

Pikachu2000 · May 4, 2010

Well, I use Zend (and sometimes vim), but there are some free ones also. I'm not familiar with Crimson. I use Mac and Linux, but if you're on WinD'ohs, I hear that Notepad++ is a decent one, otherwise people also say NetBeans is a good IDE that supports PHP.

If the form values are expected to be of the integer type, there is really no need to pass them to mysql_real_escape string(). You should validate and typecast them as integers $var_name = (int) $_POST['var_name']. Also, if they are indeed integers, they shouldn't be quoted in the DB INSERT query. Only string type data should be quoted. Additionally, string type data would also be passed to mysql_real_escape_string() before being used in a DB query.

mtnmchgrl · May 4, 2010

Well, I use Zend (and sometimes vim), but there are some free ones also. I'm not familiar with Crimson. I use Mac and Linux, but if you're on WinD'ohs, I hear that Notepad++ is a decent one, otherwise people also say NetBeans is a good IDE that supports PHP.

If the form values are expected to be of the integer type, there is really no need to pass them to mysql_real_escape string(). You should validate and typecast them as integers $var_name = (int) $_POST['var_name']. Also, if they are indeed integers, they shouldn't be quoted in the DB INSERT query. Only string type data should be quoted. Additionally, string type data would also be passed to mysql_real_escape_string() before being used in a DB query.

1. All the entries are numbers (whole) with the exception of the playerFN/LN which is linked to playerID (which is a #) and the tournament name which is linked by tourneyID. not sure if those two matter as they are existing fields in db?

2. Not sure if I understood your thing about the quotes, but I took off the quotes on the column names (the first set of parenthesis in the INSERT statement (before values).

3. There aren't any strings being entered so I don't think there is a need for the mysql_real_escape_string() at all

4. I changed my query to just show all 10 players and ALL their stats (100 records total). For some reason my atBats column isn't showing up although i don't see anything weird.....

5. No longer getting parser/syntax error but we are back to this error:

The following errors occured:

    * Doubles are required.
    * Triples are required.
    * Home Runs are required.
    * Runs Scored are required.
    * Fielders Choice is required.

So close....anyone else??? Modifed attached.

[attachment deleted by admin]

Pikachu2000 · May 4, 2010

I'd start by echoing out the $_POST array to make sure it has values, and there are no misspellings. Once all your form fields validate properly, then move on to the DB insert problems.

if( isset($_POST['submit']) ) {  // < --- EXISTING CODE
// ADD THE FOLLOWING LINES 
     foreach( $_POST as $key => $val ) {
          echo "Key: " . $key . " Value: " . $val . "<br />";
     }
     echo "<br />";
// END OF DEBUGGING CODE BLOCK
     $errors = array(); // <--- CONTINUE WITH EXISTING CODE

mtnmchgrl · May 6, 2010

I'd start by echoing out the $_POST array to make sure it has values, and there are no misspellings. Once all your form fields validate properly, then move on to the DB insert problems.
if( isset($_POST['submit']) ) {  // < --- EXISTING CODE
// ADD THE FOLLOWING LINES 
     foreach( $_POST as $key => $val ) {
          echo "Key: " . $key . " Value: " . $val . "<br />";
     }
     echo "<br />";
// END OF DEBUGGING CODE BLOCK
     $errors = array(); // <--- CONTINUE WITH EXISTING CODE

I tested that. No problems with the inputted data, so it has to be my query.....am I referencing the foreign keys right? Remember, the 2 foreign keys referenced are linked to 2 different tables.......to me it would make sense to right sql query like:

mysql> insert into stats (FK_tourneyID, FK_playerID, atBats, hits, walks, RBI, strkOuts, singles, do

ubles, triples, homeRuns, runsScrd, fieldersCh) VALUES (1, 13, 10, 3, 1, 0, 0, 1, 1, 1, 0, 0, 0) w

here FK_playerID=13 and FK_tourneyID=1;

But this does not seem to work. I also took off the FK_ and it didn't work either.

Any help would be appreciated....

[attachment deleted by admin]

siric · May 6, 2010

Is this a local database? If so, I would run something a client like SQLyog or Navicat Lite and run the query in there. This will allow you to identify where you error is occurring and to play around with it until it is right.

Even if it is a hosted database, use PhpMyAdmin and do the same thing.

mtnmchgrl · May 6, 2010

Is this a local database? If so, I would run something a client like SQLyog or Navicat Lite and run the query in there. This will allow you to identify where you error is occurring and to play around with it until it is right.

Even if it is a hosted database, use PhpMyAdmin and do the same thing.

Yes, I use xampp/apache to run this site...just local host. Can I use either of those programs with that?? I have access to PHPMyAdmin but I hate using it...I ended up doing all my tables from scratch in command line. Sigh.....

siric · May 6, 2010

Send me a copy of the database structure and I will look into it.

mtnmchgrl · May 6, 2010

Send me a copy of the database structure and I will look into it.

I just sent it in a message to you. Thank you!

siric · May 7, 2010

Send me a copy of the database structure and I will look into it.

I just sent it in a message to you. Thank you!

The problem jumped out at me once I tried to run it. You cannot do an INSERT...WHERE. That would imply that you already have a row that meets the WHERE criteria in which case you do an UPDATE.

It would be

UPDATE stats set atBats='$abats', hits = '$hits', walks='$walks', RBI= '$RBI', strkOuts='$strkOuts', singles='$singles', doubles='$doubles', triples='$triples', homeRuns='$homeruns', runsScrd='$runsScrd', fieldersCh='$fieldersCh'  where FK_playerID='$FK_playerID' and FK_tourneyID='$FK_tourneyID';

I have just assumed that those are the variable names and if not, you get the drfit.

mtnmchgrl · May 7, 2010

Send me a copy of the database structure and I will look into it.

I just sent it in a message to you. Thank you!

The problem jumped out at me once I tried to run it. You cannot do an INSERT...WHERE. That would imply that you already have a row that meets the WHERE criteria in which case you do an UPDATE.

It would be
UPDATE stats set atBats='$abats', hits = '$hits', walks='$walks', RBI= '$RBI', strkOuts='$strkOuts', singles='$singles', doubles='$doubles', triples='$triples', homeRuns='$homeruns', runsScrd='$runsScrd', fieldersCh='$fieldersCh'  where FK_playerID='$FK_playerID' and FK_tourneyID='$FK_tourneyID';
I have just assumed that those are the variable names and if not, you get the drfit.

That makes sense b/c the first 2 fields already exist!!! However, I just did this and I'm still getting an error. Plus the query to actually insert the records doesn't contain a WHERE. The select statement below that (that shows all the stats) is the one that has the where and it is working just fine. Its the first query that is not working.....the one to add stuff to the db. Man i thought this was really going to fix it too. Grrrr.... :confused:

mtnmchgrl · May 7, 2010

Send me a copy of the database structure and I will look into it.

I just sent it in a message to you. Thank you!

Anyone else??

The problem jumped out at me once I tried to run it. You cannot do an INSERT...WHERE. That would imply that you already have a row that meets the WHERE criteria in which case you do an UPDATE.

It would be
UPDATE stats set atBats='$abats', hits = '$hits', walks='$walks', RBI= '$RBI', strkOuts='$strkOuts', singles='$singles', doubles='$doubles', triples='$triples', homeRuns='$homeruns', runsScrd='$runsScrd', fieldersCh='$fieldersCh'  where FK_playerID='$FK_playerID' and FK_tourneyID='$FK_tourneyID';
I have just assumed that those are the variable names and if not, you get the drfit.

[attachment deleted by admin]

siric · May 7, 2010

Ok,

Ran the code and found a few errors and once corrected, the code worked.

The change from string to integer should be run against the variables that are set at the top of the script and not the $_POST variables

Change

$H = (int)$_POST('hits');
$W = (int)$_POST('walks');
.....

to

$H = (int)$H;
$W = (int)$W;
.....

and you do not need to assign the $tournID and $playID variables again in this section as you have already done it in the first section.

Sign In

Inserting New sql Records Thru PHP form & drop down menus from db column data

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

Join the conversation

Important Information