[CS-FSLUG] another sql qustions
Frank Bax
fbax at sympatico.ca
Fri Dec 26 17:13:54 CST 2008
David McGlone wrote:
> I can't seem to figure out how to write an UPDATE query for this form. I've
> tried several different ways but nothing gets updated in the db. Here is the
> code I have
>
> $query = "SELECT * FROM subheaderText WHERE subheaderTextID=1";
> $result = @mysql_query($query);
>
> echo "<form name='subheader' action='$_SERVER[PHP_SELF]' method='POST'>";
>
> while ($row = mysql_fetch_assoc($result)){
> echo "<OPTION VALUE='subheaderText'>$row[subheaderText]<br>";
> echo "<input type=text name='subheaderText' size='30'
> value='$row[subheaderText]'>";
> echo "<input type=text name='pageID' size='10' value='$row[pageID]'>";
> echo "<textarea name='headerText' cols='50' rows='15'
> />$row[headerText]</textarea>";
> }
> $UpdateQuery = "UPDATE subheaderText SET 'subheaderText=subheaderText',
> 'pageID=pageID', 'headerText]=headerText'";
>
> $result2= mysql_query($updateQuery);
>
> echo '<input type="submit" name="submit" value="Update" /><br>';
> echo '</form><br>';
>
> What in Tarnation have I done wrong?
> I'm slowly learning mySQL, but it sure is mentally tiring!
The problem is neither MySQL nor PHP; I think the problem is that you
must CREATE a PHP string containing an SQL statement using PHP
variables. This is always messy and a tricky thing to learn.
A couple of observations.
1) You can get away with action='' in the form tag; the default is to
send the POST data back to the calling script ($_SERVER['PHP_SELF']).
2) In your code; you refer to $row[pageID] which should technically be
$row['pageID']. I think newer php versions issue warnings about this.
I always log all warnings and cleanup code to avoid them.
3) Your mysql update should come before the while loop used to present
data from the database. The update should be wrapped in code that
checks to see if the user clicked submit button called "Update".
Something like:
if( isset($_POST['Update']) ) {
$UpdateQuery = ...
}
Then execute your query to retrieve data from database and present it to
browser; in this way, the user is always presented with current contents
of database; ready for update/delete/whatever.
4) Your code makes no reference to the special php variable $_POST which
is an array containing all data submitted by user when they clicked
"Update". Try adding this code early in your script:
echo '<pre>'; var_dump($_POST); echo '</pre>';
The code will dump the contents of $_POST array to the browser. When
you first load the page; $_POST will be null; but when a form button is
clicked; then it will have values.
5) OK, the hard part. How to contruct an sql statement using php
variables. As I said; this is not an easy task because you are actually
mixing two languages - php and sql. Also, the code gets messy with all
the single and double quotes. You wrote
$UpdateQuery = "UPDATE subheaderText SET 'subheaderText=subheaderText',
'pageID=pageID', 'headerText]=headerText'";
a) Your sql statement has no WHERE clause so every row in table would be
updated when it works (this will not be a good thing).
b) I find it confusing to have multiple database objects with the same
name; it looks like you have a table and a field called subheaderText.
You also changed both table and field names from your first post.
c) You coded 'field=value' when it should be field='value' (note the
quotes). The 'value' should be php values from $_POST array.
$UpdateQuery = "UPDATE subheaderText SET
subheaderText='".$_POST['subheaderText']."',"
headerText='".$_POST['headerText']."'"
WHERE pageID='".$_POST['pageID']."'";
I sure hope I got all these quotes right! You might consider adding
echo $UpdateQuery;
to your script to see if the string you've created looks like valid sql
before calling mysql_query().
You should try to find log files for both mysql and apache. I often
have terminal windows open that run 'tail -f' on these files so that I
can see error messages as they are logged. These often provide very
useful clues when you make a typo.
Frank
More information about the Christiansource
mailing list