0

I have two tables that I have linked together in a query. The assignments table holds the template assigned to a phone. The templateinfo table holds all the template settings, there is a templatename within the templateinfo. I have a script that creates a file from the inner join. IF I input by hand the name of a specific template the script runs find and creates the correct number of files. My issue is that I need to query the assignments table for the different names of the templates and use it in the inner join to create the files. There could be any number of template names that need to be applied. Here is what I have now, th ere is a template name of "Wednesday" that will create ALL the files needed for the phones that have been assigned with the Wednesday templates.

   <?php
require('/var/www/html/cqadmin/utils/connect.php');


// Check connection
if (!$link) {
    die("Connection failed: " . mysqli_connect_error());
}

// attempt insert query execution
$sql = "INSERT INTO assignments 
                (extension, secret, macaddress, template) 
        VALUES  (?,?,?,?)";

$result = $link->prepare($sql);

foreach ($_POST['extension'] as $idx => $extension) {
    $result->bind_param('ssss',
                        $extension,
                        $_POST['secret'][$idx],
                        $_POST['phone'][$idx],
                        $_POST['template'][$idx]
                        );

    if( $result->execute() ) {
        echo "Records $idx added successfully.";
    } else{
        echo "ERROR: Could not execute $sql. " . $result->error;
        exit;
    }
}

$sql2 = "select template from assignments";
$result2 = $link->query(sql2);
$sql = "SELECT * FROM templateinfo INNER JOIN assignments ON template = templatename where assignments.template= Wednesday";
$result = $link->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) 
{

The rest of the script creates files using fwrite.

Thanks for looking

Ed Lentz
  • 7
  • 3
  • 1
    Doesn't wednesday need to be in quotes where assignments.template= 'Wednesday'"; But I really don't understand the problem/question yet. What is the PK/FK relationship of assignments and templateInfo. `Template `and `TemplateName` seems insufficient. but maybe that's fine; I don't understand the columns that relate in both tables yet. – xQbert Sep 28 '17 at 16:05
  • Is your question "How do I make 'Wednesday' a variable? (parameter) so that you can pass in any template name and get the resulting related info? – xQbert Sep 28 '17 at 16:09
  • Yes the Wednesday would need to be in quotes. I just threw that in there so you understood "where" my key needed to be. With a template in named Wednesday in both table/rows the script will create the file(s). So to be more clear. In the templateinfo table there is a record with templatename as the field name. In the assignments there is a record(s) with the fieldname of template. This is kind of a one to many relationship. kind of. – Ed Lentz Sep 28 '17 at 17:58

1 Answers1

0

I think, it will work for you

$sql = "SELECT t1.id, t2.* FROM  assignments t1 INNER JOIN templateinfo t2 ON t1.id = t2.templateId";
$result = $link->query($sql); 
if ($result->num_rows > 0) {
 // output data of each row 
  while($row = $result->fetch_assoc()) {
   }
}

Or your SQL may be differ as per column

  $sql = "SELECT t1.*, t2.* FROM  assignments t1 INNER JOIN templateinfo t2 ON t1.template = t2.templateName";

I hope you foreignKey column is templateId

Ankur Garg
  • 577
  • 3
  • 9