Please help me, I am getting the following error;

There was an error with SQL: 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 'AND visible=1' at line 1

Not sure what is is, below is my entire coding with the said line highlighted

<?php
/**
*  This file is part of Almond Classifieds Component for Joomla! (site:http://www.almondsoft.com/j )
*  Copyright (C) 2008-2009 AlmondSoft.Com. All rights reserved.
*  http://www.gnu.org/licenses/gpl-2.0.html GNU/GPL  
*/

include('funcs2.php');
 
function print_categories()
{ 
global $html_header, $html_footer, $categories,$ad_ind_width,$schopt,$ads_fields,$subcnmb,$javastl,$hghltcat,
$catl_width, $top_page_info,  $top_page_width, $topsearchfields, $top_leftcol,$catcols,$_REQUEST,$hdhctgrs,
$tbclr_1, $tbclr_2, $tbclr_3, $tbclr_4, $fntclr_1, $msg, $top_rightcol,$btmtophtml, $templ,
$top_bottom,$getcityval, $popctgr, $top_rightcol, $tmltads, $slctcntr, $indx_url, $use_ajax, $locations;

$top_rightcol="";

global $rmmbrloc;
if ($_REQUEST[$schopt]!=''){  

$cityhtml="&nbsp;<p class='pst1'>
<font class=stfntb> 
&nbsp; &nbsp; <b>".$ads_fields[$schopt][0].": ".$_REQUEST[$schopt]." </b></font>
&nbsp; &nbsp;&nbsp; &nbsp;  <font class='stfnt'>( <a href='{$indx_url}allc=1'>".$msg['all_cities']."</a> $rmmbrloc )</font> </font>
 
<br>
";

$citgtv1=$_REQUEST[$schopt];
$getcityval="$schopt=".checkgetfld($citgtv1)."&";
 
}

if ($_REQUEST[$schopt]!=""){$ltstcityv="&city=".checkgetfld($_REQUEST[$schopt]);}
else{$ltstcityv="";}


$grp_cnt=get_grpcnt();

$popctgr=""; $divkey1="0";  
foreach ($categories as $key => $value)
{
 
$aa1=split("_",$key);

if ($aa1[0]=='newcolumn'){$divkey1="0";   $categ[$key]="</td><td valign='top' align=left>";}
else {
if($aa1[0] == 'title')
{   

$p_val1=""; if ($divkey1=="1"){$p_val1="<p class='pst1'>";}
$categ[$key]= " 
$p_val1<p class='pst1'>
<TABLE BORDER=0 WIDTH='90%' cellspacing=0 cellpadding=0  >
<TR><td class='ct_group'><a href='{$indx_url}".$getcityval."md=browse&mds=search&gmct=".$aa1[1]."'>
<font class='ttl_grp'>".$categories[$key].":</a></font> &nbsp; (".$grp_cnt[$aa1[1]].") 
</TD></TR></table>
";

}else 
{

if (($categories[$key][2] != 'h') and ($key != 'evntcl'))
{$divkey1="1";$categ[$key]=print_cat_name($key);}

}
}

}

include ($templ['top']); 
return;
}

function get_grpcnt()
{ 
global $categories, $sql_mct1, $table_ads, $grpcnt, $gkey2, $HTTP_GET_VARS;
 
foreach ($categories as $key => $value)
{
$aa1=split("_",$key);
if($aa1[0]== 'title'){$kk1=0; 
if ($sql_mct1!=""){

get_gcnt2();
 
}
$sql_mct1="";
}
if($kk1==1 and $key!="evntcl" and  $key!="evcl_rpl" and 
$categories[$key][2]!="h" and $aa1[0]!='newcolumn'){$sql_mct1=$sql_mct1." catname='$key' or";}
if($aa1[0]== 'title'){$kk1=1; $gkey2=$aa1[1];}
}

get_gcnt2();
 
return $grpcnt;
}

