Mysql update fails ... i think

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
Rippie
Forum Commoner
Posts: 76
Joined: Sun Jan 10, 2010 11:32 am
Location: Nottingham

Mysql update fails ... i think

Post 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
User avatar
a.heresey
Forum Commoner
Posts: 59
Joined: Wed Dec 13, 2006 7:31 pm
Location: Chesapeake, VA, US

Re: Mysql update fails ... i think

Post 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?
Rippie
Forum Commoner
Posts: 76
Joined: Sun Jan 10, 2010 11:32 am
Location: Nottingham

Re: Mysql update fails ... i think

Post 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";
}
}
}
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Mysql update fails ... i think

Post 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'].
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Rippie
Forum Commoner
Posts: 76
Joined: Sun Jan 10, 2010 11:32 am
Location: Nottingham

Re: Mysql update fails ... i think

Post by Rippie »

I dont understand why i need to run a while, could i not just do $newvar = $row['id']; ?
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Mysql update fails ... i think

Post 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'];
}?>
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Rippie
Forum Commoner
Posts: 76
Joined: Sun Jan 10, 2010 11:32 am
Location: Nottingham

Re: Mysql update fails ... i think

Post by Rippie »

Got this working. a simple mistake. thx everyone !
Post Reply