Select random row, update column as seen

Question

I want to have a row with a column called: 'seen', then this column will have either a default of no value or a 'yes' value.

So..

Grab one row where it hasn't been 'seen' before.
Update the above row 'seen column' to say 'yes'.
If all rows have the value of 'yes' then a notice/error displays: You have successfully completed all numbers.

I've tried the best I can do achieve it, but it's not working. I think my logic in tackling this may be incorrect?

    include 'DB.php';
    $con = mysqli_connect($host,$user,$pass);
    $dbs = mysqli_select_db($databaseName, $con);

    // Grabs one row where it hasn't been seen before
    $query = mysqli_query("SELECT number, association, image_file, skeleton, sound, colour, comments FROM num_image WHERE seen='' ORDER by rand() LIMIT 1");

    // Updates the above row with the 'seen' column saying 'yes''
    $query = mysqli_query("UPDATE num_image SET seen = yes");

    // Fetches Result
    $thestuff = mysqli_fetch_row($query);


$seenme=$_POST['seen']; // get value of 'seen' column

$result = mysqli_query("SELECT * FROM num_image where seen=$seenme");

// Trying to delivery a message if the enitre 'seen' column is ALL yes.
while($row = mysqli_fetch_row($result))
{
    if($row['seen'] == 'yes')
    { // All numbers seen
      echo 'You have successfully completed all numbers.';
      echo json_encode($thestuff); 
    }
    else
    { // Show numbers
      echo json_encode($thestuff); 
    }
}

Does the SELECT and UPDATE row also have to be an if statement? Cheers

`$query = mysqli_query("UPDATE num_image SET seen = yes");` You know that this will update all the rows of the table? — Agam Banga, Apr 27 '17 at 12:06
@AgamBanga How would I only update only the chosen SELECT'ed record? Is there a way to link both together? — aussiedan, Apr 27 '17 at 12:10
you seem to have been trying to convert your code from mysql_ to mysqli_; it's failing on many levels. You should read the manuals first. — Funk Forty Niner, Apr 27 '17 at 12:10
@aussiedan You need to fetch some primary key `(may be database increment id in your case)` from the `Select` Query & then pass that to `Where` Condition in `Update` — Agam Banga, Apr 27 '17 at 12:12
and the value of `$seenme` and its related POST array is unknown, if it's an integer or a string. — Funk Forty Niner, Apr 27 '17 at 12:12
[Yep I was right,](http://stackoverflow.com/questions/43657196/select-random-row-update-column-as-seen#comment74361991_43657196) seeing your other question http://stackoverflow.com/q/43605094/1415724 that is what you tried to do. Again; visit the official manuals on php.net for the mysqli_ api and you'll see what you did wrong. — Funk Forty Niner, Apr 27 '17 at 12:16
@Fred -ii- thanks, yeah I tried that way but was having issues with sessions, so I'm trying a different approach. With a seen column approach. I'll try and wrap my head around it, just struggling with little time and what seems like it should be easy, is not so much for me haha. Appreciate the messages — aussiedan, Apr 27 '17 at 12:19

score 1 · Accepted Answer · answered Apr 27 '17 at 14:02

1

You have to escape the value in the Update sentence:

$query = mysqli_query("UPDATE num_image SET seen = 'yes' ");

answered Apr 27 '17 at 14:02

Charly

101
1
8

Select random row, update column as seen

1 Answers1