MySQL UPDATE query problems [SOLVED]

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
tua03332
Forum Newbie
Posts: 2
Joined: Tue Nov 17, 2009 3:24 pm

MySQL UPDATE query problems [SOLVED]

Post by tua03332 »

Hey guys,

After several days of googling and asking on irc chats I have come to this forum on a suggestion by a friend. I have two different errors occurring based on two slightly different scenarios.

Background: I have two webpages one for viewing a table (TableMain.php) and the other containing a form to update the table (Update.php). Both pages use $table to store the name of the table selected by the user in a previous drop-down-list so that I have only one view and one update webpage for over some 20 different tables. Also, since the table changes dynamically based on the users selection, I also dynamically create the column names and column values per the table selected. I believe the problem is with the update query I am submitting to the server.

Scenario #1: $table is set to "Accession" by default in case $table never gets a value from $_GET['table']. When I submit the form on Update.php, mysql_query will return true and then redirect to TableMain.php?table=$table. However, in this table I have two columns. Accession_PK and Batch_Accession. Accession_PK is the primary key and auto-incremented. Batch_Accession is a string of numbers formatted as such YY-### where ### is the index of the item in the current year. When I submit 00-004 the query submitted to the server will say 00-004, but when TableMain.php loads it shows -4. Now if I change the value to 12345 and submit it comes out fine on the other end. I checked the datatypes in the DB and everything is set to text.

Scenario #2: After the mysql_query($query, $conn) I have included or die(mysql_error() . " Query Statement " . $query); as to output the submitted query to double check i got everything correct. When I select a different table other than the default I get the following error message.

Unknown column 'Architecture' in 'field list' Query Statement UPDATE Category SET Category_ID = 2, Category = Architecture WHERE Category_ID = 2

I have seen other posts with a similar error in which it states unknown column and the person has the correct column name, however none of their tips or solutions helped me out. I believe both of these issues are related to the same mistake so if anyone can shed some light on where I might be messing things up I would greatly appreciate it. Also, if you need more code or explanation just ask and I will gladly add it.

Update.php code to build the query:

Code: Select all

//Sets a default value for $table
$table = "Accession";
 
//Tests if $test was passed through the URL and then updates the current $table value
if (isset($_GET['table']))
{
    $table = $_GET['table'];
}
 
//Grabs the column names from the current table and creates the $column array
$columnquery = mysql_query("SHOW COLUMNS FROM $table",$conn) or die(mysql_error());
$column = array();
 
//Loops through the columns array and stores the values in the $column array
while ($row = mysql_fetch_array($columnquery))
    {
        $column[] = $row[0];
    }
 
//Tests to see if the update form was submitted
if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1"))
    {
        //Begins the sql query string
        $query = "UPDATE $table SET ";  
        
        //Loops through each column name apending "Column Name = Column Value, " to $query
        foreach($column as $value)
            {
                $query .= $value . " = " . $_POST[$value] . ", ";
            }
        
        //Removes the last ", " from $query
        $query = substr($query, 0, -2);
        
        //Appends " WHERE Primary Column Name = Primary Column Value"
        //Primary Column Name and Primary Column Value are the primary key value of the table submitted by the form
        $query .= " WHERE " . $column[0] . " = " . $_POST[$column[0]];
        
        //Executes the query or outputs the mysql error with the final $query
        $Result1 = mysql_query($query, $conn) or die(mysql_error() . " Query Statement " . $query);
Update.php Form code

Code: Select all

<div id="right">
      <table width="800" border="0" align="center">
        <tr valign="middle">
          <th width="150" align="left" nowrap="nowrap">
          <?php echo "<a href='TableMain.php?table=" . $table . "'>Return to " . $table . " Home"; ?></th></a>
          <th width="500" align="center" nowrap="nowrap"><h1><?php echo $table . " Home"; ?></h1></th>
          <th width="150" align="right" nowrap="nowrap">
          <?php echo "<a href='Delete.php?table=" . $table . "'>Delete a Record"; ?></a></th>
        </tr>
        <tr>
          <td colspan="3" align="right" valign="baseline" nowrap="nowrap"></td>
        </tr>
      </table>
      <form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">
        <table align="center">
<?php
foreach ($column as $value)
    {
        echo "
          <tr valign='baseline'>
            <td nowrap='nowrap' align='right'>" . $value . ":</td>
            <td align='left' valign='middle'>
            <input type='text' name=" . $value . " value='" . $row_Recordset1[$value] . "' size='30' /></td>
          </tr>";
    }
?>
          <tr valign="baseline">
            <td nowrap="nowrap" align="right">&nbsp;</td>
            <td align="left" nowrap="nowrap"><input type="submit" value="Update record" /></td>
          </tr>
        </table>
        <input type="hidden" name="MM_update" value="form1" />
      </form>
      <p>&nbsp;</p>
      <p>&nbsp;</p>
    </div>
Last edited by tua03332 on Tue Nov 17, 2009 4:41 pm, edited 1 time in total.
User avatar
iankent
Forum Contributor
Posts: 333
Joined: Mon Nov 16, 2009 4:23 pm
Location: Wales, United Kingdom

Re: MySQL UPDATE query problems

Post by iankent »

Simple answer to both I think.

The first problem is likely that you're actually submitting 00-004 to the database, i.e. 0-4, or -4. If you want to store it as '00-004' you need to store it as text and that means enclosing it in apostrophe's when using the UPDATE query
e.g.
UPDATE table SET col=00-004;
sets col to -4 (even if the column type is text!)
while
UPDATE table SET col='00-004';
sets col to '00-004' (and will generate an error if the column type is numerical!)

in your second query its trying to assign a value from the column Architecture to the column Category. I.e., in this query:
UPDATE Category SET Category_ID = 2, Category = Architecture WHERE Category_ID = 2
its looking for a column Architecture in table Category. Again, Architecture would need to be enclosed in quotes so its treated as a string not a column name, i.e.
UPDATE Category SET Category_ID = 2, Category = 'Architecture' WHERE Category_ID = 2

(also, no need to set category_id to 2 when you've got WHERE Category_ID = 2 at the end - if its matching against 2 then its already 2 so no need to update that column!)

put simply, if your column is numerical then don't use any quotes, if its textual/binary/date etc. then you must use quotes (and also escape the values using mysql_real_escape_string() or similar)

hth

edited to correct some mistakes, and also welcome to the forum :)
tua03332
Forum Newbie
Posts: 2
Joined: Tue Nov 17, 2009 3:24 pm

Re: MySQL UPDATE query problems

Post by tua03332 »

Wow do I feel dumb now. Several days tweaking it to forget the stinking quotes. Well I now have a new favorite forum as all my other resources have missed something so simple. Almost forgot, thank you very much!! :D
User avatar
iankent
Forum Contributor
Posts: 333
Joined: Mon Nov 16, 2009 4:23 pm
Location: Wales, United Kingdom

Re: MySQL UPDATE query problems

Post by iankent »

tua03332 wrote:Wow do I feel dumb now. Several days tweaking it to forget the stinking quotes. Well I now have a new favorite forum as all my other resources have missed something so simple. Almost forgot, thank you very much!! :D
you're welcome :) and no need to feel dumb, we all make mistakes and its always the obvious ones that are hardest to see... I dread to think how many hours I've spent looking for elusive punctuation in php scripts and mysql queries :P
Post Reply