I have a table that includes change control data - therefore there are multiple records with my primary key that I need for a query. The most recent record is denoted by a '0' and all older records yield a '1' in the deleted column. The tables are from SugarCRM v6.0. I know that my connection is good because my simple queries run fine. The error is on the WHILE line. I know that my syntax is wrong some where.
--------------------------------------table structure----------------------------------------------------------------
leads2_auto_cstm.id_c Value1
leads_lead2_auto_c.leads_lead36fbd2_auto_ldb Value1
leads_lead2_auto_c.leads_leada7f6toleads_ida $var
[login to view URL] 0 or 1 (='0' at the record that I need to use)
------------------------------------SQL Query Start----------------------------------------------------------------
mysql_select_db($dbname, $con);
$result= mysql_query("SELECT leads2_auto_cstm.driver1_name_c,leads2_auto_cstm.driver1_accident_c,leads2_auto_cstm.id_c,leads2_auto_cstm.driver2_name_c FROM leads2_auto_cstm WHERE EXISTS (SELECT leads2_auto_cstm.id_c FROM leads_lead2_auto_c,leads2_auto_cstm WHERE leads_lead2_auto_c.leads_leada7f6toleads_ida='%$var%' AND leads_lead2_auto_c.deleted='0' AND leads_lead2_auto_c.leads_lead36fbd2_auto_ldb=leads2_auto_cstm.id_c)");
while($row = mysql_fetch_array($result))
{
echo "<strong><p>id_c from leads2_auto_cstm :&nbsp;" . $row['id_c'] . "</strong> - driver1_accident_c: " . $row['driver1_accident_c'] .
"<br>driver1 name: &nbsp;" . $row['driver1_name_c'] . "<br>driver2 name: &nbsp;" . $row['driver2_name_c'] . "<br> ~~~~~~~~~~~~~~~~~ </p>";
}
// end SQL query of array