[CS-FSLUG] another sql qustions

[Frank Bax]

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