Loop output within another loop with SQL and PHP

Question

Loop output within another loop with SQL and PHP

Navigation

#1 by (5 votes)
#2 by (4 votes)
#3 by (2 votes)

2

I have these values in my SQL database, I want an output this way and this file in the browser using PHP

Database structure with the values I want on the output My attempts, although it does not help you at all: pastebin.com/6tCNbpY4

DATABASE schedule

TABLE stream_playlist
id  streamname      |   playlistname |  repeat |  scheduled
=====================================================================
1   stackoverflow   |   plname1      |  true   |  2009-12-11 16:25:05
2   other_stream    |   plother2     |  false  |  2011-11-11 17:01:11



TABLE videos_for_stream_playlist
id |  streamname     | src                    |  start |  length
================================================================
1  |  other_stream   | http:/local/demo.mp4  |  60    |  -1
2  |  stackoverflow  | C:\sample.mp4          |  0     |  20
3  |  other_stream   | otherlocation/mystream |  0     |  -1

The output must be written to a .txt file and displayed in the browser when executing the PHP script. Videos can be sorted by id .

<smil>
  <body>

    <stream name="stackoverflow"></stream>

    <playlist name="plname1" playOnStream="stackoverflow" repeat="true" scheduled="2009-12-11 16:25:05">
        <video src="C:\sample.mp4" start="0" length="20"/>
    </playlist>


    <stream name="other_stream"></stream>

    <playlist name="plother2" playOnStream="other_stream" repeat="false" scheduled="2011-11-11 17:01:11">
        <video src="http:/local/demo.mp4" start="60" length="-1"/>
        <video src="otherlocation/mystream" start="0" length="-1"/>
    </playlist>

  </body>
</smil>

How can I do this? I can not give a functional loop inside another loop , everything duplicates.

php sql xml

asked by anonymous 26.05.2014 / 18:41

3 answers

5

Well, the way it was marked as a solution I see a problem, you will call your database several times to receive each row of the first table its corresponding in the second table, ie consumption.

Your Select could be done differently.

SELECT sp.*, vsp.* FROM stream_playlist sp INNER JOIN videos_for_stream_playlist vsp ON sp.streamname = vsp.streamname

That way your select will return exactly what you want and with both tables online.

It would look something like this.

TABELA DE RETORNO.
id  streamname      |   playlistname |  repeat |  scheduled             | id |  streamname     | src                    |  start |  length
=====================================================================================================================================
2   other_stream    |   plother2     |  false  |  2011-11-11 17:01:11   | 1  |  other_stream   | http:/ local/demo.mp4  |  60    |  -1
1   stackoverflow   |   plname1      |  true   |  2009-12-11 16:25:05   | 2  |  stackoverflow  | C:\sample.mp4          |  0     |  20
2   other_stream    |   plother2     |  false  |  2011-11-11 17:01:11   | 3  |  other_stream   | otherlocation/mystream |  0     |  -1

There will only be one problem with this command that I made, the names of some fields will repeat themselves, so the ideal thing is to do naming the fields directly.

SELECT sp.ID as spID, sp.streamname as spStreamName, sp.playlistname as spPlayListName ... FROM stream_playlist sp INNER JOIN videos_for_stream_playlist vsp ON sp.streamname = vsp.streamname

Preferably omit one of the streamname fields since they will be exactly the same.

27.05.2014 / 14:13

4

Check the source code of the file. If you want to store in a text file, just store all echo in a variable and write to the file.

<!DOCTYPE html>
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title></title>
</head>
<body id="page">
    <?php
    $con = new PDO("mysql:host=localhost;dbname=config_server_schedule",     "root", "suporte");

    $rs = $con->query("SELECT * from stream_playlist");
    echo "<pre>" . "\n";
    echo '  <smil>' . "\n";
    echo '  <body>' . "\n";

    $streamname = array();

    while ($row = $rs->fetch(PDO::FETCH_OBJ)) {
        $streamname[] = $row;
    }
    foreach ($streamname as $name) {
        //print_r($name);
        echo "\n" . '</playlist>' . "\n";
        echo "<stream name=\"{$name->streamname}\"></stream>" . "\n";

        $rs = $con->prepare("SELECT * FROM videos_for_stream_playlist WHERE streamname=?");
        $rs->bindParam(1, $name->streamname);
        $rs->execute();
        ?>
    <playlist name="<?php echo $name->playlistname; ?>" playOnStream="<?php echo $name->streamname; ?>" repeat="<?php echo $name->repet; ?>" scheduled="<?php echo $name->scheduled; ?>">
        <?php
        while ($result = $rs->fetch(PDO::FETCH_OBJ)) {
            //print_r($result);
            ?>
            <video src="<?php echo $result->src; ?>" start="<?php echo $result->starte; ?>" length="<?php echo $result->length; ?>"/>
            <?php
        }
        echo "\n" . '</playlist>' . "\n";
    }
    echo '  </smil>' . "\n";
    echo '  </body>' . "\n";
    ?>

