Empty insert query

[genista]

Hi all,

 

I seem to be having a problem with updating a database and I suspect it is the php outputting html that might be the cause, here is the order of the script:

 

having got the database records out we output some html based on certain criteria being met:

if (($service1 == "true")){ 
        echo "<tr><td><br>Your price for service1  is: £$service1price use this field to update your price:</tr></td><tr><td><input type=\"text\" name=\"service1price\" value=\"\" maxlength=\"25\"></tr></td></br>";
        }
        else{
        echo "<br>You do not provide service1</br>";
    }  

 

You can see here that the user can update the price of a service provided they.. provide it!

 

This works fine, now I need to set the data to be posted back to the database. I post the supplierid because this is a new table and it might be the first time the user is updating the table:

 

if(isset( $submit )) 
{      
$supplierid = isset($_POST['supplierid']) ? $_POST['supplierid'] : "";
$service1price = isset($_POST['service1price']) ? $_POST['service1price'] : "";

 

Now I do some error checking before updating the database:

 

}

  if(!empty($messages)){
    displayErrors($messages);
}  
    else
    {
$query = "INSERT INTO supplierprices (supplierid, serviceprice1) VALUES('".$supplierid."', '".$service1price."') ON DUPLICATE KEY UPDATE supplierid = '" . $supplierid . "'";  

$result = mysql_query($query, $link) or die('Update failed: ' . mysql_error()); 
echo $query; 

//print_r($query);  
mysql_info($link) ; 
    if(mysql_affected_rows($link) == 0) 
    { 
      //$link next to affected rows and mysql query
        echo ''; 
    } 
     
    else 
    { 
        echo 'Your profile has been updated successfully, please click <a href=suppliers.php>here</a> to go back to the main member page.'; 
    }           
}  

 

So the above is not completly finished but the query itself is. The problem when I output the query is that having entered a value for serviceprice1 it is empty here. I have also tried changing the duplicate key to serviceprice1 but still doesnt work.

 

Any ideas?

 

Thanks,

 

G

[genista]

As an update I have now commented out the dynamic creation of html in PHP, and instead coded the html below the php. I have done this in order to try and fix the problem with the query. I have echo'd the query and as you can see the value for the supplierid is empty:

 

INSERT INTO supplierprices (supplierid, service1price) VALUES('', ''20'') ON DUPLICATE KEY UPDATE supplierid = ''

 

Here again is the query:

 

$query = "INSERT INTO supplierprices (supplierid, serviceprice1) VALUES('".$supplierid."', '".$service1price."') ON DUPLICATE KEY UPDATE supplierid = '" . $supplierid . "'";  

$result = mysql_query($query, $link) or die('Update failed: ' . mysql_error()); 
echo $query; 

//print_r($query);  
mysql_info($link) ; 
    if(mysql_affected_rows($link) == 0) 
    { 
      //$link next to affected rows and mysql query
        echo ''; 
    } 
     
    else 
    { 
        echo 'Your profile has been updated successfully, please click <a href=suppliers.php>here</a> to go back to the main member page.'; 
    }           
}  

 

Any ideas?

 

Thanks,

 

 

G

[Lodar]

Is there a text field or hidden input in the form that contains the value for the supplierid var? If no then the $_POST['supplierid'] wont contain any value at all.

 

I cant really tell from you code as not all of the form is there.

[genista]

Is there an alternative to get the supplierid into the query, ie if I take it out of the$POST array? Is using hidden fields secure?

 

In other words what is the best way to get supplierid into the query?

 

Thanks,

 

G

[Lodar]

You could use a session to hold the supplier id.

 

How is the supplier ID populated in the first place? Is it stored in a table say against there username? Do you require them to input the supplier id?

 

If it is held in the users table, then when they have authenticated to login, before you redirect them to another page you can retrieve the supplierid from the appropriate table and hold it in a session var called supplierid.

 

Then just use $_SESSION['supplierid'] instead of  $supplierid in your insert statement.

 

Also is the supplierid column set to be null or not null? If you require it I would set it to be not allow null values.

[genista]

Ok I will take a look at that and let you know how it goes, I may revert to the hidden field....

 

Thanks for your help,

 

G