how to fetch data from a table if any of the result matches from mysql results

Question

I am creating a system in which I want to fetch data from a table complaints Now i Have two variables that on behalf of which i want to fetch data from table complaints. One is the district variable and second is pin code and there are a lot of pin codes attached to one center. For example a center signs up and he wants to work on multiple pin codes lets say 110007 and 110008 so his pin code data is stored in a table named center. And his details like his district is stored in users table. Now i have made a page in which i want to show data from a table complaints where district matches his center and pin code matches his pin code. And the code is working fine if one pin code matches but if there are multiple pin codes attached to a center and the complaint is of only 1 pin code then it shows no result but i want it to show results even if it matches only 1 or any of the pin code.

i have tried some code this is my code

session_start();
if(!isset($_SESSION['username']) || (trim($_SESSION['username']) == '')) {
    header("location: login.php");
    exit();
}
$username = $_SESSION['username'];
if ($username=="dhruv") {
    header("location: complaint.php");
}

$sql = "SELECT * FROM users where username = '$username'";
$result = mysqli_query($con, $sql);

if (mysqli_num_rows($result) > 0) {

    // output data of each row
    while ($row = mysqli_fetch_assoc($result)) {
        $district = $row['district'];
    }
}

$sql = "SELECT * FROM center where username = '$username'";
$result = mysqli_query($con, $sql);

if (mysqli_num_rows($result) > 0) {

    // output data of each row
    while ($row = mysqli_fetch_assoc($result)) {
        $pin = $row['id'];
        echo $pin."<br>";
    }
}

$sql = "SELECT * 
        FROM complaints 
        where city_pin = '$pin' 
        AND status = 'pending' 
        And district ='$district'";
$result = mysqli_query($con, $sql);

if (mysqli_num_rows($result) > 0) {

    // output data of each row
    while($row = mysqli_fetch_assoc($result)) {
        $status = $row['status'];
?>
         <tr>
           <td><?php echo"<a href='otp.php'>".$row['center']."</a>"?></td>
           <td><?php echo $row['time2']?></td>
           <td><?php echo $row['product']?></td>
           <td><?php echo $row['quantity']?></td>
           <td><?php echo $row['warranty']?></td>
           <td><?php echo $row['date_of_purchase']?></td>
           <td><?php echo $row['customer_name']?></td>
           <td><?php echo $row['contact']?></td>
           <td><?php echo $row['customer_email']?></td>
           <td><?php echo $row['customer_address']?></td>
           <td><?php echo $row['city_pin']?></td>
           <td><?php echo $row['issue_complaint']?></td>
           <td class="red"><a href="otp.php?id=<?php echo $row['id'] ?>"><?php echo $row['status']?></a></td>
        </tr>
<?php
    }
} else {
    echo "0 results".mysqli_error($con);
}

please feel free to ask if you didn't understand any part of my question

Good code indentation would help us read the code and more importantly it will help **you debug your code** [Take a quick look at a coding standard](http://www.php-fig.org/psr/psr-2/) for your own benefit. You may be asked to amend this code in a few weeks/months and you will thank me in the end. — RiggsFolly, Nov 11 '19 at 12:43
@David suppose you own a service center and you want to work on areas like 110007 and 110008 now I want to show you complaints that are in your area now what's happening with my code is that if you have two areas and the complaint is from only 1 area then it is showing 0 results but it should show the complaint that is in your pin code. And if you have only 1 pin code registered with you and the complaints are from that particular pin codes then it is showing all the data. Basically what I want is I want to show the centeres all the complaints that are in their pin codes. — dhruv, Nov 11 '19 at 12:47
If you were to do a quick session on the sql LINK syntax, you coudl do all those quwries in one query and save a lot of messing around — RiggsFolly, Nov 11 '19 at 12:48
So I think you are saying is: this query can return MORE than ONE row `SELECT * FROM center where username = '$username'"` But your code is NOT making use of MULTIPLE `PINs` in the next query. So you will either have to save the pins in an array OR loop the last query inside the loop for the GetPin query — RiggsFolly, Nov 11 '19 at 12:50
@RiggsFolly can you please give an example or rewrite the query if you don't mind — dhruv, Nov 11 '19 at 12:54
You should reduce your 3 sql statement with inner join to only **one** — nbk, Nov 11 '19 at 12:54
Also if you are only interested in an `id` from a query then do `SELECT id FROM` rather than `SELECT * FROM` — RiggsFolly, Nov 11 '19 at 12:56
Your code is vulnerable to SQL injection. You should use prepared statements. — Dharman, Nov 11 '19 at 13:03
It is a very bad idea to print `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, Nov 11 '19 at 18:17

score 0 · Answer 1 · answered Nov 11 '19 at 16:26

So i made you a mysqli procedural Code. That you have to test,

Take all advices from the comments about sql injection and the rest.

as you can see, you have to bind the result,

So please check https://phpdelusions.net/pdo#prepared For the future use.

So to the code.

I combined all SQL statement to one, you should still get all complaints to one user. Of course yoi shoult test ot in phpmyademin or mysql workbench if you get all the complaints. To the mysqli i used still the procedural stile, feel free to change every thing to a more object oriented style and of course pdo

session_start();
if(!isset($_SESSION['username']) || (trim($_SESSION['username']) == '')) {
    header("location: login.php");
    exit();
}
 $username = $_SESSION['username'];
if ($username=="dhruv") {
    header("location: complaint.php");
}


$stmt = mysqli_prepare($con, "SELECT 
          c.id
          ,c.center
          ,c.time2
          ,c.product
          ,c.quantity
          , c.warranty
          , c.date_of_purchase
          , c.customer_name
          , c.contact
          , c.customer_address
          , c.city_pin
          , c.issue_complaint
          , c.status 
        FROM 
          complaints c
          INNER JOIN (SELECT `district` FROM users where username = ?) u
          ON c.`district` = u.`district`
              INNER JOIN (SELECT id FROM center where username = ?) ce
          ON c.city_pin = ce.id
        where 
         c.status = 'pending';";

mysqli_stmt_bind_param($stmt, "ss", $username, $username);

mysqli_stmt_execute($stmt);

if (mysqli_stmt_affected_rows($stmt)) > 0) {

    /* bind result variables */
    mysqli_stmt_bind_result($stmt,$id, $center, $time2, $product, $quantity,
                             $warranty, $date_of_purchase, $customer_name, $contact,
                             $customer_address, $city_pin, $issue_complaint,$status);

    /* fetch values */
    while (mysqli_stmt_fetch($stmt)) {
?>
         <tr>
           <td><?php echo"<a href='otp.php'>".$center."</a>"?></td>
           <td><?php echo $time2 ?></td>
           <td><?php echo $product ?></td>
           <td><?php echo $quantity ?></td>
           <td><?php echo $warranty ?></td>
           <td><?php echo $date_of_purchase ?></td>
           <td><?php echo $customer_name ?></td>
           <td><?php echo $contact ?></td>
           <td><?php echo $customer_email ?></td>
           <td><?php echo $customer_address ?></td>
           <td><?php echo $city_pin ?></td>
           <td><?php echo $issue_complaint ?></td>
           <td class="red"><a href="otp.php?id=<?php echo $id ?>"><?php echo $status ?></a></td>
        </tr>
<?php
    }
} else {
    echo "0 results".mysqli_error($con);
}
/* close connection */
mysqli_close($link);

how to fetch data from a table if any of the result matches from mysql results

1 Answers1