syntax issue (I think)

[jim.davidson]

this works when customer_id = 46

 

mysql_select_db($database_imcrecycle, $imcrecycle);

$query_GetStates = "SELECT state_id, state_name, country FROM other_states WHERE customer_id = 46 UNION SELECT state_id, state_name, country FROM states ORDER BY country, state_name";

$GetStates = mysql_query($query_GetStates, $imcrecycle) or die(mysql_error());

$row_GetStates = mysql_fetch_assoc($GetStates);

$totalRows_GetStates = mysql_num_rows($GetStates);

 

 

 

This doesn't work even though $custID is 46

$custID=46;

 

mysql_select_db($database_imcrecycle, $imcrecycle);

$query_GetStates = "SELECT state_id, state_name, country FROM other_states WHERE customer_id = $custID UNION SELECT state_id, state_name, country FROM states ORDER BY country, state_name";

$GetStates = mysql_query($query_GetStates, $imcrecycle) or die(mysql_error());

$row_GetStates = mysql_fetch_assoc($GetStates);

$totalRows_GetStates = mysql_num_rows($GetStates);

 

This has got to be a syntax thing for $custID.  Which do I use

 

.$custID.

 

 

'".$custID."'

 

'.$custID.'

 

[kenrbnsn]

How do you know it doesn't work? Is there an error message? Echo out the query before doing the mysql_query to see that you're generating the correct query.

 

Ken

[jim.davidson]

The first section works i get my recordset just fine.

 

 

This is the error i get when I try to load with the other one.

I'm running php, dreamweaver 8, and mySQL 4.1.21

 

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 'UNION SELECT state_id, state_name, country FROM states ORDER BY country, state_n' at line 1

[TreeNode]

Precendence can be controlled by using (parenthesis), try separating your union out by using such. Also, you might want to reduce the ambiguous of which fields you have in the WHERE clause, so use aliasing... FROM other_stats AS ost.customer_id = 46... Also, there are a few flags that can be applied to the union, such as DISTINCT or ALL. Finally, MySQL 4.1.x doesn't effecienly use unions correctly, if not at all.

[jim.davidson]

Thanks I'll give that a shot.

 

What's funny is that even though I get the error I find it still works.  I get the results in the recordset that I'm looking for.