Sorting Data into a table using to if conditions

[mrking]

Man, I need help. :D

I have a database and the table has 4 fields.

ident, chroma, hue, value

I have the script to display ALL the data into the table. But what I am looking to do is to set a condition for displaying only data i want.

For example:

Only list the items in the table that fit this condition:

chroma=7
value=9

Here is my current code that just displays the data in a table:

[PHP]<?
include("XXXXXX XX.php");
mysql_connect($ dbhost,$usernam e,$password);
@mysql_select_d b($database) or die( "Unable to select database");
$query="SELECT * FROM rembrandt";
$result=mysql_q uery($query);

$num=mysql_numr ows($result);



mysql_close();

echo "<b><center>Dat abase Output</center></b><br><br>";

?>
<table border="1" cellspacing="6" cellpadding="6" >
<tr>
<th><font face="Arial, Helvetica, sans-serif">Pastel Identifier</font></th>
<th><font face="Arial, Helvetica, sans-serif">Hue</font></th>
<th><font face="Arial, Helvetica, sans-serif">Chroma</font></th>
<th><font face="Arial, Helvetica, sans-serif">Value</font></th>
</tr>

<?
$i=0;
while ($i < $num) {
$ident=mysql_re sult($result,$i ,"ident");
$hue=mysql_resu lt($result,$i," hue");
$chroma=mysql_r esult($result,$ i,"chroma");
$value=mysql_re sult($result,$i ,"value");
?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$ident"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$hue"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$chroma"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$value"; ?></font></td>
</tr>

<?

$i++;

}

echo "</table>";

?>[/PHP]

I have tried putting this IF statement in but with no success as I really don't know where to put it.

