comparing column # values

[generic]

I'm trying to figure out a way to query my available units column ('available') so that I can either continue to the contract (if there is sufficient stock) or give the user an error message (if there isn't enough available of a certain product). My guess is it will require some sort of 'reserved' column as well for each product.

The project is just a simple inventory control and rental contract generator. It's pretty straitforward - for example I have table 'inventory' that holds rows 'product', 'available' and 'cost' (product name, available units, rental cost per day).

The second part of the script (the contract generator) is what's giving me grief. I want to have a dropdown list of what's in the 'product' column so we can select it and have it parsed into the contract to be printed. BUT, if there isn't enough stock available to rent (as per what's in the 'available' column for that product) then it produces an error and won't continue to the contract.

I know this is a tall order but if anyone has any advice, I'm pretty new to mySQL and I can use any help I can get. (and I've already looked over the PHP and mySQL sites so you really don't need to post links to them 🙂 )

Thanks in advance!

gen

[cgraz]

if you want atleast 4 in the available column for that particular product

$query = mysql_query("SELECT * FROM your_table WHERE product_id='$product_id' AND available >= '4'");

Where product id is what identifies one product from another.

What code are you using for your drop down? I don't know if I understand exactly what you're saying, but t
he following should work without any errors

$result=mysql_query("SELECT DISTINCT field_name FROM your_table");
$row_array=mysql_fetch_row($result);
$string='<select name=product><option value=>Any Product</option>';
for ($i=0;$i < mysql_num_rows($result);$i++)
{
	if ($row_array[0]=="")
	{
		$row_array=mysql_fetch_row($result);
	}
	else
	{
	$string .='<option value="'.$row_array[0].'">'.$row_array[0];
	$row_array=mysql_fetch_row($result);
	}
}
$string .='</SELECT>';
echo $string;

Cgraz

[mattr]

Originally posted by cgraz
if you want atleast 4 in the available column for that particular product

$query = mysql_query("SELECT * FROM your_table WHERE product_id='$product_id' AND available >= '4'");

[/i]

If they are numeric columns then do NOT enclose in quotes. It should be

$query = mysql_query('SELECT * FROM your_table WHERE product_id=' . intval( $product_id ) . ' AND available >= 4');

[[deleted]]

Numeric columns don not have to be enclosed by quotes, but they are allowed to be.

In some cases it is required, for example with some fractions you must enclose in wuotes otherwise SQL could choke on the comma or dot.

[mattr]

Originally posted by vincente
Numeric columns don not have to be enclosed by quotes, but they are allowed to be.

In some cases it is required, for example with some fractions you must enclose in wuotes otherwise SQL could choke on the comma or dot.

It is only 'allowed' in MySQL due to MySQL's use of non-transactional queries. In the SQL standard insertion of a non-numeric into a numeric column should roll back the transaction with a conversion error. Since you can't roll back (with certain table types, and of course initially you only had ISAM) in MySQL they took the kludge approach and silently change out-of-bounds and other invalid types.

This is not a feature, but a horrible workaround. It is non-standard and will not work in any other RDBMS. You should never use this syntax as it is horribly non-standard (I can accept things like LIMIT and AUTO_INCREMENT since analogous constructs are in most DBMS') and exists in few, if any, other DBMS.

Commas are not in the standard (I suppose you are talking about ‘123,000’), but some DBMS' can handle them provided you set it up correctly. Insertion of 12/24 does not break SQL, the division is performed and the decimal is stored in the row. 1200.00 works provided your datatype is NUMERIC or DECIMAL.

[[deleted]]

As with most things in the SQL standard, none of the databases take much notice of these relatively small things. Neither postgresql not mysql seem to mind quotes around integers.

One Q though: what on earth does being a transationcal database have to do with it? Why would MySQL be allowed to have a more lenient approach to typecasting than other databases just because it doesn't know transactions?

[mattr]

PostgreSQL is probably following MySQL's lead in order to make it easier for users to switch. On the contrary, most vendors follow the 'relatively small things' -- just not MySQL.

Think outside the MySQL box and it is fairly obvious why MySQL is 'loose' with conversions. Since you cannot commit or rollback (for the sake of typing I am going to ommit INNODB tables; they did not exist when this convention was established) transactions a single failure in a bulk insert (insert into select *), update, etc. will leave your database in an inconsistent state. So, they took the position that anything that would otherwise cause an error (out of bounds, wrong type, etc.) would be silently rounded, truncated, etc..

As an example, UPDATE table SET price = price * 2

It is not inconceivable that a price will go out of bounds. On any other DBMS the transaction would fail on a bounds, roll back, and tell you an error (not necessarily in that order). Your database is exactly the same as it was before.

If MySQL took the above approach (minus the rollback) you would be left with an inconsistent database -- who knows what rows were updated and which weren't. Instead, MySQL will silently set it to the MAX of the column type and continue on. Is a 127 value an out of bounds round-down or a real value? You can't tell! In some ways I think this way is worse because it does not even pop a warning letting you know which rows break.

Case in point are the users who use auto-increments on small datatypes and keep wondering why they have 100 products with the same ID.

The short of it is there is no logical or compelling reason to deviate from the standard in this manner. All it does is reinforce bad coding skills and make your code extraordinarily non-portable.

[[deleted]]

So basically it doesn't have anything to do with transactions at all, it's just minimising the number of error-reports 🙂

Don't get me wrong on this, I'm all for sticking to standards, I was just wondering what transactions had to do with it.

"Case in point are the users who use auto-increments on small datatypes and keep wondering why they have 100 products with the same ID. "

Actually in mysql the auto_increment column must be Primery Key, so you end up with 'cannot insert duplicate value for key 1' errors, not with a bunch of duplicates 🙂

[mattr]

Originally posted by vincente
So basically it doesn't have anything to do with transactions at all, it's just minimising the number of error-reports 🙂

It has everything to do with transactions. They can't throw errors because there are no transactions.

[[deleted]]

"It has everything to do with transactions. They can't throw errors because there are no transactions."

Ofcourse they can throw as many errors as they like. They just chose not to, just like mysql chose not to implement PK/FK, and they chose not to do subqueries etc...

Like you said, the inserts fail silently: your data is still corrupt, error or no-error. The lack of transactions just makes it worse, but (no transaction + no-error) != consistent data.

But this is turning into a bit of an off-topic me-against-you thing, so lets just say I disagree with you on this matter and get on with more important things, such as Christmas!

Merry Christmass everybody! 🙂