PHP compare query array to var and check it into a table

Question

I have 3 table

+----+--------+----+  +----+--------+----+  +----+--------+
+ id +  name  +perc+  + id +  name  +perc+  + id +  name  +
+----+--------+----+  +----+--------+----+  +----+--------+
+ 12 + banana +100 +  + 2  + mario  +100 +  + 1  + apple  +
+ 5  + apple  + 50 +  + 5  + luigi  +100 +  + 2  + banana +
+ 7  + luigi  + 30 +  + 99 + apple  + 20 +  + 3  + input  +
+----+--------+----+  + 14 + input  + 10 +  + 4  + luigi  +
                      +----+--------+----+  + 5  + mario  +
                                            +----+--------+

The 3rd table was created from the 1st and the 2nd. In my HTML file there is a table that get all 'name' from table 3. In the first column there are all table3.name and in the 2nd,3rd columns i need to CHECK if the variables are at 100 like this: [V=check but not 100,X=not check,G=check with 100]

+--------+------+------+
+ name   + tab1 + tab2 +
+--------+------+------+
+ apple  +   V  +   V  +
+ banana +   G  +   X  +
+ input  +   X  +   V  +
+ luigi  +   V  +   G  +
+ mario  +   X  +   G  +
+--------+------+------+

My code:

    $result = $data->query("SELECT t3.name,t2.id t2_id,t1.id t1_id
    FROM table3 t3
    LEFT JOIN table2 t2 ON t2.name=t3.name
    LEFT JOIN table1 t1 ON t1.name=t3.name");
        while($line = mysql_fetch_array($result))
        {       
            echo '<tr><td>'.$line['name'].'</td>';
            if(!empty($line['t2_id'])) {
                echo 'tick'; //for each column
            } else {       //for each column
                echo 'cross';//for each column 
            }
        }   
        echo "</table>";

my query for the 'perc':

$perc_1 = $data->query("SELECT `perc`FROM `tab1`");
$globe1 = mysql_fetch_array($perc_1);
$perc_2 = $data->query("SELECT `perc`FROM `tab2`");
$globe2 = mysql_fetch_array($perc_2);

i have just try this but not work:

$compare=100;
while($line = mysql_fetch_array($result)){       
    echo '<tr><td>'.$line['name'].'</td>';
    if(!empty($line['t2_id'])) {
        if ($globe1 == $compare){
            echo 'gold'; 
        } else {     
          echo 'trick';
        }
     } else {
         echo 'cross';
     }

edit:correct some problems.but that don't resolve my problem.I don't have a PHP issue,but i don't know how to check if my var is 100

echo 'cross' doesn't have a ;

Class
– Class

2013-09-16 07:40:42 +00:00
Commented Sep 16, 2013 at 7:40 — Class
– Class, Commented Sep 16, 2013 at 7:40
correct, but i don't have this problem in my phpfile :D

enrico-dev
– enrico-dev

2013-09-16 07:55:08 +00:00
Commented Sep 16, 2013 at 7:55 — enrico-dev
– enrico-dev, Commented Sep 16, 2013 at 7:55

Scalpweb · Accepted Answer · 2013-09-16 07:42:54Z

2

Did you try to enable php error display to see if there is any PHP error ?

http://php.net/manual/fr/function.error-reporting.php

I can see that your MySQL query is not escaped as a string, this is a problem. Instead of this:

$result = $data->query(SELECT t3.name,t2.id t2_id,t1.id t1_id
    FROM table3 t3
    LEFT JOIN table2 t2 ON t2.name=t3.name
    LEFT JOIN table1 t1 ON t1.name=t3.name);

You should have this:

$result = $data->query("SELECT t3.name,t2.id t2_id,t1.id t1_id
    FROM table3 t3
    LEFT JOIN table2 t2 ON t2.name=t3.name
    LEFT JOIN table1 t1 ON t1.name=t3.name");

Also, on the last line, you have this:

echo 'cross'

Should be this:

echo 'cross';

Anyway, please activate error reporting and tell us if you have any error shown.

answered Sep 16, 2013 at 7:42

Scalpweb

1,9711 gold badge12 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

enrico-dev Over a year ago

I don't write the ";" and the " in this post but in my phpfile they are ok

Alma Do · Accepted Answer · 2013-09-16 07:44:59Z

1

You can get your data directly from SQL, like:

SELECT
  table3.name,
  CASE
    WHEN table1.perc = 100 THEN 'G'
    WHEN table1.perc!= 100 THEN 'V'
    WHEN table1.perc IS NULL THEN 'X'
  END AS tab1,
  CASE
    WHEN table2.perc = 100 THEN 'G'
    WHEN table2.perc!= 100 THEN 'V'
    WHEN table2.perc IS NULL THEN 'X'
  END AS tab2
FROM
  table3
    LEFT JOIN table1 
      ON table3.name=table1.name 
    LEFT JOIN table2 
      ON table3.name=table2.name

-so you'll have name, tab1, tab2 columns in result row set as you've described and all you'll need is to output it to your HTML.

answered Sep 16, 2013 at 7:44

Alma Do

37.4k10 gold badges81 silver badges106 bronze badges

7 Comments

enrico-dev Over a year ago

No, you don't understand my problem.I need to check if the name from table1 and table2 are in the table3.If they are in the table3 i need to see if they have a "100" in the perc column

Alma Do Over a year ago

So it would do the stuff since LEFT JOIN will return NULL-s for those names, that are in table3, but not in table1/table2

enrico-dev Over a year ago

i'm trying your method and i have a question:how can i get the result in my tablehtml?I'm trying with if($line['table1.perc'] == 'G'){ echo '<img src="/img/gold.png">'; }else{ but don't work

Alma Do Over a year ago

You've misunderstood the point. Entire result set will contain columns name (from table3), tab1 and tab2 - for the corresponding perc fields calculations from other tables. I.e. you have your data already ready in that tab1, tab2 columns.

enrico-dev Over a year ago

so...how i can get the out put?

|

Collectives™ on Stack Overflow

PHP compare query array to var and check it into a table

2 Answers 2

1 Comment

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related