Issues with a mysql query

Beginners - Learning PHP
#1

So I’m new to using php and mysql. here is my error message: "The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

select
        count(part_id) as total_parts,
        businesses.business_id,
        password,
        user_type,
        business_name,
        businesses.name,
        address,
        city,
        state,
        zip,
        phone,
        cell_phone,
        paypal_address,
        tax_number,
        credits,
        auto_donate_amount,
        suspended,
        businesses.category_id,
        categories.name as categoryname 
    from
        businesses 
    join
        categories 
            on businesses.category_id = categories.category_id 
    left join
        parts 
            on businesses.business_id = parts.business_id 
    where
        suspended='n' 
    group by
        businesses.business_id,
        password,
        user_type,
        business_name,
        businesses.name,
        address,
        city,
        state,
        zip,
        phone,
        cell_phone,
        paypal_address,
        tax_number,
        credits,
        auto_donate_amount,
        suspended,
        businesses.category_id,
        categoryname 
    ORDER BY
        business_name ASC

Warning: mysql_fetch_array() expects parameter 1 to be resource, null given in /hermes/bosnacweb05/bosnacweb05bh/b2841/dot.rbjassoc/public_html/mypartshelf.com/admin_manage_businesses.php on line 65"

not sure what to do ???

#2

Unfortunately, the forum software Fmucked Up your query Beyond All Recognition, so it’s not clear if your post shows all the actual query. You can edit your post above and add bbcode code tags around the query so that it will be displayed as is, hopefully witch some new-lines to format it properly.

However, use short table alias names, such as b for businesses, c for categories, and p for parts. Use the alias_name.column_name syntax for all columns so that anyone reading the query can tell which columns are from which tables without needing to know your table definitions or your differing column naming schemes (you need to be consistent in your column naming.)

Next, the GROUP BY term should only contain the thing(s) you are trying to group the data by, for producing the aggregate COUNT() value. If you are trying to produce a count of parts per business, you would only have GROUP BY b.business_id.

#3

What question are you asking of the database for starters, and what is the query for (ie Reporting, ect)?

You can do what was suggested and change this SQL_BIG_SELECTS; You can also limit the returning values, and paginate the returned rows.

To follow up, I would do a select actually in the database to see what that max query size is set at as well. But, I think paginating the result set would be more useful to you.

#4

@
astonecipher](https://www.phphelp.com/u/astonecipher)

would you be interested in looking into my issue and fixing it for me ??

thx
Bob J

#5

this is the query:
query = "select count(part_id) as total_parts, businesses.business_id, password, user_type, business_name, businesses.name, address, city, state, zip, phone, cell_phone, paypal_address, tax_number, credits, auto_donate_amount, suspended, businesses.category_id, categories.name as categoryname from businesses ".

	"join categories on businesses.category_id = categories.category_id ".

	"left join parts on businesses.business_id = parts.business_id ".

	"where suspended='n' ".

	"group by businesses.business_id, password, user_type, business_name, businesses.name, address, city, state, zip, phone, cell_phone, paypal_address, tax_number, credits, auto_donate_amount, suspended, businesses.category_id, categoryname ORDER BY" . " "  .  $sortorder;
#6

I honestly don’t have time, I can help on here, but I need to know the answer to the question on what it is for.

#7

I have a DB with many tables , I want to query all the data from one table “businesses”, and I want to total the part_id from another table named “parts”, the common column is the business_id, in addition I only want to return the those items that have totals which are greater than 500. I am not worried about the categories

#8

then an inner join on both tables with an appropriate where condition would be suitable.

#9

thx I know that much but I am having issues with the syntax???

#10

At a minimum, you need to correct the GROUP BY term. See my previous reply in the thread.

#11

this code works fine in myphp - myadmin when I query directly against the DB,

“SELECT b.business_name, b.user_type, b.paypal_address, b.password, b.credits, b.auto_donate_amount, COUNT( DISTINCT p.part_number ) AS part_total
FROM businesses AS b
INNER JOIN parts AS p ON b.business_id = p.business_id
WHERE b.suspended = ‘n’
GROUP BY b.business_name
HAVING COUNT( DISTINCT p.part_number ) >=500
ORDER BY part_total DESC
LIMIT 0 , 100;”

now its only a matter of getting the actual php code to make this happen through the browser

#12

What php code are you currently using that is causing the issue? That would help to clear this hurdle.

#13

The server I am using has PHP 5.3

#14

That isn’t what I asked.

Sponsor our Newsletter | Privacy Policy | Terms of Service