Page 1 of 1

problems inserting data into query

Posted: Mon Feb 12, 2007 10:59 am
by genista
Hi all,

I have two problems relating to one script. I have a table called suppliers with a supplierid in, I want update the prices for the services they offer in another table - supplierprices. The link between the two tablesis supplierid. Hopefully this will help with the following code:


At this point I have run a select statement and all the details have been selected ok:

Now we have some html generated based on certain criteria -

Code: Select all

if (($serviceprice1 == "true")){ 
		echo "<tr><td><br>Your price for service 1is: £$serviceprice1 use this field to update your price:</tr></td><tr><td><input type=\"text\" name=\"serviceprice1\" value=\"\" maxlength=\"25\"></tr></td><br/>";
		}
		else{
		echo "<br>You do not provide service1<br/>";
	}
Lower down the page I check for isset and setup the indexes to go to the supplierprices table:

Code: Select all

if(isset($_POST['submit']))   
	
{  	
 
$supplierid = isset($_POST['supplierid']) ? $_POST['supplierid'] : "";
$serviceprice1 = isset($_POST['serviceprice1']) ? $_POST['serviceprice1'] : "";
supplierid is called from the suppliers table, there might not be a supplierid in the supplierprices table yet.

Now to update the supplierprices table

Code: Select all

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

When I run this query the supplierid is empty alog with any values I enter.

So I have two problems, the first is in the html that is generated, when I hit submit the query is empty of any values for service1price. In order to test I added a field below the php in html for service1price and it adds the value to the query.


The second problem is that when the query is run the supplierid is empty which means (if you look at the query) it cannot update the database.

I have got myself in a real fix, any ideas very welcome!


G

Posted: Mon Feb 12, 2007 11:31 am
by louie35
try to write the $query before executing and see how it looks

Code: Select all

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

Posted: Mon Feb 12, 2007 11:46 am
by RobertGonzalez
Post the generated HTML so we can look at the form. Then, before you do anything in the way of inserting or anything else in your code, do a var_dump($_POST) to see what PHP is seeing. It would be helpful if we could see all of the processing code surrounding that insert as well.

Posted: Tue Feb 13, 2007 3:37 am
by genista
Ok here are the query results:

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

As you can see all the values are empty!

Having viewed the source and obviously looking at the html it prints out fine:

<tr><td><br>Your price for service1 is: £'' use this field to update your price:</tr></td><tr><td><input type="text" name="service1price" value="" maxlength="25">


Does that help?


G

Posted: Tue Feb 13, 2007 3:42 am
by louie35
if you have the insert into a function you need to set up the global otherwise there must be something wrong wit the way you are posting data

Posted: Tue Feb 13, 2007 4:56 am
by genista
But if I change the query to :

Code: Select all

$query = "INSERT INTO supplierprices SET `supplierid` ='$supplierid' , `service1price` = '$service1price' ON DUPLICATE KEY UPDATE supplierid = '" . $supplierid . "'";
It still doesnt work.

here is the complete code (with the other query):

Code: Select all

//set session id 
$id = $_SESSION['username']; 
echo $id; 

//select query 
$query = "SELECT suppliers.supplierid, suppliers.username, suppliers.service1, supplierprices.service1price FROM suppliers 
LEFT JOIN supplierprices USING (supplierid) WHERE suppliers.username = '$id'"; 

    $result=mysql_query($query, $link) or die("MySQL query $query failed.  Error if any: ".mysql_error()); 

echo $query; 
    //get the first (and only) row from the result  
    $row = mysql_fetch_array($result, MYSQL_ASSOC);  

//create the rows 
$supplierid = $row['supplierid']; 
  $service1 = $row['service1']; 
  $service1price = $row['service1price']; 

//Check for issset 
if(isset($_POST['submit']))    
    /* if(isset( $submit ))*/  
{       
  
$supplierid = isset($_POST['supplierid']) ? $_POST['supplierid'] : ""; 
$service1price = isset($_POST['service1price']) ? $_POST['service1price'] : ""; 
 

//Going to add some data checking here before it gets posted to database if there are no errors go ahead with the quer 

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.';   
    }             
 }   
?> 
//end of php now for html. 
<html> 
<title><?php print $title ?></title> 
<html> 
<head> 
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> 
    <form name="form1" method="post" action="#" enctype="multipart/form-data"> 
    <p><td><tr><input type="text" name="service1price" value="" maxlength="25"></td></tr> 
    <p></td><td><input name="submit" type="submit" value="Submit"></p>  
    </table>  
   </form> 
</html>
Thanks for your help,

G

Posted: Tue Feb 13, 2007 6:01 am
by louie35
try this way:

Code: Select all

//set session id 
$id = $_SESSION['username']; 
echo $id; 

//select query 
$query = "SELECT suppliers.supplierid, suppliers.username, suppliers.service1, supplierprices.service1price FROM suppliers 
LEFT JOIN supplierprices USING (supplierid) WHERE suppliers.username = '$id'"; 

    $result=mysql_query($query, $link) or die("MySQL query $query failed.  Error if any: ".mysql_error()); 

echo $query; 
    //get the first (and only) row from the result  
    $row = mysql_fetch_array($result, MYSQL_ASSOC);  

//create the rows 
$supplierid = $row['supplierid']; 
  $service1 = $row['service1']; 
  $service1price = $row['service1price']; 

