Using a $var in a mysqli WHERE LIKE statement

Question

I'm trying to get a list of items in a region.

I'd like the Region Name as a Heading Then all the items in that region in a table below i.e.

HEADING - Region1

Table - Region1 Items

HEADING - Region2

Table - Region2 Items

HEADING - Region3

Table - Region3 Items

HEADING - Region4

Table - Region4 Items

I can output:

HEADING

I can output the region details in a table if I use LIKE 'Actual Region Name'

I would like to output using LIKE $region so I don't need to write a new statement for each 'Actual Region Name'.

The 2 SELECT Queries:

$region = mysqli_query($conn,"SELECT DISTINCT region FROM country") or die($conn->error);
$result = mysqli_query($conn,"SELECT * FROM country WHERE region LIKE '$region'") or die($conn->error);

OUTPUT 1

This outputs each region as

Region Name

$i = 0;
while($row = $region->fetch_assoc())
{
 if ($i == 0) {
 foreach ($row as $value) {
 echo "<p>" . $value . "</p>";
                      }

             }
}

OUTPUT 2

This outputs all the region data and puts it in a table.

echo "This is table Build";
echo "<table border='1'>";

$i = 0;
while($row = $result->fetch_assoc())
{
    if ($i == 0) {
      $i++;
      echo "<tr>";
      foreach ($row as $key => $value) {
        echo "<th>" . $key . "</th>";
      }
      echo "</tr>";
    }
    echo "<tr>";
    foreach ($row as $value) {
      echo "<td>" . $value . "</td>";
    }
    echo "</tr>";
}
echo "</table>";

mysqli_close($conn);

?>

OUTPUT 1 and OUTPUT 2 both work separately. I can't get them to work together

[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, Jul 01 '19 at 12:31
This should not be using two queries to begin with. Write one query, that gets you all the data, properly sorted - and then implement a simple [control break](https://en.wikipedia.org/wiki/Control_break). — 04FS, Jul 01 '19 at 12:44
@04FS Don't suppose you'd like to elaborate on the single query that might help me? I've been googling and trying different options for hours - that's why I'm here. — webecho, Jul 01 '19 at 13:14
It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Oct 27 '19 at 11:53

Swati · Accepted Answer · 2019-07-02T13:51:52.330

0

You can get all region names from query and then passed it to your next query and print all the record related to that query i.e :

<?php

$region = mysqli_query($conn,"SELECT DISTINCT region FROM country") or die($conn->error);

if($region->num_rows > 0) 
    {                               {
       while($row = $region->fetch_assoc())
         {
         //printing region name
         echo "<p>" . $row['region'] . "</p>";
         //only those row will be retrieve which belong to current region name 
         $result = mysqli_query($conn,"SELECT * FROM country WHERE region LIKE '$row['region']'") or die($conn->error);
//printing table 
      echo "This is table Build";
      echo "<table border='1'>"; 
              $i = 0;
    while($row1 = $result->fetch_assoc())
    {
        if ($i == 0) {
          $i++;
          echo "<tr>";
          foreach ($row1 as $key => $value) {
            echo "<th>" . $key . "</th>";
          }
          echo "</tr>";
        }
        echo "<tr>";
        foreach ($row1 as $value) {
          echo "<td>" . $value . "</td>";
        }
        echo "</tr>";
    }
    echo "</table>";
             }

}
  mysqli_close($conn);

        ?>

edited Jul 02 '19 at 13:51

answered Jul 01 '19 at 13:19

Swati

28,069
4
21
41

Thanks @Swati That gives me an error : Undefined index: region on this line if($row['region '] !=$region){ – webecho Jul 01 '19 at 13:27
there is little space here `$row['region ']` remove that space i.e : `$row['region']` and check onces – Swati Jul 01 '19 at 13:34
Still showing as undefined – webecho Jul 01 '19 at 14:04
Does `$row['region']` exist ? I mean column `region` ? Also is this error on `$row['region']` or `$region` ? – Swati Jul 01 '19 at 14:23
Yes region exists as a column. Using the $region = SELECT DISTINCT... I can list out all the region names using the code in OUTPUT 1 – webecho Jul 01 '19 at 15:28
I have edited the original post, as I'm not sure it was completely clear what I am trying to achieve. – webecho Jul 01 '19 at 15:32
Sorry i misunderstood your question , now check updated code, this should work. – Swati Jul 02 '19 at 13:52
Thank you! That's exactly what I was trying to do. I had to make 1 change in the $result line `LIKE '".$row['region']."'` – webecho Jul 04 '19 at 03:23
Great it worked , try to accept this answer if it helped you :) – Swati Jul 04 '19 at 03:45

Using a $var in a mysqli WHERE LIKE statement

1 Answers1