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

Question

    <?php
    session_start();
    $user = $_SESSION['login'];
    $mysql_connect = mysql_connect("localhost", "root", "");
    $mysql_select_db = mysql_select_db("site");
    $sql = "SELECT * FROM `msg_inbox` WHERE `to` = '$user'";
    $result = mysql_query($sql) or die(mysql_error());
    while ($row = mysql_fetch_array($result)) {    
            $mysql_connect = mysql_connect("localhost", "root", "");
            $mysql_select_db = mysql_select_db("site");
            $query = ("UPDATE msg_inbox  SET unread = 0 WHERE id= ".$row['id']);
            $result = mysql_query($query);
    }

And I receive error: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in on line 8 Can you help me?

Are you sure that your `mysql_connect` and `mysql_select_db` worked? Try adding an `or die` statement for those. — Travesty3, Feb 06 '12 at 14:37
Looking at the "Related" section here, I think there might be some duplicates to this question.. — Markus Hedlund, Feb 06 '12 at 14:37
Answer of DaveRandom tells everything. Another problem is sometimes, using a bit more complex querys, that the query is not valid. In these cases, just output the query, copy and paste it and execute it manually in your database (preferred PhpMyAdmin), this will show you the errors. — Florian Müller, Feb 06 '12 at 14:42

DaveRandom · Accepted Answer · 2012-02-06T14:45:11.267

You have reassigned $result in your loop. After the first iteration, the variable $result will hold a boolean indicating the success or failure of your UPDATE statement.

Change:

$result = mysql_query($query);

To:

$result2 = mysql_query($query);

Or just:

mysql_query($query);

And be careful of SQL injection holes.

EDIT actually, your whole code could and should be shortened to:

<?php

  session_start();
  $user = $_SESSION['login'];
  // A blank password for root? Really?
  $mysql_connect = mysql_connect("localhost", "root", "");
  $mysql_select_db = mysql_select_db("site");
  $sql = "
    UPDATE `msg_inbox`
    SET `unread` = 0
    WHERE `to` = '".mysql_real_escape_string($user)."'
  ";
  // Don't show the result of mysql_error() in a production environment!
  $result = mysql_query($sql) or die(mysql_error());

score 0 · Answer 2 · answered Feb 06 '12 at 14:40

0

You inner query is overwriting $result, causing the outer loop to fail.

As well, you're opening a new connection inside the loop. This is highly inefficient and wasteful. Let's say your main query finds 500 rows. That means you're opening 500 connectsion to mysql. This is utterly pointless.

answered Feb 06 '12 at 14:40

Marc B

356,200
43
426
500

Not only that, he is looping a result set and updating the table the results came from based solely on the IDs of the returned rows - the whole thing can be summed up in a single `UPDATE`. – DaveRandom Feb 06 '12 at 14:44
@dave: not really. the update would be writing to the same table which the select is coming from. Can't be done in mysql as a `update ... where in (select from...)` type query. There are workarounds, of course. – Marc B Feb 06 '12 at 14:45
Why can't he just do `UPDATE msg_inbox SET unread = 0 WHERE to = '$user'`? – DaveRandom Feb 06 '12 at 14:46
Ah yeah... good point. I think I'll just slink off to get a coffee and finish waking up. – Marc B Feb 06 '12 at 14:48
See I am cheating by living in a timezone where it's nearly 3pm. Doesn't mean I'm awake, of course... – DaveRandom Feb 06 '12 at 14:50
@DaveRandom: Well, to get incredibly nitpicky, if the database had such a terrible design as there may be duplicate IDs, then you might need a `where in` clause in order to update all records that have the same ID as `$user`, so if the database design was terrible and they were trying to do something dumb...technically he's right :-) – Travesty3 Feb 06 '12 at 14:54

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

2 Answers2