More help with coding

[bobg7]

I found this script I would like to use for inventory listings but am having an issue with it pulling data from 2 tables.

 

If someone could take a look at it and help me fix it I would be forever gratefull.

 

<?php

 

// Set the various database connect information.  Host is usually

// localhost.  If no password, just leave blank.  This information is 

// given by your web host OR if your own server, you setup yourself.

// See php manual for setup instructions.

$db_host  = 'xxx.xxx.xxx.xxx';

$db_user  = 'xxxx';

$db_pass  = 'xxxx';

$db_name  = 'xxxx';

 

// More then one table can be used but in ths example we use just

// one. :-)  And in many scripts just one is used.  And it's easiest

// to comment on.

//$db_table = 'products_description';

$db_table = 'products';

 

 

 

// ---- END CONFIGURATIONS ------------------------------------------//

 

// Make a connect to mysql.  Through this connect many databases

// and tables can be selected.  $conn will be what we call this

// connection.

$conn = mysql_connect($db_host,$db_user,$db_pass);

 

if ($conn == true) {

 

 

// Select the database.

  mysql_select_db($db_name,$conn); 

 

// We are selecting all fields (*) from database and using the $conn

// database connection.  By default $conn would be used but we're

// learning so may as well not be lazy :-)  Usually you wouldn't 

// select * but for the lazy and unsure, it works nicely.

//

// This is a very powerful function, one can query *any* SQL statement

// and do many things, like update/delete/insert/select data.  A good 

// basic SQL tutorial is : sqlcourse.com .  The result of this 

// query is named $result

  $result = mysql_query("SELECT * from $db_table",$conn);

 

// While a row exists to equal the fetched object (row) of data

// continue to loop until end of fetch (at the end)

// The way data is called may be new.  $row->dbfieldname . See 

// the manual on mysql_fetch_object() for more info.  Other functions

// can just as easily have been used, like mysql_fetch_array.  That method 

// one would use format as : $row['id']; , $row['name'] , etc.  See manual 

// for all the possible (and beautiful) tools that are available.

    while($row = mysql_fetch_object($result)) {

 

// These will loop through entire database.  $tmp keeps getting

// appended to so it's getting very large. This is what's printed

// and where one makes look pretty :-)  Below the database fields

// id, name and url are being printed from the $db_table table.  And, 

// the .= means it appends (concatenates) to $tmp so $tmp grows larger

// and later.  See : http://www.zend.com/zend/tut/using-strings.php

// Again, id,name and url are just examples ... replace according to 

// your database.

 

        $tmp .= "Model  : $row->products_model  <br>\n"; 

        $tmp .= "Name  : $row->products_name  <br>\n";

        $tmp .= "Price  : $$row->products_price  <br><hr>\n";   

 

// The while loop ends here at this brace so the above cycles through until 

// all data is gathered.

    }

 

// Else we are not connected ($conn == false) so print error.

} else {

    echo 'could not connect to database : '. mysql_error();

}

 

// Print the pulled data.

print $tmp;

 

?>

 

I can get it ro read ond output one or the other tables, but not both. I have tried to contact the person who wrote it, but the email bounces back. :(

 

Thanks in advance,

 

Bob G.

[Jack_mcs]

The code isn't setup to access two tables. He just says it is possible. You would have to enter the tables in an array to have the code access more than one.

 

But you would probably be better off using oscommerce type code to ensure all rules are followed for your shop. Take a look at the Optimize Database contribution. The code is already in place to cycle through all of the tables. If you just copy it, you would have just about the same script you have now except better.

 

Jack

[bobg7]

The Optimize Database won't do what the script does.

[Jack_mcs]

Yes, it would have to be modified else you would just be optimizing the database. The point was that you can take the shell of the code and be better off.

 

Jack

[bobg7]

Scratching head, can't get it to work at all.

[Jack_mcs]

Let's start over. Forget about either of the above scripts for the moment and describe what it is you are trying to do. Just get a list of products?

 

Jack

[bobg7]

What I need is a simple script to read from 2 tables and deliver a simple report I can print.

 

Since I have the OSC website and use a heavly modified copy of OSC using the same database for the retail part of my shop, I need something like this for doing inventory and by dropping the Quantity field I can copy/paste the info into a label program I have to create sticky's for the shelf items.

 

Example:

From Table Products, get Products_Model, Products_Price and Products_Quantity

From Table Products_Description get Products_Name

 

With output like this:

----------------------

Model: 12345

Name: Test Item1

Price: $1.99

Quantity: 22

----------------------

Model: 23456

Name: Test Item2

Price: $2.99

Quantity: 23

----------------------

Model: 34567

Name: Test Item3

Price: $3.99

Quantity: 24

 

Hope this all makes sense and thanks for your help.

 

Bob G.

[Jack_mcs]

Here's all you need:

 $product_query = tep_db_query("select pd.products_name, pd.products_id, p.products_id, p.products_model, p.products_price, p.products_quantity from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "'");

while ($product = tep_db_fetch_array($product_query)) {
 echo 'Model: ' . $product['products_model'] . '<br>' .
      'Name: ' . $product['products_name'] . '<br>' .
      'Price: ' . $product['products_price'] . '<br>' .
      'Quantity: ' . $product['products_quantity'] . '<br>';
}

Just plug that into a regular oscommerce page and it should do what you want. I haven't test it so it may need some tweaking. For display, you should replace the each statement with html code.

 

Jack

[bobg7]

WOW, thanks, just what I needed, just added an extra <br> to give a space between each output block to make it read a little easier. :D

 

<?php
require('includes/application_top.php');
$product_query = tep_db_query("select pd.products_name, pd.products_id, p.products_id, p.products_model, p.products_price, p.products_quantity from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "'");

while ($product = tep_db_fetch_array($product_query)) {
echo 'Model: ' . $product['products_model'] . '<br>' .
     'Name: ' . $product['products_name'] . '<br>' .
     'Price: ' . $product['products_price'] . '<br>' .
     'Quantity: ' . $product['products_quantity'] . '<br><br>';
}
?>

 

I really want to thank you for your help with this, it will make life a lot more simple. :thumbsup:

 

Bob G.

[Jack_mcs]

NP.. Have fun. :)

 

Jack