How to select specific data from one table and insert a part of it back to another table by user action

Question

I am pretty new to PHP and MySQL. Anyway, I currently try to write an application for Karaoke events.

The visitors of the event should be able to search online in a database for a specific song they would like to sing and then should be able to add this song to a kind of queue. The queue can then be display on a screen so the visitors can see how many people are singing before them and what they are singing etc.

My msql database is pretty simple. The table is called "songs" and I have 4 columns

id, title, artist, language

So first thing I need is a search form in which the people can type in any kind of searchterm and they get back the matching songs. It should not matter weather they search for artist or song, so they could type in "Love" as well es "Beatles" ot get the matching results.

So if the searchterm is "love" for example, the visitor gets back a table of maybe 100 lines of songs with the term "love" in the title.

I finally made this work, here is my code so far

<form action="searchform.php" method="get"   > 
 <input type="text" name="searchquery" dir="ltr">
  <input type="submit" value="start search">
  </form>

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
require 'inc/db.php';
$daten = array();

if(isset($_GET ['searchquery']) && $_GET['searchquery'] !== ' ') 
    { 
    $searchquery= trim($_GET['searchquery']);
    echo "<p>Your search was: <b>". $searchquery ."</b></p>";
    $search_for = "%$searchquery%";
    $songsearch=$db->prepare("SELECT title,artist FROM songs WHERE artist LIKE ? OR title LIKE ?");
    $songsearch->bind_param('ss', $search_for, $search_for);
    $songsearch->execute();
    $songsearch->bind_result($showtitle, $showartist);
    echo "<table><tr><th>Title</th><th>Artist</th></tr>";
    while ($songsearch->fetch()) {
  echo "<tr><td>$showtitle</td>";
    echo "<td>$showartist</td></tr>";
}
echo "</table>";
}
?>

inc/db.php

<?php
error_reporting(E_ALL);
$db = new mysqli('localhost', 'root', 'root', 'songlist');
$db->set_charset('utf8');
?>

Now I want the visitor to be able to click on one songtitle of the returned list (table) and with this click I want the song to be added to a queue. My idea was to just add another table to the database called "queue" with the columns id,title,artist and to add the choosen song to this table using the "INSERT" command.

Something like this...

$addtoqueue= $db->prepare("INSERT INTO queue (title, artist) VALUES (?, ?)");

After adding a song to the queue, it should forward to a "queue.php" which displays the data from the queue-table and refreshes every 30 seconds or so.

So I would probably need some kind of "action" around the

echo "<tr><td>$showtitle</td>";

line...like

<tr><td><a href="function to add dataset to the queue">$showtitle</a></td>

But I have no idea how to "select" the specific dataset on which the visitor has clicked so that all columns of this dataset are inserted to the "queue" table.

p.s. I now tried the following, however I get errors on this and not the expected result of course

$queuedata = array();
if (isset($_GET['action']) and $_GET['action']=='queue') {
    if ( isset($_GET['id']) ) {
    $id_fetch = (INT) $_GET['id'];
    if ($id_fetch > 0)
    {   
        $dsfetch=$db->prepare("SELECT id, title, artist FROM songs WHERE id = ?");
    $dsfetch->bind_param('iss', $id, $title, $artist);
        $queuedata[] = $dsfetch;
        }
}
$addqueue = $queuedata->prepare("INSERT INTO queue (id, title, artist) VALUES ('$id, $title, $artist')");
$addqueue->bind_param('sss', $id, $title, $artist); 
if ($addqueue->execute()) {
    header('Location: queue.php?action=feedback');
    die();
        }
    }
if (isset($_POST['action']) and $_POST['action']=='feedback') {
    echo '<p class="feedbacksuccess">Song successfully added to queue</p>';
}

and for the output

echo "<tr><td><a href=\"?action=queue&id=$queuedata->id;\">$showtitle</td>";

I get this error:

Error: Notice: Trying to get property of non-object in C:\xampp\htdocs\karaoke-files\suchtest2.php on line 55

For a novice, your grasp on mysqli is very good. Hovever, asking for a code review is not very suitable on SO, there is a dedicated site on the network, codereview.stackexchange.com — Your Common Sense, Feb 02 '17 at 13:57
Thanks and sorry for that, I wasn`t aware of that page....so let´s focus on my main question regarding an "add to queue" function.... — John982, Feb 02 '17 at 14:26
So focus on it in your question, and remove all unnecessary information, including the code that works. — Your Common Sense, Feb 02 '17 at 14:28
But isn´t the working code (the part of the SELECT on the table songs and the variables used there etc) needed to get these data and use them for the INSERT into the other table?I could probably remove the html-part of the question though.... — John982, Feb 02 '17 at 14:37

score 0 · Answer 1 · edited May 23 '17 at 12:16

I am also new to programming, but I will try to help out.

If I understand you need to make a selection and then send info to a php script. Problem is that php is server side language, as far as I understand. You need something to do the work on the front-end of things. So look up some javascrpt.

You can include bundle.js into your index file, and you it's functions for selecting items.

I will give some small examples of code that you can easily find online.

For example. Use javascript to select needed things. And you function to make to do the work on the front-end.

<button id="1" onClick="reply_click(this.id)">B1</button>
<button id="2" onClick="reply_click(this.id)">B2</button>
<button id="3" onClick="reply_click(this.id)">B3</button>

<script type="text/javascript">
function reply_click(clicked_id)
{
    alert(clicked_id);
}
</script>`

Using javascript you can also call php script. Read this post Call php function from javascript

Or you can simply use html form. You can fill it up with php using echo. In action you can specify what php to send data to.

<form action="action_page.php">
  First name:<br>
  <input type="text" name="firstname" value="Mickey">
  <br>
  Last name:<br>
  <input type="text" name="lastname" value="Mouse">
  <br><br>
  <input type="submit" value="Submit">
</form>

And php script you are calling in this case action_page.php can receive your input like so $firstName = $_REQUEST['firstname'];

And if you want for example to populate you form's drop down box and fill it with data you can read this post Populate a Drop down box from a mySQL table in PHP

It is a bit long answer, hope it will help you.

Thanks for your answer, however I don´t understand even half of it, as I am not very familiar with javascript as well...so you say I need Javascript to get and send the data from one selected dataset in the php file to write this dataset back to the mysql table? — John982, Feb 02 '17 at 15:32
well you will need to read the post's i linked also. but if you don't want to use javascript you can use simple form (second part of answer). use php `echo` to populate from and use forme `action` to call php that will use and work with your data. bad thing is that as i said php is server language and as far as i know for clicking action you need javascript. — user3350597, Feb 02 '17 at 15:46
ok, but how can I determine which dataset is used to populate the form.... I mean the search result probably returns more than one dataset in most cases and I somehow have to forward the correct set of data to the form....or do I have a seperate form for every dataset? — John982, Feb 02 '17 at 16:58
well if you want to do it all server side. you can add to the database something like `song_start_time`, `song_length`, `song_end_time` and populate your html with simple list `
` and then you can do something like this http://stackoverflow.com/questions/8848389/suggest-a-way-to-update-time-every-minute. although there are probably better solutions... like look at javascript/ajax tutorials — user3350597, Feb 03 '17 at 07:14

score 0 · Answer 2 · answered Feb 04 '17 at 22:42

I finally found a solution

if (isset($_GET['action']) and $_GET['action']=='queue') {
    if ( isset($_GET['id']) ) {
    $id_fetch = (INT) $_GET['id'];
    if ($id_fetch > 0)
    {   
$dsfetch=$db->prepare("SELECT id, title, artist FROM songs WHERE id = ?");
    $dsfetch->bind_param('i', $id_fetch);
    $dsfetch->execute();
    $dsfetch->bind_result($id, $title, $artist);
    while ($dsfetch->fetch()) {
            echo $id . ' / '. $title .' '. $artist;
        }
    $addqueue = $db->prepare("INSERT INTO queue (id, title, artist, user) VALUES (?, ?, ?, ?)");
    $addqueue->bind_param('isss', $id, $title, $artist, $name); 
    if ($addqueue->execute()) {
    header('Location: index.php?aktion=feedback');
    die();
        }
    }
}   
}
if (isset($_GET['aktion']) and $_GET['aktion']=='feedback') {
    echo '<p class="feedbacksuccess">Song has been added to queue</p>';
}

and later in the output

echo "<tr><td><a href=\"index.php?aktion=queue&id=$showid\">$showtitle</a></td>";

Not sure wether or not this is "good code", but it works :-)

Thanks for all the hints

How to select specific data from one table and insert a part of it back to another table by user action

2 Answers2