On Duplicate Key Update Problem

PHP
1

Hello, I’m back again.
Alright, so, My problem is I’m getting the Column Count Doesn’t Match Value Count At Row 1 error. Now, I’ve triple checked the number of columns and the code worked in the test version I did. I just can not see why i’m getting the error.

	<?php
$scheme_title = $_POST['scheme_title'];
	mysql_query("Insert into info values ('', '".$scheme_title."')");
	$scheme_id = mysql_insert_id();

	foreach ($_POST['hex_value'] as $row=>$hex_value)
	{
		mysql_query("INSERT INTO colours VALUES ('', '". $scheme_id . "', '". $hex_value ." ')") or die(mysql_error());
	}

	foreach ($_POST['tag_name'] as $row=>$tag_name){
		mysql_query("INSERT INTO tags (tag_id, tag_name, count) VALUES ('', '".$tag_name."', '1') ON DUPLICATE KEY UPDATE count=count+1") or die(mysql_error());
		$tag_id = mysql_insert_id();
		mysql_query("INSERT INTO scheme_tags VALUES ('', '".$tag_id."', '".$scheme_id."')") or die(mysql_error());
	}
	
?>

The above code was working before I added the on duplicate key update, Any help would be muchly appreciated.

2

Unless tag_name is the key (which… i’m doubting because you’ve got a tag_id synthetic key field. I’m assuming tag_id is an autoincrementing PRIMARY ), you will never reach the duplicate key part of the statement.

What’s the structure of the tags table, including keys.

3

COUNT is a reserved word. Try putting it in backticks.

4

Ahh! Guido! I’ll give it a try.
EDIT
You know what? I’m really silly. i had been using the wrong php file in my form, the old not the new one. let’s see how that works, although i will change it from count to tag_count also

5

Nope, that didn’t work either

6

What is the structure of the colours table?

7

Colours Table
colour_id, scheme_id, hex_value

before i added the on duplicate key update part the code worked fine.

8

I’m not positive if this may be the problem but to reference a value contained in the insert statement you would use count = VALUES(count)+1. I’m not exactly sure what count=count+1 does but I’m fairly certain its incorrect for your intended result.

The other thing is you will need to use INSERT IGNORE if not providing all the required columns as if creating a new row. INSERT IGNORE will allow you to update using a duplicate key update with only a the necessary columns rather than all columns required to create a new row.

Edit:

count=count+1 will increment the count column value in database by 1.

9

I don’t understand why it would be harder to link scheme and tag name than it is to link scheme and tag id?

10

Sorry, i wasn’t paying attention when i wrote that, i’m not getting the error anymore, that was because i’m a duffer and had the old php which only had 2 fields not the 3 So technically the problem this was about is half fixed, now it’s just not updating the field count (now renamed tag_count). i’m going to put the name back to count and see if that works.

No luck on that

11

You have 4 insert queries in your code. Are you sure it’s the one with the UPDATE that is throwing the error? Why?

12

each tag can be linked to multiple schemes it’s easier for me to link the tag_id, & scheme_id is there anyway it can auto-increment without being a key?

13

ah darn my net for being slow, could you give me an example?

14

the id is linked to another part of the script, i need it for linking scheme and tag

15

except tag_name is unique. So you can link it by name.

16

In the tagid-less scenario, you’d have to link the two with the tag name.

17

Since the tag names are unique, I don’t see why you would need a tag id. So getting rid of the autoincrementing key and making the tag name the primary key wouldn’t cause any harm. I think :slight_smile:

18

so drop the primary key? keep the unique?

19

Good one. Looking at the insert, I’m sure it’s an autoincrementing field.

20

tag_id is primary key tag_name is a unique key