Page 1 of 1

MYSQL + UPDATE RECORDS

Posted: Tue Jul 07, 2009 1:25 am
by jamkelvl
Okay so I'm writing a little script to update rows of a database.

I am able to update the records but ONLY with numbers. It is not allowing me to update with alpha chars, and this has nothing to do with MYSQL as far as I know. All of my fields are set properly ie: category is VARCHAR.

Code: Select all

 
<?php
    // Suppress all errors
    error_reporting(0);
 
    // Set all variables    
    $update = $_POST['update']; 
    $hostname = "mysqlhost";
    $username = "xxxxxxxxxx";
    $password = "xxxxxxxxxx";
    $dbid="xxxxxxxxx";
 
    // Attempt database connection
    $connect = mysql_connect($hostname, $username, $password);
    if($connect == false){
        echo('<p class="error">We are having technical difficulties and apologize for the inconvenience. Please try again later.</p>');
    }
    
    // Select database
    $db = mysql_select_db($dbid);
 
    // Query the database
    $select = "SELECT * FROM parts where id = ".$id."";
    $result = mysql_query($select);
 
    while($row = mysql_fetch_array($result)){
        extract($row);
        // do a whole bunch of nothing!
    }   
?>
<form action="edit.php" method="post">  
    <input type="hidden" name="update" value="true" />
    
    <label for="category">ID:</label>
    <input type="text" name="id" id="id" value="<?php echo $id; ?>" readonly="readonly" />
    
    <label for="category">Category:</label>
    <input type="text" name="category" id="category" value="<?php echo $category; ?>" />
        
    <label for="price">Price:</label>
    <input type="text" name="price" id="price" value="<?php echo $price; ?>"/>
        
    <label for="datein">Date In:</label>
    <input type="text" name="datein" id="datein" value="<?php echo $datein; ?>" />  
    
    <label for="quantity">Quantity:</label>
    <input type="text" name="quantity" id="quantity" value="<?php echo $quantity; ?>" />    
    
    <label for="image">Image:</label>
    <input type="text" name="image" id="image" value="<?php echo $image; ?>"/>
    
    <label for="description">Description:</label>
    <textarea rows="5" name="description" cols="50" id="description"><?php echo $description; ?></textarea>
    
    <br />
        
    <button type="submit">Update</button>
 
</form>
<?php
    // If user is trying to edit part...
    if ($update == true) {
    
        // wtf?
        $category = $_POST['category'];
        $price = $_POST['price'];
        $datein = $_POST['datein'];
        $quantity = $_POST['quantity'];
        $image = $_POST['image'];
        $description = $_POST['description'];
        
        // Build query
        $insert = "UPDATE parts SET category = ".$_POST['category']." WHERE id = ".$id."";
        $result = mysql_query($insert);
        
        if ($result == true) {
            echo '<p class="success">Update complete.</p>';     
        } else {
            echo '<p class="error">There was a problem editing this part.</p>';
        }
    }
?>
 </body>
</html>
 

Re: MYSQL + UPDATE RECORDS

Posted: Tue Jul 07, 2009 2:01 am
by Christopher
How do you know it is "It is not allowing me to update with alpha chars" ? Does the UPDATE query return an error?

PS - you should escape all values used in SQL.

Re: MYSQL + UPDATE RECORDS

Posted: Tue Jul 07, 2009 2:27 am
by genconv
Yes, You should escape strings in your SQL and use the addslashes() or mysql_real_escape_string() function (if Magic Quotes GPC are off)
$insert = 'UPDATE parts SET category = "'. addslashes($_POST['category']) .'" WHERE id = '.$id;

Re: MYSQL + UPDATE RECORDS

Posted: Tue Jul 07, 2009 2:13 pm
by jamkelvl

Code: Select all

       if ($result == true) {
            echo '<p class="success">Update complete.</p>';    
        } else {
            echo '<p class="error">There was a problem editing this part.</p>';
        }
When I enter in alpha chars I get the error message.

I will try what has been suggested though.

***EDIT***

Code: Select all

        if (!get_magic_quotes_gpc()){
            $category = addslashes($category);
            $price = addslashes($price);
            $datein = addslashes($datein);
            $quantity = addslashes($quantity);
            $image = addslashes($image);
            $description = addslashes($description);
          } 
Did not work.

Re: MYSQL + UPDATE RECORDS

Posted: Tue Jul 07, 2009 2:56 pm
by Skara

Code: Select all

mysql_query($insert) or die(mysql_error());
What is the output of that?

My guess is that you have the type of category as a numeric type.

Re: MYSQL + UPDATE RECORDS

Posted: Tue Jul 07, 2009 3:30 pm
by jamkelvl
No category is VARCHAR.

Re: MYSQL + UPDATE RECORDS

Posted: Tue Jul 07, 2009 4:02 pm
by Skara
Sorry, I guess I missed that. What is the output of mysql_error()?
ah... wait a minute... you're not quoting your string. I missed it because of the way you have it formatted, but that's the problem.

Code: Select all

$insert = "UPDATE parts SET category = ".$_POST['category']." WHERE id = ".$id."";
//this last "" is meaningless as well.  It could be ended by :: id = ".$id;
//although you should still end it with a semicolon
//It should be:
$insert = "UPDATE parts SET category = '{$_POST['category']}' WHERE id = '{$id}';";
//(or, if you prefer:)
$insert = "UPDATE parts SET category = '".$_POST['category']."' WHERE id = '".$id."';";
//assuming you're only updaing a single entry (that `id` is unique), you should end your query with LIMIT 1:
$insert = "UPDATE parts SET category = '{$_POST['category']}' WHERE id = '{$id}' LIMIT 1;";
//Though many people don't use this notation, I personally always quote (backtick/grave) my tables and columns as well:
$insert = "UPDATE `parts` SET `category` = '{$_POST['category']}' WHERE `id` = '{$id}' LIMIT 1;";
Always, always quote your data in SQL inserts whether the type is numeric or otherwise. ;)

(Ahem.. I'm going to feel dumb if this doesn't fix things..)

Re: MYSQL + UPDATE RECORDS

Posted: Tue Jul 07, 2009 10:23 pm
by jamkelvl
Works!

Thank you very much.

I will be updating more than one entry though.
:drunk: