21 Replies Latest reply: Aug 21, 2013 10:23 AM by osgood_ RSS

    mySQL/php getting prices from database

    osgood_ CommunityMVP

      I have the need to get some prices from a database and display on a page.

       

      I can use a repeat region (see below) but have the necessity to reset it before looping through the database again to get the other prices. I need to do this because the prices are scattered all over the page.

       

      <?php do { ?>

      <?php

      if ($row_productPrice['productName'] == "Air Nozzles") {

      echo $row_productPrice['productPrice'];

      }

      ?>

      <?php } while ($row_productPrice = mysql_fetch_assoc($productPrice)); ?>

       

      Then reset the recordset (before looping through the database again):

       

      <?php

      // reset the recordset after a repeat region

      mysql_data_seek($productPrice, 0);

       

      // get the first row from the recordset

      $row_productPrice = mysql_fetch_assoc($productPrice);

      ?>

       

      OR I can do it by repeating the same mysql query over and over again before echoing the productPrice:

       

      <?php

      mysql_select_db($database_conProduct, $conProducts);

      $query_productPrice = "SELECT productPrice FROM products WHERE productName = 'Air Nozzles'";

      $productPrice = mysql_query($query_productPrice, $conProducts) or die(mysql_error());

      $row_productPrice = mysql_fetch_assoc($productPrice);

      $totalRows_productPrice = mysql_num_rows($productPrice);

      ?>

       

      <?php echo $row_productPrice['productPrice']; ?>

       

       

      Both solutions work but seem a bit messy to me is there any alternative solution which would make the code more efficient?

       

      Cheers

       

      Os.

        • 1. Re: mySQL/php getting prices from database
          MurraySummers CommunityMVP

          Resetting the data pointer is not messy really - I do this often.  But why not just read the recordset once, and set up all your prices during that read?

           

          So, for the initial recordset, pull both product name *and* product price ($rsProducts), then have something like this -

           

          do {

          switch ($row_rsProducts['productName']) {

          case 'Air Nozzle':

               $airNozzle = $row_rsProducts['productPrice'];

               break;

          case 'Hose Clamp':

               $hoseClamp = $row_rsProducts['productPrice'];

               break;

          case 'Venturi Tube':

               $venturiTube = $row_rsProducts['productPrice'];

               break;

          /* continue for each product name, and you will now have a series of variables, with sensible names, each containing the desired price */

               } /* end switch */

          } while ($row_rsProducts = (mysql_fetch_assoc($rsProducts));

           

          Then when you need a price on the page (like for a venturi tube), you can just do -

           

          echo $venturiTube;

           

          Would that work?

          • 2. Re: mySQL/php getting prices from database
            osgood_ CommunityMVP

            Whooha! That's brilliant.

             

            Much more efficient and less messy than my 2 solutions.

             

            Went through and changed all the affected pages, works like a dream.

             

            Although I've come across the php 'switch' before I never really understood how it works or in what circumstances to deploy it so have never implemented it up until now.

             

            Gotta think deep about this stuff and I've only just scratched the surface but already doing things which was way beyond my capabilities a couple of years ago.

             

            You learn something new everyday!

             

             

            Thanks Murray

            • 3. Re: mySQL/php getting prices from database
              MurraySummers CommunityMVP

              Switch is like a series of successive if() commands. The difference is that when one of them succeeds, you do not continue to execute the rest. I find a way to use it all the time, and think this is a particularly good application.

               

              Here's something else I often do - I pull a recordset, then dump the entire recordset into an array variable so that I can have free access to the individual elements of the total recordset. It's simple enough to do something like this -

               

              do {

                $variable[] = $row_rsWhatever;

              } while $row_rsWhatever=(mysql_fetch_assoc($rsWhatever));

               

              Then $variable[0] contains the first record, $variable[1] contains the second record, etc.

              • 4. Re: mySQL/php getting prices from database
                osgood_ CommunityMVP

                MurraySummers wrote:

                 

                Switch is like a series of successive if() commands. The difference is that when one of them succeeds, you do not continue to execute the rest. I find a way to use it all the time, and think this is a particularly good application.

                 

                 

                Yup, I knew when I saw the solution it was a perfect fit for what I was wanting to do. Will use it more often in future, I'm sure the opportunity will arise or it will just naturally work its way into my workflow now I understand it more.

                 

                 

                MurraySummers wrote:

                 

                Here's something else I often do - I pull a recordset, then dump the entire recordset into an array variable so that I can have free access to the individual elements of the total recordset. It's simple enough to do something like this -

                 

                do {

                  $variable[] = $row_rsWhatever;

                } while $row_rsWhatever=(mysql_fetch_assoc($rsWhatever));

                 

                Then $variable[0] contains the first record, $variable[1] contains the second record, etc.

                 

                 

                That's a good one too. How would I use it to echo out the productManufacturer details in the 1st record:

                 

                productName     productPrice      productManufacturer

                Chocolate           1.50                     Cadburys

                 

                What would $variable[0] look like? Comma seperated? - Chocolate,1.50, Cadburys

                 

                I'll have to do some investigation. Arrays are another aspect I've only just touched upon in the past but the more I discover  the more the possibilities seem to open up.

                 

                Os. 

                • 5. Re: mySQL/php getting prices from database
                  MurraySummers CommunityMVP

                  (assuming your recordset also contains the product Manufacturer name)

                   

                  foreach ($variable as $value) {

                  echo ($value['productManufacturer']=='Cadburys'?$value['productManufacturer']:"");

                  }

                   

                  The nice thing is that you don't have to reset the array pointer as it does so automatically when you exit the foreach(). And assuming that the Cadburys record is the very first one in the recordset and that the fields are pulled in the order shown, then $variable[0] would look like what you have.

                   

                  You can test this yourself by putting this immediately after looping through the recordset to populate the $variable array -

                   

                  echo "<pre>";exit(print_r($variable));

                  • 6. Re: mySQL/php getting prices from database
                    osgood_ CommunityMVP

                    Ok, I'm going to have to get my head around this by doing some testing to see what results are returned.

                     

                    Unfortunately my brain becomes a bit scrambled until I do some 'live' experimenting to unravel the various aspects of what arrays, foreach etc are doing. When I see the results on the page my brain can usually grasp what is going on.

                     

                    Thanks again for your input Murray. I'm pretty sure I'll be using this approach more often in future to streamline the work and processing. Up until now I've managed to do what I need but not necessarily always using the most efficient approach.

                     

                    Os.

                     

                    Off to watch football now.

                    • 7. Re: mySQL/php getting prices from database
                      MurraySummers CommunityMVP

                      Good luck, Os! And to your team, too.

                      • 8. Re: mySQL/php getting prices from database
                        bregent CommunityMVP

                        What you are really looking for is a dictionary object - which is just an associative array - so you can reference the array element by name rather than by index. I'm not a php'er but it would be something like:

                         

                        do {

                         

                        $DictProduct[$row_productPrice['productname']] = $row_productPrice['productPrice'];

                        }

                        while ($row_productPrice = mysql_fetch_assoc($rSproducts));

                        • 9. Re: mySQL/php getting prices from database
                          TjmSmith Community Member

                          Hi Murray,

                           

                          I have a similar problem that I can't seem to get to work.  My code is as follows.  I know I'm connecting to the database/table since the last echo line does display the total records in the table.  But all I get from the lines where I echo variable[0] and variable[1] is the word- array.  Also, there are 8 fields in each record so how would I break the record down into separate variables array?

                          BTW:  when I put the open parenthesis after the equal sign in the while statement I as per your example above get a syntax error!  Thanks in advance.  TJ

                           

                          mysql_select_db($database_faa112, $faa112);
                          $query_getPXinventory = "SELECT * FROM px_inventory";
                          $getPXinventory = mysql_query($query_getPXinventory, $faa112) or die(mysql_error());
                          $row_getPXinventory = mysql_fetch_assoc($getPXinventory);
                          $totalRows_getPXinventory = mysql_num_rows($getPXinventory);
                          ?>
                          <?php
                          do {
                          $variable[] = $row_getPXinventory;
                          } while ($row_getPXinventory = mysql_fetch_assoc($getPXinventory));
                          ?>
                          <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
                          <html xmlns="http://www.w3.org/1999/xhtml">
                          <head>
                          <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
                          <title>Untitled Document</title>
                          </head>

                          <body>


                          <?php echo $variable[0]; ?><br />
                          <?php echo $variable[1]; ?> <br />
                          <?php echo $totalRows_getPXinventory; ?>

                          • 10. Re: mySQL/php getting prices from database
                            osgood_ CommunityMVP

                            You should be using the below to echo out the $variable array:

                             

                            <?php

                            foreach ($variable as $value) {

                                echo $value."<br>";

                            }

                            ?>

                            • 11. Re: mySQL/php getting prices from database
                              David_Powers CommunityMVP

                              osgood_ wrote:

                               

                              How would I use it to echo out the productManufacturer details in the 1st record:

                               

                              productName     productPrice      productManufacturer

                              Chocolate           1.50                     Cadburys

                              Basically, you need to create a multidimensional array like this:

                               

                              $products = array();

                              for ($i = 0; $ < $result_num_rows; $i++) {

                                    $row = mysql_fetch_assoc($result);

                                    $products[$i]['productName'] = $row['productName'];

                                    $products[$i]['productPrice'] = $row['productPrice'];

                                    $products[$i]['productManufacturer'] = $row['productManufacturer'];

                              }

                               

                              You can then loop through the array like this:

                               

                              foreach ($products as $product) {

                                  echo $product['productName'] . ' is made by ' . $product['productManufacturer'] . ' and costs ' . $product['productPrice'];

                              }

                              • 12. Re: mySQL/php getting prices from database
                                osgood_ CommunityMVP

                                Thanks David, have copied that and pasted in my snippets file for future use.

                                 

                                While you are around is there any bettter way to do this - I'm getting info from a database and repeating the results in a  php while loop which I can do like below:

                                 

                                 

                                $sql = 'SELECT * FROM products WHERE product_manufacturer = "'.$productManufacturer.'" AND product_category = "'.$productCategory.'"';

                                //get number of rows

                                $num_rows = $conn->query($sql)->num_rows;

                                $products = $conn->query($sql) or die($conn->error);

                                 

                                 

                                However using the same query is it possible to get the result so I can echo the 'product category' out on the page 'outside' of a repeat loop?

                                 

                                I can get it like below by adding another query:

                                 

                                $sql = 'SELECT * FROM products WHERE product_category = "'.$productCategory.'"';

                                $productPortable = $conn->query($sql) or die($conn->error);

                                $productPortable = $productPortable->fetch_assoc();

                                ?>

                                 

                                Then echo it out as the main page header like this:

                                 

                                <h1><?php echo $productPortable['product_category']; ?></h1>

                                 

                                i.e, so the page is something like:

                                Pickle

                                <!-- repeat region -->

                                Branston Pickle

                                Heinz Pickle

                                Pan Yan Pickle

                                Premier Pickle

                                 

                                 

                                Obviously this works for the while region:

                                 

                                <?php while($row = $products->fetch_assoc()) { ?>

                                 

                                <?php echo $row['product_manufacturer']; ?> <?php echo $row['product_category']; ?>

                                 

                                <?php } // end while loop ?>

                                 

                                BUT this obviously doesnt work outside of the while region:

                                 

                                <h1><?php echo $row['product_category']; ?></h1>

                                 

                                WHILE this does:

                                 

                                <h1><?php echo $productPortable['product_category']; ?></h1>

                                 

                                 

                                Just wondered if the sqli query could be combined instead of being seperate?

                                • 13. Re: mySQL/php getting prices from database
                                  David_Powers CommunityMVP

                                  That should be quite simple. Just get the first row outside the loop, and then use a do... while loop.

                                   

                                  <?php $row = $products->fetch_assoc(); ?>

                                  <h1><?php echo $row['product_category']; ?></h1>

                                  <ul>

                                  <?php do { ?>

                                  <li>

                                  <?php echo $row['product_manufacturer'] . ' ' . $row['product_category']; ?>

                                  </li>

                                  <?php } while($row = $products->fetch_assoc());

                                  </ul>

                                  • 14. Re: mySQL/php getting prices from database
                                    osgood_ CommunityMVP

                                    Brilliant, I've just tacked that onto the main query. I can do away with the additional query now. Thanks!

                                     

                                     

                                    $sql = 'SELECT * FROM products WHERE product_manufacturer = "'.$productManufacturer.'" AND product_category = "'.$productCategory.'"';

                                    //get number of rows

                                    $num_rows = $conn->query($sql)->num_rows;

                                    $products = $conn->query($sql) or die($conn->error);

                                    $row = $products->fetch_assoc();

                                    • 15. Re: mySQL/php getting prices from database
                                      bregent CommunityMVP

                                      Just one additional comment about your SQL

                                       

                                      >$sql = 'SELECT * FROM products WHERE...

                                       

                                      You should never use " SELECT * " unless you are using all of the fields from your table. It will lead to performance and scaling problems. List the columns that you need explicitly.

                                      • 16. Re: mySQL/php getting prices from database
                                        osgood_ CommunityMVP

                                        I have an problem Houston.

                                         

                                        The below works good if it is inserted BEFORE the while repeat loop (like below):

                                         

                                        <?php $row = $products->fetch_assoc(); ?>

                                        <h1><?php echo $row['product_category']; ?></h1>

                                         

                                        <?php while($row = $products->fetch_assoc()) { ?>

                                        <?php echo $row['product_manufacturer']; ?> <?php echo $row['product_category']; ?>

                                        <?php } // end while loop ?>

                                         

                                         

                                        However if it is inserted AFTER the while repeat loop (like below) no result is returned:

                                         

                                        <?php while($row = $products->fetch_assoc()) { ?>

                                        <?php echo $row['product_manufacturer']; ?> <?php echo $row['product_category']; ?>

                                        <?php } // end while loop ?>

                                         

                                        <?php $row = $products->fetch_assoc(); ?>

                                        <h1><?php echo $row['product_category']; ?></h1>

                                         

                                        Is there a workaround for that, without the need to write the whole query again after the while loop - can I reset the query with a simple line of code to go back and loop through the database again?

                                         

                                        Cheers

                                         

                                        Os

                                        • 17. Re: mySQL/php getting prices from database
                                          osgood_ CommunityMVP

                                          bregent wrote:

                                           

                                          Just one additional comment about your SQL

                                           

                                          >$sql = 'SELECT * FROM products WHERE...

                                           

                                          You should never use " SELECT * " unless you are using all of the fields from your table. It will lead to performance and scaling problems. List the columns that you need explicitly.

                                           

                                          Really? It's taught in a lot of tutorials or used to be. Is it likely to lead to much of a noticable performance/scaling issue?

                                           

                                          http://www.w3schools.com/sql/

                                          • 18. Re: mySQL/php getting prices from database
                                            David_Powers CommunityMVP

                                            osgood_ wrote:

                                             

                                            However if it is inserted AFTER the while repeat loop (like below) no result is returned:

                                            It's not clear why you want to insert it after the loop, but the simple answer is to assign it to a variable inside the loop, and then use the variable.

                                             

                                            <?php while($row = $products->fetch_assoc()) {

                                            // assign the valuable to the variable only once

                                            if (!isset($product_category)) {

                                                  $product_category = $row['product_category'];

                                            }  ?>

                                            <?php echo $row['product_manufacturer']; ?> <?php echo $product_category; ?>

                                            <?php } // end while loop ?>

                                             

                                            <?php $row = $products->fetch_assoc(); ?>

                                            <h1><?php echo $product_category; ?></h1>

                                            • 19. Re: mySQL/php getting prices from database
                                              osgood_ CommunityMVP

                                              David_Powers wrote:

                                               

                                              osgood_ wrote:

                                               

                                              However if it is inserted AFTER the while repeat loop (like below) no result is returned:

                                              It's not clear why you want to insert it after the loop, but the simple answer is to assign it to a variable inside the loop, and then use the variable.

                                               

                                              <?php while($row = $products->fetch_assoc()) {

                                              // assign the valuable to the variable only once

                                              if (!isset($product_category)) {

                                                    $product_category = $row['product_category'];

                                              }  ?>

                                              <?php echo $row['product_manufacturer']; ?> <?php echo $product_category; ?>

                                              <?php } // end while loop ?>

                                               

                                              <?php $row = $products->fetch_assoc(); ?>

                                              <h1><?php echo $product_category; ?></h1>

                                               

                                              The reason I need to freely echo the results is because I have a number of instances on the page where the information needs to appear, these are before and after the repeat loop.

                                               

                                              For instance I have product_description which is generic to all the specific product_category database fields ie the same information

                                               

                                              So I might want to echo out the product_description only ONCE along side the repeat loop to describe the product_category but outside of it.

                                               

                                              I'll give your new solution a go. It works very well as I say if it comes before the repeat loop but I might well revert to my original solution because for the kind of senario that I'm using it seems to work a little more smoothly and I don't have to fill the database rows with the same information time and again, just the once.

                                               

                                              I'm most likely not really doing this in the most economical way as I would guess I probably need to break up the database tables a bit more into relationalship tables but that's a bit of a step too far for me at the moment.

                                               

                                              Thanks David

                                               

                                              Onwards and upwards.

                                              • 20. Re: mySQL/php getting prices from database
                                                bregent CommunityMVP

                                                >Really? It's taught in a lot of tutorials or used to be.

                                                 

                                                Yeah.

                                                 

                                                >Is it likely to lead to much of a noticable performance/scaling issue?

                                                 

                                                It depends. Consider that each column that is selected but unused is wasting memory. Multiply that by the number of rows returned, and again by the number of concurrent users. In our applications, we could easily run out of memory using that syntax.

                                                • 21. Re: mySQL/php getting prices from database
                                                  osgood_ CommunityMVP

                                                  bregent wrote:

                                                   

                                                   

                                                  >Is it likely to lead to much of a noticable performance/scaling issue?

                                                   

                                                  It depends. Consider that each column that is selected but unused is wasting memory. Multiply that by the number of rows returned, and again by the number of concurrent users. In our applications, we could easily run out of memory using that syntax.

                                                  Good point, I'll take that into account. Doesn't seem to have posed much of an issue so far to me but that's maybe because the information in my databases is usually quite limited.