Better way to do this?

Hi all, I’m in the process of creating a price comparison site, coming along nicely. On the homepage I have a summary of the cheapest prices for various items. I don’t think I should have lots of mysql-queries to the same table over and over again.

The problem is that I want to get results for things with a prod_code as you can see below:

<?php
include('includes/connection.php');
$d90 = mysql_query("SELECT * FROM compare WHERE prod_code=25");
$d60 = mysql_query("SELECT * FROM compare WHERE prod_code=26");
$d50 = mysql_query("SELECT * FROM compare WHERE prod_code=27");
$row = mysql_fetch_array($d90);
$row1 = mysql_fetch_array($d60);
$row2 = mysql_fetch_array($d50);
?>
<!-- BOF Main Content -->
<div id="mainContent">
<div class="container"><img src="../images/nikon_dslrs_header.jpg" width="541" height="62" alt="Nikon DSLRs" /></div>
<div class="container"><img src="../images/nikon_d90.jpg" width="178" height="145" alt="Nikon D90" /></div>
<div class="container"><img src="../images/nikon_d300.jpg" width="184" height="145" alt="Nikon D300" /></div>
<div class="container"><img src="../images/nikon_d300s.jpg" width="179" height="145" alt="Nikon D300s" /></div>
<div class="container" style="background-image: url(../images/nikon_d90_backing.jpg); background-repeat: no-repeat; width: 178px; height: 56px;"><span style="font-size: 11px;">Best price: <b>&pound;<?php echo $row['price'] ?></b></span><br><span style="font-size: 11px; font-weight: bold; color: #CCC;"><?php echo $row['store'] ?></span></div>
<div class="container" style="background-image: url(../images/nikon_d300_backing.jpg); background-repeat: no-repeat; width: 184px; height: 56px;">Hello</div>
<div class="container" style="background-image: url(../images/nikon_d300s_backing.jpg); background-repeat: no-repeat; width: 179px; height: 56px;">Hello</div>

So I’m not sure how I could get around this?

You could use an OR on the prod_code in the SELECT statement

Hi, thanks for your message. But how to do I echo out the different values such as those of prod_code 26? Tried using the OR but echoing out all the same results.

You’ll need to loop through the rows from that single SELECT statement using PHP.

See example #2:
http://www.php.net/manual/en/function.mysql-fetch-array.php

If you want the items in a certain order, you can specify it by using the ORDER BY clause in mysql: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

This may help you in the right direction…


$results = mysql_query("SELECT * FROM compare WHERE prod_code IN (25, 26, 27)");
while($row = mysql_fetch_assoc($results))
{
	// Do something with $row.
}

I’ve used mysql_fetch_assoc() instead of mysql_fetch_array() as if you’re not using numeric indexes, then you may as well save on memory by only using field names as keys. If you don’t know what I just said, then don’t worry, use mysql_fetch_assoc() anyway.

Hi there, cheers for the help. Do I have to loop it? Just I’m echoing out the results all over the page in different places, not a set table as it where. See the attached code below if it’s any help.


<!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" xml:lang="en" lang="en">
<?php include('includes/head.ini'); ?>
<body>
<div id="mainWrapper">
<?php include('includes/tpl_nav.ini'); ?>
<div class="contentWrap">
<?php include('includes/leftnav.ini'); ?>
<?php
include('includes/connection.php');


$results = mysql_query("SELECT * FROM compare WHERE prod_code IN (25, 26)");
$row = mysql_fetch_assoc($results);

?>
<!-- BOF Main Content -->
<div id="mainContent">
<div class="container"><img src="../images/nikon_dslrs_header.jpg" width="541" height="62" alt="Nikon DSLRs" /></div>
<div class="container"><img src="../images/nikon_d90.jpg" width="178" height="145" alt="Nikon D90" /></div>
<div class="container"><img src="../images/nikon_d300.jpg" width="184" height="145" alt="Nikon D300" /></div>
<div class="container"><img src="../images/nikon_d300s.jpg" width="179" height="145" alt="Nikon D300s" /></div>
<div class="container" style="background-image: url(../images/nikon_d90_backing.jpg); background-repeat: no-repeat; width: 178px; height: 56px;"><div style="margin: 5px 0px 0px 27px;"><span style="font-size: 11px;">Best price: <b>&pound;<?php echo $row['price'] ?></b></span><br><span style="font-size: 11px; font-weight: bold; color: #666;"><?php echo $row['store'] ?></span></div></div>

