MySql Query not returning all data

PHP
1

I am confused as to why my query is not returning all the data. I have a table departments which has 3 fields, id,code,name. For some reason, the query is returning the data from the name field apart from if an entry in the code field is also present in the name field. So, if the code = demo it will return all data for the code ‘demo’ apart from the ‘demo’ in the name field.

I am wondering if it is something to do with the distinct statement? If I run this query directly in phpMyAdmin, it returns all data correctly. I would be grateful if someone could point out my error.

Example:

|id|code|name|boxstatus||

|154|BRIT|BRIT|0|
|135|BWLS|BWLS|0|
|150|BWLS|ACCOUNTS|0|
|103|DEMO|DEMO222|2|
|125|DEMO|DEMO|11|
|144|DEMO|DEMO 00|0|
|120|DEMO|DEMO5|5|
|119|DEMO|DEMO4|2|
|132|DEMO|DEMO DEPT|0|
|167|DEMO|DEMO455|0|
|168|DEMO|DEMO46|0|
|92|DEMO|DEMOBILL|5|
|166|DEMO|DEMO44|0|

$rtvcompany = $_GET['rtvcompany'];
$_SESSION['company'] = $rtvcompany;

$sql = "Select Distinct
    departments.name,
    boxes.status
From
    departments,
    boxes
Where
    departments.boxstatus != '0' And
    departments.code = '".$rtvcompany."' And
    boxes.status = 1
Order By
    departments.name ASC";
$result = mysqli_query($conn, $sql) or die ("box retrieve dept failed: ".mysqli_error($conn));
$row_result = mysqli_fetch_assoc($result);
$totalRows_result = mysqli_num_rows($result);


if (mysqli_num_rows($result) > 0) {
  echo "<script type=text/javascript>\n";
  echo "$(function() {\n";
  echo "$(\"#rtrvmessage\").html('')\n";
  echo "$(\".dept\").show()\n";
  echo "$('#dept').attr('data-placeholder', \"Choose your department\").prop('disabled', false).val('').trigger('chosen:updated')\n";
  echo "});\n";
  echo "</script>\n";
  while ($row_result = mysqli_fetch_array($result)) {
    echo "<option value='$row_result[name]'>$row_result[name]</option>";
  }
}
2

why would it consider this factoid? You haven’t told the query to consider that.

Your query result above appears correct to me. Give us a set of rows in your two tables that you think should be in the result that isnt.

3

Thanks for reply. This is what is being returned in phpMyAdmin, which is correct.

DEMO
DEMO222
DEMO4
DEMO5
DEMOBILL

Returned from script.

DEMO222
DEMO4
DEMO5
DEMOBILL
4

Well allow me to say that if you’re running the same query in phpMyAdmin and in PHP, and getting different results, the problem isn’t your query.

Are you absolutely sure that the PHP script is connecting to the same database? Not running a local copy by accident or something?

Also your echo line should be putting braces around the variables, if you’re trying to use array references. Also, you’ve not put the <option>'s inside a select.

5

I am using the chosen jquery plugin for my selects. This is the code I am using in my html.

<select class="chosen-select" id="dept" name="dept" data-placeholder="No data to display" disabled>
     <option value=""></option>
</select>

The blank option is for the data-placeholder. I am running on localhost in wamp and connecting to the correct database. Sorry I don’t understand your comment

Could you please clarify? Thanks

6

echo "<option value='{$row_result['name']}'>{$row_result['name']}</option>";

What value do you get when you add this:
echo mysqli_num_rows($result);

1 Like
7 
$row_result = mysqli_fetch_assoc($result);

There is your first row.

1 Like
8

lol. I didn’t even see that. Well spotted.

9

And use at least escaping function for SQL params. Your resource is potential victim for all hackers in the world.

10

I get 5 but it is only showing 4 in the select and in the resource tab of console. It seems to be leaving out the variable value which is value in the code field. In this case demo.

11

As @igor_g has pointed out to you, your PHP code is slicing a row off of the result before you get to the loop.

12

Yeh I commented that out and it is working fine now. Thanks to everyone for all the help.

14

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.