Need help accessing new table aaded to db

[charles]

:? :? :? 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

[Mark Russell]

'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

[charles]

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

[Mark Russell]

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

[charles]

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

[charles]

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

[Ajeh]

If the select statement does not happen, then the fetch cannot happen.

 

Get your select query to function right then the fetch should fall into place.

[charles]

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

[Ajeh]

Check that field name again as I do not see one in the table product_attributes called options_pricing_id

[charles]

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.

[Ajeh]

Edit your table and make sure there is not a space before or after the field name ... php hates that ... :shock:

[charles]

:oops: Sorry, I mean the post to zebra with the per foot charge problem.

 

Charles

[charles]

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

[Ajeh]

I see things most people don't ... :shock: