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:
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>