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.

Can you show us what your desired output is? The table structure your PHP is building is very different than your JS fiddle example. — ak_
– ak_, Commented Feb 2, 2016 at 15:03
Can you share the data you are working with and how is it structured? You are going to run into problems if the two SQL queries return results of different lengths. You may need to use a SQL join to retrieve the data you are interested in. — ak_
– ak_, Commented Feb 2, 2016 at 15:09
I have edited the question and i have added a image that show what i am trying to get. The data is a column with name of services(string) and another column with the id(integer) of each. The jquery split the table depending on a variable in jquery($extraRows.length; i = i+4),4 in this case. — user19566
– user19566, Commented Feb 2, 2016 at 16:23

ak_ · Accepted Answer · 2016-02-02 16:59:08Z

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

arcee123 · Accepted Answer · 2016-02-02 16:47:18Z

0

Try pushing your for statement outwards, and insert a couple of ifs. Please forgive...I wrote this in memory, and not in a IDE.

  <?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) > count($data2) ? count($data1) : count($data2));
         for ($i = 0; $i < $count; $i++) {
            if ($i % 2 = 0) {
                echo '</table>';
            }
            if (($i = 0) || ($i % 2 = 0)) {
                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>';
            }
            echo '<tr ><td>' . (isset($data2[$i]) ? $data2[$i] : 'N/A') . '</td>';
            echo '<td>' . (isset($data1[$i]) ? $data1[$i] : 'N/A' ) . '</td></tr>'; 
        }
        echo '</table>';
        pg_free_result($name);
        pg_close($dbconn);
    ?>

You are using your rowcount to determine when to end the table. The Modulus(%) operator says if the current row is divisible by 2, then end the table and start a new one. If you want three rows per table, change 2 -> 3.

DEVNOTE: you don't need to use echo. You can use ?> HTML CODE <?PHP. In your case, instead of using the line echo '</table>';, you can use ?></table><?PHP. For your consideration.

Cheers.

edited Feb 2, 2016 at 16:47

answered Feb 2, 2016 at 16:29

arcee123

30514 gold badges61 silver badges139 bronze badges

5 Comments

ak_ Over a year ago

This is going to fail when the SQL queries don't return the same number of rows.

ak_ Over a year ago

Thats good based on the information we know. However, a SQL Join is really what's needed. Without the proper query, the PHP is going to display unsorted results and we'll end up with ID's printed next to names in a table, and they wont match.

arcee123 Over a year ago

granted. And you are correct, in the business sense. The problem here is that we don't know, so I just went with what he specifically asked for. However, given the option, and the understood mentalities of SQL, I would go for your recommendation.

user19566 Over a year ago

Great. Thank you very much for your answer.

arcee123 Over a year ago

No problem. If you lose connection between ID and name, go with Adam's Recommendation. He accounts for it.

Collectives™ on Stack Overflow

JQuery table in php

2 Answers 2

PHP

Result

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

PHP

Result

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related