Page 1 of 1

trying to update database

Posted: Sat Feb 19, 2011 10:21 pm
by BelowZero
can't figure out why the database isn't being updated...

Code: Select all

<?php
include("opendatabase.php");

----drop down list----
$query="SELECT team_name FROM teams";
$result = mysql_query ($query);
echo "<select name=\"team\"></option>";
while($team=mysql_fetch_array($result))
{
echo "<option value=$team[team_name]>$team[team_name]</option>";
}
echo "</select>";
mysql_close($con)
?>
----Form----

Code: Select all

<FORM Method = "POST" action ="insert_teams.php">
Owner: <INPUT TYPE = "text" size="35" name ="Owner">
Paid: <INPUT TYPE = "text" size="10" name = "Paid">
<br /><br />
E-Mail: <INPUT TYPE = "text" size="50" name ="Email">


<input type="submit" name="Submit" value="Submit">
</FORM>
<br />
---insert_teams.php---

Code: Select all

<?php	

header("Location: admin_enterteams.php");

include("opendatabase.php");

$team = $_POST['team'];
$owner = $_POST['Owner'];
$email = $_POST['Email'];
$pd = $_POST['Paid'];

$query="UPDATE teams SET owner='$owner' email='$email' owner_pd='$pd' WHERE team_name = '$team' ";

$result=mysql_query($query);

if(!$result){

 echo " Unable to update your database";

}else{

echo "Your database updated successfuly";

}

mysql_close($con);

?>
Any help appreciated!!

Re: trying to update database

Posted: Sun Feb 20, 2011 1:02 am
by califdon
In your insert_teams.php script, change your statement to:

Code: Select all

$result=mysql_query($query) or die(mysql_error().$query);
This is the first step in debugging a MySQL problem. If there is no error, nothing special happens, but if the mysql_query() raises an error, the error message will be sent to the browser and the script will "die" or terminate, making it much easier to diagnose the problem. When the script is running properly, you should remove the "or die(... )" because if an error occurs in a production script, the error message can give useful clues to a hacker who may be trying to hack into your server.

Re: trying to update database

Posted: Sun Feb 20, 2011 7:13 am
by ganesh_dabhade
try to change

Code: Select all

echo "<option value=$team[team_name]>$team[team_name]</option>";
to

Code: Select all

echo "<option value=$team['team_name']>$team['team_name']</option>";
single quote.

If the problem persists, try to echo the error and paste it here.

Re: trying to update database

Posted: Sun Feb 20, 2011 10:38 am
by BelowZero
This is the error message I get after adding the "or die" statement...

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'email='fredhess@tonalproductions.net' owner_pd='Yes' WHERE team_name = ''' at line 1UPDATE teams SET owner='Fred Hess' email='fredhess@tonalproductions.net' owner_pd='Yes' WHERE team_name = ''


Apparently the program isn't returning a value for $team, which was chosen from the drop down list, even after I added the quotes.

Thanks again for your help.

Re: trying to update database

Posted: Sun Feb 20, 2011 12:55 pm
by califdon
BelowZero wrote:This is the error message I get after adding the "or die" statement...

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'email='fredhess@tonalproductions.net' owner_pd='Yes' WHERE team_name = ''' at line 1
UPDATE teams SET owner='Fred Hess' email='fredhess@tonalproductions.net' owner_pd='Yes' WHERE team_name = ''

Apparently the program isn't returning a value for $team, which was chosen from the drop down list, even after I added the quotes.
The way to interpret that error message is that MySQL understood your SQL up to the point where it begins quoting your SQL. So the problem is just before that. In this case, you are missing a comma between your value assignments. It should be:

Code: Select all

UPDATE teams SET owner='Fred Hess', email='fredhess@tonalproductions.net', owner_pd='Yes' WHERE team_name = '' 

Re: trying to update database

Posted: Sun Feb 20, 2011 2:12 pm
by BelowZero
Thanks califdon.

I figured out that I didn't have the drop down list inside the <form>. Once I changed that, the error message changed. Now it showed "$team", except that "$team" only included the text up to where there was a space (i.e. "Kansas City Chiefs" returned "Kansas") in the WHERE condition.

I inserted the commas as you suggested and voila, I got the message "Your database updated successfully".

Then I browsed the database and found that nothing had been updated!!
Any more suggestions?
Here is the code after changes:

----form----

Code: Select all

<FORM Method = "POST" action ="insert_teams.php">
<?php
include("opendatabase.php");
$query="SELECT team_name FROM teams";
$result = mysql_query ($query);
echo"<select name=\"team\"></option>";
while($team=mysql_fetch_array($result))
{
echo "<option value=$team[team_name]>$team[team_name]</option>";
}
echo "</select>";
mysql_close($con)
?>

Owner: <INPUT TYPE = "text" size="35" name ="Owner">
Paid: <INPUT TYPE = "text" size="10" name = "Paid">
<br /><br />
E-Mail: <INPUT TYPE = "text" size="50" name ="Email">
<br /><br />

<input type="submit" name="Submit" value="Submit">
</FORM>
----insert_teams.php----

Code: Select all

<?php	

//header("Location: admin_enterteams.php");

include("opendatabase.php");

$team = $_POST["team"]; 
$owner = $_POST['Owner'];
$email = $_POST['Email'];
$pd = $_POST['Paid'];

$query="UPDATE teams SET owner='$owner', email='$email', owner_pd='$pd' WHERE team_id = '$team' ";

$result=mysql_query($query)or die(mysql_error().$query);

if(!$result){

 echo " Unable to update your database";

}else{

echo "Your database updated successfully";

}

mysql_close($con);

?>
Thanks again for your help!

