Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13
  1. #1
    New Coder
    Join Date
    Jan 2013
    Posts
    13
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Question PHP -MYSQL Aprox price Calc help

    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.
    Last edited by Apostle4; 01-22-2013 at 10:13 PM.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,980
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #3
    New Coder
    Join Date
    Jan 2013
    Posts
    13
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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 typeMIN(price) AS minpriceMAX(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 ?
    Last edited by Apostle4; 01-22-2013 at 10:20 PM.

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,980
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • Users who have thanked Fou-Lu for this post:

    Apostle4 (01-22-2013)

  • #5
    New Coder
    Join Date
    Jan 2013
    Posts
    13
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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
    Last edited by Apostle4; 01-23-2013 at 12:05 AM.

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,980
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #7
    New Coder
    Join Date
    Jan 2013
    Posts
    13
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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..

    Last edited by Apostle4; 01-23-2013 at 01:53 AM.

  • #8
    New Coder
    Join Date
    Jan 2013
    Posts
    13
    Thanks
    1
    Thanked 0 Times in 0 Posts
    ..bump..

  • #9
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,980
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #10
    New Coder
    Join Date
    Jan 2013
    Posts
    13
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    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.

  • #11
    New Coder
    Join Date
    Jan 2013
    Posts
    13
    Thanks
    1
    Thanked 0 Times in 0 Posts
    ...Bump...

  • #12
    New Coder
    Join Date
    Jan 2013
    Posts
    13
    Thanks
    1
    Thanked 0 Times in 0 Posts
    ...Bump...

  • #13
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,980
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    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?
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •