[Resolved] This update query works in the mysql client but not using php

[digia]

When using the client, I force $productid to a valid productid. I'm updating
if ($current_priority == 0)

There are no errors returned with mysql_error() and mysql_affected_rows() returns 1 as it should. I don't understand why this query works in the client but not with php. Many other update queries I have written in php work just fine. Any and all help is greatly appreciated.

Thanks in advance, Adam.

$rand_banner_query = "update affiliate_products join affiliates on affiliate_products.affiliateid=affiliates.affiliateid set affiliate_products.current_priority=affiliates.priority where affiliate_products.productid='$productid'";
$rand_banner_result = query($rand_banner_query);

  mysql> describe affiliates;

+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| affiliateid | int(10) unsigned | | PRI | NULL | auto_increment |
| name | varchar(50) | | | | |
| website | varchar(100) | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
| pi | varchar(20) | YES | | NULL | |
| priority | int(10) unsigned | YES | | NULL | |
+-------------+------------------+------+-----+---------+----------------+

mysql> describe affiliate_products;
+------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+----------------+
| productid | int(10) unsigned | | PRI | NULL | auto_increment |
| product | varchar(100) | | | | |
| description | text | YES | | NULL | |
| type | varchar(10) | | | | |
| url | varchar(150) | YES | | NULL | |
| affiliateid | int(10) unsigned | YES | | NULL | |
| current_priority | int(10) unsigned | YES | | NULL | |
+------------------+------------------+------+-----+---------+----------------+

[bradgrafelman]

$rand_banner_result = query($rand_banner_query); 

query() ? Isn't it mysql_query() or did you using a user-defined func here?

[digia]

query() is user defined, tested quite thoroughly as well. I know that's not the problem.

[bradgrafelman]

Hmm... have you tried echo'ing out $rand_banner_query to make sure it looks right?

EDIT: Once you echo it out, try pasting the query into phpMyAdmin (or the MySQL CLI if nothing else) and see what happens.

[digia]

I use print, same effect. I printed all variables including the query and everything is correct. So far I've stumped three people including myself with this problem, I hope you aren't the fourth!

[bradgrafelman]

Did you try copying the outputted query into phpMyAdmin/CLI as I suggested?

[digia]

Yes I have tried that and it works.

[bradgrafelman]

Person #4

[digia]

🙁 I've even tried rewriting the query, same results. Ugh this is frustrating.

[leatherback]

You say that in the client you are forcing the $productid to a valid product.. Have you tried doing that in the PhP?

Sounds to me like that might be a problem.

Could you post the actual outputted query (From echo $rand_banner_query😉 so we can have a look at it?

J.

[bradgrafelman]

The time now is 03:37 AM.

Doesn't anyone sleep around here? :x

[leatherback]

Hey Brad,

I must be sleeping, because I do not see where you got that quote from!?

Anyway. SLept 5 hours, went to my garden and got some strawberries & garlic, and am back in full programming mode. The coffee is dripping, the keyboard sweating, and I am strying to stay awake!

J.

[bradgrafelman]

lol. I see.

By the way, the "quote" was the timestamp at the bottom of the page:

All times are GMT -7 hours. The time now is 03:46 AM.

I'm headin off to bed. Hope you can help this one.. I'm clueless.

[leatherback]

Hi Brad,

Here it is 11 AM again. But eh.. Why are YOU up at this time!?

Sleep well!

J

[digia]

It appears the problem lies in $productid 's definition which is $productid = ${'banner'.$cnt}[0];
I made this conclusion by manually setting $productid=4; which works fine.

Here's the code:

$rand_banner_query = "select productid, product, url, description, current_priority, affiliateid from affiliate_products where type='NSFW' order by current_priority DESC limit $banner_limit";

$rand_banner_result = query($rand_banner_query);
while ($rand_banner_tmp = mysql_fetch_row($rand_banner_result))
{ $random_banners[] = $rand_banner_tmp; }

for ($cnt=0; $cnt<$banner_limit; $cnt++)
{
${'banner'.$cnt} = $random_banners[$cnt];
$productid = ${'banner'.$cnt}[0];

...
}

[leatherback]

Hi,

I am not familiar with the construct you are using here. It has to do with variable variable nanems, I supose, which I stopped trying to figure out, since it gave me headaches.. Probably simple when you get the concept to work.

Why do you use it?

Can't you just arrays for this? It is a bit difficult to asses, since I do not know what your query function does. but to me.. this is much more straightforward, wouldn't it give you what you need?:

$rand_banner_query = "select productid, product, url, description, current_priority, affiliateid 
from affiliate_products 
where type='NSFW' 
order by current_priority DESC 
limit $banner_limit";

$rand_banner_result = mysql_query($rand_banner_query);
$i =0;
while ($row = mysql_fetch_array($rand_banner_result))
    {
    $productid[$i] = $row[0];
    $i++;
    }

[leatherback]

Or you could even just change this:

${'banner'.$cnt} = $random_banners[$cnt];
$productid = ${'banner'.$cnt}[0];

to this:

$productid = $random_banners[$cnt][0];

[digia]

Thanks for all of your suggestions but I was wrong, $productid was being set fine. The actual problem was due to my logic. The problem as been solved. FINALLY!

🙂

[leatherback]

OK,

Good to hear that it works.

Of course are now curious what is was.. 😉

please mark the thread as solved (See link at bottom of thread)

J.