I'm having trouble with pulling database information from 'rownum' column and putting it into an array and then using that array information for my next query that randomly selects one line of the array and then displays it.
<?php
// Connect to database
include 'DB.php';
$con = mysqli_connect($host,$user,$pass);
$dbs = mysqli_select_db($databaseName, $con);
// Select Rownum to get numbers and only where there is no value in seen.
$firstquery = "SELECT rownum FROM num_image WHERE seen=''";
// If there are results store them here
$result = mysqli_query($firstquery) or die ("no query");
// Put the results taken from the table into array so it displays as: array(56, 44, 78, ...) etc...
$result_array = array();
while($row = mysqli_fetch_assoc($result))
{
$result_array[] = $row;
}
// Select the data I require
$query = mysqli_query("SELECT number, association, image_file, skeleton, sound, colour, comments FROM num_image WHERE rownum='$row' LIMIT 1;");
$test = mysqli_query("UPDATE num_image SET Seen='yes' WHERE rownum='$row';");
// Fetch Results of Query, Ignore test.
$arrayss = mysqli_fetch_row($query);
// Echo Results as a Json
echo json_encode($arrayss);
?>
I'm not sure what I have done wrong? Does the array have to be echoed and then my $query line calls that instead?
Updated code - Solved my problem
Thanks for tips guys, it helped me wrap my head around it and came up with a working solution.
<?php
// Connect to database
include 'DB.php';
$con = mysqli_connect($host,$user,$pass);
$dbs = mysqli_select_db($databaseName, $con);
// Select Rownum to get numbers and only where there is no value in seen.
$firstquery = "SELECT rownum FROM num_image WHERE seen=''";
// If there are results store them here
$result = mysqli_query($firstquery) or die ("no query");
// Put the results taken from the table into array so it displays as: array(56, 44, 78, ...) etc...
$result_array = array();
while($row = mysqli_fetch_assoc($result))
{
$result_array[] = $row;
}
for ($i = 0; $i < count($result_array); $i++) {
$all_rownums[] = implode(',', $result_array[$i]);
}
//pick a random point in the array
$random = mt_rand(0,count($all_rownums)-1);
//store the random question
$question = $all_rownums[$random];
// Select the data I require
$query = mysqli_query("SELECT number, association, image_file, skeleton, sound, colour, comments FROM num_image WHERE rownum='$question' LIMIT 1;");
$test = mysqli_query("UPDATE num_image SET Seen='yes' WHERE rownum='$question';");
// Fetch Results of Query, Ignore test.
$arrayss = mysqli_fetch_row($query);
// Echo Results as a Json
echo json_encode($arrayss);
?>
This part is what helped me solve it:
for ($i = 0; $i < count($result_array); $i++) {
$all_rownums[] = implode(',', $result_array[$i]);
}
Happy Dance