Re: trying to update database

Posted: Sun Feb 20, 2011 3:04 pm
by califdon
OK, you've made a lot of progress. As ganesh_dabhade explained, you need single quotes around the array index in the line:

Code: Select all

echo "<option value=$team['team_name']>$team['team_name']</option>";
Either that or (my preference) use mysql_fetch_assoc() instead of mysql_fetch_array(), followed by extract() function, which automatically creates variable names, which I think makes it easier, like this:

Code: Select all

while($team=mysql_fetch_assoc($result))
{
extract($team);
echo "<option value=$team_name>$team_name</option>";
}
You received the "Database updated successfully" message because you programmed it to show that message if no error resulted from your query. But if it didn't find the record to update, that's not an error. What you should do is use the mysql_affected_rows() function and only show that message if it returns the value 1. Reference: http://php.net/manual/en/function.mysql ... d-rows.php

Re: trying to update database

Posted: Sun Feb 20, 2011 11:16 pm
by BelowZero
Okay, thanks.
I'm still not updating the database which is confusing me. The drop down box and form are working correctly.

I'm selecting a team from the drop down menu and that value is stored in team. Is this correct?
echo"<select name=\"team\"></option>";

Then I change that to a variable.
$team = $_POST["team"];

Then I search the database to match $team and team_name from the table.
$query="UPDATE teams SET owner='$owner', email='$email', owner_pd='$pd' WHERE team_name = '$team' ";

As I stated before, I think "$team" is only returning the text until it finds a space (Baltimore Ravons only returns Baltimore), so "team_name" will never match "$team". At least that's what the error message implied. If spaces are messing things up, what should I do? Or might the problem lie somewhere else?

Thanks for all your time and brainpower!
btw, the url is www.beat-the-spread.net/admin_enterteams.php

Re: trying to update database

Posted: Sun Feb 20, 2011 11:48 pm
by califdon
You're thinking is correct, but what you need to do is use various debugging methods to determine what is going on. For example, instead of just guessing what your SQL is asking MySQL to search for, insert a temporary line that echo's the SQL to the screen as soon as it is formed by your code. That will immediately confirm whether or not the problem lies with the truncation of the name variables. Looking at your web page, use your browser's "View Source" option to see exactly what HTML has been generated by your PHP. For your dropdown box, you will find:

Code: Select all

<FORM Method = "POST" action ="insert_teams.php">
<select name="team_1"></option><option value=Arizona Cardinals>Arizona Cardinals</option><option value=Atlanta Falcons>Atlanta Falcons</option> ...
As you can see, you are not placing quotes around the values inside the <option> tags. In other words, your PHP code must explicitly put quotes (single or escaped double, but single is usually simpler, within a double quote delimited string) around the values. That's where your names are getting truncated. As you see from above, without the quotes, the HTML parser sees value= and expects either a quoted string or a number. When it encounters a space, it doesn't know what any following characters mean. The way I would code it is:

Code: Select all

while($team=mysql_fetch_assoc($result))
{
extract($team);
echo "<option value=\'$team['team_name']\'>$team['team_name']</option>";
}
The reason it's unnecessary between the <option> tag and the </option> tag is that it is plain HTML at that point, the parser isn't trying to interpret something within the tag element.

I also noticed an extra "</option>" tag immediately following the <select> tag (see above). Apparently it isn't causing any harm, but it's incorrect and should be removed.

Re: trying to update database

Posted: Mon Feb 21, 2011 5:42 pm
by BelowZero
Wow!!
First of all, I must tell you that I got it working the way I want!
I thank you for all you've helped me learn in this process.

However, I'll let you know that this statement:

echo "<option value=\'$team['team_name']\'>$team['team_name']</option>";

kept returning errors. When I removed the quotes inside the [..], the error message went away but still nothing got updated. Perhaps there's a syntax error in there somewhere.

When you said that value= is either expecting a string or a number, it got me thinking that I could just post the info WHERE the team_id's matched, (using numbers rather than a text string) and it worked perfectly.

Now it's on to other data entry that I want to perform, plus including all the error checking that needs to be entered. It should go faster now that I'm using what I've learned from you. This all started out as just a test to see if I could figure out how to write a website. I'm amazed at all that is involved, all that you need to remember and all the chances for errors when the smallest detail is overlooked. You have my respect and graditude for being willing to peruse this forum and help other beginners. I have a feeling that I'll be posting more in here as the programming continues...

Re: trying to update database

Posted: Mon Feb 21, 2011 7:44 pm
by califdon
The best programming instructor I ever had used to say there are only two basic rules to become a good programmer:
  1. Everything matters; and
  2. Never give up.
Now that I look more closely at your line:
echo "<option value=\'$team['team_name']\'>$team['team_name']</option>";
I see the problem (which I should have seen before): the expression $team['team_name'] is not a simple variable, it is an element of the array $team ('team_name' is the index of the element within the array). Within a double-quoted string, simple variables are interpreted, but not arrays and functions. There are 2 ways to handle this:
1: Break up the string and concatenate the array values:

Code: Select all

echo "<option value='".$team['team_name']."'>".$team['team_name']."</option>";
or 2: (probably easier, as well as clearer), assign the array value to a variable, then use the variable in the string:

Code: Select all

$teamname = $team['team_name'];
echo "<option value='$teamname'>$teamname</option>";
Note that the single-quotes in the value= part are mandatory because the HTML parser is looking for a string value to set within the <option> tag. No quotes are needed between the <option> and </option> tags because that's just plain HTML syntax--you wouldn't put quotes there if you were hard-coding the list.

Sure, come back whenever you run into trouble or have a question. There are lots of us here to help.