JQuery table in php

Question

How i could get work a php file with sql queries and JQuery together?. The final object is to get multiple tables as the http://jsfiddle.net/96Lhog5g/3/ demo but using the php code below.Something like this but in php -Php code:

    <?php
         $dbconn = pg_connect(
         $sql1 = "SELECT unaccent(name) from base1;";
         $sql2 = "select id from servic;";

         $name = pg_query($sql1);
         $ident= pg_query($sql2);
         $data1 = pg_fetch_all_columns($name);
         $data2 = pg_fetch_all_columns($ident);
         $count = count($data1);
            echo '<table id="mainTable" border="1" style="width:450px;position:relative;left:80px;">'; 
                echo '<tr >';   
                echo '<th style="background: #3498db; width:5px;text-align: center ;font-size:12px;font-family:Arial Narrow">ID</th>';
                echo '<th style="background: #3498db; width:10px;text-align: center ;font-size:12px;font-family:Arial Narrow">NAME</th>';   
                echo '</tr>';
                for ($i = 0; $i < $count; $i++) {
                    $data1[$i];
                    $data2[$i];
                echo '<tr ><td>' . $data2[$i] . '</td>';
                echo '<td>' . $data1[$i] . '</td></tr>';    
                }
            echo '</table>';
        pg_free_result($name);
        pg_close($dbconn);
    ?>

• JQuery code:

  var $main = $('#mainTable'),
  
  $head = $main.find('tr:first'),
  
  $extraRows = $main.find('tr:gt(2)');
  
  for( var i = 0; i < $extraRows.length; i = i+4){
      $('<table>').append($head.clone(),  
      $extraRows.slice(i,i+2)).appendTo($main.parent()); 
  }
  

,from http://jsfiddle.net/96Lhog5g/3/. I have tried adapt this code to php but my knowledge about it are limited. Thanks in advance.

Answer 1

Its difficult to tell without knowing the structure of your data. You definitely need to do a SQL join on the two database columns so that you're working with the correct data.

Without the SQL join, the data you're getting in both queries may not be the same length, and it definitely isn't going to match. (i.e. its going to display the name next to the ID it doesn't belong with)

PHP

/*
//This is an example SQL join, guessing at how your data is structured based on the queries used in your question.
$sql = "SELECT unaccent(base1.name),servic.id FROM base1 INNER JOIN servic ON base1.id = servic.id;";
$result = pg_query($sql);
$data = pg_fetch_all($result)
*/


//lacking any SQL data, we'll build a test dataset for now
$data = array();
function buildTestData() {
    global $data;
    for($i = 1; $i < 24; $i++) {
        $testData = array("name" => "name ".chr($i+64), "id" => $i);
        array_push($data,$testData);
    }
}

buildTestData();
//end of test data


//set the number of rows you'd like each table to have
$splitPoint = 4;


$count = count($data);

for ($i = 0; $i < $count; $i++) {

    if($i % $splitPoint == 0) {
        echo "\n<table id=\"mainTable\" border=\"1\" style=\"width:450px;position:relative;left:80px;\">"; 
        echo "\n\t<tr>";
        echo "\n\t\t<th style=\"background: #3498db; width:5px;text-align: center ;font-size:12px;font-family:Arial Narrow\">ID</th>";
        echo "\n\t\t<th style=\"background: #3498db; width:10px;text-align: center ;font-size:12px;font-family:Arial Narrow\">NAME</th>";
        echo "\n\t</tr>";
    }


    echo "\n\t<tr>";
    echo "\n\t\t<td>" . $data[$i]['id'] . "</td>";
    echo "\n\t\t<td>" . $data[$i]['name'] . "</td>";
    echo "\n\t</tr>";    

    if(($i +1) % $splitPoint == 0 || $i == $count-1) {
        echo "\n</table>";
    }
}

 

Result

The PHP will produce formatted HTML like this:

<table id="mainTable" border="1" style="width:450px;position:relative;left:80px;">
    <tr>
        <th style="background: #3498db; width:5px;text-align: center ;font-size:12px;font-family:Arial Narrow">ID</th>
        <th style="background: #3498db; width:10px;text-align: center ;font-size:12px;font-family:Arial Narrow">NAME</th>
    </tr>
    <tr>
        <td>1</td>
        <td>name A</td>
    </tr>
    <tr>
        <td>2</td>
        <td>name B</td>
    </tr>
    <tr>
        <td>3</td>
        <td>name C</td>
    </tr>
    <tr>
        <td>4</td>
        <td>name D</td>
    </tr>
</table>
<table id="mainTable" border="1" style="width:450px;position:relative;left:80px;">
    <tr>
        <th style="background: #3498db; width:5px;text-align: center ;font-size:12px;font-family:Arial Narrow">ID</th>
        <th style="background: #3498db; width:10px;text-align: center ;font-size:12px;font-family:Arial Narrow">NAME</th>
    </tr>
    <tr>
        <td>5</td>
        <td>name E</td>
    </tr>
    <tr>
        <td>6</td>
        <td>name F</td>
    </tr>
    <tr>
        <td>7</td>
        <td>name G</td>
    </tr>
    <tr>
        <td>8</td>
        <td>name H</td>
    </tr>
</table>
<table id="mainTable" border="1" style="width:450px;position:relative;left:80px;">
    <tr>
        <th style="background: #3498db; width:5px;text-align: center ;font-size:12px;font-family:Arial Narrow">ID</th>
        <th style="background: #3498db; width:10px;text-align: center ;font-size:12px;font-family:Arial Narrow">NAME</th>
    </tr>
    <tr>
        <td>9</td>
        <td>name I</td>
    </tr>
    <tr>
        <td>10</td>
        <td>name J</td>
    </tr>
    <tr>
        <td>11</td>
        <td>name K</td>
    </tr>
    <tr>
        <td>12</td>
        <td>name L</td>
    </tr>
</table>
<table id="mainTable" border="1" style="width:450px;position:relative;left:80px;">
    <tr>
        <th style="background: #3498db; width:5px;text-align: center ;font-size:12px;font-family:Arial Narrow">ID</th>
        <th style="background: #3498db; width:10px;text-align: center ;font-size:12px;font-family:Arial Narrow">NAME</th>
    </tr>
    <tr>
        <td>13</td>
        <td>name M</td>
    </tr>
    <tr>
        <td>14</td>
        <td>name N</td>
    </tr>
    <tr>
        <td>15</td>
        <td>name O</td>
    </tr>
    <tr>
        <td>16</td>
        <td>name P</td>
    </tr>
</table>
<table id="mainTable" border="1" style="width:450px;position:relative;left:80px;">
    <tr>
        <th style="background: #3498db; width:5px;text-align: center ;font-size:12px;font-family:Arial Narrow">ID</th>
        <th style="background: #3498db; width:10px;text-align: center ;font-size:12px;font-family:Arial Narrow">NAME</th>
    </tr>
    <tr>
        <td>17</td>
        <td>name Q</td>
    </tr>
    <tr>
        <td>18</td>
        <td>name R</td>
    </tr>
    <tr>
        <td>19</td>
        <td>name S</td>
    </tr>
    <tr>
        <td>20</td>
        <td>name T</td>
    </tr>
</table>
<table id="mainTable" border="1" style="width:450px;position:relative;left:80px;">
    <tr>
        <th style="background: #3498db; width:5px;text-align: center ;font-size:12px;font-family:Arial Narrow">ID</th>
        <th style="background: #3498db; width:10px;text-align: center ;font-size:12px;font-family:Arial Narrow">NAME</th>
    </tr>
    <tr>
        <td>21</td>
        <td>name U</td>
    </tr>
    <tr>
        <td>22</td>
        <td>name V</td>
    </tr>
    <tr>
        <td>23</td>
        <td>name W</td>
    </tr>
</table>

You can see the results of the code in this PHP Sandbox: http://sandbox.onlinephpfunctions.com/code/e2d73696445f709840084f8a7f40311353b0d8fc