PHP -MYSQL Aprox price Calc help

Apostle4 · 01-22-2013, 08:16 AM

Hi,

I have a table in MYSQL that has a column named price.

I have multiple other columns in the table that have type, subtype, ssubtype. etc.

eg:

Table Bob
====================================
type | subtype | ssubtype | price
====================================
1 | 2 | 1 | 40
1 | 2 | 2 | 50
1 | 2 | 3 | 60
2 | 3 | 2 | 10

I would like to know how i would go about selecting the lowest and highest price of an item given there may be 2+ versions of that product as above where there are 3 type 1's and the price ranges from 40-60.

That is the info i would like to display in a form just the 40-60.

My initial select statement looks like the below that gathers input from user defined variables.

PHP Code:


			
$result = mysql_query("SELECT * FROM bob where type='$drop' AND subtype='$drop_2' AND ssubtype='$drop_3'");

and then goes on to display the info:

PHP Code:


			
echo "<table border='2'>

<tr>

<th>Aprox Lowest Price</th>

<th>Aprox Highest Price</th>

</tr>";



while($row = mysql_fetch_array($result))

  {

  echo "<tr>";

  echo "<td>" . $row['price'] . "</td>";

  echo "<td>" . $row['price'] . "</td>";

    echo "</tr>";

  }

echo "</table>";



mysql_close($con);



}

    ?>

<form action="" method="post">

        <!--pushing this button resets the search page -->

    <input type="submit" name="Details"value="Reset Search" />

</form>



</body>

</html>

=======================================

What this produces is just a list like the following:

Aprox Lowest Price | Aprox Highest Price
$40.00 | $40.00
$50.00 | $50.00
$60 | $60.00

but what i want is:

Aprox Lowest Price | Aprox Highest Price
$40.00 | $60.00

Hope someone can help out as i'm new to this..

ps I want to then go on and provide a button that shows some of the other fields of the initial user defined variables if the user wants to know ho can supply the product type.

Fou-Lu · 01-22-2013, 01:53 PM

This is more of a SQL question.
Use the MIN and MAX aggregates:

Code:

SELECT type, MIN(price) AS minprice, MAX(price) AS maxprice FROM bob GROUP BY type WHERE type='$drop' AND subtype='$drop_2' AND ssubtype='$drop_3'

Which should return a result set with the type, minprice and maxprice within it. The WHERE condition is optional depending on how many records you want.

Apostle4 · 01-22-2013, 07:36 PM

Thanks for the reply but when i use your code it fails as per below:
***********************
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\wamp\www\index.php on line 134

***********************

line 134 is the following:

PHP Code:


			
while($row = mysql_fetch_array($result))

If I remove the items in bold in your code:

PHP Code:


			
SELECT type, MIN(price) AS minprice, MAX(price) AS maxprice FROM bob GROUP BY type WHERE type='$drop' AND subtype='$drop_2' AND ssubtype='$drop_3'

it sort of works but seems to display only the first two values of the requested data:
Aprox Lowest Price Aprox Highest Price
$40.00 $50.00

is there something i'm missing ?

Fou-Lu · 01-22-2013, 08:36 PM

Yeah you need the group by.
My bad though, the group by clause comes after the where clause, not before; I pasted the copy into the wrong place. Switch them around and it should work. 'type' is not required within the select, but helps to keep track of the data you need.

Apostle4 · 01-23-2013, 12:02 AM

Yep the query now works..

Any idea how i then go about adding a button to display the additional fields within each row if the user so desires ?

The code to display the table would be this:

PHP Code:


			
$con = mysql_connect("localhost","root","password");

if (!$con)

  {

  die('Could not connect: ' . mysql_error());

  }



mysql_select_db("DB_Name", $con);



$result = mysql_query("SELECT * FROM services where sname='$drop'AND scategory='$drop_2' AND ssubcategory='$drop_3' AND ssubcategorytype='$drop_4' AND sregion='$drop_5' AND scity='$drop_6' AND ssuburb='$drop_7'");



echo "<table border='2'>

<tr>

<th>Provider Name</th>

<th>Provider Address</th>

<th>Provider Website</th>

<th>Provider Email</th>

<th>Service Sub Type</th>

<th>Aprox Price Range</t

</tr>";



