problem with inserting to database

[pjc2003]

hi ive created this page:

it is supposed to update details of stock in a database, the user is able to choose a stock location and also if the stock is valid.. however im having problems.. all its doing it adding a new field into the database, i think my SQL statement is wrong? Should I be using an update statement?


<?php
require('./header.php');
require('./dbconn.php');

$part_number = intval($_GET['part_number']);

$action = 'show_form';
if(!empty($_POST)) {
$action= 'process_data';
}

if($action== 'process_data')  {
$location = trim($_POST['location']);
$location_id = trim($_POST['location_id']);
$valid = trim($_POST['valid']);

$sql= "INSERT INTO stockdata (location , location_id , valid,  WHERE  stockcheck.part_number=$part_number )  " .
"VALUES ('$location' , '$location_id', '$valid' )";
$ok= mysql_query($sql);

//echo $sql;    this is to check the queery is running ok
//end running database query

}


if($action == 'show_form')  {
?>

<form method="post" action="stock_control.php">
  <p>
  <select name="location_ids[]" multiple="multiple">
    <?php
//setup and run query
$sql = "SELECT * FROM location ORDER BY name ASC";

$result= mysql_query($sql);

//iterate over results
while($row = mysql_fetch_assoc($result)){
$location_id = $row['location_id'];
$location_name = $row['name'];
echo"<option value=\"$location_id\"><p>Location</p> $location_name</option>\n";
}
?>
  </select>
</p>
  <p>
    <select name="valid">
      <option =1 <?php if ($valid =0) echo ' selected '?> > 0</option>
      <option =2 <?php if ($valid =1) echo ' selected '?> > 1</option>
    </select>
</p>
  <input name="submit" type="submit" value="save"/>
  </p>
</form>
<?php
}
require('./footer.php');
?>

[effigy]

[b]INSERT[/b] [i]inserts[/i] (adds) a new row. [b]UPDATE[/b] [i]updates[/i] an existing row. Check the manual for the correct syntax.

[emehrkay]

your query is all screwed up.

$sql= "INSERT INTO stockdata (location , location_id , valid,  WHERE  stockcheck.part_number=$part_number )  " .
"VALUES ('$location' , '$location_id', '$valid' )";

change it to

$sql= "INSERT INTO stockdata (location , location_id , valid)  " .
"VALUES ('$location' , '$location_id', '$valid' )
WHERE  stockcheck.part_number=$part_number ";

and what is stockcheck.part_number?

[pjc2003]

hi there thanks for your reply,

the stockcheck.part_number is a part_number variable from my database table "stockcheck"

when im running my program now, it picks up if i have selected a zero or one from the select drop down box, but its not picking up the value of my location_name and location_id variables... Also I am having trouble with it picking up the part_number variable I have posted from a previous page.

I have changed the SQL statement to

$sql= "UPDATE INTO stockdata (location , location_id , valid)  " .
"VALUES ('$location_name' , '$location_id', '$valid' )
WHERE  part_number=$part_number ";

[pjc2003]

heres how I got the value of part_number into the stock_control page that im working on now...

<a class='LinkText' href='stock_control.php?part_number=<?php echo $part_number?>'>Edit Item</a>




heres how my stock control page looks at the moment:

<?php
require('./header.php');
require('./dbconn.php');


$part_number = ($_GET['part_number']);

$action = 'show_form';
if(!empty($_POST)) {
$action= 'process_data';
}

if($action== 'process_data')  {
$location_name = trim($_POST['location']);
$location_id = intval(trim($_POST['location_id']));
$valid = trim($_POST['valid']);
$part_number = trim($_GET['part_number']);

$sql= "UPDATE stockdata (location , valid)  " .
"VALUES ('$location_name', '$valid' )
WHERE  part_number=$part_number ";
$ok= mysql_query($sql);

if($ok){
echo "<p class='ErrorText'>DETAILS UPDATED SUCESSFULLY </p>";

}
else
{
echo "<p class ='ErrorText'>SORRY I CANNOT COMPLETE YOUR REQUEST AT THIS TIME </p>";
}
}
else {
$action = 'show_form';
}

echo $sql;   

//end running database query


if($action == 'show_form')  {
?>

<form method="post" action="stock_control.php">
  <p class ='MainText'>
  Change Location -
<select name="location_id">
<?php
//setup and run query
$sql = "SELECT * FROM location ORDER BY location ASC";
$result= mysql_query($sql);

//iterate over results
while($row = mysql_fetch_assoc($result)){
$this_location_id = $row['loaction_id'];
$this_location_name = $row['location'];
echo"<option value=\"$this_location_id\"";
if($this_location_id == $location_id) {
echo "selected=\"selected\"";
}
echo ">$this_location_name</option>\n";
}

?>

</select> 
 
</p>
  <p class ='MainText'>
    Change Validity-
      <select name="valid">
      <option =1 <?php if ($valid =0) echo ' selected '?> > 0</option>
      <option =2 <?php if ($valid =1) echo ' selected '?> > 1</option>
    </select>
</p>
  <input type="submit" value="save"/>
  </p>
</form>
<?php
}
require('./footer.php');
?>


but its not getting the value of "part_number" OR "locaiton"

any ideas?!

[emehrkay]

i dont know if it matters or not, but take the parens off of this get

$part_number = ($_GET['part_number']);
$part_number = $_GET['part_number'];

[pjc2003]

hmm ive changed it but still doin the same thing...


Any ideas of why its not picking up the location_name variable?  if you want to actually see it working go to:

www.longnines.net/mobile

select location A and ull see what im trying to do...