0

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;
} 



?>
3
Contributors
10
Replies
11
Views
8 Years
Discussion Span
Last Post by jjtao
0

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.

0

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

0

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;
} 



?>
0

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

0

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().

0

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.

0

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 :(.

0

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

0

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.

0

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.