while($row = mysql_fetch_array($result))

  {

  echo "<tr>";

  echo "<td>" . $row['sprovidername'] . "</td>";

  echo "<td>" . $row['sprovideraddress'] . "</td>";

  echo "<td><a href='{$row['sproviderurl']}'>{$row['sproviderurl']}</a></td>\n";

  echo "<td><a href='mailto:{$row['semail']}'>{$row['semail']}</a></td>\n";

  echo "<td>" . $row['ssubcategorytype1'] . "</td>";

  echo "<td>" . $row['scategorycost'] . "</td>";

    echo "</tr>";

  }

echo "</table>";



mysql_close($con);



}



    ?>

Cheers

Fou-Lu · 01-23-2013, 12:17 AM

Depends on what you mean by additional fields.
You can get it to display however you want, but PHP's job is server side so it only works in request/response approaches. What you would do is either use AJAX or simply populate the entire table with data you need, and use javascript or jquery or whatever to hide and show the columns. PHP would need to keep getting information on what to show.

Apostle4 · 01-23-2013, 01:22 AM

ok so the additional fields(Column names) are as above/below:

sprovidername
sprovideraddress
sproviderurl
semail
ssubcategorytype1

Which have already been selected and defined within the $Drop, $Drop_1 etc. variables chosen by the user.

So flow would be like so:
User selects each drop down box and enters desired options.
This then displays lowest/highest price (which we got working above with your help)
A button then displays that would give the user an option to display further details about the rows that are defined between lowest/higest price.

hope that makes sense..

Apostle4 · 02-07-2013, 10:11 PM

..bump..

Fou-Lu · 02-07-2013, 11:13 PM

Okay, I may be a bit confused here. So you are just wanting to add like, a link to let you fetch specific data?
The query would be pretty much identical to the one I posted, but without the min/max aggregates and no group by. That would give all matching results of type, subtype and ssubtype. That would be all the records. You could probably use the HAVING clause with a group by if you wanted to remove the min/max off of it as well, but that's really a question for the SQL guys as they can tell you what to do for that.

Apostle4 · 02-08-2013, 02:46 AM

Quote:

Originally Posted by Fou-Lu

Okay, I may be a bit confused here. So you are just wanting to add like, a link to let you fetch specific data?
The query would be pretty much identical to the one I posted, but without the min/max aggregates and no group by. That would give all matching results of type, subtype and ssubtype. That would be all the records. You could probably use the HAVING clause with a group by if you wanted to remove the min/max off of it as well, but that's really a question for the SQL guys as they can tell you what to do for that.

Thanks for the reply, So what i'm after is the following:

The user enters there search createria and it produces a list of possible prices between $X and $Y. (This I have completed already)

After this i need a button to appear that the user can then submit and display further details of the items the price range may include:

this would be the following code:

PHP Code:


			
<?php 



{ 

$result1 = mysql_query("SELECT * FROM services where sname='$drop'AND scategory='$drop_2' AND ssubcategory='$drop_3' AND ssubcategorytype='$drop_4' AND sregion='$drop_5' AND scity='$drop_6' AND ssuburb='$drop_7'")or die(mysql_error()); 



if($result1) { 

echo "<table border='2'>

  <tr>

  <th>Provider Name</th>

  <th>Provider Address</th>

  <th>Provider Website</th>

  <th>Provider Email</th>

  <th>Service Sub Type</th>

  <th>Aprox Cost</th>

  </tr>";



while($row = mysql_fetch_array($result1))

  {

  echo "<tr>";

  echo "<td>" . $row['sprovidername'] . "</td>";

  echo "<td>" . $row['sprovideraddress'] . "</td>";

  echo "<td><a href='{$row['sproviderurl']}'>{$row['sproviderurl']}</a></td>\n";

  echo "<td><a href='mailto:{$row['semail']}'>{$row['semail']}</a></td>\n";

  echo "<td>" . $row['ssubcategorytype1'] . "</td>";

  echo "<td>$" . $row['scategorycost'] . "</td>";

  echo "</tr>";

  }

echo "</table>"; 

}



mysql_close($con);



}

}

    ?>

So the above code would execute when the user clicks on the button but i'm not sure how to add this buton.

hope that makes sense.

Apostle4 · 02-12-2013, 03:56 AM

...Bump...

Apostle4 · 02-18-2013, 07:37 PM

...Bump...

Fou-Lu · 02-19-2013, 05:57 PM

I still don't know what you are looking for here.
What is the relationship here between this last block of code and the use of MIN and MAX? Are you dealing with multiple pages here for results or what?