Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

This question already has an answer here:

    <?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?

share|improve this question
2  
Exploits of a mom – knittl Feb 6 '12 at 14:37
Are you sure that your mysql_connect and mysql_select_db worked? Try adding an or die statement for those. – Travesty3 Feb 6 '12 at 14:37
Looking at the "Related" section here, I think there might be some duplicates to this question.. – Znarkus Feb 6 '12 at 14:37
Does var_dump($mysql_connect); output "Ressource xy"? – powtac Feb 6 '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 6 '12 at 14:42

marked as duplicate by Jocelyn, hjpotter92, Jean, Sindre Sorhus, Sébastien Renauld Apr 23 at 17:19

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2 Answers

up vote 5 down vote accepted

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());
share|improve this answer
Thank you! (just testing :)) – Vladislav Il'ushin Feb 6 '12 at 14:52
up vote 0 down vote

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.

share|improve this answer
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 6 '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 6 '12 at 14:45
Why can't he just do UPDATE msg_inbox SET unread = 0 WHERE to = '$user'? – DaveRandom Feb 6 '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 6 '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 6 '12 at 14:50
show 1 more comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.