</body>
</html>

26.05.2014 / 23:31

Equations in module How to use Python to search and store Stack Overflow API data?

score 2 · Accepted Answer

Example:

It works like this, I get all the items in the stream_playlist table, make a foreach in it, and in each item it calls another SQL of the relation table videos_for_stream_playlist through the streamname field.

<smil>
  <body>
<?php
    $pdo   = new PDO('mysql:dbname=testdb;host=127.0.0.1', 'root', 'senha');
    $query = $pdo->query('SELECT * FROM stream_playlist');
    foreach($query as $row) {
?>
    <stream name="<?php echo $row['streamname'];?>"></stream>
    <playlist name="<?php echo $row['playlistname'];?>" 
              playOnStream="<?php echo $row['streamname'];?>" 
              repeat="<?php echo $row['repeat'];?>" 
              scheduled="<?php echo $row['scheduled'];?>">
<?php
    $sts = $pdo->prepare("SELECT * FROM videos_for_stream_playlist WHERE streamname=?");
    $sts->bindValue(1,$row['streamname'], PDO::PARAM_STR);
    $sts->execute();
    $result = $sts->fetchAll(PDO::FETCH_ASSOC);
    foreach($result as $str){
?>    
        <video src="<?php echo $str['src'];?>" start="<?php echo $str['start'];?>" length="<?php echo $str['length'];?>"/>
<?php
    }
?>
    </playlist>    

<?php
    }
?>
  </body>
</smil>

Note: This table model could be related to the PrimaryKey of the parent table because the search would be faster, performative not bringing future problems, that is, without normalization

Another way would be with only SQL doing a Join and returning associated data by specific key (in fetchAll of PDO , this PDO::FETCH_ASSOC | PDO::FETCH_GROUP join).

<smil>
  <body>
<?php
    function Head($name,$playlistname,$repeat,$scheduled){
        return PHP_EOL.'<stream name="'.$playlistname.'"></stream>'.PHP_EOL.
              '<playlist name="'.$name.'" playOnStream="'.$playlistname.'" repeat="'.$repeat.'" scheduled="'.$scheduled.'">';
    }
    function Center($str){
        return PHP_EOL.'<video src="'.$str['src'].'" start="'.$str['start'].'" length="'.$str['length'].'"/>';
    }

    $pdo = new PDO('mysql:dbname=testdb;host=127.0.0.1', 'root', 'senha');
    $sts = $pdo->prepare('SELECT a.streamname, b.streamname, a.playlistname, 
    a.repeat, a.scheduled, b.src, b.start, b.length
    FROM testdb.stream_playlist a inner join 
    videos_for_stream_playlist b on b.streamname=a.streamname 
    ORDER BY a.streamname asc');
    $sts->execute();
    $res = $sts->fetchAll(PDO::FETCH_ASSOC | PDO::FETCH_GROUP);     
    //var_dump($res);die();                
    foreach($res as $idx=>$value){          
        $repeat       = '';
        $scheduled    = '';
        $center       = '';
        $streamname   = '';
        $playlistname = '';
        foreach($value as $str){
            $center .= Center($str);            
            $repeat       = $str['repeat'];
            $scheduled    = $str['scheduled'];
            $streamname   = $str['streamname'];
            $playlistname = $str['playlistname'];           
        }
        echo Head($playlistname,$idx,$repeat,$scheduled); 
        echo $center;
        echo PHP_EOL.'</playlist>'.PHP_EOL;
    }    
?>
  </body>
</smil>