count in MySQL

what data type to use for a field

[smallcompany]

If you are writing keywords into the table, and want to increment the number every time the keyword repeats, what data type would you use for that particular count field?

I ask this because I have a little script that connects to the database and writes these two things into it.
I created the database with two fields, and both were VARCHAR at the beginning.
First entry would enter the keyword into right field, and also put numeric "1" onto the other. The next time the keyword would be entered, instead of increasing the "1" to "2", I get this:

Array{count}

Then I figured that maybe I used the wrong data type, so I changed it to INT, and got "0" to be there all the time.

Would this be about data type?

Thanks

[dreamcatcher]

No, I think this is about your PHP code. The varchar field gives you the clue. Instead of storing an integer, its storing text, so incorrect formatting. Its actually storing what the PHP is parsing.

An INT field defaults to 0 if the data isn`t recognised as an integer.

So, I would look at the code you have to generate the count.

dc

[smallcompany]

Thanks.

Here is the code that does it. I just dropped the connection part.

$tableName = "advTable";
$mysql_link = mysql_connect(CONN_HOST, CONN_USER, CONN_PASS);
mysql_select_db(CONN_DB, $mysql_link);
$sql = "select * from $tableName where query='$key';";
$result = mysql_query($sql, $mysql_link) or die('Query failed:[$sql]<br>Error is: ' . mysql_error($mysql_link));
if ($result && mysql_num_rows($result) > 0)
{
$row = mysql_fetch_assoc($result);
$row{count}++;
$sql ="update $tableName set count='$row{count}' where query='$key';";
$result = mysql_query($sql, $mysql_link) or die('Query failed:[$sql]<br>Error is: ' . mysql_error($mysql_link));
}
else
{
$sql ="insert into $tableName set query='$key', count='1';";
$result = mysql_query($sql, $mysql_link) or die('Query failed:[$sql]<br>Error is: ' . mysql_error($mysql_link));
}

[idfer]

Your problem is this:

$row{count}

, to access an element of an array, you want to use square brackets instead of curly brackets, so:

$row['count']++;
$sql ="update $tableName set count={$row['count']} where query='$key'";

The curly brackets in the second statement tell PHP that

['count']

is also part of the variable you're referring to. Anyhow, it looks you want to increment the value of "count" in your table, if you have a "unique index" defined for the field "query", you can do it all in a single SQL statement:

$sql = "insert into $tableName values ('$key', 1)
on duplicate key update count = count + 1";

[smallcompany]

That did it! Thanks very much.

I will "play" with your simplified suggestion. Yes, it is about incrementing existing keys for 1, or entering new ones if they're not already in the table.

Thanks again!

[smallcompany]

To early to (fully) celebrate.

It works fine in 1&1 hosting, but with Pair, it makes an entry and adds value of "1" for count, and it stops there. It's not incrementing, just adding new queries. Pair hosting database is version 5.0x and 1&1 is 5.1 which should not make difference I would think.
I created everything at Pair just as I did previously at 1&1.

Why would it work differently?

Thanks

[idfer]

Check that:

- You have the field "query" defined as unique (in phpMyAdmin, click on the table structure and in the Indexes section, you should see an entry with Type "primary" or "unique" and Field "query").

- The MySQL user, i.e. whatever CONN_USER is set to, has UPDATE priviledges on the database (in phpMyAdmin, click on Priviledges)

That's all i can think of.

[smallcompany]

None of that helped on existing table.

Then, I deleted the table and created a new one, same thing, except that instead of 256 I used 100 for length for Query field.
Note that I already tried reducing 256 to 100 in old table which did not give any result.

Anyhow... it works now, but the existing code is increasing the empty query every time a page has been loaded (like when you're browsing through the site). More programing needed...