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="" 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.
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
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="" 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