<div class="container" style="background-image: url(../images/nikon_d300_backing.jpg); background-repeat: no-repeat; width: 184px; height: 56px;"><div style="margin: 5px 0px 0px 30px;"><span style="font-size: 11px;">Best price: <b>&pound;<?php echo $row['price'] ?></b></span><br><span style="font-size: 11px; font-weight: bold; color: #666;"><?php echo $row['store'] ?></span></div></div>
<div class="container" style="background-image: url(../images/nikon_d300s_backing.jpg); background-repeat: no-repeat; width: 179px; height: 56px;">Hello</div>


<div class="container"><img src="../images/nikon_d700.jpg" width="178" height="179" alt="Nikon D700" /></div>
<div class="container"><img src="../images/nikon_d3.jpg" width="184" height="179" alt="Nikon D3" /></div>
<div class="container"><img src="../images/nikon_d3x.jpg" width="179" height="179" alt="Nikon D3x" /></div>
<div class="container" style="background-image: url(../images/nikon_d700_backing.jpg); background-repeat: no-repeat; width: 178px; height: 54px;">Hello</div>
<div class="container" style="background-image: url(../images/nikon_d3_backing.jpg); background-repeat: no-repeat; width: 184px; height: 54px;">Hello</div>
<div class="container" style="background-image: url(../images/nikon_d3x_backing.jpg); background-repeat: no-repeat; width: 179px; height: 54px;">Hello</div>
<div class="container"><img src="../images/nikon_d3s.jpg" width="178" height="184" alt="Nikon D3s" /></div>
<div class="container"><img src="../images/nikon_d3000.jpg" width="184" height="184" alt="Nikon D3000" /></div>
<div class="container"><img src="../images/nikon_d3100.jpg" width="179" height="184" alt="Nikon D3100" /></div>
<div class="container" style="background-image: url(../images/nikon_d3s_backing.jpg); background-repeat: no-repeat; width: 178px; height: 65px;">Hello</div>
<div class="container" style="background-image: url(../images/nikon_d3000_backing.jpg); background-repeat: no-repeat; width: 184px; height: 65px;">Hello</div>
<div class="container" style="background-image: url(../images/nikon_d3100_backing.jpg); background-repeat: no-repeat; width: 179px; height: 65px;">Hello</div>



</div>
<?php include('includes/tpl_prices.ini'); ?>
<?php include('includes/tpl_rightads.ini'); ?>

<!-- EOF Main Content -->
</div>
<?php include('includes/tpl_footer.ini'); ?>
</div>
</body>
</html>

Usually somewhere, as it’s a standard practice. A fetch statement only returns one of the rows. By looping, you can generally prevent errors that would otherwise be generated if there are too few or too many rows than what you were expecting. But, you can also just use an if-statement.

Yes, you have to loop it to get at all returned rows. You simply build an array in the loop if that will make life easier for you. For example…


$results = mysql_query("SELECT * FROM compare WHERE prod_code IN (25, 26, 27)");
rows = array();
while($row = mysql_fetch_assoc($results))
{
	$rows[] = $row;
}

// Then you can refer to each row like so...
$rows[0]['store']

// Or loop back through it later...
foreach($rows as $row) {
 // Something
}

Hi, I’ve tried the solution that you provided Warddrop but it’s now just echo each row from records 24? I’m guessing there is probably a small problem with my code. Any ideas?


<?php
include('includes/connection.php');


$results = mysql_query("SELECT * FROM compare WHERE prod_code IN (24, 25, 26) ORDER BY price");
$row = array();
while ($row = mysql_fetch_assoc($results)){
	$rows[] = $row;
}

?>
<!-- BOF Main Content -->
<div id="mainContent">
<div class="container"><img src="../images/nikon_dslrs_header.jpg" width="541" height="62" alt="Nikon DSLRs" /></div>
<div class="container"><img src="../images/nikon_d90.jpg" width="178" height="145" alt="Nikon D90" /></div>
<div class="container"><img src="../images/nikon_d300.jpg" width="184" height="145" alt="Nikon D300" /></div>
<div class="container"><img src="../images/nikon_d300s.jpg" width="179" height="145" alt="Nikon D300s" /></div>
<div class="container" style="background-image: url(../images/nikon_d90_backing.jpg); background-repeat: no-repeat; width: 178px; height: 56px;"><div style="margin: 5px 0px 0px 27px;"><span style="font-size: 11px;">Best price: <b>&pound;<?php echo $rows[0]['price'] ?></b></span><br><span style="font-size: 11px; font-weight: bold; color: #666;"><?php echo $rows[0]['store'] ?></span></div></div>