if ( $chroma=7 && $value=9 ) {

Can someone help me here.

Thank you!!!!!

[ronverdonk]

Why not put the IF in the SELECT statement like

$query="SELECT * FROM rembrandt WHERE chroma=7 AND value=9";

Ronald

[mrking]

I did that and it does work, but I have a bigger plan in mind as well.

Eventually, I want to have a main page with three drop downs:

Brand, Colour, Value

Where "Brand" in the TABLE in the data base. In my script it is calling 'rembrandt'.

Colour=Chroma

This will be a bit of an issue for me as the data in the database is integer based and the colour will be defined based on the range of chroma. So RED would equal a chroma of between 0 and 10. Orange between 11 and 22. I need to figure out how to code that as well.

Value=value

After the user selects what they want and hits the SUBMIT button they will be taken to a page that will display the chosen results in a table.

I thought I would start off small at first.

But if there are suggestions for implementing that code as well, I would very happy! :D

[ronverdonk]

Ok, let's assume that the user-entered values are in $chroma_value and in $value_value. Then your table makeup will be something like[php]
if ($chroma == $chroma_value AND $value == $value_value) {
?>
<tr>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$ident"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$hue"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$chroma"; ?>/font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$value"; ?></font></td>
</tr>
<?php
}
[/php]
Is this what you have been looking for?

Ronald

[mrking]

Ok, let's assume that the user-entered values are in $chroma_value and in $value_value. Then your table makeup will be something like[php]
if ($chroma == $chroma_value AND $value == $value_value) {
?>
<tr>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$ident"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$hue"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$chroma"; ?>/font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$value"; ?></font></td>
</tr>
<?php
}
[/php]
Is this what you have been looking for?

Ronald

Worked great! Thanks!!

Would you be willing to help me on the drop downs and how that data gets put into that script? See my next post...

[mrking]

In my form HTML I have:

<html>
<body>

<form action="value-3.php" method="get">
Brand:
<select name="brand">
<option value="Rembrant">Rembrant</option>
<option value="Unison">Unison</option>
</select>

Colour:
<select name="colour">
<option value="Red">Red</option>
<option value="Orange">Orange</option>
<option value="Yellow">Yellow</option>
<option value="Green">Green</option>
<option value="Blue">Blue</option>
<option value="Violet">Violet</option>
<option value="Neutral">Neutral</option>
</select>

Chroma:
<select name="chroma">
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>
</select>

Value:
<select name="value">
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>
<option value="8">8</option>
<option value="9">9</option>
</select>
<input type="submit" />
</form>

</body>
</html>

And I altered my PHP to:
[PHP]
<?
include("dbinfo .inc.php");
mysql_connect($ dbhost,$usernam e,$password);
@mysql_select_d b($database) or die( "Unable to select database");
$query="SELECT * FROM rembrandt";
$result=mysql_q uery($query);

$num=mysql_numr ows($result);



mysql_close();

echo "<b><center>Dat abase Output</center></b><br><br>";

?>
<table border="1" cellspacing="6" cellpadding="6" >
<tr>
<th><font face="Arial, Helvetica, sans-serif">Pastel Identifier</font></th>
<th><font face="Arial, Helvetica, sans-serif">Hue</font></th>
<th><font face="Arial, Helvetica, sans-serif">Chroma</font></th>
<th><font face="Arial, Helvetica, sans-serif">Value</font></th>
</tr>

<?
$i=0;
while ($i < $num) {
$ident=mysql_re sult($result,$i ,"ident");
$hue=mysql_resu lt($result,$i," hue");
$chroma=mysql_r esult($result,$ i,"chroma");
$value=mysql_re sult($result,$i ,"value");
if ($chroma==$_REQ UEST["chroma"] AND $value==$_REQUE ST["value"]) {
?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$ident"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$hue"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$chroma"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$value"; ?></font></td>
</tr>

<?
}

$i++;

}

echo "</table>";

?>
[/PHP]

Works great for that.

However I want to be able to search via COLOUR and VALUE more than anything. The VALUE search is easy but with COLOUR it is different.

I want the user to select either
RED, ORANGE, YELLOW, GREEN, BLUE, VIOLET

but letters are useless as the data for these colours are actually numerical in the database.

RED=1 to 10
Orange= 11 to 21
etc.... up to 100

How do i translate that into the PHP file?

[ronverdonk]

Stick the numerical values in the SELECT list, like this[php]<select name="colour">
<option value="1,10">Re d</option>
<option value="11,20">O range</option>
<option value="21,30">Y ellow</option>
<option value="31,40">G reen</option>
<option value="41,50">B lue</option>
<option value="51,60">V iolet</option>
<option value="61,70">N eutral</option>
</select>[/php]
Then read them both in via [php]$color_array=ex plode(',', $_POST['colour'];[/php]
Now you have 2 (min and max) in $color_array[0] and $color_array[1].
Checking it is something like (as you did with chroma and value):
[php] if ($hue >= $color_array[0] AND $hue <= $color_array[1]) {
.... etc
}[/php]
Ronald

[mrking]

Got it thanks!!!

Now my next questions...

I want the POST data to tell which TABLE to look in. i thought it would be easy but I get an error when I do this...

[PHP]
<?
include("dbinfo .inc.php");
mysql_connect($ dbhost,$usernam e,$password);
@mysql_select_d b($database) or die( "Unable to select database");
$query="SELECT * FROM $_REQUEST["brand"]";
$result=mysql_q uery($query);

$num=mysql_numr ows($result);

etc
etc
[/PHP]

it gives me this error:


Parse error: parse error, unexpected '\"', expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/content/m/r/k/mrking/html/pastel-sorting/value-3.php on line 5

Same thing when I try this:

[PHP]$query="SELECT * FROM ($_REQUEST["brand"])";[/PHP]

How do I tell the php area to sue the data from teh pull down?

Brand being:

Brand:
<select name="brand">
<option value="rembrant">Rembrant</option>
<option value="unison">Unison</option>
</select>

[ronverdonk]

You are having a double quoted string within a double quoted string. You either escase the double quotes or put the string within single quotes, i.e.[php]$query="SELECT * FROM {$_POST['db']}";[/php]Ronald

[mrking]

You are having a double quoted string within a double quoted string. You either escase the double quotes or put the string within single quotes, i.e.[php]$query="SELECT * FROM {$_POST['db']}";[/php]Ronald

That doesn't seem to work for me. I now get this error...


Warning: mysql_numrows() : supplied argument is not a valid MySQL result resource in /home/content/m/r/k/mrking/html/pastel-sorting/value-3.php on line 8
Database Output

I am thinking it can't execute numrows as it cannot find the correct table.

[ronverdonk]

You database table is, of course, 'brand' so you must replace 'db' with 'brand' like
[php]$query="SELECT * FROM {$_POST['brand']}";
[/php]Ronald

[mrking]

You database table is, of course, 'brand' so you must replace 'db' with 'brand' like
[php]$query="SELECT * FROM {$_POST['brand']}";
[/php]Ronald

hehe ;)

Yes that is what I put into it.

just found an typo in my other file. oops.

THANK YOU!!!

[ronverdonk]

You are welcome.

Ronald