MySql - From one value to another

I understand how to just get values from using a form. Say if someone wants only red cups....then I can pull only items that have a color of red....

what i dont understand is how to do a range of results

say I have a 2 form fields. the user selects a range

starting weight: 1lb 2lb 3lb 4lb 5lb
ending weight: 1lb 2lb 3lb 4lb 5lb

i need to get the results of say

2lb to 5 lb

3lb to 4lb

what is the correct Sql function

 

SELECT something FROM table WHERE something BETWEEN value_from AND value_to

 

that makes sense.

what if I have an array than needs an order but it not naturally ordered

for instance like quality of diamonds, the order is WS1, WS2, SI3, VS1 etc

the first letters are not in the order of quality.....

how can this be used in a BETWEEN statment....

 

You need another table to define the order. For example:


Table "quality":

quality_id	quality_mark	quality_order
-----------------------------------------
1		WS1		1
2		WS2		2
3		VS1		4
4		SI3		3

Code (markup):

Table "diamonds":

diamond_id	diamond_quality	diamond_info
-----------------------------------------------
1		1		info...
2		3		info...
3		2		info...
4		2		info...
5		1		info...
6		4		info...
7		3		info...

Code (markup):

This is the query you need:

SELECT
	diamonds.*,
	quality.*
FROM
	diamonds,
	quality
WHERE
	quality_order BETWEEN 1 AND 3
AND
	diamonds_quality = quality_id

Code (markup):

 

Again Thank you. This almost makes sense


My client will supply me with an Excel spreadsheet

Cut Color Clarity

He will be using the quality mark and not the code....

after i upload the spreadsheet to mysql will the spreasheet be able to define quality mark to quality id

 

You'll probably have to upload everything to one table. After you do that, post the "CREATE TABLE" statement (table structure) here and I'll tell you how to make the necessary changes.

 

You are being very helpful. Everytime I think I understand something about this a loop is thrown my way.

I take things in steps so I can see what is going on. And as far as I can tell the code I am about to show should yield two rows, but for some reason it is showing the 2 rows 5 times

I don't even have 10 rows in either table! and I cannot tell why it is calling it 5 times.

Shouldn't this code:

$query="SELECT diamonds.*, clarity.*  FROM diamonds, clarity WHERE diamonds.clarity = 'VS1'  ";

Code (markup):

Render the same results as this code

$query="SELECT *  FROM diamonds WHERE clarity = 'VS1'  ";

Code (markup):

It pulls the same results but repeats it 5 times when I use it here

<?php
	//Open a connection to the mysql server
	


	$link=mysql_connect("localhost","root","");
	if(!$link) {
		print("Failed to establish connection to mysql server!");
		exit();
	}
	//Select the database
	$status=mysql_select_db("diamonds");


//Run query WHERE shape = '$shape'
	$query="SELECT diamonds.*, clarity.*  FROM diamonds, clarity WHERE diamonds.clarity = 'VS1'  ";
	

	$rs=mysql_query($query);
	if(!$rs) {
		print("Query Error: ".mysql_error());
	}
	$numrows=mysql_num_rows($rs);
	print("Number of rows returned: $numrows <br />
");





if (!$rs) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}

if (mysql_num_rows($rs) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
}


while ($row = mysql_fetch_assoc($rs)) {

echo "<ul>";
    echo "<li>".$row["clarity"].$row["stock number"]."</li>";
   echo "</ul>"; }
   

    ?>

Code (markup):

 

The first query should be:

$query="SELECT diamonds.*, clarity.*
FROM diamonds, clarity
WHERE diamonds.clarity = 'VS1' [color=red]AND diamonds_clarity = clarity_id[/color]";

Code (markup):