Page 1 of 1

Mysql update fails ... i think

Posted: Tue Feb 09, 2010 5:46 pm
by Rippie
guys, this is not doing anything. any ideas ?
mysql_connect($host, $user, $pass);
mysql_select_db($database);

$result = mysql_query("SELECT * from spVendors where vendName = '$vendorname'") or die (mysql_error());
$row = mysql_fetch_array($result);

$query = "UPDATE spVendors SET vendID = '$vendorid', vendName = '$vendorname' WHERE id = '".$row['id']."'";
mysql_query($query);

I can connect to mysql
spVendors is a table that has id , vendID and vendName inside it.

dont see why it wont update. help please

Re: Mysql update fails ... i think

Posted: Tue Feb 09, 2010 5:57 pm
by a.heresey
You need to see the error message.

Code: Select all

 
mysql_connect($host, $user, $pass);
mysql_select_db($database);
$q="SELECT * FROM spVendors WHERE vendName = '$vendorname'";
$result = mysql_query($q) or die (mysql_error());
$row = mysql_fetch_array($result);
 
$q = "UPDATE spVendors SET vendID = '$vendorid', vendName = '$vendorname' WHERE id = '".$row['id']."'";
$r = mysql_query($q) or die(mysql_error());
 
if($r){
//success!
echo "Should have updated";
}else{
//fail
echo "didn't work";
}
 
If id is a number shouldn't it not be in quotes?

Re: Mysql update fails ... i think

Posted: Wed Feb 10, 2010 7:07 am
by Rippie
ID is a number. so what do you mean it should be in qoutes, it is already in quotes from what i can tell.

Below is the code i have tried, no errors from mysql, still saying it has been updated, but nothing has changed. HELP

Code: Select all

if (empty($_POST['vendorname'])) {
// if vendorname was removed from input box when clicked edit
echo "vendorname is blank";
} else {
// if vendorname is not blank.
$vendorname = $_POST['vendorname'];
$vendid = ereg_replace("[^A-Za-z0-9]", "", $vendorname);
$vendorid = strtolower($vendid);
 
mysql_connect($host, $user, $pass);
mysql_select_db($database);
 
$result = mysql_query("SELECT * from spVendors where vendName = '$vendorname'") or die (mysql_error());
$count = mysql_num_rows($result);
$row = mysql_fetch_array($result);
 
if ($count == 1) {
echo "error - Vendor already exist $vendorname . ".$row['id']."";
} else {
$q = "UPDATE spVendors SET vendID = '$vendorid', vendName = '$vendorname' WHERE id = '".$row['id']."'";
$r = mysql_query($q) or die(mysql_error());
 
if($r){
echo "Vendor in database has been updated";
} else {
echo "Fail";
}
}
}

Re: Mysql update fails ... i think

Posted: Wed Feb 10, 2010 9:23 am
by social_experiment
If im not mistaken the $row = mysql_fetch_array($result) needs to be in a while loop for the values retrieved in it to be useable. Unless you assign the values from $row to variables inside the loop, then use them outside it.

Code: Select all

<?php while ($row = mysql_fetch_array($result)) {
   $value = $row['id'];
}?>
$value will now have the value of $row['id'].

Re: Mysql update fails ... i think

Posted: Wed Feb 10, 2010 11:16 am
by Rippie
I dont understand why i need to run a while, could i not just do $newvar = $row['id']; ?

Re: Mysql update fails ... i think

Posted: Wed Feb 10, 2010 1:20 pm
by social_experiment
You dont need the while loop, you just need to equate the variables inside it.

Code: Select all

<?php while ($value = mysql_fetch_array($sqlResult)) {
 $variable1 = $value['id'];
}?>

Re: Mysql update fails ... i think

Posted: Wed Feb 10, 2010 3:37 pm
by Rippie
Got this working. a simple mistake. thx everyone !