Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

Posted by Mauro Rinaldi
Mauro Rinaldi
645 Points

updating MySql DB with php scripts

Hi everybody,

In my DB among others, I have these two tables: 'songs' and'Votes'. In the table 'votes' are stored fields 'value','songId' and 'idUser'. In the table 'songs' are present various descriptive fields (title, author, album, etc ...) as well the field 'score', which is the sum of the votes (field'Value') obtained from the song. in PHPMyAdmin I wrote a query to update the field 'score': ...UPDATE 'songs' SET score = (SELECT SUM (value) FROMvotes WHERE songs.songId =votes.songId)... and it works. Now, I would like to find a way to refresh the page automatically, whenever there are new votes, and I wrote this method within the class 'Songs':

<?php
calculate_score public function () {
       global $database;
     $sql ​​= mysqli_query ($database->connection, "UPDATE" .self :: $table_name. "SET". $this->score. "=(SELECT SUM (value) FROM `voti`WHERE songs.songId = votes.songId) ");
     
     return $sql;
    }
?>

the above method is called in the page lista_brani.php

<table>
        <tr>
            <th>Title</th>
            <th>Author</th>
            <th>Album</th>
            <th>Year</th>
            <th>score</th>
            <th>file</th>
            <th>Listen</th>
            <th>Rate</th>
        </tr>

     <?php foreach($songs as $song):  ?>
    
        <tr>
            <td><?php echo $song->title; ?></td>
            <td><?php echo $song->author; ?></td>
            <td><?php echo $song->album; ?></td>
            <td><?php echo $song->year; ?></td>
            <td><?php echo $song->calculate_score(); ?></td>
            <td><?php echo $song->filename; ?></td>
            <td><audio controls="true"><source src="../<?php echo $song->file_path(); ?>"/></audio </td>
            <td><a href="vote_song.php?songId=<?php echo $song->songId; ?>&IdUser=<?php echo ($_GET['idUser']); ?>"> <button type="button">rate!</button></td></a>
        </tr>
         <?php endforeach; ?>
    </table>

But when I run the page, the 'score' column is empty, and no error message is shown. What's wrong?

Greg Kaleka
Greg Kaleka
39,021 Points
Greg Kaleka
Greg Kaleka
39,021 Points

Hey, can you surround your code with back tics instead of periods? It's the key with the ~.

Colin Marshall
Colin Marshall
32,861 Points
Colin Marshall
Colin Marshall
32,861 Points

I just spent awhile cleaning up your code. There were unnecessary spaces in almost every tag. You have spaces in your between the $ and the variable name which would prevent any of your variable calls from working. You also used a lot of capital letters where it should be all lowercase.

I can't figure out how to get your last block of code to display correctly on the forum. Another mod is going to have to step in and help us out.

Ricky Catron
Ricky Catron
13,023 Points
Ricky Catron
Ricky Catron
13,023 Points

I took a shot at it too Colin....no dice. I can't figure out what the issue is. Maybe malformed PHP tags?

--Ricky

Colin Marshall
Colin Marshall
32,861 Points
Colin Marshall
Colin Marshall
32,861 Points

Thanks Ricky. I thought it might be something wrong with a tag somewhere but could not find it.

Colin Marshall
Colin Marshall
32,861 Points
Colin Marshall
Colin Marshall
32,861 Points

I guess it is time to call in a forum legend to help us sort this mess out. @James Barnett, any idea why this last block of code won't display properly?

2 Answers

Jose Soto
Jose Soto
23,407 Points
Jose Soto
Jose Soto
23,407 Points

It's hard to say since your formatting needs a little work, but my first guess is to modify some spaces in the sql query:

<?php
//instead of this:
$sql ​​= mysqli_query ($database->connection, "UPDATE" .self :: $table_name. "SET". $this->score. "=(SELECT SUM (value) FROM `voti`WHERE songs.songId = votes.songId) ");

//trythis:
$sql ​​= mysqli_query($database->connection, "UPDATE " . self::$table_name . " SET " . $this->score . " = (SELECT SUM(value) FROM `votes` WHERE songs.songId = votes.songId) ");

//Your query should look something like this:
//UPDATE `songs` SET `score` = (SELECT SUM(`value`) FROM `votes` WHERE `songs`.`songId` = `votes`.`songId`)
Mauro Rinaldi
Mauro Rinaldi
645 Points

GREAT! IT WORKS! Thanks a lot to Jose, Greg...and the magic back tics!

Mauro Rinaldi
Mauro Rinaldi
645 Points
Mauro Rinaldi
Mauro Rinaldi
645 Points

Sorry! I apologize to everyone. I am Italian, and to make more understandable my question and code (class names, variables and methods were all in italian) I used the google translator, but when I pasted in the textbox everything was formatted strangely... I tried to correct, but it was late at night and after a while I gave up. however, I can assure you that there are no spaces between the $ and the variable names, and in general, that there are no syntax errors in these blocks of code (Netbeans IDE don't give me any error message about it). I rewrite the php code block. this is the method in the class 'Song':

 public function calculate_score()   {
       global $database;
     $sql = mysqli_query($database->connection, "UPDATE".self::$table_name. " SET " .$this->score. " =(SELECT SUM(value) FROM `votes`WHERE songs.songId = votes.songId)");
          return  $sql;      
    }

This is a part of the php web page which shows the list of songs in the database:

$Songs = song::find_all();
<?php foreach ($Songs as $song): 

          ?> 
            <td><?php echo $song->title;?></td>
            <td><?php echo $song->author;?></td>
            <td><?php echo $song->album;?></td>
            <td><?php echo $song->year;?></td>
            <td><?php echo $song->calculate_score(); ?></td>
            <td><?php echo $bsong->filename;?></td>
            <td><audio controls="true"><source src="../<?php echo $song->file_path();?>"/></audio></td>
            <td><a href="vote_song.php?songId=<?php echo $song->songId; ?>&idUser=<?php echo ($_GET['idUser']); ?>"><button type="button">rate!</button></td></a>
        </tr>


          <?php endforeach; ?>

I inserted the method here. The problem is this: if I put the reference to the variable $score , it works, and shows me the score values (but not updated). If I put the method, the page shows me empty spaces in the score column, without giving any error message... thanks again for your patience.

Mauro