<div class="container" style="background-image: url(../images/nikon_d300_backing.jpg); background-repeat: no-repeat; width: 184px; height: 56px;"><div style="margin: 5px 0px 0px 30px;"><span style="font-size: 11px;">Best price: <b>&pound;<?php echo $rows[1]['price'] ?></b></span><br><span style="font-size: 11px; font-weight: bold; color: #666;"><?php echo $rows[1]['store'] ?></span></div></div>

<div class="container" style="background-image: url(../images/nikon_d300s_backing.jpg); background-repeat: no-repeat; width: 179px; height: 56px;"><div style="margin: 5px 0px 0px 20px;"><span style="font-size: 11px;">Best price: <b>&pound;<?php echo $rows[2]['price'] ?></b></span><br><span style="font-size: 11px; font-weight: bold; color: #666;"><?php echo $rows[2]['store'] ?></span></div></div>


I’m not sure what output you require, but give this a whirl.


<?php
$res = mysql_query(
  'SELECT prod_code, price, store FROM compare WHERE prod_code IN (24, 25, 26) ORDER BY price DESC'
);

$records = array();

while($record = mysql_fetch_assoc($res)){
  $records[$record['prod_code']][] = $record;
}
?>
<!-- BOF Main Content -->
<div id="mainContent">
<div class="container"><img src="../images/nikon_dslrs_header.jpg" width="541" height="62" alt="Nikon DSLRs" /></div>
<div class="container"><img src="../images/nikon_d90.jpg" width="178" height="145" alt="Nikon D90" /></div>
<div class="container"><img src="../images/nikon_d300.jpg" width="184" height="145" alt="Nikon D300" /></div>
<div class="container"><img src="../images/nikon_d300s.jpg" width="179" height="145" alt="Nikon D300s" /></div>

<div class="container" style="background-image: url(../images/nikon_d90_backing.jpg); background-repeat: no-repeat; width: 178px; height: 56px;">
  <?php foreach($products[25] as $product): ?>
    <div style="margin: 5px 0px 0px 27px;">
      <span style="font-size: 11px;">
        Best price: <b>&pound;<?php echo $product['price']; ?></b>
      </span><br>
      <span style="font-size: 11px; font-weight: bold; color: #666;">
        <?php echo $product['store']; ?>
      </span>
    </div>
  <?php endforeach; ?>
</div>

<div class="container" style="background-image: url(../images/nikon_d300_backing.jpg); background-repeat: no-repeat; width: 184px; height: 56px;">
  <?php foreach($products[26] as $product): ?>
    <div style="margin: 5px 0px 0px 27px;">
      <span style="font-size: 11px;">
        Best price: <b>&pound;<?php echo $product['price']; ?></b>
      </span><br>
      <span style="font-size: 11px; font-weight: bold; color: #666;">
        <?php echo $product['store']; ?>
      </span>
    </div>
  <?php endforeach; ?>
</div>

<div class="container" style="background-image: url(../images/nikon_d300s_backing.jpg); background-repeat: no-repeat; width: 179px; height: 56px;">
  <?php foreach($products[24] as $product): ?>
    <div style="margin: 5px 0px 0px 27px;">
      <span style="font-size: 11px;">
        Best price: <b>&pound;<?php echo $product['price']; ?></b>
      </span><br>
      <span style="font-size: 11px; font-weight: bold; color: #666;">
        <?php echo $product['store']; ?>
      </span>
    </div>
  <?php endforeach; ?>
</div>

Ah, I see what you want. :smiley:


<?php
$res = mysql_query(
  'SELECT prod_code, MAX(price) AS price, store FROM compare WHERE prod_code IN (24, 25, 26) GROUP BY prod_code'
);

$records = array();

while($record = mysql_fetch_assoc($res)){
  $records[$record['prod_code']] = $record;
}
?>
<!-- BOF Main Content -->
<div id="mainContent">
<div class="container"><img src="../images/nikon_dslrs_header.jpg" width="541" height="62" alt="Nikon DSLRs" /></div>
<div class="container"><img src="../images/nikon_d90.jpg" width="178" height="145" alt="Nikon D90" /></div>
<div class="container"><img src="../images/nikon_d300.jpg" width="184" height="145" alt="Nikon D300" /></div>
<div class="container"><img src="../images/nikon_d300s.jpg" width="179" height="145" alt="Nikon D300s" /></div>

