CODEWITHSUNDEEP
phpmysqlsql
Procode
Procode

Reputation: 17

Update the first row mysql php

I'm trying to update my first row in my database. I use the Limit 1 to only update the first row but nothing is happening. There are definitely matching rows but nothing changes in the database.

Here is the code:

foreach ($player_fromsite as $match_player_in_game) {

    //$querytwo = 'INSERT INTO `'.$tablename.'` '.' (`'.$match_player_in_game.'`) '.'VALUES'.'("' . 'yes' . '")';

    $querytwo = 'UPDATE '.$tablename.' SET `'.$match_player_in_game.'` = "'.'yes'.'"   WHERE `'.$match_player_in_game.'` = "'.'NULL'.'" LIMIT 1';

    $querythree = 'UPDATE '.$tablename.' SET `'.$match_player_in_game.'` = "'.'yes'.'" WHERE `'.$match_player_in_game.'` = "'.'NULL'.'" LIMIT 1';

    for($a=0;$a<11;$a++){
        if($match_player_in_game == $home_players[$a]){
            // Insert a row of information into the table "example"
            mysql_query($querytwo) or die(mysql_error());       
       }else{
            mysql_query($querythree) or die(mysql_error());
       }
    }
}

Is the query correct?

Upvotes: 1

Views: 961

Answers (3)

Mike Brant
Mike Brant

Reputation: 71414

You need to define "first row". First row based on an autoincrementing id value? First based on a timestamp date? You need to specify this as MySQL has no concept of "first row".

For example, if you do something like this in MySQL:

SELECT * FROM table LIMIT 1

You are not guaranteed to get the same record back each time.

Most likely you will need to specify an ORDER BY condition on a key column, as without it, you have no guarantee of which row your LIMIT 1 will apply to. I really can't think of a case where one might use LIMIT without an ORDER BY clause, as the two really go hand in hand.

So your query should look like:

UPDATE table
SET field = 'yes'
WHERE field IS NULL
ORDER BY some_key_field ASC
LIMIT 1

Note that even this query would not update the same row every time. It would update the first record (as specified by ORDER BY) that has a NULL value for the specified field. So if you ran this query 10 times, it would change 10 different records (assuming there are that many records with NULL values).

Upvotes: 0

mdoyle
mdoyle

Reputation: 727

NULL isn't a string, so you shouldn't be using = 'NULL', unless you actually set it to that string value. Use IS NULL instead.

Upvotes: 0

Joel
Joel

Reputation: 3060

In MySQL use IS NULL to compare with NULL.

For example: "UPDATE table SET field = 'yes' WHERE field IS NULL"

Upvotes: 1

Related Questions