[RESOLVED] problem with syntax

sheepsheep

I keep getting the error message
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 '' at line 18

line 18 is AND tblMachine.ClientRef = ' ".$sr." ' ";

I have tested the variables, they are all OK up to this point, it is just getting the correct syntax for including the variable $sr in this query.

What I am doing is sending an e-mail to a list of users who fit within a certain criteria, so I am selecting users from a mysql database tblUser as follows
Create a list of Clients, and look at each of these clients in turn, and for each of these clients only show me the users who haven't sent me results this month.

Can anyone see where I am going wrong - I have gone over it time after time and cannot see anything......... Thanks

My code is

if ($FirstRemind == $today)
{
$query = "SELECT ClientId FROM tblClient WHERE ConcesRef = '$ConcesRef'  ";
$result21 = mysql_query($query) 	
or die (mysql_error());
while($row21 = mysql_fetch_array($result21))
$ClientR[] = $row21["ClientId"]; 

foreach ($ClientR as $sr)
  {	

$sql = "SELECT tblUser.UEmail, tblUser.UName, tblMachine.MachineId, tblMachine.CommonName, 
tblMachine.SerialNo, tblClient.ClientId, tblClient.CoName
FROM (tblClient INNER JOIN tblUser 
ON (tblClient.ConcesRef = tblUser.ConcesRef) 
AND (tblClient.ClientId = tblUser.ClientRef) 
	LEFT JOIN tblMachine 
ON (tblUser.ConcesRef = tblMachine.ConcesRef) 
AND (tblUser.ClientRef = tblMachine.ClientRef) 
	LEFT JOIN tblResults 
ON (tblMachine.ConcesRef = tblResults.ConcesRef) 
AND (tblMachine.ClientRef = tblResults.ClientRef) 
AND (tblMachine.MachineId = tblResults.MachineRef) 
AND tblResults.DateLogRef = '$DateLog'
AND tblResults.ResultsId Is Null
AND tblUser.Removed = '0000-00-00 00:00:00'
AND tblUser.AuthorityLevel >1
AND tblMachine.Removed = '0000-00-00 00:00:00'
AND tblMachine.ClientRef = ' ".$sr." ' ";
$getinfo = mysql_query($sql)   or die (mysql_error());
while ($row22 = mysql_fetch_array($getinfo, MYSQL_ASSOC))
{
$UEmail = $row22["UEmail"];
$UName = $row22["UName"];
$MId = $row22["MachineId"];
$MName = $row22["CommonName"];
$serialno = $row22["SerialNo"];
$CliName = $row22["CoName"];
$CliRef = $row22["ClientId"];

	$body2 = "email body text here ";
	mail ("$UEmail", "subject", "$body", "FROM:X@x.com");


			} //close while
			} //close foreach
			} // close if first remind is today

NogDog

I don't see an obvious problem, but it might help to output the actual query as part of your die() error message:

$getinfo = mysql_query($sql)   or die (mysql_error()."<br>\nQuery: $sql");

sheepsheep

Thanks for this - I am not sure what you would expect the output be be, but I get this output;
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 '' at line 18

Query:SELECT tblUser.UEmail, tblUser.UName, tblMachine.MachineId, tblMachine.CommonName, tblMachine.SerialNo, tblClient.ClientId, tblClient.CoName FROM (tblClient INNER JOIN tblUser ON (tblClient.ConcesRef = tblUser.ConcesRef) AND (tblClient.ClientId = tblUser.ClientRef) LEFT JOIN tblMachine ON (tblUser.ConcesRef = tblMachine.ConcesRef) AND (tblUser.ClientRef = tblMachine.ClientRef) LEFT JOIN tblResults ON (tblMachine.ConcesRef = tblResults.ConcesRef) AND (tblMachine.ClientRef = tblResults.ClientRef) AND (tblMachine.MachineId = tblResults.MachineRef) AND tblResults.DateLogRef = '1' AND tblResults.ResultsId Is Null AND tblUser.Removed = '0000-00-00 00:00:00' AND tblUser.AuthorityLevel >1 AND tblMachine.Removed = '0000-00-00 00:00:00' AND tblMachine.ClientRef = '545'

It is still giving the sql error message, and then it repeats the query $sql, filling in one of the values of $sr ('545').

Is this what you were expecting? I am not sure where to take this.........

leatherback

Not having used inner joins on multiple colums, this is just a long shot, but eh.. Don't you need a separate set of brackets around the sets of jins:

not: LEFT JOIN tblMachine ON (tblUser.ConcesRef = tblMachine.ConcesRef) AND (tblUser.ClientRef = tblMachine.ClientRef)

but:

LEFT JOIN tblMachine ON ((tblUser.ConcesRef = tblMachine.ConcesRef) AND (tblUser.ClientRef = tblMachine.ClientRef))

NogDog

Is tblMachine.ClientRef an integer type field? If so, the problem may be the spaces between the quotes:

AND tblMachine.ClientRef = ' ".$sr." ' ";
                            ^       ^

If the field is numeric, you don't really need the quotes, so you could just do:

AND tblMachine.ClientRef = $sr";

This is just an educated guess on my part, so no guarantees. 🙂

Piranha

You miss a closing paranthesis. After FROM you have one ( that is never closed.

Edit: Or remove the one after FROM, I can't see that it does anything. And when you are at it you probably want to change the row AND tblResults.DateLogRef = '1' and start it with a where instead WHERE tblResults.DateLogRef = '1', then that and the rest is for the whole query and not only the join.

Edit2: Why do you use one query to get the ClientId and then another to get the rest of the information? It would probably be a lot quicker to do it in one query. The only thing that is needed is to add the where-part of the first query to the second one (this is untested).

SELECT 
	tblUser.UEmail, 
	tblUser.UName, 
	tblMachine.MachineId, 
	tblMachine.CommonName, 
	tblMachine.SerialNo, 
	tblClient.ClientId, 
	tblClient.CoName 
FROM tblClient 
	INNER JOIN tblUser 
		ON (tblClient.ConcesRef = tblUser.ConcesRef) 
		AND (tblClient.ClientId = tblUser.ClientRef) 
	LEFT JOIN tblMachine 
		ON (tblUser.ConcesRef = tblMachine.ConcesRef) 
		AND (tblUser.ClientRef = tblMachine.ClientRef) 
	LEFT JOIN tblResults 
		ON (tblMachine.ConcesRef = tblResults.ConcesRef) 
		AND (tblMachine.ClientRef = tblResults.ClientRef) 
		AND (tblMachine.MachineId = tblResults.MachineRef) 	
	WHERE tblResults.DateLogRef = '1' 
		AND tblResults.ResultsId Is Null 
		AND tblUser.Removed = '0000-00-00 00:00:00' 
		AND tblUser.AuthorityLevel >1 
		AND tblMachine.Removed = '0000-00-00 00:00:00' 
		AND tblMachine.ClientRef = '545' 
		AND tblClient.ConcesRef = '$ConcesRef'

sheepsheep

thanks to everyone

I am not sure about the other fixes from leatherback and nogdog, as I didn;t have to try them

It was piranha's missing ')'

The query now works with no error messages, and it also outputs the expected results when I query $sql.

So many thanks to you all.