kaldi kaldi - 5 years ago 129
SQL Question

How to permanently append user input to a variable in PHP from a SQL table

Any help would really be appreciated. What I'm trying to is have users add songs to the playlist of the SCM player that I have in my forum. So this is the control flow I've got right now. The first part is getting the user input.

<form action="/phpbbauto/scmplayersend.php" method="post">
<?php
echo '<input class="searchbox" type="text" name="liensc" placeholder="Add to Playlist..." /><input class="searchbutton" type="submit" value="&#xf067" onclick=$onc title="Search" />';
?>
</form>


That works perfectly. Now here's the php file it send the data to.

<?php

define('DB_NAME', 'xxxxxxxxxx');
define('DB_USER', 'xxxxxxxxxx');
define('DB_PASSWORD', 'xxxxxxxxxx');
define('DB_HOST', 'xxxxxxxxxx');

$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);

if(!$link){
die('Could not connect: ' . mysql_error());
}

$db_selected = mysql_select_db(DB_NAME, $link);

if(!db_selected){
die('Can\'t find ' . DB_NAME . ': ' . mysql_error());
}

//Connection Established//Data Hander Below This Line//

$valone = $_POST['liensc']; //Data from form stored//

$sql = "INSERT INTO demo (liensc) VALUES ('$valone')";

if(!mysql_query($sql)){
die('Error: '. mysql_error());
}

mysql_close();

?>


That works fine as well. Now here's the two problems I'm having.

<!-- Song Player http://playlist.me -->
<script type="text/javascript" src="http://playlist.me/w/script.js"
data-config="{
'skin':'skins/black/skin.css',
'volume':10,
'autoplay':true,
'shuffle':true,
'repeat':1,
'placement':'top',
'showplaylist':false,
'playlist':[
<?php
define('DB_NAME', 'xxxxxxxx');
define('DB_USER', 'xxxxxxxxx');
define('DB_PASSWORD', 'xxxxxxxxxx');
define('DB_HOST', 'xxxxxxxxx');

$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);

if(!$link){
die('Could not connect: ' . mysql_error());
}

$db_selected = mysql_select_db(DB_NAME, $link);

if(!db_selected){
die('Can\'t find ' . DB_NAME . ': ' . mysql_error());
}

$appe = "SELECT liensc FROM demo";
$tshell = ",{'title':'$appe','url':'$appe'}";
$stringco = "{'title':'Eddy Christiani and Frans Poptie - Rythm For You','url':'https://www.youtube.com/watch?v=E3sOnASmR24'},";

echo $stringco . $tshell;

$sql = "DELETE FROM demo WHERE ID=1";

if(!mysql_query($sql)){
die('Error: '. mysql_error());
}

mysql_close();?>
]}" >
</script>
<noscript><a href="http://playlist.me/songs/">player playlist</a></noscript>
<!-- playlist.me script end -->


First problem: Yes , it actually appends. Problem is doesn't append the value inside the column, only the actual SQL command. How can I fix this?

Second problem: It appends, but I am not sure it will be permanent when the next value get appended.

Any help will be appreciated. I've worked on this all day and because I'm a beginner I've just driven myself into a wall. I think I'm close, but I also could be super far away from the actual answer.

Thank you.

Answer Source

Here are some things I noticed on your code:

  • The most important thing here is to use PDO and its API, as it will help you to prevent SQL injection attacks on your site.

  • As noted by @Havenard in the comments, you're printing your SQL query instead of executing and getting the results.

  • It appears that your playlist.me plugin expects the title and url of the song, but your form only asks for one of those.

  • To get the correct output for your plugin, you need to get the rows from your database and print them with json_encode(), which will output something like:

[{'title':'whatever song','url':'http://theaddress'},{'title':...

I rewrote your code with these suggestions in mind:

add songs form

<form action="scmplayersend.php" method="post">
    <input class="searchbox" type="text" name="title" placeholder="Title of song" />
    <input class="searchbox" type="text" name="url" placeholder="Url" />
    <input class="searchbutton" type="submit" value="&#xf067" title="Search" />
</form>

scmplayersend.php

<?php
if (count($_POST)>0) {
    $server = "myServer"; $database = "DbName"; $username = "myself"; $password = "secret";
    $conn = new PDO("mysql:host=$server;dbname=$database", $username, $password);

    $title = $_POST['title'];
    $url = $_POST['url'];

    $sql = "INSERT INTO demo (title, url) VALUES (:title, :url)";
    $statement = $conn->prepare($sql);
    $statement->bindValue(":title", $title, PDO::PARAM_STR);
    $statement->bindValue(":url", $url, PDO::PARAM_STR);
    $success = $statement->execute();

    if (!$success) {
        exit("<pre>\nPDO error: " . $statement->errorInfo()[2] . "</pre>");
    }

    exit('Song added successfully. <a href="' . $_SERVER["SCRIPT_NAME"] . '">Go back</a>');
}
?>

player.php

<!-- Song Player http://playlist.me -->
<script type="text/javascript" src="http://playlist.me/w/script.js" 
data-config='{
    "skin":"skins/black/skin.css",
    "volume":10,
    "autoplay":true,
    "shuffle":true,
    "repeat":1,
    "placement":"top",
    "showplaylist":false,
    "playlist":<?php  

$server = "myServer"; $database = "DbName"; $username = "myself"; $password = "secret";
$conn = new PDO("mysql:host=$server;dbname=$database", $username, $password);

$sql = "SELECT title, url FROM demo";
$statement = $conn->prepare($sql);
$statement->execute();

$result = $statement->fetchAll(PDO::FETCH_ASSOC);

echo json_encode($result);
?>'>
</script>
<noscript><a href="http://playlist.me/songs/">player playlist</a></noscript>
<!-- playlist.me script end -->
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download