[SOLVED] PHP code to move between tables

[chinclub]

I am trying to write my very first PHP code and it is not going well at all!!  I am trying to code a way for someone to click a link to move an item (and all of its variables) from one table in the SQL database to another.

 

I thought I had it working but it didn't copy over everything so I editted it and now I get an error:

 

"Column count doesn't match value count at row 1"

 

Also, I don't know what code I need to include to remove the info from the first table.  Right now it is just copying it.

Here is how the database tables are layed out.One is called "pets" and the other is called "pound_pets"

 

  `user` varchar(255) NOT NULL default '',
  `petID` int(11) NOT NULL default '0',
  `id` int(11) NOT NULL auto_increment,
  `points` int(11) NOT NULL default '0',
  `status` varchar(255) NOT NULL default '',
  `petName` varchar(255) NOT NULL default '',
  `pet` varchar(255) NOT NULL default '',
  `species` varchar(255) NOT NULL default '',
  `date` varchar(255) NOT NULL default '',
  `health` int(11) NOT NULL default '100',
  `wins` int(11) NOT NULL default '0',
  `loses` int(11) NOT NULL default '0',
  `ties` int(11) NOT NULL default '0',
  `img` text NOT NULL,
  `level` int(11) NOT NULL default '1',
  `items` int(11) NOT NULL default '0',
  `maxhealth` int(11) NOT NULL default '0',
  `site` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)

 

 

Here is what I have so far (that is giving me the error):

 

if($a == 'giveaway') {
 	$id = $_GET['id'];
	$check = mysql_num_rows(mysql_query("SELECT * FROM `pets` WHERE `user`='{$_COOKIE['ipets']}' AND `petID`='$id' AND `site`=\"$Z\""));

		$date = date("m/d/y");
		$status = 3;
		$insert = mysql_query("INSERT INTO `pound_pets` (`site`, `img`, `status`, `user`, `petID`, `species`, `date`, `petName`, `items`, `wins`, `loses`, `ties`, `maxhealth` ) VALUES(\"$Z\", \"$img\", '$status', '{$_COOKIE['ipets']}', '$id', \"$petName\", '$date')");

		if($insert) {
			$sql = mysql_query("SELECT * FROM `pets` WHERE `user`='{$_COOKIE['ipets']}' AND `petID`='$id' AND `site`=\"$Z\"");
			while($row = mysql_fetch_array($sql)) {
			 	$idd = $row["id"];
			}

 

 

Can anyone help me with the code that will remove the data from pets after it copies it to pound_pets

and also tell me why it has the ""Column count doesn't match value count at row 1" error.

 

Thanks!!

 

 

[AndyB]

Column count doesn't match value count means exactly that - the number of columns doesn't match the number of values.

 

But there's a much easier way to do what I think you want.  Use one table and add a column to identify whether the thing is a 'pet' or a 'pound pet'.  Then all you need do is a simple UPDATE query to change that column's value.  When looking for a particular type of pet, add a WHERE petplace = 'pound_pet' (for example) to identify only the pound pets in the table.

[chinclub]

That is a wonderful idea!!!  If I set the user to pound then I can have it set the user to whoever adopts it.

 

But now I have a new problem.  I created my PHP code to do that and the code seems to run fine, but it isn't changing the database.  What am I missing??

 

 <?
include("header.php");

$a = $_GET['action'];
if($a == 'giveaway')  {

$sql = mysql_query("UPDATE `pets` SET `user`='pound' WHERE `id`='$id'");
		if($sql) {
		 	print "How sad!";
		}
		}
include("footer.php");
?>

[AndyB]

probably because the value of $id is unknown to the query.  You need to pass BOTH the id and the action to the script ... your editing page needs to generate a clickable URL like a href='update.php?action=giveaway&id=some_id-value

[chinclub]

That is what I am doing : giveaway.php?action=giveaway&id=156

 

Let me ask you this, it shouldn't matter whether I am typing in the URL or clicking a link from another page should it?  I have been typing in the URL to test the script.  ???

[chinclub]

Is there some sort of setting within the database itself that would not allow it to be changed? Are there permissions in databases as there are in website directories?

[AndyB]

Clicking a link or typing in the address bar are the same thing (which makes $_GET liable to abuse).

 

You should probably start reading up on how to 'sanitize' user input to protect your database. (worth searching the php help forum since it's a frequent question/problem).

 

As to why your code isn't performing, it's not obvious that a database connection and database selection ever occurs.  As a matter of good practice when testing, use error messages to tell you what's happening (or not) and echo actual queries.

 

$sql = mysql_query("UPDATE `pets` SET `user`='pound' WHERE `id`='$id'");
if($sql) {
print "How sad!";
}

 

Better:

$sql = "UPDATE pets SET user='pound' WHERE id='$id'";
echo "query: ". $sql. "<br/>"; // what's happening
$result = mysql_query($sql) or die("Error: ". mysql_error(). " with query ". $sql); // helpful
if($result) {
    print "How sad!";
}

 

[chinclub]

Problem solved.  It turned out I was missing some code.  I added:

 

$id = (int) $_GET['id']; 

 

and now it works perfectly. Thank you so much for taking the time to help me.