将数据库转换为JSON

I have a method of creating JSON into an array that looks like this:

[{"date":"","name":"","image":"","genre":"","info":"","videocode":""},{...},{...}]

I first tried getting the data from a html page (not the database) like this:

$arr = array();

$info = linkExtractor($html);
$dates = linkExtractor2($html);
$names = linkExtractor3($html);
$images = linkExtractor4($html);
$genres = linkExtractor5($html);
$videocode = linkExtractor6($html);

for ($i=0; $i<count($images); $i++) {
    $arr[] = array("date" => $dates[$i], "name" => $names[$i], "image" => $images[$i], "genre" => $genres[$i], "info" => $info[$i], "videocode" => $videocode[$i]);
}

echo json_encode($arr);

Where each linkExtractor looks a bit like this - where it grabs all the text within a class videocode.

function linkExtractor6($html){ 
    $doc = new DOMDocument(); 
    $last = libxml_use_internal_errors(TRUE); 
    $doc->loadHTML($html); 
    libxml_use_internal_errors($last); 
    $xp = new DOMXPath($doc); 
    $result = array(); 
    foreach ($xp->query("//*[contains(concat(' ', normalize-space(@class), ' '), ' videocode ')]") as $node) 
        $result[] = trim($node->textContent); // Just push the result here, don't assign it to a key (as that's why you're overwriting)

    // Now return the array, rather than extracting keys from it
    return $result; 
}

I now want to do this instead with a database.

So I have tried to replace each linkExtractor with this - and obviously the connection:

function linkExtractor6($html){ 
    $genre = mysqli_query($con,"SELECT genre
    FROM entries
    ORDER BY date DESC");

    foreach ($genre as $node) 
            $result[] = $node; 
    return $result; 
} 

But I am getting the error:

Invalid argument supplied for foreach()

Avoid redundancy and run a single SELECT

function create_json_db($con){ 
    $result = mysqli_query($con,"SELECT date, name, image, genre, info, videocode
                                 FROM entries
                                 ORDER BY date DESC");

    $items= array();
    while ($row = mysqli_fetch_assoc($result)) {
       $items[] = $row;
    }

    return $items ; 
} 

Try to use this. More info in the official PHP documentation:

function linkExtractor6($html){ 
    $result = mysqli_query($con,"SELECT genre
    FROM entries
    ORDER BY date DESC");

    $items = array();
    while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
       $items[] = $row;
    }

    return $items; 
} 

First, you are not iterating through your results via something like mysqli_fetch_array. So here is the function with mysqli_fetch_array in place. But there is a much larger issue. Read on.

function linkExtractor6($html){ 
    $result = mysqli_query($con,"SELECT genre
    FROM entries
    ORDER BY date DESC");

    $ret = array();
    while ($row = mysqli_fetch_array($result)) {
       $items[] = $row;
    }

    return $ret ; 
} 

Okay, with that done, it still won’t work. Why? Look at your function. Specifically this line:

$result = mysqli_query($con,"SELECT genre

But where is $con coming from? Without a database connection mysqli_query will not work at all. So if you somehow have $con set outside your function, you need to pass it into your function like this:

function linkExtractor6($con, $html){ 

So your full function would be:

function linkExtractor6($con, $html){ 
    $result = mysqli_query($con,"SELECT genre
    FROM entries
    ORDER BY date DESC");

    $ret = array();
    while ($row = mysqli_fetch_array($result)) {
       $items[] = $row;
    }

    return $ret ; 
} 

Remember, functions are self-contained & isolated from whatever happens outside of them unless you explicitly pass data into them.