Loop output within another loop with SQL and PHP

Asked

Viewed 1,143 times

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 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 running the PHP script. Videos can be ordered 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 that? I can’t give one loop functional within another loop, everything comes out duplicate.

php sql xml

  • From the looks of it, you need 2 separate loops, not one inside the other. And if you really need the txt intermediate, that’s two very different questions. One is how to generate txt from SQL, another is to show txt in the browser. From what I saw of your code, you better solve the first table, and after solved, make the code to solve the second, taking advantage of the connection. In the same PHP, but one part after the other, and not mixing.

  • You mainly need to explain better what you want and what is working or not. Break down the problem in steps and ask one at a time. For example, can you connect to DB and read the data? If you don’t just ask about that part. If so, go to the next step. The way you’re asking, someone’s gonna have to write the whole program for you to solve.

  • Sorry for my arrogance. Thank you @Bacco I will follow this example of yours, I have set the code and I think I already have an idea of how to start doing it. Thank you.

  • it would no longer be practical to query only and have a single loop ?

  • I don’t think @Otto, because as his friend Harry Potter replied, there’s a loop for videos also, what I already knew I had to have, but I did not imagine how it could be.

3 answers

5

Well, the way it was marked as a solution I see a problem, you will call several times your database 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

This way your select will return exactly what you want and with the two tables in line.

It would be something like.

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 a problem in this command I made, the names of some fields will repeat, so the ideal is to name 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 it will surely be exactly the same.

  • I just wanted to remind you that if you have a better answer regarding the question, post, and are graced with votes or even change the answer to the question for you, what matters here is to create a repository of questions and answers that can be used by several people. No use putting half answer, because the user also wanted output output from PHP with the joining of the tables. I understand what you said, I can agree upon factors, but, your answer is still incomplete. Don’t get me wrong, it was just a way of saying what Sopt expects of us, thank you.

  • It is not a half answer, just a demonstration of an approach that does not generate as many accesses to the BD and has the same output, as the answers that are correct or wrong to your is there, correct and marked as such.

4

Check the source code of the file. If Voce wants to store in a text file, just store all the echo into 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>

  • Yeah, I’m wearing something like that. $output .= "";&#xA;$output = "Meu códido";&#xA;echo $output; Soon, I get the result and I can manage it according to what I need.

2


Example:

It works like this, I get all the items from the table stream_playlist, make a foreach on it, and in each item calls another SQL of the relationship table videos_for_stream_playlist by 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 mother table because the research would be faster, performing not bringing future problems, ie, is without normalization

Another way would be with just one SQL doing a Join and returning associated data by specific key (no fetchAll of PDO, that junction PDO::FETCH_ASSOC | PDO::FETCH_GROUP).

<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>

Browser other questions tagged php sql xml

You are not signed in. Login or sign up in order to post.