CODEWITHSUNDEEP
phpmysql
AKor
AKor

Reputation: 8892

Checking to see if a MySQL row is populated

I have a page that writes to a MySQL table. The table has a set amount of rows (24).

I have an $id variable that's set by a rand() function. I basically want to pull the row at that $id, so if $id was 3, I want to pull the third row. Then, I want to check if there is a price set at that row (indicating that the row is being used). If there is no price, I want to keep $id at the value it has been set at and proceed with the query. If there is a price, I want to re-randomize the $id variable, and check again if that row is used up. When it finds an empty row, proceed with the query.

My solution semi-works, but it seems to have a <10% chance of overwriting a used row, for some reason. I want it to never overwrite a used row.

Here's my code:

    mysql_select_db("delives0_booklet", $con);

    $query = "SELECT * FROM booklet WHERE id = '$id'";

    $res = mysql_query($query,$con);

    $newId = $id;

    while($row = mysql_fetch_array($res))
    {
        if($row['price'] != 0)
        {
            do{
                $newId = rand(1, 24);
            }while($newId == $id);
        }
    }
    $id = $newId;
    mysql_query("UPDATE booklet SET price = '$price', advertiser = '$advertiser', image = '$image', monthsRemaining = '$monthsRemaining', availability = 1 WHERE id = '$id'");

Edit I had the idea to do this. I loop through the table and I put the 'id' of each unfilled spot into an array. Then I pick randomly from that array. However, there seems to be a bug that I can't find, since the array keeps showing as having nothing in it, even after the loop is run, and $i is the correct figure.

        mysql_select_db("delives0_booklet", $con);

    $query = "SELECT * FROM booklet";

    $res = mysql_query($query,$con);
    $i = 0;
    $isEmpty = array();

    while($row = mysql_fetch_array($res))
    {

            if($row['price'] == 0)
            {
                $isEmpty[i] = $row['id'];
                $i = $i + 1;
            }
    }
    echo $i . " unfilled spots.";
    $n = 0;
    while($n<$i)
    {
        echo $isEmpty[$n];
        $n = $n + 1;
    }
    if($i > 0)
    {
        $id = $isEmpty[rand(0, $i)];
    }
    if($i == 0)
    {
        echo 'All spots have been filled.';
    }

Upvotes: 1

Views: 171

Answers (2)

Edwin van Maastrigt
Edwin van Maastrigt

Reputation: 436

If I understand the problem correct, this should work:

SELECT * 
FROM booklet
WHERE price = 0 OR price IS NULL
ORDER BY RAND()

Upvotes: 1

john personna
john personna

Reputation: 442

I think it is a top level logic problem. Because you populate with random ids, you can get duplicate ids, and so when you update "WHERE id = '$id'" you may be picking up rows already populated.

I don't know your goal, but perhaps using an auto-increment id, and dropping rows that you want to get rid of, is the way to go. A rolling set of rows (24 at a time) but with ever increasing ids, would prevent mistaking one for the other.

Upvotes: 1

Related Questions