0

probably a simple case. Basically I have a Database with 5 fields, which are showing Orders and their progress: 

idRefs, Quantity, refsDone, refsRunning, refsUntouched

Now I want to select all those Rows, where Quantity = refsDone (this means the order is done). I am wondering how I can do this using PDO ? This is what I've tried:

$filter = $_POST['filter'];
$limiter = $_POST['limiter'];
$refid = $_POST['refid'];
if($refid == "") $refid="%";

case("finished"):
$sql = "SELECT * FROM Orders WHERE REFID LIKE :refid AND quantity=refsDone ORDER BY dateAdded DESC LIMIT :min,:max";
$ps_orders = $db->prepare($sql);
$ps_orders->bindParam(':refid', $refid, PDO::PARAM_STR);
$ps_orders->bindParam(':min', $min, PDO::PARAM_INT);
$ps_orders->bindParam(':max', $limiter, PDO::PARAM_INT);
$ps_orders->execute();
$rowCount = $ps_orders->fetchColumn();
$data = $ps_orders->fetchAll();
break;

Cant say whats happening wrong, but I am kinda sure it is the part "AND quantity=refsDone" which causes it.

kentor
  • 16,553
  • 20
  • 86
  • 144
  • Can you show sample data and the create statement of your table? – Jens Sep 06 '14 at 19:40
  • Add `$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);` right after the connection is opened. That'll tell you where your errors are. *"but I am kinda sure it is the part "`AND quantity=refsDone`"* - ***Yep...*** you're right ;) – Funk Forty Niner Sep 06 '14 at 19:40
  • 1
    Is `refsDone` a *field name* or a *status text*? If it's the former, what you're doing appears right. Your quantity field appears to possibly be capitalized though? Also, this is plain SQL, nothing to do with PDO. – Jared Farrish Sep 06 '14 at 19:41
  • $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); <- changes nothing. I guess the resultSet is just empty – kentor Sep 06 '14 at 19:57
  • Scrowler you were right, but I've uploaded the wrong database Structure - I am sorry. This is the structure which I am using: http://puu.sh/bohgm/45182f708d.png – kentor Sep 06 '14 at 20:00
  • And here an example dataSet which should be shown then: http://puu.sh/bohGr/13e9f76e70.png – kentor Sep 06 '14 at 20:04
  • @scrowler: [MySQL column names are not case-sensitive](http://dev.mysql.com/doc/refman/5.6/en/identifier-case-sensitivity.html) – Bill Karwin Sep 06 '14 at 20:14
  • Your `quantity` column is an `int`, yet you're using `quantity=refsDone`, why? It shouldn't be done that way. `int`s are meant to be used as integers, not strings. Yes, I know `refsDone` isn't wrapped in quotes, but still, not a good method. I'm totally confused at this point, as to what is what. Please edit your question/code accordingly. – Funk Forty Niner Sep 06 '14 at 20:19
  • @Fred-ii-, both `quantity` and `refsDone` are column identifiers. They are both int columns. I was confused at first too, because comparing one column to another column in the same row of the same table is a little bit unusual. – Bill Karwin Sep 06 '14 at 20:28
  • @BillKarwin This `AND quantity=refsDone` is what I don't get. Is that what you meant and can be done, yet considered "unusual"? – Funk Forty Niner Sep 06 '14 at 20:36
  • @BillKarwin Scratch my last comment, I see what you mean now. I didn't know that could be done; one never ceases to learn ;) – Funk Forty Niner Sep 06 '14 at 20:46
  • 2
    @Fred-ii-, yeah I assume refsDone starts at 0 and as the units for the order are processed, the app increments refsDone. When quantity=refsDone, the order is fully processed, or fully shipped, or whatever. – Bill Karwin Sep 06 '14 at 20:48
  • @Fred-ii- - Think about it like this: Say you have a contract where you lease equipment. Someone leases 10 pieces of equipment, but picks up the ten at different times. So the number `leased` would be how many have been taken out by the customer. When `quantity` is equal to `leased`, the contract is fulfilled. This is not particularly unusual (and hence I disagree with Bill Karwin on that point), even if it's only to mark the contract `status` as completed. – Jared Farrish Sep 06 '14 at 21:27
  • @JaredFarrish, I don't think the business process is unusual, but with respect to database design, it's not strictly normalized. – Bill Karwin Sep 06 '14 at 21:34
  • @BillKarwin - "strictly" normalized. There are times where it's not necessary to store extended details on a transaction. And that doesn't mean there isn't a table somewhere holding data on each. The point was to explain how you could end up comparing two columns to get a derived meaning. – Jared Farrish Sep 06 '14 at 22:24
  • Bill explained it correct. There is a programm updating refsDone. How should a strictly normalized database look like ? – kentor Sep 07 '14 at 02:13
  • @kentor, I think if it's strictly normalized, you would enter one row in a second table each time a subset of the units for an order were processed. For example, if you ship 6 units on a 20 unit order, enter a row recording the shipment, with the order id and the quantity 6. It might be worthwhile to do that to record the date time of the shipment, who processed it, etc. When the `SUM()` of those quantities is equal to the total `quantity` in your Orders table, then the order is complete. But Jared Farrish is also correct that it might not be necessary to go to that trouble. – Bill Karwin Sep 07 '14 at 04:49

1 Answers1

2

I tested your query in this SQLFiddle: http://sqlfiddle.com/#!2/32ae40/8

The query returns an empty set in several cases:

  • if $refid is null
  • if $refid is empty string ('')
  • if $min is 1 or more
  • if $limiter is 0

I think you should make sure your variables are values you expect them to be, because if they aren't the query is likely to return an empty set.

You can troubleshoot parameterized queries yourself by enabling the MySQL general query log, which logs both the prepared query and the query with parameter values included.

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Well, I use a AJAX Request to perform this Query and I check the rowCount. When its empty I output "No orders found", but this doesn't work as well, so I think there needs to be a error anywhere which I can't find. I am going to enable the MySQL general query log now. – kentor Sep 06 '14 at 21:21