How to search a row that has been joined with another table

Question

My query works when I don't try to search using the store_ID. Here is what I have that works.

<?php
$search = $mysqli-> real_escape_string($_GET['store_ID']);
$end = $mysqli-> real_escape_string($_GET['end']);
$start = $mysqli-> real_escape_string($_GET['start']);
$resultSet = $mysqli->query("SELECT * FROM orders
      INNER JOIN customers
      ON orders.store_ID=customers.store_ID
      WHERE order_date between '$start'  and '$end'
      AND PurchaseMethod LIKE 'deferred");

    if($resultSet-> num_rows>0){
      while($rows = $resultSet->fetch_assoc())
      {
            $storename= $rows['store_name'];
            $orderID = $rows['OrderID'];
            $storeID = $rows['store_ID'];
            $orderdate = $rows['order_date'];
            $purchaseMethod = $rows['PurchaseMethod'];
            $price = $rows['TotalPrice'];

            $output .= "<tr><td style='width:14.28%;'>$storename</td>
            <td style='width:14.28%;'>$orderID</td><td style='width:14.28%;'>$storeID</td>
            <td style='width:14.28%;'>$orderdate</td><td style='width:14.28%;'>$purchaseMethod</td>
            <td style='width:14.28%;'>$$price</td></tr>";
          }

        }else{
          echo var_dump($resultSet);
          $output = "No results";
        }
        }
    ?>
    <?php echo $output; ?>

This is what I'm trying to get to work:

$resultSet = $mysqli->query("SELECT * FROM orders
      INNER JOIN customers
      ON orders.store_ID=customers.store_ID
      WHERE order_date between '$start'  and '$end'
      AND PurchaseMethod LIKE 'deferred' AND store_ID = '$search'");

When I try to add the AND store_ID = '$search'"); it gives me this error:

Notice: Trying to get property of non-object in customerreportC.php on line 66

Use error reporting. I'd guess `store_ID` is ambiguous (elsewhere you define the table it relates to, `orders.store_ID=customers.store_ID`) — user3783243, Aug 17 '19 at 15:44
@user3783243 based on `orders.store_ID=customers.store_ID` its pretty clear that `AND store_ID = '$search'`gives a ambiguous error.. Also topicstarter you should be reading about SQL injections and [how to prevent it](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Raymond Nijland, Aug 17 '19 at 15:49
@RaymondNijland yes it does give me an ambiguous error! Thanks. It's been stumping me for some time. Any idea on how i should fix it? Thank you again. — ashraft it, Aug 17 '19 at 16:29

score 0 · Accepted Answer · answered Aug 17 '19 at 16:37

0

As store_id is present in both the table it will give ambiguous error. Try this

$resultSet = $mysqli->query("SELECT * FROM orders
  INNER JOIN customers
  ON orders.store_ID=customers.store_ID
  WHERE order_date between '$start'  and '$end'
  AND PurchaseMethod LIKE 'deferred' AND orders.store_ID = '$search'");

answered Aug 17 '19 at 16:37

Mahesh

371
3
11

Thank you. Such a simple mistake on my part. Thanks – ashraft it Aug 17 '19 at 16:59

How to search a row that has been joined with another table

1 Answers1