Skip navigation
Currently Being Moderated

mySQL/php getting prices from database

Mar 9, 2013 11:27 AM

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.

 
Replies
  • Currently Being Moderated
    Mar 9, 2013 12:11 PM   in reply to osgood_

    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?

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 10, 2013 6:16 AM   in reply to osgood_

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 10, 2013 9:15 AM   in reply to osgood_

    (assuming your recordset also contains the product Manufacturer name)

     

    foreach ($variable as $value) {

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

    }

     

    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));

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 10, 2013 9:32 AM   in reply to osgood_

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

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 10, 2013 8:12 PM   in reply to osgood_

    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));

     
    |
    Mark as:
  • Currently Being Moderated
    Aug 19, 2013 11:09 PM   in reply to MurraySummers

    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; ?>

     
    |
    Mark as:
  • Currently Being Moderated
    Aug 20, 2013 7:38 AM   in reply to osgood_

    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'];

    }

     
    |
    Mark as:
  • Currently Being Moderated
    Aug 20, 2013 8:28 AM   in reply to osgood_

    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>

     
    |
    Mark as:
  • Currently Being Moderated
    Aug 20, 2013 11:11 AM   in reply to osgood_

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Aug 21, 2013 1:13 AM   in reply to osgood_

    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>

     
    |
    Mark as:
  • Currently Being Moderated
    Aug 21, 2013 10:15 AM   in reply to osgood_

    >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.

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (1)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points