[SOLVED] How do I access an entire table after retrieving it from SQL data base????

[CaseyC1]

I have a short table of 10 x 4 values that I SELECT from an SQL database. I am able to work with this table by using "mysql_fetch_assoc", but only one row at a time. As retrieved, the array gives me "Resource ID#3" when accessed directly. What I want to do is something like this:

 

for ($i=0; $i < count($array); $i++) {

 

if ($row[catalog_price] >= $array[ i ][value_from] && $row[catalog_price] <= $array[ i ][value_to]) {

$markup2 = $array[ i ]['markup'];

$found = 1;

}

 

Is there a function like the "fetch" that will format the array so that I can use it indexed as in the example? I wouldn't mind using a field index in addition to the record index if I can't use the field names.........

 

Thank you,

Klaus Cook

Houston, Texas

[farkewie]

i think this is what you want???

 

<?php
while ($row = mysql_fetch_array($result)){
echo $row[catalog_price] . "<br/>" ; // will echo thecat price for all rows 
//do stuf here and will repeat through all the rows selected 
}
?>

[CaseyC1]

Thank you for the reply farkewie, but I don't think I made myself clear. Sorry, here is a larger section of the code:

 

//---------------------------

// Retrieve the markup table

//---------------------------

$query = "SELECT * FROM CubeCart_markup_table";

$array = mysql_query ($query);

// $array1 = mysql_fetch_assoc ($array);

// print_r ($array1);

 

//---------------------------------------------------------------------------------------

// Retrieve the "cost price", "retail price" and "Product Code" from the Inventory Table

//---------------------------------------------------------------------------------------

$query = "SELECT my_price, catalog_price, productCode FROM CubeCart_inventory";

$results = mysql_query($query);

 

//------------------------------------

// Get data for one product at a time

//------------------------------------

while($row = mysql_fetch_array($results, MYSQL_ASSOC)){

$found = 0;

//-------------------------------------

// Filter to get correct markup variable

//-------------------------------------

 

for ($i=0; $i < count($array); $i++) {

 

if ($row[catalog_price] >= $array[ i ][value_from] && $row[catalog_price] <= $array[ i ][value_to]) {

$markup2 = $array[ i ]['markup'];

$found = 1;

}

if (found == 1) {break;}

}

 

I get one product into "$results" and then filter the "Catalog Price" through values in the "$array". It is "$array" that I am having trouble with. I guess I could fetch a record at a time, but that would impact execution time. I need to run approx 3,000 products through this.

[roopurt18]

I just have to ask, why not just use another while($row_inner = mysql_fetch_assoc($array)){ ?

 

<?php

//---------------------------
//   Retrieve the markup table
//---------------------------
   $query = "SELECT * FROM CubeCart_markup_table";
   $array = mysql_query ($query);
//   $array1 = mysql_fetch_assoc ($array);
//   print_r ($array1);

//---------------------------------------------------------------------------------------
//   Retrieve the "cost price", "retail price" and "Product Code" from the Inventory Table
//---------------------------------------------------------------------------------------   
   $query = "SELECT my_price, catalog_price, productCode FROM CubeCart_inventory";
   $results = mysql_query($query);
   
//------------------------------------
//   Get data for one product at a time
//------------------------------------
      while($row = mysql_fetch_array($results, MYSQL_ASSOC)){
      $found = 0;
//-------------------------------------
//   Filter to get correct markup variable
//-------------------------------------

         while($row_inner = mysql_fetch_assoc($array)){

            if ($row[catalog_price] >= $row_inner[value_from] && $row[catalog_price] <= $row_inner[value_to]) {
               $markup2 = $row_inner['markup'];
               $found = 1;
            }
            if (found == 1) {break;}
         }
      }
?>

 

Depending on many records these tables have, your code has the potential to be very inefficient.  If you are expecting either of these tables to have more than a couple hundred rows each, I suggest you give us the table structure, how they relate to each other, and describe your overall goal so we can help you optimize your algorithm.

 

Here is your algorithm as it currently stands:

M = SELECT FROM CubeCart_markup_table
N = SELECT FROM CubeCart_inventory

FOREACH M
  FOREACH N
    PROGRAM LOGIC
  ENDFOREACH
ENDFOREACH

Here is why it is inefficient.  Let M' and N' (pronounced m-prime and n-prime) stand for the number of records in each set.  Your code will essentially loop M' x N' times.  If M' and N' are relatively small, say 200 records each, then you are already looping 4000 times, which isn't insignificant, but it's not terrible either.  But if M' and N' are large, say only 1000 records each (and that's technically not large in terms of what MySQL can do), now you're code is looping 1,000,000 times.  You can expect all of your pages that run that code to timeout.

[CaseyC1]

The markup table "M" only has 7 records of three fields.

The product table currently has a little less than 3,000 records.......

[roopurt18]

So in the worst case scenario, your code loops 21,000 times.

 

How about telling us a little about your tables and what you're trying to accomplish?  I'll wager money that with some WHERE clauses and maybe a JOIN you can cut your record-sets down to almost nothing.

[CaseyC1]

Thanks for the interest. For the time being, I think I solved this thing.....also, let me mention this, I am retired programmer (Fortran IV, ASM, Cobol...the old timer languages, lol.....and I am trying to teach myself PHP.)

 

What I was trying to accomplish:

Database of roughly 3,000 items. In the stock shopping cart package there are 2 prices per item, a regular price and a sales price. If there are any pricing changes I had to use the admin section of the store to manually change the prices on a per item basis, a big PIA.

I base my pricing on the catalog price of an item, i.e. I try to be below that to encourage sales.

 

So, what have I done:

I added the catalog and cost price fields to the product table. I also added a markup constant field. I then wrote a php script and HTML form to input the pricing brackets into a separate table (i.e. from 0.01 to 9.99 the markup is 3.0, from 10.00 to 19.99........... you get the point). No problems there.

 

Next effort was to read this table into memory, and then select each product record, extract the catalog price and find which markup band it fell in. Take the cost, multiply  by the new markup and store the new data back into the record. Sounds easy......and it was. Biggest problem??? I did not know that I had to declare the variables that I read the markup bands into as arrays. Everything was working ok, the only thing that I added to make it work was "$from_value = array();".  I did this for all three variables and I was home free. I got the wrong impression when looking at an example. It said you didn't have to declare arrays, just store the data into it. In the example they stored multiple items in one expression, I was trying to store one at a time with an index.

 

Again, thanks for the interest in my problem, and I'm sure I'll be back with more questions as I find other things about this package that I might want to change.

 

Thanks,

Klaus Cook

Houston, Texas

 

 

[roopurt18]

If I understand what you're saying, then you have something like:

 

products

product_id

cost

price

 

pricing_brackets

from

to

markup

 

Your algorithm is then to pull everything from both tables, loop over the products, and determine which bracket it falls into.  As I said before, in the worst case scenario, your code has to loop 21,000 times just to determine the pricing bracket of each product; now I know that's not the real-world case, but I'm talking about in terms of algorithm analysis.

 

We can use a table join to return the product information and the matching pricing bracket in a single query; this means you effectively cut your loop down to iterating only as many times as there are items in the product table.

  SELECT
    i.`my_price`, i.`catalog_price`, i.`productCode`, m.`markup`
  FROM `CubeCart_inventory` i
  INNER JOIN `CubeCart_markup_table` m ON i.`my_price` BETWEEN m.`value_from` AND m.`value_to`

That query uses the database to match the products with their appropriate markup; if you don't believe me, add m.`value_from` and m.`value_to` to the list of fields returned and run it in phpMyAdmin.  Note also that since we are updating the `catalog_price` column, you probably don't need to return it as well.  (The "best practice" when running queries is to ask for only the columns you are actually using.)

 

This effectively changes your PHP code to:

<?php
  $sql = "
    SELECT
      i.`my_price`, i.`catalog_price`, i.`productCode`, m.`markup`
    FROM `CubeCart_inventory` i
    INNER JOIN `CubeCart_markup_table` m ON i.`my_price` BETWEEN m.`value_from` AND m.`value_to`  
  ";
  $q = mysql_query($sql);
  if($q){
    while($row = mysql_fetch_assoc($q)){
      $row['catalog_price'] *= $row['markup'];
      // #1 Perform any other logic on the row
      // #2 Update the product row in the database
    }
  }else{
    die('Error: ' . mysql_error());
  }
?>

 

We can take this one step further, we always want to push as much work into the DB engine as possible.  Here is a query that should do everything your PHP code was doing.

  UPDATE
    `CubeCart_inventory` i, `CubeCart_markup_table` m
  SET i.`catalog_price` = i.`my_price` * m.`markup`
  WHERE i.`my_price` BETWEEN m.`value_from` AND m.`value_to`

 

I used to do things the "long" way myself until I picked up a book on MySQL.  I recommend following suit, you'll save yourself oodles of headache and complicated program code. 

[CaseyC1]

Great answer, roopurt18. Thanks a million! That really made things a lot cleaner. I wasn't too clear with my explanation of which records were in which table, so I rearranged the query a little. The only thing I was not able to put into place was the multiplication in the UPDATE, but that's ok. Here is what I finally wound up with:

 

<?

include './config.php';

//  |--------------------------------------------|

// | Get the records from the db              |

// |--------------------------------------------|

$query = "SELECT CubeCart_inventory.catalog_price, CubeCart_inventory.productCode, CubeCart_inventory.my_price, CubeCart_markup_table.markup FROM CubeCart_inventory, CubeCart_markup_table WHERE CubeCart_inventory.catalog_price BETWEEN CubeCart_markup_table.value_from and CubeCart_markup_table.value_to";

$result = mysql_query ($query);

// |--------------------------------------------|

// | process one record at a time            |

// |--------------------------------------------|

while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

$price = $row[my_price] * $row[markup];

$markup = $row[markup];

$productCode = $row[productCode];

$catalog_price = $row[catalog_price];

// echo $price, $markup, $productCode, $catalog_price ."<br>";

// |--------------------------------------------|

// | update db record here                    |

// |--------------------------------------------|

mysql_query ("UPDATE CubeCart_inventory SET markup = '$markup', price = '$price' WHERE productCode = '$productCode'");

$error = mysql_error();

if ($error != "") {

echo $error ."SQL error on Product :" .$productCode ."<br>";

}

}

 

echo "DONE!";

?>

 

I am constantly looking at this language  (PHP) with amazement. Having coded in FORTRAN IV, ASM and Cobol for 30 years, I can't get over what one can do with one statement in PHP.

 

Again, thank you very much,

 

Klaus Cook

Houston, Texas