Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

How to execute a select all tables in database query?


Bilal81

Recommended Posts

Posted

I have tried different ways to come to know in which tables there is a products_id column.

 

What I want to do is comparing the listing of two products based on their products_id. But I want this comparison to hit every place where there is a products_id column and the columns which are in the same table situated.

 

One of the things I tried for example is this query:

 

SELECT * FROM TABLE oscommerce WHERE products_id = 0 AND products_id = 23

 

I also tried:

 

SELECT * WHERE products_id = 0 AND products_id = 23

 

But these do not work, is their a way to get it working?

Posted

To find the tables with a column products_id, go to your install directory and open the oscommerce.sql and search for it. So you'll get all the filenames.

 

Your select statement will give you no results, since you're asking for two values of the same field in an AND statement. You need to combine them with OR.

Posted

OK thank you.

 

But it doesn't solve my problem.

 

Maybe someone knows if I tell the exact problem.

 

I have inserted some products manually to the database, importing them with a third party tool from a csv file straight into the database. The tables and columns I filled were:

 

TABLE products:

-products_id

-products_model

-products_price

-products_status (set to 1 = active)

 

TABLE products_to_categories

-products_id

-categories_id

 

I disabled auto_increment and have unset primary key for the products_id, because that was the only way to disable increment products_id totally. (The database became very very slow, could that be because I have unset the primary key? Or is it because I imported some 31093 products?)

 

If I go to the catalog home page, the number of products are visible at the end of the category name, but when I click a specific category it displays that there are no products!

 

In the admin the same problem, I click the category and there are no products. I have added a test product through the admin interface, and this one became listed without any problems.

 

So I think there is a field I must have missed filling it. But I don't know which column or columns that should be?

Posted

You got to create an entry to the table products_description for each of your products. The values can be empty except of products_id products_name and language_id, where language_id should have a value for each of the languages you are using. Standard is 1 if you're using only one language.

 

Speaking of a slow database, that is most likely a problem of the mising key and missing index. You should define the primary key again and refresh the index. Should speed it up.

Posted
SELECT * WHERE products_id = 0 AND products_id = 23

this won't return anything because its like saying "if x=0 and x=5 at the same time".

 

The autoincrement field is used by various scripts that insert products into your catalog. So you should leave that as is, or at least keep a dbase backup before altering the table's structure. The products_id is used in several tables including the product attributes, reviews, specials, customer's basket etc as well as with many contributions some of them you may have installed. So all these tables have to be synchronized. And you cannot rely on the products_id name. Because you may have a custom table that lists the products_id as pid. Or another module that uses the products_id for a different purpose like the order_products table which is history.

 

so to retrieve 2 products with 2 different ids you could do

 

SELECT * FROM TABLE oscommerce WHERE products_id = 11 OR products_id = 5

 

and if you have multiple products you want to extract make an array and use the "in" check with the query

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...