function get_gcnt2()
{
global $sql_mct1, $table_ads, $grpcnt, $gkey2;
$city_query='';
if (isset($_GET['city']) && $_GET['city']) {
$city_query = " AND city='".$_GET['city']."' ";
}       
$sql_mct1 = preg_replace("/or$/", "", $sql_mct1);
$sql_query = "SELECT COUNT(idnum) FROM $table_ads WHERE $sql_mct1 AND visible=1 $city_query";
$sql_res = mysql_query($sql_query);
if (!$sql_res) {
$errID = mysql_errno();
$errEr = mysql_error();
die('There was an error with SQL: '.(isset($errID) ? $errID.': '.$errEr : $errEr));
}
$row=mysql_fetch_row($sql_res);
 
if ($row) {
$grpcnt[$gkey2]=$row[0];

}
}


 
function print_cat_name($key)
{
global $categories, $fntclr_1, $msg, $subcnmb, $getcityval,$popctgr, $indx_url;

# $dtupd=get_date_update($key); if ($dtupd!=''){$dtupd=$msg['updated_t'].": ".$dtupd;}

$vhtml= "
<nobr><a href='".sturl_ct_top($key,$getcityval, "1")."' >
".$categories[$key][0]."</a> <font class='ct_count'>(".get_cat_count($key).")</font></nobr> <br>
";
return $vhtml;
}




function checkgetfld($value)
{
$value=ereg_replace(' ', '+', $value);
$value=ereg_replace('@', '%40', $value);
$value=ereg_replace('!', '%21', $value);
$value=ereg_replace("'", '%27', $value);
$value=ereg_replace("\"", '%27', $value);

return $value;
}


function get_total_count()
{
global $cat_fields, $table_ads, $ct, $page, $adsonpage, $html_header, 
$html_footer; 
 
global $sqlaflcl;

$sql_query="select count(idnum) from $table_ads where $sqlaflcl
visible=1 ";
if( !($sql_res=@mysql_query("$sql_query")))
{echo $html_header;
echo "
<center>
<font FACE='ARIAL, HELVETICA'  COLOR='#bb0000' size=-1><b>
Error in connecting to ads MySQL table <font color='#000099'>'$table_ads'</font>.
<br> Seems, this table is not created, 
<a href='createtb.php'>click here </a> to create this table.
</b></font></center>
";
echo $html_footer;
return;
}

$row=mysql_fetch_row($sql_res);
$count=$row[0];
return $count;
} 



?>

your quotes is not right .Try whit :
$sql_query = "SELECT COUNT(idnum) FROM $table_ads WHERE $sql_mct1 AND visible=1 $city_query";
echo $sql_query;
end see where the comma is missing or is surpluse.

Hi jjtao,

Could you change your code to the below and tell us the output?

...
$sql_query = "SELECT COUNT(idnum) FROM $table_ads WHERE $sql_mct1 AND visible=1 $city_query";
echo '$sql_mct1 = ' + $sql_mct1;
echo '$sql_query = ' + $sql_query;
$sql_res = mysql_query($sql_query);
...

Where is $sql_mct1 initiated? It looks to me like it's "", which would create an SQL statement with "...WHERE AND...".

I could be missing something...

Chris Fry
Perth, Australia

Jumping jellybeans. Firstly thank you for all your help. I get this error now SELECT COUNT(idnum) FROM jos_aclassft WHERE AND visible=1 There was an error with SQL: 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 'AND visible=1' at line 1 Confusing as No idea why it doesnt like the flipping And although its in the right place. Anyway here is my coding again just for refernence, I am very new to Mysql and PHP

<?php
/**
*  This file is part of Almond Classifieds Component for Joomla! (site:http://www.almondsoft.com/j )
*  Copyright (C) 2008-2009 AlmondSoft.Com. All rights reserved.
*  http://www.gnu.org/licenses/gpl-2.0.html GNU/GPL  
*/

include('funcs2.php');
 
function print_categories()
{ 
global $html_header, $html_footer, $categories,$ad_ind_width,$schopt,$ads_fields,$subcnmb,$javastl,$hghltcat,
$catl_width, $top_page_info,  $top_page_width, $topsearchfields, $top_leftcol,$catcols,$_REQUEST,$hdhctgrs,
$tbclr_1, $tbclr_2, $tbclr_3, $tbclr_4, $fntclr_1, $msg, $top_rightcol,$btmtophtml, $templ,
$top_bottom,$getcityval, $popctgr, $top_rightcol, $tmltads, $slctcntr, $indx_url, $use_ajax, $locations;

$top_rightcol="";

global $rmmbrloc;
if ($_REQUEST[$schopt]!=''){  

$cityhtml="&nbsp;<p class='pst1'>
<font class=stfntb> 
&nbsp; &nbsp; <b>".$ads_fields[$schopt][0].": ".$_REQUEST[$schopt]." </b></font>
&nbsp; &nbsp;&nbsp; &nbsp;  <font class='stfnt'>( <a href='{$indx_url}allc=1'>".$msg['all_cities']."</a> $rmmbrloc )</font> </font>
 
<br>
";

$citgtv1=$_REQUEST[$schopt];
$getcityval="$schopt=".checkgetfld($citgtv1)."&";
 
}

if ($_REQUEST[$schopt]!=""){$ltstcityv="&city=".checkgetfld($_REQUEST[$schopt]);}
else{$ltstcityv="";}


$grp_cnt=get_grpcnt();

$popctgr=""; $divkey1="0";  
foreach ($categories as $key => $value)
{
 
$aa1=split("_",$key);

if ($aa1[0]=='newcolumn'){$divkey1="0";   $categ[$key]="</td><td valign='top' align=left>";}
else {
if($aa1[0] == 'title')
{   

$p_val1=""; if ($divkey1=="1"){$p_val1="<p class='pst1'>";}
$categ[$key]= " 
$p_val1<p class='pst1'>
<TABLE BORDER=0 WIDTH='90%' cellspacing=0 cellpadding=0  >
<TR><td class='ct_group'><a href='{$indx_url}".$getcityval."md=browse&mds=search&gmct=".$aa1[1]."'>
<font class='ttl_grp'>".$categories[$key].":</a></font> &nbsp; (".$grp_cnt[$aa1[1]].") 
</TD></TR></table>
";

}else 
{

if (($categories[$key][2] != 'h') and ($key != 'evntcl'))
{$divkey1="1";$categ[$key]=print_cat_name($key);}

}
}

}

include ($templ['top']); 
return;
}

function get_grpcnt()
{ 
global $categories, $sql_mct1, $table_ads, $grpcnt, $gkey2, $HTTP_GET_VARS;
 
foreach ($categories as $key => $value)
{
$aa1=split("_",$key);
if($aa1[0]== 'title'){$kk1=0; 
if ($sql_mct1!=""){

get_gcnt2();
 
}
$sql_mct1="";
}
if($kk1==1 and $key!="evntcl" and  $key!="evcl_rpl" and 
$categories[$key][2]!="h" and $aa1[0]!='newcolumn'){$sql_mct1=$sql_mct1." catname='$key' or";}
if($aa1[0]== 'title'){$kk1=1; $gkey2=$aa1[1];}
}

get_gcnt2();
 
return $grpcnt;
}

function get_gcnt2()
{
global $sql_mct1, $table_ads, $grpcnt, $gkey2;
     
$city_query='';
if (isset($_GET['city']) && $_GET['city']) {
$city_query = " AND city='".$_GET['city']."' ";
}
       
$sql_mct1 = preg_replace("/or$/", "", $sql_mct1);
$sql_query = "SELECT COUNT(idnum) FROM $table_ads WHERE $sql_mct1 AND visible=1 $city_query";
echo $sql_query; 
echo $sql_mct1;
$sql_res = mysql_query($sql_query);
if (!$sql_res) {
$errID = mysql_errno();
$errEr = mysql_error();
die('There was an error with SQL: '.(isset($errID) ? $errID.': '.$errEr : $errEr));
}
$row=mysql_fetch_row($sql_res);

if ($row) {
$grpcnt[$gkey2]=$row[0];
}
}

 
function print_cat_name($key)
{
global $categories, $fntclr_1, $msg, $subcnmb, $getcityval,$popctgr, $indx_url;

# $dtupd=get_date_update($key); if ($dtupd!=''){$dtupd=$msg['updated_t'].": ".$dtupd;}

$vhtml= "
<nobr><a href='".sturl_ct_top($key,$getcityval, "1")."' >
".$categories[$key][0]."</a> <font class='ct_count'>(".get_cat_count($key).")</font></nobr> <br>
";
return $vhtml;
}




