Why is this going wrong?!

[TouranMan]

Hi,

 

I'm a bit of a noob but can find my way around the basic stuff but I have a problem that I just can't figure out!

 

I'm using Wordpress with a paid theme and I'm altering the theme to suit my needs better.  I have the following code which deals with displaying advertising blocks:

 

/* Set the number of ad blocks */ 
$adBlocks = 12;

/* Set the number of ads sold */
$soldAds = 24;

$totalAds = range(1,$soldAds);
$img_url = array();
$dest_url = array();

shuffle($totalAds);

for ($counter = 0; $counter < $adBlocks; $counter++) {

$img_url[$counter+1] = get_option('woo_ad_image_'.$totalAds[$counter]);
$dest_url[$counter+1] = get_option('woo_ad_url_'.$totalAds[$counter]);

/* Update ad impression counter */
  $query = "UPDATE trk_tracking SET count=count+1 WHERE id = " . $counter;

  mysql_query($query);

}

 

The main problem is that when the query updates the database, it doesn't update the correct records. If I run the above code then it gives me the array:

 

Array ( [0] => 7 [1] => 13 [2] => 14 [3] => 1 [4] => 17 [5] => 16 [6] => 4 [7] => 15 [8] => 9 [9] => 8 [10] => 11 [11] => 20 [12] => 19 [13] => 10 [14] => 3 [15] => 6 [16] => 12 [17] => 21 [18] => 2 [19] => 24 [20] => 22 [21] => 23 [22] => 5 [23] => 18 ) 

 

Which is exactly what I need.  Then, the first 12 ads are assigned variable then I need to update the counter of the appropriate record to track which ads have been shown.  This is where it goes wrong!

 

This is a dump of the query from the above array:

 

UPDATE trk_tracking SET count=count+1 WHERE id = 7
UPDATE trk_tracking SET count=count+1 WHERE id = 13
UPDATE trk_tracking SET count=count+1 WHERE id = 14
UPDATE trk_tracking SET count=count+1 WHERE id = 1
UPDATE trk_tracking SET count=count+1 WHERE id = 17
UPDATE trk_tracking SET count=count+1 WHERE id = 16
UPDATE trk_tracking SET count=count+1 WHERE id = 4
UPDATE trk_tracking SET count=count+1 WHERE id = 15
UPDATE trk_tracking SET count=count+1 WHERE id = 9
UPDATE trk_tracking SET count=count+1 WHERE id = 8
UPDATE trk_tracking SET count=count+1 WHERE id = 11
UPDATE trk_tracking SET count=count+1 WHERE id = 20

 

But what the db gets updated with is:

 

id     date_created               URL                             Count    Name

1  	2008-09-03 23:43:51  	http://www.google.com/1  	2  	Google 1
2 	2008-09-04 22:18:20 	http://www.google.com/2 	0 	Google 2
3 	2008-09-04 22:19:15 	http://www.google.com/3 	0 	Google 3
4 	2008-09-06 13:14:34 	http://www.google.com/4 	2 	Google 4
5 	2008-09-06 13:14:34 	http://www.google.com/5 	0 	Google 5
6 	2008-09-06 13:53:33 	http://www.google.com/6 	0 	Google 6
7 	2008-09-06 13:53:33 	http://www.google.com/7 	2 	Google 7
8 	2008-09-06 13:55:04 	http://www.google.com/8 	1 	Google 8
9 	2008-09-06 13:55:04 	http://www.google.com/9 	1 	Google 9
10 	2008-09-06 13:55:54 	http://www.google.com/10 	1 	Google 10

 

I just can't understand why the code isn't working as I need it to.  I know the query is correct as I can input that directly via phpmyadmin and it works fine.

 

Any help would be REALLY appreciated as I'm tearing my hair out now!

 

Thanks.

[toplay]

Since you're using just $counter in the "where" clause it will only update id's 0 (which is invalid if ID is an auto increment column) through 11.

 

The variable that you show as containing the array of id values is what you should be using. For example:

 

$query = "UPDATE trk_tracking SET count=count+1 WHERE id = " . $myArray[$counter];

 

You show 24 values (0-23) in your array, so the "for" loop needs to iterate through that number of entries. For example:

 

for ($counter = 0, $max=count($myArray); $counter < $max; $counter++) {

}

 

 

hth and good luck.

 

 

[TouranMan]

Thanks for replying toplay.

 

I was playing about so the query should actually read:

 

  $query = "UPDATE trk_tracking SET count=count+1 WHERE id = " . $totalAds[$counter];

 

The idea of the code is to display a selection of 12 ads from a total of say 24.  That's why I'm shuffling the array and using the first 12 elements rather than choosing random numbers (to minimise the event of displaying two ads the same).

 

So, aside from the wrong query I printed, all other data is correct as to what's happening.

 

Any further ideas?

[toplay]

You should check for errors after the query to see what's wrong.

 

However, I think it's because "count" is a reserved word in MySQL, so change the column name to something else, or enclosed in backtick marks like this:

 

$query = "UPDATE trk_tracking SET `count` = `count` + 1 WHERE id = " . $totalAds[$counter];

 

 

[TouranMan]

Hi,

 

I tried changing the colum name AND putting it in backticks and that didn't work either!

 

Is there a way to display errors then?

[toplay]

Why do you think it's not working? What is the expect result you want/expect?

 

Sample below is from manual:

 

$result = mysql_query($query);

 

// Check result

// This shows the actual query sent to MySQL, and the error. Useful for debugging.

if (!$result) {

    $message  = 'Invalid query: ' . mysql_error() . "\n";

    $message .= 'Whole query: ' . $query;

    die($message);

}

[TouranMan]

The idea of the code is to update the count of the record as it is displayed thus logging the number of impressions of the ad that has been displayed.

 

Tried the above code and it doesn't produce any errors!  :'(

[TouranMan]

Can anyone help on this? It's driving me crazy!  :-\