Skip navigation
osgood_
osgood_
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.

554 Views   8 Replies   Latest reply: bregent, Mar 10, 2013 8:12 PM
 
Replies
  • MurraySummers
    Currently Being Moderated
    1. MurraySummers,
    Mar 9, 2013 12:11 PM   in reply to osgood_
    Report

    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:
  • osgood_
    Currently Being Moderated
    2. osgood_,
    Mar 10, 2013 1:35 AM   in reply to MurraySummers
    Report

    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

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

    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:
  • osgood_
    Currently Being Moderated
    4. osgood_,
    Mar 10, 2013 9:03 AM   in reply to MurraySummers
    Report

    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. 

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

    (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:
  • osgood_
    Currently Being Moderated
    6. osgood_,
    Mar 10, 2013 9:26 AM   in reply to MurraySummers
    Report

    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.

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

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

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

    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:
Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

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