Fatal error: Uncaught PDOException: SQLSTATE[42000]:?

Question

I'm trying to build an infinite scroll with search. So far I came to this problem:

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DESC LIMIT 0, 4'.

Can anyone help me with this problem?

if(isset($_GET["starts"], $_GET["limits"])){
 $search = htmlspecialchars($_GET['Search'],ENT_QUOTES,'utf-8');
$start = htmlspecialchars($_GET['starts'],ENT_QUOTES, 'utf-8');
$limit = htmlspecialchars($_GET['limits'],ENT_QUOTES, 'utf-8');
$stmt = $conn->prepare("SELECT  `jobtitle`, `company`, `location`, 
`employment`, `email`, `Description` FROM `featured job` WHERE jobtitle LIKE 
`:jobtitle` DESC LIMIT :starts, :limits");
 $stmt->bindParam(":starts", intval(trim($start)), PDO::PARAM_INT );
 $stmt->bindParam(":limits", intval(trim($limit)), PDO::PARAM_INT );
$stmt->bindParam(":jobtitle",$search);
$stmt->execute();
foreach ($posts as $data) {
  echo "<h2>".$data['jobtitle']."</h2>";
 }

Here's my ajax code

  $(document).ready(function(){
   var limits = 4;
   var starts = 0;

   var action = 'inactive';
   function load_job_data(limits, starts)
   {
    $.ajax({
   url:"load_more.php",
   method:"GET",
   data:{limits:limits, starts:starts},
   cache:false,
   success:function(data)
   {
   $('.results').append(data);
   if(data == '')
   {
   $('#load_data_messages').text("Your potential jobs is loading");
   $('#load_data_messages').css("color", "green");

   action = 'active';
   }
   else
   {
   $('#load_data_messages').text("Out of jobs! please come back later!");
   $('#load_data_messages').css("color","red");
   action = "inactive";
   }
   }
   });
   }

   if(action == 'inactive')
   {
   action = 'active';
   load_job_data(limits, starts);
   }
   $(window).scroll(function(){
   if($(window).scrollTop() + $(window).height() > $(".load_data").height() 
&& action == 'inactive')
{
 action = 'active';
 starts = starts + limits;
 setTimeout(function(){
 load_job_data(limits, starts);
 }, 1000);
 }
 });

 });

WHERE having a direction doesn't make sense. Are you missing an `ORDER BY` clause? You also shouldn't need the backticks around `:jobtitle`. — Jonnix, Aug 10 '19 at 16:02
I expect you want both but I can't tell. It's not an either or, you can have both. — Jonnix, Aug 10 '19 at 16:09
I feel like you are unsure what the purpose of `htmlspecialchars` is. You should use only when you output something to the HTML. In you case you use on integers which go into the database, and that makes no sense. — Dharman, Aug 10 '19 at 18:16

ScaisEdge · Answer 1 · 2019-08-10T18:40:47.067

3

:jobtitle is not a column name so you should not use the backtics and you miss the ORDER BY clause

SELECT  `jobtitle`
  , `company`
  , `location`
  , `employment`
  , `email`
  , `Description` 
FROM `featured job` 
WHERE jobtitle LIKE :jobtitle 
ORDER BY `jobtitle` DESC LIMIT :starts, :limits

and try use PARAM_STR for $search;

$stmt->bindParam(":jobtitle",$search, PDO::PARAM_STR);

edited Aug 10 '19 at 18:40

answered Aug 10 '19 at 16:22

ScaisEdge

131,976
10
91
107

answer updated with a suggestion – ScaisEdge Aug 10 '19 at 16:34

score 1 · Answer 2 · answered Aug 10 '19 at 16:11

1

I think you should remove the DESC keyword. If you want to sort it, just use ORDER BY <column name> DESC before the limit clause.

Try:

SELECT `jobtitle`, `company`, `location`, `employment`, `email`, `Description` FROM `featured job` WHERE jobtitle LIKE `:jobtitle` LIMIT :starts, :limits

answered Aug 10 '19 at 16:11

Karlo

242
2
7

What about ``:jobtitle``? – Dharman Aug 10 '19 at 18:13

Fatal error: Uncaught PDOException: SQLSTATE[42000]:?

2 Answers2