Repeat PHP code

Question

I have this code that I would like to know if there is an easier way to have it repeated. I have about 60 of these and I will always be adding more so it would be nice if someone could help me figure out a loop or something so that I won't have to manually add each one. Each of these is making a link to a download, so I am guessing I will probably have to use the variable name as the file name. The file name 01 Intro.mp3 is what tells where the download is, so I am guessing to make this work I would also have to change that to the variable name.

Here is the code I use to get the variable names.

while ($row = mysqli_fetch_array($resultstwo))
    {
    extract($row);
    }

Here is the code that I would like to be repeated. I included two of them.

<?php
            if ($intro ==1) {
                $strKey = createKey();
                mysqli_query($resDB,"INSERT INTO downloads (downloadkey, file, expires) VALUES ('{$strKey}', '01 Intro.mp3', '".(time()+(60*60))."')");
                echo "<a href='download.php?key=$strKey'>Intro</a>";
            }
        ?>
        <?php
            if ($fightingfires ==1) {
                $strKey = createKey();
                mysqli_query($resDB,"INSERT INTO downloads (downloadkey, file, expires) VALUES ('{$strKey}', '02 Fighting Fires.mp3', '".(time()+(60*60))."')");
                echo "<a href='download.php?key=$strKey'>Fighting Fires</a>";
            }
        ?>

Answer 1

Building on other answers, here's how to do the loop and hit the database only once.

$query = "INSERT INTO downloads (downloadkey, file, expires) VALUES ";

while ($row = mysql_fetch_array($resultstwo)) {
    $strKey = createKey();
    $time = time()+(60*60);

    $query .= "('{$strKey}', '{$row['filename']}', {$time}),";
    echo "<a href='download.php?key=$strKey'>{$row['caption']}</a>";
}

$query = substr ( $query, 0, -1 ); // Remove the last comma.

mysql_query ( resDB, $query );

The end result of the query will be something like:

INSERT INTO downloads (downloadkey, file, expires) VALUES 
                      ('adfsdfgsdgs', 'Bladerunner.avi', 12345678),
                      ('dfghfyadfsg', 'How_I_met_your_mother.avi', 12345678),
                      ('34t34t3t', 'Simpson_the_movie.avi', 12345678),
                      ('taert43te', '{$row['filename']}', 12345678),
                      ('at43t3t', '{$row['filename']}', 12345678),

This will insert all the values into mysql using this one query only.

Answer 2

I would suggest having some variables within the database if not already included, such as:

id,caption,filename
--------------------
1,Intro,01 Intro.mp3
2,Fighting Fires,02 Fighting Fires.mp3

Here is the SQL query for creating this table:

CREATE TABLE `music` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(32),
  `filename` varchar(32),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uc_filename` (`filename`)
);

As well as some test data that you can also insert.

INSERT INTO `music`(`caption`,`filename`) VALUES('Intro','01 Intro.mp3'),('Fighting Fires','02 Fighting Fires.mp3');

Assuming the user table follows the current format:

id,username,intro,fightingfires
-------------------------------
1,michael,1,NULL
2,dave,NULL,NULL

I believe that it should be altered to show this instead:

id,username,music_id
--------------------
1,michael,1,
2,dave,NULL

The structure of this table can be written as:

CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `username` varchar(32),
 `music_id` int(11),
 PRIMARY KEY (`id`),
 KEY `music_id` (`music_id`),
 CONSTRAINT `users_music_id_ibfk_1` FOREIGN KEY (`music_id`) REFERENCES `music` (`id`) ON DELETE CASCADE
);

Now when a music row is deleted from the music table, any users wishing to download that music will also be removed.

Try to query the database like this now:

$resultstwo=$mysqli->query("SELECT `music_id` FROM `users` WHERE `username`='michael'");

Change the username based on which user is logged in, but I imagine you have that under control.

Then you can manage the data better like this:

while ($row = mysqli_fetch_array($resultstwo)) {
    $strKey = createKey();
    mysqli_query($resDB,"INSERT INTO downloads (downloadkey, file, expires) VALUES ('{$strKey}', '".$row['filename']."', '".(time()+(60*60))."')");
    echo "<a href='download.php?key=$strKey'>".$row['caption']."</a>";
    break;
}

Then you can better manage downloading of music by adding or deleting columns from the table above.

Answer 3

I don't understand completely what you want to do, but I assume the following:

You're reading from a table which contains filenames, and for each file you want to create the entry in the table downloads, as well as output the HTML link to the download.

Here's how you could do it, but it looks like you need to change your table structure for the files to something like the following:

id  track_no  filename

In that case, you could do everything directly in the first loop:

<?php
while($row = mysqli_fetch_array($resultstwo))
{
    $strKey = createKey();
    mysqli_query($resDB, 
        "INSERT INTO downloads (downloadkey, file, expires) 
        VALUES(
            $strKey, 
            $row['track_no']." ".$row['filename'], 
            (time()+(60*60))
        )");
    echo "<a href=\"download.php?key=$strKey\">$row['filename']</a>";        
}

If that doesn't answer your question, you should tell us more about your table containing the files.

Answer 4

How about:

function f($condition, $filename, $prettyname) 
{ 
    if ($condition) 
    {
        $strKey = createKey();
        mysqli_query($resDB,"INSERT INTO downloads (downloadkey, file, expires) VALUES ('{$strKey}', '$filename', '".(time()+(60*60))."')");
        echo "<a href='download.php?key=$strKey'>$prettyname</a>";
    }
}

And then simply recover the values from the database, a file or just an array as you see fit.