MYSQL QUERY and display output to html

Question

I want to show the total rating of each company and total will put on specific category

the code is this

if (isset($_POST['search'])){

    $key = $_POST['keyword'];

    $com = mysql_query("
SELECT * 
  FROM company
 WHERE id LIKE '%$key%' 
    OR name LIKE '%$key%' 
    OR address LIKE '%$key%' 
    OR city LIKE '%$key%' 
    OR province LIKE '%$key%' 
    OR region LIKE '%$key%' 
    OR country LIKE '%$key%' 
    OR description LIKE '%$key%'
 ");
    while($comshow = mysql_fetch_array($com)){
        $comshowid = $comshow['id'];

        echo '
    <form  method="POST">   
    <div class = "row">
<div class = "text-right">                      
    <b><input  name = "reviewid" style = "margin-right:50%;" class = "text-right" id = "id" type = "text" value = "'.$comshow['id'].'" readonly></b>                    
</div>
</div>
<div class = "row" style = "margin-left:1%;">
    <div class ="col-md-4">
        <img src = "'.$comshow['dp'].'" style = "width:80%;">
    </div>
    <div class = "col-md-6">

        <h2><i>'.$comshow['name'].'</i></h2>
        <hr />
        <p>'.$comshow['description'].'</p>
        <h4><b>Ratings</b></h4>
        <p>Recruitment:</p>
        <p>Tenure:</p>
        <p>Separation:</p>  
        <input class = "btn btn-info" type = "submit"  name ="review" value = "REVIEW">
    </div>                      
</div><br><br></form>

    ';

    }

This is the rating table

This is the company table That is the output

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Oct 30 '17 at 19:32
***Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).*** [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Oct 30 '17 at 19:32
Use a `JOIN` to combine the company table with the ratings table. — Barmar, Oct 30 '17 at 19:42
If you don't know how to do a JOIN, you need to read a good SQL tutorial. This is not the place to learn. — Barmar, Oct 30 '17 at 19:43

Luke Paoloni · Accepted Answer · 2017-10-31T09:59:55.827

@Barmar is correct. You need to JOIN your company table with the rating table. I would also recommend having a column named ratings_id which is a foreign key to rating.id. You can then have a column named rating_value in the rating table.

Insert the following:

    ALTER TABLE ‘rating’ ADD ‘rating_value’ INT NULL;
    ALTER TABLE ‘company’ ADD ‘ratings_id’ INT NULL;

    SELECT c.id, c.name, c.address, c.city, c.province, 
    c.region, c.country, c.description, c.ratings_id, 
    r.id, r.rating_value 
    FROM company c 
    JOIN rating r
    ON c.ratings_is = r.id
    AND
    c.id LIKE '%$key%' 
    OR c.name LIKE '%$key%' 
    OR c.address LIKE '%$key%' 
    OR city LIKE '%$key%' 
    OR c.province LIKE '%$key%' 
    OR c.region LIKE '%$key%' 
    OR c.country LIKE '%$key%' 
    OR c.description LIKE '%$key%';

I would highly recommend looking into PDO Prepare Statements as many have said before you are at risk of SQL Injections.

MYSQL QUERY and display output to html

1 Answers1