function checkgetfld($value)
{
$value=ereg_replace(' ', '+', $value);
$value=ereg_replace('@', '%40', $value);
$value=ereg_replace('!', '%21', $value);
$value=ereg_replace("'", '%27', $value);
$value=ereg_replace("\"", '%27', $value);

return $value;
}


function get_total_count()
{
global $cat_fields, $table_ads, $ct, $page, $adsonpage, $html_header, 
$html_footer; 
 
global $sqlaflcl;

$sql_query="select count(idnum) from $table_ads where $sqlaflcl
visible=1 ";
if( !($sql_res=@mysql_query("$sql_query")))
{echo $html_header;
echo "
<center>
<font FACE='ARIAL, HELVETICA'  COLOR='#bb0000' size=-1><b>
Error in connecting to ads MySQL table <font color='#000099'>'$table_ads'</font>.
<br> Seems, this table is not created, 
<a href='createtb.php'>click here </a> to create this table.
</b></font></center>
";
echo $html_footer;
return;
}

$row=mysql_fetch_row($sql_res);
$count=$row[0];
return $count;
} 



?>

What is $sql_mct1?

It looks like $sql_mct1 is an empty string, which is creating the "WHERE AND" statement in your SQL. There should be a condition between the WHERE and the AND

E.g.
SELECT * FROM Persons
WHERE FirstName='Foo' AND LastName='Bar'

Chris Fry
Perth, Australia

You still get this error because you don't have a die() function end make some debuging. After echo $sql_query; echo $sql_mct1; insert function die() end SEEN the SQL statements.After you debug remove function die().

not sure what the Mct1, this was an application created for me and my site, so been trying to get to grips with what is happening because I am a noob to SQL. I have inserted the die function will run the query now and see what she does. I am wondering if I should delete that string since its pointed to nothing.

SELECT COUNT(idnum) FROM jos_aclassft WHERE AND visible=1 SELECT COUNT(idnum) FROM jos_aclassft WHERE AND visible=1

Think I am going to see if the coding needs that empty string, its not pointing to anything on the database :(.

okay So I removed the die funtions and this is what I get

SELECT COUNT(idnum) FROM jos_aclassft WHERE AND visible=1
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /usr/***/components/com_aclassf/top.php on line 120

$row=mysql_fetch_row($sql_res); So there really is nothing sitting there.

I should have stuck to graphic design lol

Try this:

$sql_mct1 = preg_replace("/or$/", "", $sql_mct1);

if(!(isnull($sql_mct1) || $sql_mct1 == ""))
    {$sql_mct1_clause = $sql_mctl . ' AND';}
else
    {$sql_mct1_clause = "";}

$sql_query = "SELECT COUNT(idnum) FROM $table_ads WHERE $sql_mct1_clause visible=1 $city_query";

$sql_res = mysql_query($sql_query);

You can remove the echo's added before - they were just for testing.

Try this:

$sql_mct1 = preg_replace("/or$/", "", $sql_mct1);

if(!(isnull($sql_mct1) || $sql_mct1 == ""))
    {$sql_mct1_clause = $sql_mctl . ' AND';}
else
    {$sql_mct1_clause = "";}

$sql_query = "SELECT COUNT(idnum) FROM $table_ads WHERE $sql_mct1_clause visible=1 $city_query";

$sql_res = mysql_query($sql_query);

You can remove the echo's added before - they were just for testing.

Thank you once again guys for all your help. So yes I seem to be getting this error

SELECT COUNT(idnum) FROM jos_aclassft WHERE AND visible=1
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /usr/www/users/stealtf/components/com_aclassf/top.php on line 120

which I only think this whole coding string is trying to find something that doesn't exist. I guess its my fault for not making my own application and finding one on the net. Now I have no idea where it is this is connecting to. I know what you thinking (stupid noob!). So I am doing the best solution...Im removing the application. Everyone has tried to help and I admire all the brain power here but its not going to help if I don't know where its trying to fetch row from when it looks like it doesn't exist.

I won't mind seeing what the solution would be, but might as well focus your huge brain power on someone that can actaully be helped. I will find the person who made this code and ask him if it was lack or sleep or carelessness how a whole query string can connect to thin air without him or her even testing it. Thank you all again.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.