Jump to content


Photo

MySQL: supplied argument is not a valid resource


  • Please log in to reply
10 replies to this topic

#1 purefusion

purefusion
  • Members
  • PipPip
  • Member
  • 19 posts

Posted 03 October 2005 - 09:36 PM

I've got a SELECT script in my php document that get's current data. And an UPDATE script that replaces the data with modified data. The problem is, I get these errors:

mysql_fetch_array(): supplied argument is not a valid MySQL result resource.

mysql_num_rows(): supplied argument is not a valid MySQL result resource.

[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]
$query = \"SELECT * FROM conversion_tracking WHERE referrer = \'{$_POST[\'cpc\']}\' LIMIT 1\";
$result = mysql_query ($query);
while ($row = mysql_fetch_array($result)) {
    $seconverts = $row[\'oct\']; //oct is the row I want to increment by 1 (currently 0)
    $seconverts++;
    echo $seconverts; //printed as an error correcting technique
}
$num = mysql_num_rows($result);
if ($num == 1) {
     $query = \"UPDATE conversion_tracking SET oct = $seconverts WHERE referrer = \'{$_POST[\'cpc\']}\' LIMIT 1\";
     $result = mysql_query ($query);
}[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

Any ideas as to errors? when I echo seconverts, it prints 1111. If it got data from the database, and incremented the value, it should read 1, because currently the value in the db is 0.

Thanks so much!

#2 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 03 October 2005 - 09:53 PM

it means the sql had some problem. use or die(mysql_error()); after any mysql_* calls.
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#3 purefusion

purefusion
  • Members
  • PipPip
  • Member
  • 19 posts

Posted 04 October 2005 - 01:52 PM

now it's not giving me any errors, yet it's still not updating the database. This is so weird...

Nothing is clicking in my head regarding any syntax errors.

#4 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 04 October 2005 - 03:36 PM

you do not need a select and update for incremeneting; just use:

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']update[/span] table set field=field+1 where id=1 [!--sql2--][/div][!--sql3--]

if you still have trouble, post your most recent code.
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#5 purefusion

purefusion
  • Members
  • PipPip
  • Member
  • 19 posts

Posted 05 October 2005 - 02:54 PM

effigy, thanks tons for that. I wasn't sure if that could be done.


I'm still getting a resource error on the mysql_affected_rows function.


new code:

[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]
[span style=\"color:#0000BB\"]<?
[/span][span style=\"color:#007700\"]require_once([/span][span style=\"color:#DD0000\"]\"/home/novav2/mysql_connect.php\"[/span][span style=\"color:#007700\"]);
[/span][span style=\"color:#0000BB\"]$mon [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]date[/span][span style=\"color:#007700\"]([/span][span style=\"color:#DD0000\"]\'M\'[/span][span style=\"color:#007700\"]);
[/span][span style=\"color:#0000BB\"]$ref [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\'google\'[/span][span style=\"color:#007700\"];
[/span][span style=\"color:#0000BB\"]$query [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\"UPDATE conversion_tracking SET $mon=$mon+1 WHERE referrer=\'google\' LIMIT 1\"[/span][span style=\"color:#007700\"];
[/span][span style=\"color:#0000BB\"]$result [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_query[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$query[/span][span style=\"color:#007700\"]);
if ([/span][span style=\"color:#0000BB\"]mysql_affected_rows[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$result[/span][span style=\"color:#007700\"]) == [/span][span style=\"color:#0000BB\"]1[/span][span style=\"color:#007700\"]) {
    echo [/span][span style=\"color:#DD0000\"]\" Done.\"[/span][span style=\"color:#007700\"];
}

[/span][span style=\"color:#0000BB\"]?>
[/span]
[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

#6 purefusion

purefusion
  • Members
  • PipPip
  • Member
  • 19 posts

Posted 05 October 2005 - 02:58 PM

The error is actually a warning:

Warning: mysql_affected_rows(): supplied argument is not a valid MySQL-Link resource in /home/novav2/public_html/pages/php_mysql_testing.php on line 15


But it's still not echoing "Done." when it updates the db.
I may need this function to work, so all help is appreciated greatly.

The db connection script is at the server root. It works. What if I changed that link to an include instead of a require? What's the difference?

#7 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 05 October 2005 - 05:12 PM

you have a field in your database that is equal to $mon? or do you mean SET mon=mon+1? i do not see any error checking in your code: no die statements, no mysql_error statements, and no echos...
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#8 purefusion

purefusion
  • Members
  • PipPip
  • Member
  • 19 posts

Posted 05 October 2005 - 06:04 PM

you have a field in your database that is equal to $mon? or do you mean SET mon=mon+1? i do not see any error checking in your code: no die statements, no mysql_error statements, and no echos...

View Post


I have added the error checking statements and no errors are reported because the queries go through just fine. It's the mysql_affected_rows function that isn't working, and how can I throw a die statement in an if statement?

Also, the $mon is a variable that holds the current month in mmm for (ex: oct). So the query takes oct and increments it (oct=oct+1) -> ($mon=$mon+1)

Thanks :)

#9 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 05 October 2005 - 06:44 PM

[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--][span style=\"color:#0000BB\"]<?php
    [/span][span style=\"color:#007700\"]require_once([/span][span style=\"color:#DD0000\"]\'/home/novav2/mysql_connect.php\'[/span][span style=\"color:#007700\"]);
    [/span][span style=\"color:#0000BB\"]$mon [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]date[/span][span style=\"color:#007700\"]([/span][span style=\"color:#DD0000\"]\'M\'[/span][span style=\"color:#007700\"]);
    [/span][span style=\"color:#0000BB\"]$ref [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\'google\'[/span][span style=\"color:#007700\"];
    [/span][span style=\"color:#0000BB\"]$query [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\"UPDATE conversion_tracking SET $mon=$mon+1 WHERE referrer=\'google\' LIMIT 1\"[/span][span style=\"color:#007700\"];
    [/span][span style=\"color:#0000BB\"]$result [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_query[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$query[/span][span style=\"color:#007700\"]) or die([/span][span style=\"color:#0000BB\"]mysql_error[/span][span style=\"color:#007700\"]());
    if ([/span][span style=\"color:#0000BB\"]$result[/span][span style=\"color:#007700\"]) { [/span][span style=\"color:#FF8000\"]# the or die should stop before this
        [/span][span style=\"color:#007700\"]if ([/span][span style=\"color:#0000BB\"]mysql_affected_rows[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$result[/span][span style=\"color:#007700\"]) == [/span][span style=\"color:#0000BB\"]1[/span][span style=\"color:#007700\"]) {
            echo [/span][span style=\"color:#DD0000\"]\'Done.\'[/span][span style=\"color:#007700\"];
        }
    }
    else {
        echo [/span][span style=\"color:#DD0000\"]\'Incomplete.\'[/span][span style=\"color:#007700\"];
    }

[/span][span style=\"color:#0000BB\"]?>[/span]
[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

make sure you have or dies in the mysql_connect file as well.
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#10 purefusion

purefusion
  • Members
  • PipPip
  • Member
  • 19 posts

Posted 05 October 2005 - 07:22 PM

make sure you have or dies in the mysql_connect file as well.

View Post


Yeah, I put those in a few days ago, and I haven't removed them.

#11 purefusion

purefusion
  • Members
  • PipPip
  • Member
  • 19 posts

Posted 05 October 2005 - 08:50 PM

Well, I've managed to get it to work. I restructured the query to use the numbers in the primary key, rather than the referrer column. Here's the new posted code:

[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]
[span style=\"color:#0000BB\"]<?

[/span][span style=\"color:#007700\"]require_once([/span][span style=\"color:#DD0000\"]\"/home/novav2/mysql_connect.php\"[/span][span style=\"color:#007700\"]);
[/span][span style=\"color:#0000BB\"]$mon [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]date[/span][span style=\"color:#007700\"]([/span][span style=\"color:#DD0000\"]\'M\'[/span][span style=\"color:#007700\"]);
[/span][span style=\"color:#0000BB\"]$ref [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]1[/span][span style=\"color:#007700\"];
[/span][span style=\"color:#0000BB\"]$query [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\"UPDATE conversion_tracking SET $mon=$mon+1 WHERE cpc_id=\'$ref\' LIMIT 1\"[/span][span style=\"color:#007700\"];
[/span][span style=\"color:#0000BB\"]$result [/span][span style=\"color:#007700\"]= @[/span][span style=\"color:#0000BB\"]mysql_query[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$query[/span][span style=\"color:#007700\"]) or die ([/span][span style=\"color:#0000BB\"]mysql_error[/span][span style=\"color:#007700\"]());
if (@[/span][span style=\"color:#0000BB\"]mysql_affected_rows[/span][span style=\"color:#007700\"]() == [/span][span style=\"color:#0000BB\"]1[/span][span style=\"color:#007700\"]) {
    echo [/span][span style=\"color:#DD0000\"]\" Done.\"[/span][span style=\"color:#007700\"];
} else {
echo [/span][span style=\"color:#DD0000\"]\"Go!\"[/span][span style=\"color:#007700\"];
}

[/span][span style=\"color:#0000BB\"]?>
[/span]
[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

So I guess MySQL is really picky when you're using $variables and stuff like that.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users