0

I'm trying to return a set of values decided from a start and end session variable. The code works but for some reason doesn't return anything from the initial variable.

e.g. If the range ABC001 to ABC010 is entered all values from ABC002 - ABC010 are returned, nothing from ABC001 appears.

My query works perfectly when entered directly into SQL and I don't think I've already called the result set so why aren't the first set of results returning?

PS I'm aware a lot of the functions I've used have been/are being deprecated but this is quite an old site historically and I'm not ready to re-write it just yet.

<?php
  require_once("includes/header2.php");
  $title = "Box Contents";
  require_once("includes/head2.php");
  isLoggedIn();

  // Catch session variables
  $_SESSION['boxidStart'] = $_POST['boxContentsStart'];
  $_SESSION['boxidEnd'] = $_POST['boxContentsEnd'];

  // Define them as named variables for ease of use
  $boxidStart = $_SESSION['boxidStart'];
  $boxidEnd = $_SESSION['boxidEnd'];

  // Box selection query
  $sql = mysql_query ("SELECT boxId, fileNumber, dateEntered, destroyDate, invoiceStatus, invoiceDate FROM fields WHERE boxId BETWEEN '$boxidStart%' AND '$boxidEnd%' ORDER BY boxId ASC");

  // SELECT Business
  $companyName = mysql_query("SELECT business from fields LEFT JOIN users ON fields.CompanyId = users.Id WHERE boxId LIKE '$boxidStart%'");
  $cn = mysql_fetch_row($companyName);
  $no = 1;

  if (!$boxidStart) {
  ?>
  <div class="clearfix"></div>
  <div>
  <h2>You need to enter some text to search for!</h2>
  <p>Please try again.</p>
  <?php
    require_once("includes/footer.php");
  } else {
    if (escape($boxidStart))
  {
  ?>
  <div class="clearfix"></div>
  <div>
  <h2 style="float:left;">Files Entered from range box <?php echo $boxidStart ?> to box <?php echo $boxidEnd ?></h2>
  <!-- company name -->
  <h2 style="float:right"><?php echo $cn[0] ?></h2>
  <div>
  <table class="results" cellpadding="3" cellspacing="0" border="1" width="100%">
    <tbody align="left">
    <tr>
        <th>No.</th>
        <th>Box No.</th>
        <th>File Number</th>
        <th>Date Entered</th>
        <th>Destroy Date</th>
        <th>Invoicing</th>
     </tr>
<?php

while ($row = mysql_fetch_row($sql)) {
    echo "<tr valign='middle' class='row'>
          <td width='5%' class='company'>", $no++ ,"</td>
          <td width='5%' class='company'>", $row[0] ,"</td>
          <td width='30%' class='company'>", $row[1],"</td>
          <td width='12.5%' class='datein'>", $new_date = date('d/m/Y', strtotime($row[2])), "</td>
          <td width='12.5%' class='filenotes'>", $new_date = date('d/m/Y', strtotime($row[3])), "</td>";

    // There must be a check in the history_files table to see if there is an entry for this fileNumber
    // If there is an entry for this file number then it means that the file has already been in the system and the invoice has been paid
    $sqlReturn = mysql_query ("SELECT fileNumber FROM history_files WHERE fileNumber = '$row[1]'");


    $result = mysql_fetch_array($sqlReturn);
    //var_dump($sqlReturn);
    if ($result) {
        // If the result is true the the text "Invoice has been paid"
        echo "<td width='35%' class='filenotes'>Invoice already paid</td></tr>";
    } else {
        // If the result is false the the text "Invoice Charge €25.00" plus VAT 
        echo "<td width='35%' class='filenotes'>Invoice charge €25.00 + VAT</td></tr>";
    }


    }
            //echo "<p>".mysql_num_rows($sql)." results<p>";
?>

</tbody>
</table>


<p><strong><br><br>Signed:</strong>________________________________<br><br></p>
<p><strong>Dated:</strong> <?php echo date('d/m/Y', strtotime($dateEntered)); ?></p>
<br><br>
</div>
<?php require_once("includes/foot.php"); } }?>
4
  • Don't use mysql_* functions in new codes, because it is deprecated. Use PDO or MySQLi instead. Commented Oct 22, 2013 at 13:15
  • Haven't use this API in a while but I remember needing to do something line mysql_data_seek($no), or does mysql_fetch_row iterate? Commented Oct 22, 2013 at 13:16
  • @Subin I'm aware a lot of the functions I've used have been/are being deprecated but this is quite an old site historically and I'm not ready to re-write it just yet. Cheers for the tip :) Commented Oct 22, 2013 at 13:19
  • @ConnorBrendish I'm certainly no expert on php but AFAIK mysql_fetch_row just returns one row, it needs to be called in a loop to iterate. Commented Oct 22, 2013 at 13:22

1 Answer 1

1

The query being run includes this clause:

BETWEEN '$boxidStart%' AND '$boxidEnd%'

but the BETWEEN is doing a string compare, not a LIKE. In alpahbetical order, ABC001 comes before ABC001%, so the first row you're expecting isn't matched by the query.

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

1 Comment

Thats the ticket! I never thought that the wildcard would effect the comparison. Thank you very much :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.