[RESOLVED] Help - How To Add Another Table To Query

bobbyrabbit

I need to grab some data from another table called, in this case, [|PREFIX|]product_downloads. They primary key for that table is 'downloadid'. I hope I've given enough information. Thanks in advance.

$query = "
					SELECT p.*, FLOOR(prodratingtotal/prodnumratings) AS prodavgrating, imageisthumb, imagefile, ".GetProdCustomerGroupPriceSQL()."
					FROM [|PREFIX|]products p
					LEFT JOIN [|PREFIX|]product_images pi ON (p.productid=pi.imageprodid)
					WHERE p.prodvisible='1' AND (imageisthumb=1 OR ISNULL(imageisthumb))
					".GetProdCustomerGroupPermissionsSQL()."
					ORDER BY proddateadded DESC
				";

Bjom

That would be done with a JOIN to include the other table in your SELECT

Bjom

bobbyrabbit

Bjom - Thanks for your reply. I understand what your saying and I have attempted that, but I'm getting silly syntax errors and to fix is a bit out of my MySQL scope. I'm just hoping someone could have a better attempt than I'm having to help me on my way and to understand where I'm going wrong. :o

Bjom

Did you read carefully the material that I linked to? Better reread it.

There are no silly syntax errors. They are quite helpful. For example: they would help us to find what's going wrong. And also I would suggest you make everything as beautifully formatted as you can - to help yourself - it's less confusing. And avoid shorthands like omitting the AS keyword.

What are those "Prefixes"? That looks like a template to a query....

If you are doing something new - get out everything that could go wrong, i.e. all variables and functions. Try this and work from there:

$query = "
SELECT * 
    FROM [|PREFIX|]products AS p 
        LEFT JOIN [|PREFIX|]product_images AS pi 
           ON (p.productid=pi.imageprodid)
    WHERE p.prodvisible='1' AND (imageisthumb=1 OR ISNULL(imageisthumb))    

    ORDER BY proddateadded DESC";

bobbyrabbit

I added this query underneath the above mentioned query:

$query = "SELECT * FROM [|PREFIX|]products d LEFT JOIN [|PREFIX|]product_downloads dn ON (d.productid=dn.downloadid)";

This successfully calls the data from the products_downloads table, but now causes
Notice: Undefined index: prodavgrating in /home///etc
Notice: Undefined index: imagefile in /home///etc

This has obviously broken the original query, perhaps I am recalling the products table incorrectly, or perhaps I shouldn't be doing it twice?

Bjom

the undefined index is not an error message produced by the query but by a latter php command, right?

So that in turn means: your query is fixed, but is now more limited, than what you need.

Now rebuild it from the working but limited version by slowly including all your variables and stuff, that you had in the original.

After each change and before executing it: print it to the screen to see what it actually looks like (echo $query😉.

After it executed alright, do a var_dump($result); on the query result and scrutinize it, so as to always know what is going on.

Bjom

bobbyrabbit

The lines that now error later on in the code are:

$GLOBALS['ProductRating'] = (int)$row['prodavgrating'];
$GLOBALS['ProductThumb'] = ImageThumb($row['imagefile'], ProdLink($row['prodname']));

Bjom

$row['prodavgrating'];

since your query doesnt return 'prodavgrating'.... the array $row doesn't have the index 'prodgravating'.

bobbyrabbit

But the original query at the top of the page does

Bjom

as I mentioned...rebuild it and check every step while doing so

So that in turn means: your query is fixed, but is now more limited, than what you need.

Now rebuild it from the working but limited version by slowly including all your variables and stuff, that you had in the original.

After each change and before executing it: print it to the screen to see what it actually looks like (echo $query.

After it executed alright, do a var_dump/COLOR; on the query result and scrutinize it, so as to always know what is going on.

bobbyrabbit

Bjom, thanks for your detailed replies, but they are going over my head a little. Thanks to you I am making progress, but just need a shove in the right direction.

This is the code I have finalised with, but it is not pulling the data from the product_downloads table.

This looks pretty correct to me:

$query = "
	SELECT p.*, FLOOR(prodratingtotal/prodnumratings) AS prodavgrating, imageisthumb, imagefile, ".GetProdCustomerGroupPriceSQL()."
	FROM [|PREFIX|]products p
	LEFT JOIN [|PREFIX|]product_downloads dn ON p.productid=dn.downloadid
	LEFT JOIN [|PREFIX|]product_images pi ON p.productid=pi.imageprodid
	WHERE p.prodvisible='1' AND (imageisthumb=1 OR ISNULL(imageisthumb))
					".GetProdCustomerGroupPermissionsSQL()."
	ORDER BY proddateadded DESC
	";

Error here:

$GLOBALS['DownloadName'] = isc_html_escape($row['downname']);

downname being the field I require from TABLE [|PREFIX|]product_downloads

Bjom

after you set up your query with the

$query = "....

add these lines:

echo $query; //this shows you what your query REALLY looks like to the server
echo '<br />';
echo GetProdCustomerGroupPriceSQL(); //take another peek at this function's return value
exit; //this interrupts running of the script, because for now you only want to have a look at $query.

is "downname" anywhere to be found in the field list after the select? If not...add it.

bobbyrabbit

Yes, I did need to add the field downname in SELECT. Here is the working correct syntax.

$query = "
	SELECT p.*, FLOOR(prodratingtotal/prodnumratings) AS prodavgrating, imageisthumb, imagefile, ".GetProdCustomerGroupPriceSQL()."
	FROM [|PREFIX|]products p
	LEFT JOIN [|PREFIX|]product_downloads dn ON p.productid=dn.downloadid
	LEFT JOIN [|PREFIX|]product_images pi ON p.productid=pi.imageprodid
	WHERE p.prodvisible='1' AND (imageisthumb=1 OR ISNULL(imageisthumb))
					".GetProdCustomerGroupPermissionsSQL()."
	ORDER BY proddateadded DESC
	";

bobbyrabbit

Sorry, the correct syntax is below with dn.downname included.

bobbyrabbit;10922840 wrote:

Yes, I did need to add the field downname in SELECT. Here is the working correct syntax.

$query = "
	SELECT p.*, FLOOR(prodratingtotal/prodnumratings) AS prodavgrating, imageisthumb, imagefile, dn.downname ".GetProdCustomerGroupPriceSQL()."
	FROM [|PREFIX|]products p
	LEFT JOIN [|PREFIX|]product_downloads dn ON p.productid=dn.downloadid
	LEFT JOIN [|PREFIX|]product_images pi ON p.productid=pi.imageprodid
	WHERE p.prodvisible='1' AND (imageisthumb=1 OR ISNULL(imageisthumb))
					".GetProdCustomerGroupPermissionsSQL()."
	ORDER BY proddateadded DESC
	";

Bjom

I would suggest as a final step to get rid of the asterisk in p.* and replace it with the actual fieldnames that you query from the DB, since that makes things clearer(to yourself, when you revisit your code in a few weeks or months), but that is a minor thing.

Soooo....everything fine? Problem resolved? Best to mark the thread resolved, then 🙂