<div class="container" style="background-image: url(../images/nikon_d90_backing.jpg); background-repeat: no-repeat; width: 178px; height: 56px;">
    <div style="margin: 5px 0px 0px 27px;">
      <span style="font-size: 11px;">
        Best price: <b>&pound;<?php echo $products[25]['price']; ?></b>
      </span>
      <br>
      <span style="font-size: 11px; font-weight: bold; color: #666;">
        <?php echo $products[25]['store']; ?>
      </span>
    </div>
</div>

<div class="container" style="background-image: url(../images/nikon_d300_backing.jpg); background-repeat: no-repeat; width: 184px; height: 56px;">
    <div style="margin: 5px 0px 0px 27px;">
      <span style="font-size: 11px;">
        Best price: <b>&pound;<?php echo $products[26]['price']; ?></b>
      </span>
      <br>
      <span style="font-size: 11px; font-weight: bold; color: #666;">
        <?php echo $products[26]['store']; ?>
      </span>
    </div>
</div>

<div class="container" style="background-image: url(../images/nikon_d300s_backing.jpg); background-repeat: no-repeat; width: 179px; height: 56px;">
    <div style="margin: 5px 0px 0px 27px;">
      <span style="font-size: 11px;">
        Best price: <b>&pound;<?php echo $products[24]['price']; ?></b>
      </span>
      <br>
      <span style="font-size: 11px; font-weight: bold; color: #666;">
        <?php echo $products[24]['store']; ?>
      </span>
    </div>
</div>

Hi Anthony, I’m not at home at the moment so can’t try but think that could be the solutuon to it. I’m presuming I’ll need to echo the variable $records instead of $products (from your previous copy and paste) as there is no longer a foreach in the revised code?

Yes, abolutey right. Sorry about that, damn my copy-n-paste skills.

Here’s the revised code.


<?php
$res = mysql_query(
  'SELECT prod_code, MAX(price) AS price, store FROM compare WHERE prod_code IN (24, 25, 26) GROUP BY prod_code'
);

$products = array();

while($product = mysql_fetch_assoc($res)){
  $products[(int)$product['prod_code']] = $product;
}
?>
<!-- BOF Main Content -->
<div id="mainContent">
<div class="container"><img src="../images/nikon_dslrs_header.jpg" width="541" height="62" alt="Nikon DSLRs" /></div>
<div class="container"><img src="../images/nikon_d90.jpg" width="178" height="145" alt="Nikon D90" /></div>
<div class="container"><img src="../images/nikon_d300.jpg" width="184" height="145" alt="Nikon D300" /></div>
<div class="container"><img src="../images/nikon_d300s.jpg" width="179" height="145" alt="Nikon D300s" /></div>

<div class="container" style="background-image: url(../images/nikon_d90_backing.jpg); background-repeat: no-repeat; width: 178px; height: 56px;">
    <div style="margin: 5px 0px 0px 27px;">
      <span style="font-size: 11px;">
        Best price: <b>&pound;<?php echo $products[25]['price']; ?></b>
      </span>
      <br>
      <span style="font-size: 11px; font-weight: bold; color: #666;">
        <?php echo $products[25]['store']; ?>
      </span>
    </div>
</div>

<div class="container" style="background-image: url(../images/nikon_d300_backing.jpg); background-repeat: no-repeat; width: 184px; height: 56px;">
    <div style="margin: 5px 0px 0px 27px;">
      <span style="font-size: 11px;">
        Best price: <b>&pound;<?php echo $products[26]['price']; ?></b>
      </span>
      <br>
      <span style="font-size: 11px; font-weight: bold; color: #666;">
        <?php echo $products[26]['store']; ?>
      </span>
    </div>
</div>

<div class="container" style="background-image: url(../images/nikon_d300s_backing.jpg); background-repeat: no-repeat; width: 179px; height: 56px;">
    <div style="margin: 5px 0px 0px 27px;">
      <span style="font-size: 11px;">
        Best price: <b>&pound;<?php echo $products[24]['price']; ?></b>
      </span>
      <br>
      <span style="font-size: 11px; font-weight: bold; color: #666;">
        <?php echo $products[24]['store']; ?>
      </span>
    </div>
</div>

Hi Anthony, your the man! Brilliant, that’s perfect, I can now see that I should work, what’s better I understand how it works and what to do in the future. Cheers dude! :smiley:

Hi Antony, just tested things and it’s working - sort of. It’s showing the right results, however, if I update a price in the database which I will need to do, the price on the page updates to the new one but the store stays the same as the old and doesn’t update with the price.

Any ideas what I’m doing wrong?

Hi Anthony, final message! Just to say that it’s all fixed. I used the MySQL query from your first post and all is working honky dory now. Thank again :smiley: