MySql OR query not returning all data

I have in my db an activity field where the values can either be ‘Box Recycle’ and ‘Sack Recycle’. I have written a query which I thought would work where if it is either box or sack return, then it will return values based on either. ie; I need to display values for both box and sack. However, what is hppening is that the query is only returning the first value in the query. In this case ‘Box Recycle’.

Can someone help me code this correctly. Many thanks

<?php
  $sql = "SELECT * FROM act WHERE activity = 'Box Recycle' OR activity = 'Sack Recycle' AND new = 0";
  $resultset = mysqli_query($conn, $sql) or die("database error:". mysqli_error($conn));
    if (mysqli_num_rows($resultset) > 0) {
      while($row = mysqli_fetch_array($resultset)) {
        $newdate = date('d/m/Y H:i:s A', strtotime($row['date']));

        $data[] = array(
        'id' => $row['id'],
        'company' => $row['company'],
        'activity' => $row['activity'],
        'user' => $row['user'],
        'qty' => $row['quantity'],
        'type' => $row['type'],
        'intkdate' => $newdate
        );
    }
    echo json_encode($data);
    } 
    else 
    {
        echo '{
        "sEcho": 1,
        "iTotalRecords": "0",
        "iTotalDisplayRecords": "0",
        "aaData": []
        }';
    } 
?>

mixing your OR and AND’s isnt a good idea. It leads to some ambiguous logic.

A OR B AND C
Do i mean “Either A or B, as long as they both have C”? or do i mean “A, or B if it also has C”?
Solution: Parenthesis.

(A OR B) AND C vs A OR (B AND C).

woud that be coded like:

SELECT * FROM act WHERE activity = ('Box Recycle' OR 'Sack Recycle') AND new = 0

Many thanks

1 Like

No, just take what you already have, stick a ( before the first ‘activity’ and stick a ) after the Sack Recycle.

1 Like

This returns all db data
SELECT * FROM act WHERE activity = ('Box Recycle' OR 'Sack Recycle') AND new = 0

This returns just Box Recycle
SELECT * FROM act WHERE (activity = 'Box Recycle' OR activity = 'Sack Recycle') AND new = 0

Thanks

1 Like

Have you made sure you’ve got some records in your table that are set to Sack Recycle and have a new value of 0?

OOppss

many thanks Mr Hutley. Cheers

1 Like

hehehe… Solution #2: Queries can only find data that exists :wink:

2 Likes

haha Thanks for the heads up. Have a good day

Depending on how comprehensive your database structure is, you might want to have a look at “normalising” - really those activities should be codes that are looked up against a separate table rather than hard-coded string values. It’s a while since I read about it, so I can’t really remember what the “rules” are for determining the point where it’s more complex to do that than to have strings like this.

Back in the day when storage was more costly than it is now, the thought of a few thousand rows containing an identical 11 or 12-character string instead of a potentially single-digit id leading to one copy of that string wouldn’t have been allowed.

Hi droopsnoot
Not something I am familiar with. Will check it out. Thanks for heads up.

What he’s referring to is that since those two definitions seem to be something ‘unlikely’ to change in the future, put the terms in code:
if $term == ‘Box Recycle’{$term=1;}
Change the database field to a single digit so you don’t waste space.

Along those lines, certainly, but the values would normally (sorry…) come from a separate database table rather than being hard-coded.

I presume saving space came into it, but there’s also reducing the chances for mistakes, making it easier to add or change a term, and so on.

1 Like

Yes with a dynamic model with often changing values for sure. But I’ve been there and done that. If it’s unlikely to change in the future then go with the software coding model, if not then implement what you said. We can agree to agree! ha! Love it!

Interesting note. I’ve done this in my own projects, and once you get past 6-8 variations, it becomes a bit problematic and I’ve had to revert to your exact model/scheme. As long as the choices stay small, then it’s ok!

Also… The ENUM thing. I could populate that forever, but it’s worse than doing the same thing in the code.

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