counting list

[ligraci]

Hi to all ;-)

I have a little problem about an update with elements in a list.

This is my list:
('\"a','cat5','cat1','cat1','cat8','a\"').

As you can see I have 2 elements called 'cat1', 1 called 'cat5' and 1 called 'cat8'.

This is my query:
$numresults = mysql_query("UPDATE mytable SET number = number + 1 WHERE cat in $list");

This is the table I have at start:

CAT | NUMBER

cat1 | 0
cat5 | 0
cat8 | 0

This is the result I want after the query:

CAT | NUMBER

cat1 | 2
cat5 | 1
cat8 | 1

This is the result I have now:

CAT | NUMBER

cat1 | 1
cat5 | 1
cat8 | 1

Why cat1 is not considered twice?

Every help will be appreciated.
Thanks!

[andreas]

$thelist = array('\"a','cat5','cat1','cat1','cat8','a\"');

reset($thelist);
while(list($key, $val) = each($thelist))
{
mysql_query("UPDATE mytable SET number = number + 1 WHERE cat is '$val'");
if(!mysql_affected_rows())
mysql_query("INSERT INTO mytable (number, cat) VALUES (1, '$val')");
}

Now you should have the result you wanted (not tested)

Andreas

[ligraci]

Really thanks!
Your code works. But I have another little problem.

I get the data from a url like this: www.myurl.com/file.php?elements="a,cat5,cat1,cat1,cat8,a"

then in my php file I do this:
$list="('".ereg_replace(",","','",$elements)."')";

and I have this:
('\"a','cat5','cat1','cat1','cat8','a\"');

The problem is that if I write as you said
$thelist = array('\"a','categoria5','categoria1','categoria1','categoria8','a\"');
everything works, but if my code is something like:
$thelist = array($list);
it doesn't work. :-(

I don't understand why?
Can you please help me?

Thanks!

[andreas]

Here is eval the answer i think:

eval("\$thelist = array($list);");

This should do the trick...

Andreas

[[deleted]]

cat1 is only increased once because the IN statement only checks to see if the value of a field is "in" the list of values you supply.
How many times it is in the list is irrelevant, because it only has to be in the list once to be able to say "it is in the list"

The query goes through each record in your table, and checks if the value is in the list.

What you want (and what Andreas gave you) is to go though your list, and check each record to see if it matches. That way, if something is in your list twice, the records will be checked for that value twice.

It's a subtile difference, but an important one.

[ligraci]

Thanks for your explanation, it was really useful!

But help me in this now.

$elements is in my url.
If my code is
$list=""".ereg_replace(",","", "",$elements)."" ";
$thelist = array($list);
it doesn't work.

If it is
$thelist = array(('\"a','cat5','cat1','cat1','cat8','a\"')😉;it does.

why can't I use array($list) to turn $thelist in array?

Thanks a lot for help!

[[deleted]]

Because you should use the explode() command to turn a comma seperated string into an array;

$array=explode(",","1,2,3,4,5");

[ligraci]

It works!

Really thanks vincent for your help.

;-D

[ligraci]

It didn't solve my problem but helped me in finding the solution. Thanks a lot for your attention and your help!