//Check for issset 
if(isset($_POST['service1price']))    { 
    /* if(isset( $submit ))*/  
       
 if($supplierid <> "" && $service1price <> ""){ 
  //$supplierid = isset($_POST['supplierid']) ? $_POST['supplierid'] : ""; 
  $service1price = $_POST['service1price'];//isset($_POST['service1price']) ? $_POST['service1price'] : ""; 
  }else{//no values
    $messages = "no values";
  }

//Going to add some data checking here before it gets posted to database if there are no errors go ahead with the quer 

if(!empty($messages)){ 
    displayErrors($messages); 
    die();
}else { 
//do the insert
$query = "INSERT INTO `supplierprices` (`supplierid`, `serviceprice1`) VALUES('".$supplierid."', '".$service1price."') ";
//WHERE `supplierid` = '" . $supplierid . "'";    
 echo $query;  
  die();//comment this if you are happy   

 $result = mysql_query($query, $link) or die('Update failed: ' . mysql_error());
    
 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.';    
    }              
 }    
?> 
//end of php now for html. 
<html> 
<title><?php print $title ?></title> 
<html> 
<head> 
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> 
    <form name="form1" method="post" action=""> 
    <p><td><tr><input type="text" name="service1price" id="service1price" value="" maxlength="25"></td></tr> 
    <p></td><td><input name="submit" type="submit" value="Submit"></p>  
    </table>  
   </form> 
</html>

Posted: Tue Feb 13, 2007 8:45 am
by RobertGonzalez
after this line:

Code: Select all

<?php
$row = mysql_fetch_array($result, MYSQL_ASSOC);
?>
add this line

Code: Select all

<?php
echo '<pre>'; var_dump($row); echo '</pre>';
?>
and before the isset($POST['submit']) check, add this line:

Code: Select all

<?php
echo '<pre>'; var_dump($_POST); echo '</pre>';
?>
Please do that and post back what is echo's to the screen exactly as it is echo'ed.

Posted: Tue Feb 13, 2007 1:30 pm
by genista
Ok here is the output:

Code: Select all

array(25) {
  ["supplierid"]=>
  string(2) "15"
  ["username"]=>
  string(5) "fred"
  ["service1price"]=>
  string(0) "true"
  ["service1price"]=>
  string(0) ""
}
And then before isset:

Code: Select all

array(0) {
}

As the supplierid is empty I have also taken to adding a field with the supplierid echo'd in it so it definetly gets called during the query, but surprise surprise it isn't, still empty.


G

[edit] Upon hitting submit I get the following printed before isset:

Code: Select all

array(3) {
  ["supplierid"]=>
  string(2) "15"
  ["service1price"]=>
  string(2) "20"
  ["submit"]=>
  string(6) "Submit"
}

Posted: Tue Feb 13, 2007 3:30 pm
by RobertGonzalez

Code: Select all

<?php
// Start by setting a debug var - turn this to false to turn off
$debug = true;

//set session id
// IS THIS BEING CALLED AFTER session_start()?
$id = $_SESSION['username'];

if ($debug) echo '<br />Session username is ' . $id;

//select query
$sql = "SELECT 
            suppliers.supplierid, 
            suppliers.username, 
            suppliers.service1, 
            supplierprices.service1price 
        FROM 
            suppliers
        LEFT JOIN 
            supplierprices 
        USING 
            (supplierid) 
        WHERE 
            suppliers.username = '$id'";

if ($debug) echo '<br />Select SQL is ' . $sql; 
$result = mysql_query($sql) or die("MySQL query <b>$query</b> failed.  Error if any: " . mysql_error());

//get the first (and only) row from the result
// THIS GETS THE LAST ROW OF A MULTIROW SET
if (mysql_num_rows($result)
{ 
    $row = mysql_fetch_array($result);
}
else
{
    die('There were no rows returned from the query.');
} 

//create the rows
$supplierid = $row['supplierid'];
$service1 = $row['service1'];
$service1price = $row['service1price'];

if ($debug)
{
    echo '<br />Supplier ID is ' . $supplierid;
    echo '<br />Service 1 is ' . $service1;
    echo '<br />Service 1 Price is ' . $service1price;
}

//Check for issset
if(isset($_POST['submit']))   
{       
    if ($debug) echo '<br />The form was posted...';
    // These two assignment will overwrite the $row assignments from above
    // even if the fields are empty, they are still set, and if the submit 
    // was pressed, then the two vars are overwritten right here
    $supplierid = isset($_POST['supplierid']) ? $_POST['supplierid'] : "";
    $service1price = isset($_POST['service1price']) ? $_POST['service1price'] : "";
 
    if ($debug)
    {
        echo '<br />Supplier ID has been rewritten to ' . $supplierid;
        echo '<br />Service 1 Price has been rewritten to ' . $service1price;
    }

    $sql = "INSERT INTO 
                supplierprices (supplierid, serviceprice1) 
            VALUES ('$supplierid', '$service1price') 
            ON DUPLICATE KEY UPDATE 
                supplierid = '$supplierid'";   
    
    if ($debug) echo '<br />INSERT SQL is ' . $sql;
    $result = mysql_query($sql) or die('Update failed: ' . mysql_error());   

    if(mysql_affected_rows())   
    {   
        echo 'Your profile has been updated successfully, please click <a href=suppliers.php>here</a> to go back to the main member page.';   
    }
    else
    {
        echo 'There was a problem with the insert...';
    }             
}

if ($debug) echo '<br />We are now out of the form post check...'; 
?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title><?php print $title ?></title>
</head>

<body>
<!-- THIS FORM DOES NOT CONTAIN ALL OF THE FIELDS LOOKED FOR IN THE CODE -->
<!-- THERE IS NOT FIELD FOR SUPPLIERID -->
    <form name="form1" method="post" action="#" enctype="multipart/form-data">
    <p><input type="text" name="service1price" value="" maxlength="25" /></p>
    <p><input name="submit" type="submit" value="Submit" /></p> 
    </table> 
   </form>
</body>
</html>