MYSQL Query

[Rees]

Hiya I am trying to pull out from the database 10 random produts which are not specials, and have all the revelent information to go with the product.

 

this is my query so far:

"select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added DESC, pd.products_name";

 

I have no error messages, just doesnt pull out the correct information to go with the product.

 

Can anybody help me out?

Thanks

[Rees]

Got some progress happening, but have a couple of problems.

 

My query is:

sql2 = "select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, s.products_id, m.manufacturers_name from ?specials s, products p left join manufacturers m on (p.manufacturers_id = m.manufacturers_id), products_description pd where p.products_status = '1' and p.products_id = pd.products_id and p.products_image IS NOT NULL and p.products_id != s.products_id ?ORDER BY rand() LIMIT 10";

 

I want to pull out of the database all the products along with their title, price, and image. Providing they're not on special. I then want to display ten random products from the table.

 

The query is able to pull all the products but pulls products which are special aswell.

 

Sorry If I have broken some forums rules ;) I was unable to edit my post

 

Thanks Rees

[Guest]

sql2 = "select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added,  m.manufacturers_name from  products p left join manufacturers m on (p.manufacturers_id = m.manufacturers_id), products_description pd where p.products_status = '1' and p.products_id = pd.products_id and p.products_image IS NOT NULL   ORDER BY rand() LIMIT 10";

 

use this

 

cheers

arnie

[Rees]

that works to a certain extent, but I dont want the specials to display? apart from that its great!

 

Thanks

Any Ideas?

 

Rees

[Rob123]

Try this against your test box.

select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name from  products p left join specials s on (s.products_id = p.products_id) left join manufacturers m on (p.manufacturers_id = m.manufacturers_id), products_description pd where p.products_status = '1' and p.products_id = pd.products_id and p.products_image IS NOT NULL and s.products_id is null ORDER BY rand() LIMIT 10;

 

The code above works on all versions of MySQL.

Versions of MySQL 4.1 and higher can use "subqueries" that are easier to write and are a bit more efficient.

 

HTH,

Robert

[Rees]

Thats great mate! We have spent along time trying to find a query which works.

 

Thanks again for your time

Rees

[Guest]

Versions of MySQL 4.1 and higher can use "subqueries" that are easier to write

 

That's an understatement if I ever heard one! :)

[Rob123]

Thats great mate! We have spent along time trying to find a query which works.

 

Thanks again for your time

Rees

<{POST_SNAPBACK}>

No problem... I try to help whenever I can.

MySQL is my area of expertise... But don't ask me a design or graphics question. :-"

 

 

That's an understatement if I ever heard one!  :)

<{POST_SNAPBACK}>

My personal motto is never to insult anything that's free. :thumbsup:

[Guest]

And what an excellent Motto it is! I've been itching to get my site up to 4.1 for a while now. We run a dedicated server, so it's just a matter of getting the guy in charge of installing that stuff to do it!

 

Whenever I need to do a join my head starts to hurt. So I've started making updates to the code for the site, using subqueries. Now when they ask me when the new code will be up, I tell them as soon as MySQL is upgraded. Eventually they'll be enough pressure on the install guy that he'll have to do it. B)

[shaka]

I have accidently Mysql database can this be recovered???????????????????????????????????????????????????????????

 

Please help