how to encode properly sql search result to be decoded in JavaScript and use in different div

Question

I have a SQL database where 'title' is one of the columns. I had written the function below to be passed back via AJAX to be displayed.

I'm trying to get the entire contents in title column and is stored in position 0 of the array but all I got was the first letter of the string stored in title[0] and title[1] denotes the next letter of the contents in title of the sql query. Any pointers?

e.g.

In my database the search result title[0] returns Hello World but when I run this script title[0] returns H and title[1] returns e.

I was expecting the result of title[0] returns Hello World.

function getorders($username,$orderid)
 {
     $conn = dbConnect();
     $sql = "SELECT *  FROM `permissions` WHERE `username` = '$username' AND `orderid` = '$orderid'";
     $result=mysqli_query($conn,$sql) or die(mysqli_error($conn));
     
     if( mysqli_num_rows($result)>0 )
     {
       $row = mysqli_fetch_array($result);
         echo json_encode($row);
     }
     else{
     echo "not found";}
     
    // echo $username." ".$orderid;
 }

function myAjax() 
{
 var username = $("#username").val();
 var orderid = $("#orderid").val();

      $.ajax({
           type: "POST",
           url: 'jumper.php',
           data:{ data1:{"username": username, "orderid": orderid}},
           success:function(html) 
           { 
             const myArr = JSON.parse(html);
             document.getElementById("title").innerHTML = myArr.title[0];
           }

      });
}

Just use `myArr.title`. You already return only one row from the database, so you don't need to access a specific index. — ADyson, Aug 27 '21 at 09:46
**Warning:** Your code is vulnerable to SQL Injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / mysqli. **Never** insert unsanitised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data. — ADyson, Aug 27 '21 at 09:46
https://phpdelusions.net/mysqli also contains good examples of writing safe SQL using mysqli. See also the [mysqli documentation](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) and this: [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) . Parameterising your queries will also greatly reduce the risk of accidental syntax errors as a result of un-escaped or incorrectly quoted input values. — ADyson, Aug 27 '21 at 09:46
@ADyson Thank you for pointing that out on the sql code i will read more on sanitization. But then again i did not intend to return only a single row from the result. how do i return the entire search result ? many thanks! — xenos, Aug 27 '21 at 09:59
You need a loop. It also needs a bit of refactoring because you can't return "not found" when there are no rows, as that would cause your JS code to crash when it's expecting a list of results. — ADyson, Aug 27 '21 at 10:01
e.g. `$data = array(); while ($row = mysqli_fetch_array($result)) { $data[] = $row; } echo json_encode($data);` — ADyson, Aug 27 '21 at 10:02
Then in the JS if you want to display all the titles, it's a loop again. e.g. `success:function(json) { const myArr = JSON.parse(json); titles = ""; for (i = 0; i < myArr.length; i++) { titles += myArr[i].title + "
"; } document.getElementById("title").innerHTML = titles; }` . Something like that. Adjust the exact output format to your preferences, obviously. — ADyson, Aug 27 '21 at 10:04
@ADyson thanks a bunch my friend! it worked flawlessly! and i think ive learned much from you as well! Thanks again for the sharing and great help! cheers mate! — xenos, Aug 27 '21 at 10:21
Great. I wrote it up as a proper answer below then, so you can mark it as accepted - thanks :-) — ADyson, Aug 27 '21 at 10:27
It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Aug 27 '21 at 11:16

score 1 · Accepted Answer · answered Aug 27 '21 at 10:26

Currently your code only returns one row of data. To return a list of data including all the titles, you need a loop. It also needs a bit of refactoring because you can't return "not found" when there are no rows, as that would cause your JS code to crash when it's expecting a list of results.

PHP

$data = array();        
while ($row = mysqli_fetch_array($result)) { 
  $data[] = $row; 
}
echo json_encode($data);

Then you also need a loop in the JavaScript:

success:function(json) { 
  const myArr = JSON.parse(json); 
  titles = ""; 

  for (i = 0; i < myArr.length; i++) { 
     titles += myArr[i].title + "<br/>";
  } 

  document.getElementById("title").innerHTML = titles; 
}

how to encode properly sql search result to be decoded in JavaScript and use in different div

1 Answers1