problems inserting data into query

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
genista
Forum Commoner
Posts: 57
Joined: Fri Aug 18, 2006 3:56 pm

problems inserting data into query

Post 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
User avatar
louie35
Forum Contributor
Posts: 144
Joined: Fri Jan 26, 2007 8:40 am
Location: Dublin
Contact:

Post 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();
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
genista
Forum Commoner
Posts: 57
Joined: Fri Aug 18, 2006 3:56 pm

Post 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
User avatar
louie35
Forum Contributor
Posts: 144
Joined: Fri Jan 26, 2007 8:40 am
Location: Dublin
Contact:

Post 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
genista
Forum Commoner
Posts: 57
Joined: Fri Aug 18, 2006 3:56 pm

Post 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
User avatar
louie35
Forum Contributor
Posts: 144
Joined: Fri Jan 26, 2007 8:40 am
Location: Dublin
Contact:

Post 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>
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
genista
Forum Commoner
Posts: 57
Joined: Fri Aug 18, 2006 3:56 pm

Post 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"
}
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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>
Post Reply