I am VERY new to this, so you may have to SHOW me the answer.
The first thing I notice is that the Left Join is not capitalized but when it is, it still comes up with the error.
The code is below:
$query = " SELECT distinct(L.id),CL.ts,L.refer,L.email,L.inf_name1,L.inf_name2,L.inf_city,L.inf_state,L.sold,
C.name AS cname, A.name AS account,
CASE WHEN
L.valid THEN 'Yes' ELSE 'No' END AS valid
FROM
lead L, campaign C, account A
Left Join
customer_lead CL ON CL.id=L.cid
WHERE L.cid=C.id AND L.status = 'A' AND C.usid=A.id
$sort_as";
I ran a query inside PhpMyAdmin with the following query and it came up with “#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘$query = "SELECT AC.usid , A.name , A.inf_’ at line 1”
Any ideas what might be the problem?
$query = "SELECT AC.usid
, A.name
, A.inf_name1
, A.inf_name2
, CON.name as country
, C.name as company
FROM account_customer AC
INNER
JOIN account A
ON A.id=AC.usid
INNER
JOIN country CON
ON CON.id=A.inf_country
INNER
JOIN company C
ON C.id=A.company
$sort_as";
$query = "SELECT AC.usid,
A.name
, A.inf_name1
, A.inf_name2
, CON.name as country
, C.name as company
FROM account_customer AC
, account A
, country CON
, company C
where CON.id=A.inf_country
and C.id=A.company
and A.id=AC.usid
$sort_as";
And changed it into this:
$query = "SELECT AC.usid,
A.name
, A.inf_name1
, A.inf_name2
, CON.name as country
, C.name as company
FROM account_customer AC
INNER
JOIN account A
ON A.id=AC.usid
INNER
JOIN country CON
ON CON.id=A.inf_country
INNER
JOIN company C
ON C.id=A.company
$sort_as";
Right below it is this code:
$rv=mysql_query($query,$dbc);
$camp="select buyerid
, name as campaign
from campaign
where id=".$_REQUEST['campaign'];
$rescamp=mysql_query($camp,$dbc);
$rowcamp = mysql_fetch_array( $rescamp );
$buyer=explode(",",$rowcamp['buyerid']);
echo "<br/><table class=\\"database\\" cellpadding=\\"3\\" cellspacing=\\"0\\"
><caption>Assign Buyer for the Campaign ".$rowcamp['campaign']." ";
echo helpbutton('profanity_filter')."</caption>\
";
It is now giving out a "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource " warning error.
the “supplied argument is not a valid MySQL result resource” error is caused either by a query not returning anything (which you can prove or rule out by running the query outside of php) or else you have a php error, and i can’t help you there, sorry, i don’t do php
your ON clauses don’t quite do what they should be doing, which is to join tables
instead, you’ve got them filtering out rows based on $_SESSION[‘user_id’]
this qualifier should be applied in the WHERE clause, and it suggests that you should be calling your tables in a different sequence
it appears to me that you are attempting to return campaign information for a specific user, right?
so the logic of your FROM clause should not begin by retrieving all campaigns and their relationships, discarding the ones that aren’t for the correct user
instead, you should start with a table that the $_SESSION[‘user_id’] condition needs to be applied to, and then join from there – this way, only those rows of interest will be retrieved during the joins
like this –
SELECT C.id
, C.name
, C.descr
, C.cost * AF.ratio * AC.ratio AS cost
, lpd
FROM account_customer AC
INNER
JOIN customer_campaign CC
ON CC.usid = AC.usid
INNER
JOIN campaign C
ON C.id = CC.cid
AND C.approved = 1
AND C.active = 1
AND C.deleted = 0
INNER
JOIN account_affiliate AF
ON AF.usid = C.usid
WHERE AC.usid = $_SESSION['user_id']
ORDER
BY C.name
by the way, please don’t put quotes around numeric values that are to be compared to numeric columns
How am I doing? Im not sure of the AND CC.cid=C.id in the WHERE clause.
$query = "SELECT C.id, C.name, C.descr, C.cost*AF.ratio*AC.ratio AS cost, lpd
FROM
campaign C
INNER JOIN account_affiliate AF
ON AF.usid=C.usid
INNER JOIN account_customer AC
ON AC.usid='".$_SESSION['user_id']."'
INNER JOIN customer_campaign CC
ON CC.usid = '".$_SESSION['user_id']."'
WHERE
C.approved='1'
AND C.active='1'
AND C.deleted='0'
AND CC.cid=C.id
ORDER BY
C.name";
there weren’t any ON clauses because you didn’t make any
change this –
FROM
campaign C
INNER JOIN account_affiliate AF
INNER JOIN account_customer AC
INNER JOIN customer_campaign CC
to this –
FROM
campaign C
INNER JOIN account_affiliate AF
ON -- here, put the condition(s) to join AF to C
INNER JOIN account_customer AC
ON -- here, put the condition(s) to join AC to AF or to C
INNER JOIN customer_campaign CC
ON -- here, put the condition(s) to join CC to AC or to AF or to C
where do these conditions come from? you have to pull them out of your WHERE clause
as for the second query, i’ll bet the campaign table doesn’t have a buyerid column
I took your advice and replaced the commas in the FROM clause with INNER JOIN. I could not see any ON clauses to move the join conditions into in both of these last two problems. I know the answer is right in front of me, but I just can’t see it. Please help me more.
The error that comes up in this first one is: Unknown column C.buyerid in the WHERE clause.
$query = "SELECT C.id, C.name, C.descr, C.cost*AF.ratio*AC.ratio AS cost, lpd
FROM
campaign C
INNER JOIN account_affiliate AF
INNER JOIN account_customer AC
INNER JOIN customer_campaign CC
WHERE
C.approved='1'
AND C.active='1'
AND C.deleted='0'
AND CC.cid=C.id
AND AF.usid=C.usid AND AC.usid='".$_SESSION['user_id']."'
AND CC.usid = '".$_SESSION['user_id']."'
and C.buyerid like '%".$_SESSION['user_id']."%'
ORDER BY
C.name";
The error that comes up in this second one is: Unknown column c.buyerid in the WHERE clause
SELECT c.id, c.name, c.descr
FROM
campaign c
WHERE
c.id NOT IN (
SELECT cc.cid
FROM
customer_campaign cc
WHERE
cc.usid = '".$_SESSION['user_id']."'
)
AND c.deleted = 0
AND c.buyerid
LIKE '%".$_SESSION['user_id']."%'
GROUP BY c.id
ORDER BY c.name";
I dug into the script a little more and found a couple more that I could not change over to explicit syntax on my own. Could you please help me again?
Thanks again for your time!!!
The first one is a Unknown column C.buyerid in the WHERE clause.
$query = "SELECT C.id, C.name, C.descr, C.cost*AF.ratio*AC.ratio AS cost, lpd
FROM campaign C, account_affiliate AF, account_customer AC,
customer_campaign CC
WHERE C.approved='1' AND C.active='1' AND C.deleted='0' AND CC.cid=C.id
AND AF.usid=C.usid AND AC.usid='".$_SESSION['user_id']."' AND CC.usid = '".$_SESSION['user_id']."' and C.buyerid like '%".$_SESSION['user_id']."%'
ORDER BY C.name";
The second one, is a Unknown column c.buyerid in the WHERE clause.
SELECT c.id, c.name, c.descr
FROM campaign c
WHERE c.id NOT IN (
SELECT cc.cid
FROM customer_campaign cc
WHERE cc.usid = '".$_SESSION['user_id']."'
)
and c.deleted = 0 and c.buyerid like '%".$_SESSION['user_id']."%'
GROUP BY c.id
ORDER BY c.name";
Any time you find yourself naming two separate tables separated by a comma you should think to yourself “i could run into trouble doing this” and instead use the explicit INNER JOIN syntax.
Even the mysql manual falls into the trap of using comma join syntax and that is much easier to leave off a join condition or mix it in with an outer join where the tables aren’t in scope.
SELECT A.id
, A.name
, A.inf_name1
, A.inf_name2
, A.userlevel
, A.deleted
, AC.balance
, AC.ratio
, SUM(CC.lpd) AS lpd
, AC.pause AS pause
, CASE WHEN AC.pauseuntil > NOW()
THEN AC.pauseuntil
ELSE 0 END AS pauseuntil
FROM account A
[COLOR="Red"], account_customer AC
LEFT
JOIN customer_campaign CC
ON A.id=CC.usid [/COLOR]
LEFT
JOIN campaign C
ON CC.cid=C.id
WHERE A.id=AC.usid
GROUP
BY A.id
as you are ~very~ new, the explanation for why you get that message is probably too complex
the solution is easy, though – get rid of your implicit joins and use explicit JOIN syntax only
so change this (which is your query, slightly reformatted) –
FROM lead L
, campaign C
, account A
LEFT OUTER
JOIN customer_lead CL
ON CL.id = L.cid
WHERE L.cid = C.id
AND L.status = 'A'
AND C.usid = A.id
to this –
FROM lead L
INNER
JOIN campaign C
ON C.id = L.cid
INNER
JOIN account A
ON A.id = C.usid
LEFT OUTER
JOIN customer_lead CL
ON CL.id = L.cid
WHERE L.status = 'A'
by the way, DISTINCT is ~not~ a function, there is nothing to be gained by putting the first column in the SELECT clause into parentheses behind the DISTINCT keyword, because the DISTINCT keyword applies to the entire SELECT clause and not just the first column that comes after it
That worked perfectly. It is an interesting changeover from implicit to explicit syntax. You are right, I am ~VERY~ new at this and it is easier to show me instead of explaining it to me.
I have another problem that needs your expertise. This one comes up as "Unknown column ‘A.id’ in ‘on clause’
I tried to use some explicit codeing in it but could not get it to work. Here is what the script looks like now:
$query="select A.id,A.name,A.inf_name1,A.inf_name2,A.userlevel,A.deleted,AC.balance,AC.ratio,SUM(CC.lpd) AS lpd, AC.pause AS
pause,CASE WHEN AC.pauseuntil>now() THEN AC.pauseuntil ELSE 0 END AS pauseuntil FROM account A,account_customer AC LEFT JOIN
customer_campaign CC ON A.id=CC.usid LEFT JOIN campaign C ON CC.cid=C.id where A.id=AC.usid GROUP BY A.id $sort_as";
}
else if($_REQUEST['action']=="")
{
$query = "SELECT A.id,A.name,A.inf_name1,A.inf_name2,A.userlevel,A.deleted,
COALESCE
(AF.balance,AC.balance) AS balance,
COALESCE
(AF.ratio,AC.ratio) AS ratio,
SUM
(CC.lpd) AS lpd, AC.pause AS pause,
CASE WHEN
AC.pauseuntil>now() THEN AC.pauseuntil ELSE 0 END AS pauseuntil
FROM
account A
LEFT JOIN
account_affiliate AF ON A.id=AF.usid
LEFT JOIN
account_customer AC ON A.id=AC.usid
LEFT JOIN
customer_campaign CC ON A.id=CC.usid
LEFT JOIN
campaign C ON CC.cid=C.id
WHERE
C.id IS NULL OR C.active='1'
GROUP BY
A.id
$sort_as
";
}
remember, you’re trying to join a table based on the value of a column being equal to some other column in some other table
when you do this –
INNER
JOIN customer_campaign CC
ON CC.cid
the ON clause defaults to true (any non-zero CC.cid is true) so every single CC row is matched to all possible campaigns, whether it’s the right customer or not!!
In this query, it is supposed to retrieve each campaign seperatly to show its information. The problem I am having with this query is it is showing the number of “leads” as a repeat number in each campaign.
The number it keeps repeating in each campaign is 22. I deleted one campaign and the number went down to 16. Each campaign does not have that many leads that were generated. In fact I only had a total of 8 leads in the data base.
So, I delete one of the leads from the data base and now it shows the number as 14.
What I wanted the query to do is return how many leads were generated from each seperate campaign.
The call that I am having problems with is COUNT(L.id) AS leads
Any ideas what I might be doing wrong?
SELECT C.id
, C.name
, C.is_lock
, C.descr
, C.cost AS cost
, C.active
, C.approved
, C.ref_shedule
, C.fields_show
, C.ref_categ
, C.is_lock
, A.name AS username
, COUNT(L.id) AS leads
FROM campaign C
INNER
JOIN account A
ON A.id=C.usid
INNER
JOIN customer_campaign CC
ON CC.cid
INNER
JOIN lead L
ON L.cid
WHERE
C.deleted = 0
GROUP BY C.id
