Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Need help accessing new table aaded to db


charles

Recommended Posts

:? :? :? OK, I've worked on this for three days around the clock. I give up, can someone help me please?

 

I have a limited knowledge of PHP but am learning by modifying this code.

 

Here is the problem, which relates to a complicated pricing scenario not covered in the existing OSC code.

 

I needed a new table, which I'll call => 'products_options_pricing'.

 

It contains three fields =>

 

'products_options_pricing_id'

'language_id'

'products_options_pricing_price'

 

to accomodate this, I created a new field in table => 'products_attributes'

 

called => 'options_pricing_id'

 

For ease and logical flow of data entry I created this field by inserting it between the fields =>

 

'options_values_id'

'options_values_price'

 

Then I went to => 'applications_top' and added the following line to the table definitions =>

 

define('TABLE_PRODUCTS_OPTIONS_PRICING', 'products_options_pricing');

 

To access this data for presenting this pricing scheme on the 'products_info' page, I added in the snippet of code:

 

/* call up the products options pricing array  */



$products_options_pricing = tep_db_query("select pop.products_options_pricing_id, pop.products_options_pricing_price, pa.options_pricing_id 



from  " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_OPTIONS_PRICING . " pop 



where pa.products_id = '" . $HTTP_GET_VARS['products_id'] . "' 

and pa.options_pricing_id = pop.products_options_pricing_id 

and pop.language_id = '" . $languages_id . "'");

 

but I keep getting the error

 

1054 - Unknown column 'pa.options_pricing_id' in 'field list'



select pop.products_options_pricing_id, pop.products_options_pricing_price, pa.options_pricing_id from products_attributes pa, products_options_pricing pop where pa.products_id = '31' and pa.options_pricing_id = pop.products_options_pricing_id and pop.language_id = '1'



[TEP STOP]

 

Why can't the 'options_pricing_id' field in 'table_products_attributes' be recognized? Is there somewhere elso I must define information?

 

Also, I can find no documentation for the 'tep' functions to describe syntax, use, etc. Can someone please tell me where it is? I have looked in the PHP and the MYSQL manuals to no avail.

 

Thanks, Charles

We stand in ignorance only for questions not asked. Plug up the leaks in your knowledge base and open up a flood of understanding.
Link to comment
Share on other sites

'products_options_pricing_id'  

'language_id'  

'products_options_pricing_price'  

 

to accomodate this, I created a new field in table => 'products_attributes'  

 

called => 'options_pricing_id'

 

You named the field 'products_options_pricing_id' in table 'products_options_pricing' and then referred to it as 'options_pricing_id' in 'products_attributes' .

 

Mark

Link to comment
Share on other sites

Well, yes, I arrived at this code by copying and modifying the working, stock code for accessing the table => 'products_options_values' directly from => 'product_info'

 

/* call up the products options values array  */



       $products_options = tep_db_query("select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix 



from " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov 



where pa.products_id = '" . $HTTP_GET_VARS['products_id'] . "' 

and pa.options_id = '" . $products_options_name_values['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id 

and pov.language_id = '" . $languages_id . "'");

 

Compare what came with OSC (above) to my code (below). I followed all the same field name conventions of => 'products_options_values' and => 'products_attributes' when setting this up. Their code works - mine does not.

 

/* call up the products options pricing array  */



$products_options_pricing = tep_db_query("select pop.products_options_pricing_id, pop.products_options_pricing_price, pa.options_pricing_id 



from  " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_OPTIONS_PRICING . " pop 



where pa.products_id = '" . $HTTP_GET_VARS['products_id'] . "' 

and pa.options_pricing_id = pop.products_options_pricing_id 

and pop.language_id = '" . $languages_id . "'");

 

Thanks, Charles

We stand in ignorance only for questions not asked. Plug up the leaks in your knowledge base and open up a flood of understanding.
Link to comment
Share on other sites

I should have read closer. I was thinking 'pa.options_pricing_id' was referencing your new table. :oops:

 

Dumb question: Have you checked that 'options_pricing_id' was actually created in table product_attributes?

 

Everything else you've documented looks ok to me. There is nothing else you need to define.

 

M

Link to comment
Share on other sites

Yes, as I said, I created the field by inserting it in the middle of the table. In phpmyadmin I have run

 

Operations menu

Check table

Analyze table

Repair table

Optimize table

 

all gave positive results, OK, and

 

Structure menu

Propose table structure

 

which also showed the field, the only real difference being that it showed a min value of 0 instead of 1. I also ran a query in the Select menu for 'options_pricing_id' for a specific product number which produced correct results!

 

This is when it gets down to something REALLY simple and EQUALLY obscure. I'll laugh when it's over but for now I'm about to go postal. :lol:

 

Thanks,

Charles

We stand in ignorance only for questions not asked. Plug up the leaks in your knowledge base and open up a flood of understanding.
Link to comment
Share on other sites

OK, I've found where the tep functions are defined (includes/functions/database.php) :oops: so there is no docs for those.

 

Now, for the problem accessing the table => 'products_options_pricing', for the code

$test = tep_db_fetch_array($products_options_pricing);

I get

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in f:www_server_rootphpdevwwwoscommercecatalogincludesfunctionsdatabase.php on line 99

But I've established that the table exists and has accessible data using phpmyadmin. There HAS to be another place to define something besides 'application_top'. And, yes, it is the correct database set in configure.

 

Please, Anyone?!?

 

Thanks,

Charles

We stand in ignorance only for questions not asked. Plug up the leaks in your knowledge base and open up a flood of understanding.
Link to comment
Share on other sites

That's exactly the point. I can't get the Select statement to happen. It tells me that the column doesn't exist,

 

1054 - Unknown column 'pa.options_pricing_id' in 'field list'

 

but it does exist, honest Auntie Em! I just can't get there because I do not have the ruby slippers.

 

I mean, I've created the table, crossed it to attributes, entered the data, accessed the data through phpmyadmin, and duplicated the code apparrently correctly..... Given all the facts I've presented, could it be a bug?

 

They shoot horses, don't they?

Charles

We stand in ignorance only for questions not asked. Plug up the leaks in your knowledge base and open up a flood of understanding.
Link to comment
Share on other sites

I'm not sure I understand what your saying. 'The field 'options_pricing_id' would not be in your database, only mine, because I created it to accomodate a pricing mod I'm working on. I also created the table 'products_options_pricing', which would also not be in your database for the same reason.

 

BTW I describe the idea to you a few minutes ago in a followup post to the guy with the image problem.

We stand in ignorance only for questions not asked. Plug up the leaks in your knowledge base and open up a flood of understanding.
Link to comment
Share on other sites

YeeeeHaaaaw Solved! :lol: Linda, I missed your last post or it would have saved me another hour or so, thanks. But YES! Apparently when I typed in the field name in phpmyadmin, I typed it in with an extra space as 'options_pricing_id ' not 'options_pricing_id'. The extra space in the field name made all the difference in the world. Like I said earlier

 

something REALLY simple and EQUALLY obscure

 

in this case as obscure as an invisible 'space'.

 

I now have the ruby slippers! Now to finish sorting out the code.

 

Thanks again,

Charles

We stand in ignorance only for questions not asked. Plug up the leaks in your knowledge base and open up a flood of understanding.
Link to comment
Share on other sites

Archived

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

×
×
  • Create New...