changing records in a database

[Anon]

Hello everyone, thanks for taking the time to read my problem!

I have a form in which I pull up a record from a database. It pulls the fields up into the appropriate place on the form, and I can change the values in the form. Once I press the "Submit" button it prints out the changes I made in the form, but it's not changing the values in the database table. Therefore when I search for that record again, it pulls up the record without the changes. Here's my mysql statement.

mysql($dbName, "UPDATE teachers
SET resourceID='$resourceID',
name='$name',
school='$school',
address='$address',
city='$city',
zip='$zip',
work='$work,
home='$home',
fax='$fax',
email='$email',
URL='$URL',
totalStrand='$totalStrand'
WHERE resourceID='$resourceID'");

	print"Record Changed!";

$result = mysql($dbName, $sql);

The form that I am using to change the data uses the appropriate values "$resourceID", "$name", "$school", "$address" etc.
Example:
print "<tr><td>Name</td><td><INPUT TYPE=\"TEXT\" name=\"name\" value=\"$name\" size=\"25\"></td></tr>";

Any idea what's wrong?

[jacinthe]

well, try this out :

echo "<B>MySQL error message :</B> " . mysql_error($connexion);

if there is an SQL error it'll print out what it is, therefore it'll be easier to correct it.

Also, are you sure that all your field are string type? 'cause you seem to pass all string and if one field is numeric maybe its that...

by the way, what is "mysql" command? shouldn't you use mysql_query like this :

$result = mysql_query ($query)
or die ("Query failed");
if (!$result)
{
echo "<B>MySQL error message : </B> " . mysql_error($connexion);

}
else
{
echo "Data saved";
}

Thats the way i work...

Hope that will help you

Jacinthe

[oeveren]

If you're using special characters, for example quotes, the entry will not be updated.

An easy way to find out is to use a slightly different way of coding:

$queryStr="UPDATE teachers
SET resourceID='$resourceID',
name='$name',
school='$school',
address='$address',
city='$city',
zip='$zip',
work='$work,
home='$home',
fax='$fax',
email='$email',
URL='$URL',
totalStrand='$totalStrand'
WHERE (resourceID='$resourceID')";
//I have put the where clause between brackets
echo $queryStr;
//This one shows you the query

Now, if you find any weird characters in one of the variables, you should "urlencode" this variable. For example:
$address=urlencode($address);

Obviously, if you retrieve this variable from the database, you'll have to decode it...

$address=stripslashes(urldecode($mysqlResult-"address"]));

Good luck,

Dik

[Anon]

Hello there!
I tried:
$result = mysql_query ($query)
or die ("Query failed");
if (!$result)
{
echo "MySQL error message : " . mysql_error($connexion);
}
else
{
echo "Data saved";
}

And it said "Query failed".
As far as:
Also, are you sure that all your field are string type? 'cause you seem to pass all string and if one field is numeric maybe its that...

I'm sorry i'm really new with all this. How do you know if all your fields are string type? What is string type?

[Anon]

Hey,

I tried that and it printed:
UPDATE teachers SET resourceID='215', name='Lena', school='Lethbridge community college', address='some address I don\'t know', city='hello', zip='t1j3m9', work='[phone], home='[phone]', fax='[phone]', email='', URL='http://', totalStrand='0' WHERE (resourceID='215')

Where do I do the "urlencode" thing? before my $queryStr statement?

[jacinthe]

ok just erase the die part and rerun it.
it will give you the mysql error.

like this :

$result = mysql_query ($query)
if (!$result)
{
echo "MySQL error message : " . mysql_error($connexion);
}
else
{
echo "Data saved";
}

Jacinthe

[Anon]

It doesn't like this line:
echo "MySQL error message : " . mysql_error($connexion);

this is what is says:
Warning: Supplied argument is not a valid MySQL-Link resource in /usr/local/etc/httpd/vhosts/ctscouncil.com/content.php on line 1224
MySQL error message :

[oeveren]

Leena,

What I normally do is to urlencode all variables that can contain special characters. You don't have to do this to numeric variables, only variables that contain text. I am aware that this is extra work, but it ensures data integrity.

Before you create the $queryStr variable, you should urlencode the variables, like this:

$address=urlencode($address);
$city=urlencode($city);
$name=urlencode(name);
$school=urlencode($school);
etc...
and then:

$queryStr="UPDATE teachers SET name='$name', school='$school', address='$address', city='$city', zip='$zip', work='$work', home='$home', fax='$fax', email='$email', URL='$url', totalStrand='$totalStrand' WHERE (resourceID='$resourceID')";
mysql_query($queryStr) or die (no way Jose...);

Two notes here, there are some errors in the querystring. First. you don't have to update the resourceID, so I removed that one. Second, there was a closing quote missing in the home variable.

To retrieve data, you have to urldecode the data that you encoded.

$queryStr="select * from teachers where (resourceID='$resourceID')";
$teacherResult=mysql_query($queryStr);
$array=mysql_fetch_array($teacherResult);
$address=stripslashes(urldecode($array["address"]));
$city=stripslashes(urldecode($array["city"]));

etc...

Regs,

Dik

[Anon]

thanks so much! i'll try it

[Anon]

hey there,

It works!!! Wooo Hooooo! thanks so much!
One question though, when i print the information after it's added it prints weird.
eg. I type: hello there
In the school field, and prints: hello+there

[kparker]

You just copied his generic $connexion but in your case it looks like you're using $dbname as the connection variable. (Though that name being "name" is suspicious--you are successfully connecting to mysql, aren't you?)

[oeveren]

That's where you need to urldecode.
Check my previous reply, the last part. If you fetch data from the database urldecode the data...

Dik

[kparker]

That's because it's urlencoded, which is actually overkill here. All the dbms cares about is quotes, so using addslashes() is all you should really need.

[oeveren]

Reason why I use urlencode is that I still had problems using addslashes. When I use URLENCODE for encoding it always worked for me.
The only overkill is when you want to decode again. I need to add stripslashes in order to get the right information back ( stripslaches(urldecode($variable)) ).
Another reason to use urlencode is that sometimes when passing information from one page to another, using special characters, the called script cuts the information when it reads the first space. Don't ask me why this happens, it just happens. I really love urlencode. It's a bit extra coding, but it has always worked for me.

thx,

Dik