[Resolved] Writing a MySQL query through PHP

GIJew

Hello Anyone,

First and foremost I try to make it a point to thank someone for their help beforehand just in case I forget, so thank you in advance.

Now onto my question.

I am working on an inventory db for work. Here is what I am trying to do:

DB NAME: INVENTORY
DB TABLE: shortages
TABLE COLUMNS: inv_id, item_no, quantity, min_level, max_level

I am trying to get all of the inventory items that have a quantity that is lower than the minimum level to display on my page. Let me show you what I have tried doing.

First suggested solution:

$sql = mysql_query("SELECT *, (Min_Stock_Level - Quantity_onHand) FROM inventory WHERE quantity_onHand < Min_Stock_Level ORDER BY Item_No ASC ") ;

while ($row = mysql_fetch_array($sql)){
  echo $row['Item_No'] . "<br>";
  echo $row['min_level'] . "<br>";
  echo $row['max_level'] . "<br>";
  echo $row['quantity'] . "<br>";
}

This solution worked getting everything under the quantity on hand but there are a bunch of records that have obnoxious numbers over the maximum levels set. It shouldn't be doing that.

My next solution went something like:

$sql = ("SELECT * FROM shortages ORDER BY Item_No")
  while ($row = mysql_fetch_array($sql)) {
  $min = $row['min_levels'];
  $max = $row['max_levels'];
  $qty = $row['quantity'];

  if($qty < $min) {
    echo (insert results here);
  }else{
    echo (default msg);
  }
}

What I was attempting to do there is store the database fields into a variable and use an if/else statement to do my comparison. You are probably laughing at me (highschool all over again) but that is all I could think of.

Please, please, please help me out, give me some suggestions. I'm learning here so please bare with me. My knowledge isn't very advanced but I catch on somewhat quickly.

Thanks again.

leatherback

Hi,

I am a bit confused as to what your question is/ Do you want to display the records that have higher values than the max allowed/ or is it displaying those records?

also: this statement: (Min_Stock_Level - Quantity_onHand) doesn't seem to make much sense if you do not attatch a name to is:
(Min_Stock_Level - Quantity_onHand) as difference

GIJew

Hi J,

I am trying to grab all of the data that has a quantity that is less than the minimum level set.

That sql statement was suggested to me on the phpfreaks forum earlier so that is why i put it there (in the hopes someone could help me out making it work right).

I apologize for lack of clarity as I have a newborn baby at home so I use him for all of my excuses right now = )

leatherback

Hi,

Congratulations on the little-one ;-) My sister is expecting her first soon, so I have some idea what is happening in your head right now

ok, so I have to assume you have a database which you know how to connect to with PhP. And in this database a table:

DB NAME: INVENTORY
DB TABLE: shortages
TABLE COLUMNS: inv_id, item_no, quantity, min_level, max_level

Now all you need to do is:

$queryOut = mysql_query("select * from shortages where quantity < min_level order by inv_id");

while($row=mysql_fetch_array($queryOut))
  {
  echo "id: ".$row['inv_id'];
  echo "item: ".$row['item_no'];
  echo "quantity: ".$row['quantity'];
  echo "Minimum: ".$row['min_level'];
  echo "Maximum: ".$row['max_level'];
  }

and the results should come popping out.

GIJew

Hey again,

Thank you for the speedy reply. I am unsure why and if you could help me out with this one I would be very much obliged.

The code you gave me grabs everything that is under but there are items in the inventory that look something similiar to this:

Item: xxx-xxx-xxx
Quantity: 53
Minimum: 4
Maximum: 35

So what I am trying to say with that is this...

There are items that have a greater value than the maximum that are being displayed as well.

Is there a way you can write an if/else statement that says grab the records that have a quantity that is less than the minimum levels but if there are records that have a quantity that is greater than the maximum levels, don't display them?

I think that makes sense?

Here is a quick example of the first 5 records that are being display from that code. Notice that last record, there are still a bunch of them.

There are 1275 records.

id: 3
item: 00-0081
quantity: 0
Minimum: 1
Maximum: 2

id: 7
item: 42
quantity: 1
Minimum: 2
Maximum: 6

id: 20
item: 0044-0005
quantity: 2
Minimum: 5
Maximum: 10

id: 27
item: 625212
quantity: 1
Minimum: 2
Maximum: 5

id: 42
item: 14
quantity: 28
Minimum: 5
Maximum: 20

leatherback

Hi,

I am not sure what this could be. I am thinking it could be the columtypes you are using.. Could it be that the quantity and / or minimum / maximum levels are textfields instead of some integer fields?
Other wise you should not get fields which have a higher than minimum (Which includes higher then maximum!) quantity..

You could write an if/then statement, but I suggest you check this first, becuas I think it is a database problem, more then a script problem..

GIJew

Ya' know J. since having my son I have tried not to swear that much around him. Just know I probably said fuck about twenty two times I was so happy.

It was a simple fix in my database changing my VARCHAR fields into INT fields. Worked like a charm. You are the man.

Tell your sister good luck with her child. They are a blast and as hard as it is to do, SLEEP, SLEEP, SLEEP because you aren't getting any (of anything) afterwards.

Take care

leatherback

You're welcome.
Enjoy PhP