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>";
            }
        ?>

Save them in a separate file and include them wherever you need — Mr. Alien
– Mr. Alien, Commented May 3, 2013 at 4:53

Dave Chen · Accepted Answer · 2013-05-03 05:30:32Z

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.

edited May 3, 2013 at 5:30

answered May 3, 2013 at 5:02

Dave Chen

11k8 gold badges42 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Michael St Clair Over a year ago

I understand what you mean and I understand your while loop code. However I am not sure how I should setup the database to make it work your way. Do I need to setup a different table, because right now all the variables are stored in the same table as the username so that I can update the variable where the username is?

Michael St Clair Over a year ago

also how do I make it only give a download link if the variable is set to 1

Dave Chen Over a year ago

What does the table of the query '$resultstwo' look like? By the looks of it, it seems that there are columns within the table called 'intro' and 'fightingfires'. I would highly recommending creating a separate table that manages the list of music available on your website.

Dave Chen Over a year ago

I see, so does the user table include columns called 'intro' and 'fightingfires'? If so, wouldn't it be better to have what they want to download as a column, and that column referring to the music table?

Michael St Clair Over a year ago

I got it working and I have used the table you created. However this is for a user system so each user will have different songs available. So I need the tables to be tied somehow so that it can check to see if the user has the rights to that song. So that it checks whether the variable is set to 1 in the user table

|

Caspar Chiquet · Accepted Answer · 2013-05-03 05:16:36Z

1

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.

answered May 3, 2013 at 5:16

Caspar Chiquet

465 bronze badges

6 Comments

Kao Over a year ago

You should never do database queries inside a loop.

Caspar Chiquet Over a year ago

I didn't want to change the code too much from what the poster was asking for. Clearly just beginning with PHP, let's keep things simple.

Michael St Clair Over a year ago

I think you are suggesting the same thing as the other person. The only problem I have now is checking whether or not that user has the variable set in the users table. and @Kao why no queries inside of a loop?

Kao Over a year ago

Because you will be hitting the database multiple times, which is a performance drag. It is much better to build a SQL insert string and insert them all at once.

Kao Over a year ago

See my answer on how to do it by only hitting the DB once.

|

Kao · Accepted Answer · 2013-05-03 06:53:56Z

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.

edited May 3, 2013 at 6:53

answered May 3, 2013 at 5:36

Kao

2,2723 gold badges22 silver badges31 bronze badges

13 Comments

Michael St Clair Over a year ago

ok, I am trying to figure out how to implement this in with the other answers

Michael St Clair Over a year ago

yes this definitely works, now I just need to make it only work if the user has the variable set to 1

Kao Over a year ago

Can you explain a little about the value this user should be setting to 1?

Michael St Clair Over a year ago

So I now have two tables, music and users. Music has the filename and link name, and users has the username, password, etc., and then variables like $intro that if set to 1 they have earned the download. There is later code in my file that if they have points they can buy the download and it sets the variable to 1

Kao Over a year ago

So they earn a point to download any file, or a specific file? And do you store these points in the users table?

|

gxtaillon · Accepted Answer · 2013-05-03 04:55:28Z

0

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.

answered May 3, 2013 at 4:55

gxtaillon

1,0961 gold badge19 silver badges35 bronze badges

Collectives™ on Stack Overflow

Repeat PHP code

4 Answers 4

12 Comments

6 Comments

13 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

12 Comments

6 Comments

13 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related