0

I've got two tables in my DB MySQL:

Table predictiongame_bet (uid, mid, bet_home, bet_away)

Table users (uid, name)

This is my query:

SELECT U.name AS name, PB.bet_home as casa, PB.bet_away as trasferta
FROM predictiongame_bet as PB
    JOIN users as U ON U.uid = PB.uid
WHERE mid=49
ORDER BY U.name ASC

On PhpMyAdmin it works correctly and I get my view, but I'm trying to get those values in PHP and with an AJAX POST call where I pass the SQL query.

This is my code:

function cercaRisultati() {
  // value = $('#inputValue').val();
  // tabella = 'predictiongame_bet'; 
  // columnCondition = "mid = "+value;
  sql = "SELECT U.name AS name, PB.bet_home as casa, PB.bet_away as trasferta 
        FROM predictiongame_bet as PB 
            JOIN users as U ON U.uid = PB.uid 
        WHERE mid=49 ORDER BY U.name ASC";
  console.log('SQL: '+sql);
  $('#sqlArrayUtenti').val(sql);
  return tabella;
}

$(document).ready(function(){
  $('#buttonCercaDB').on("click",function(){
    var sql=$('#sqlArrayUtenti').val();
    $.ajax({
        url:'cerca-risultati.php',
        method:'POST', 
        data:{
            sql:sql,
        },
        dataType: 'json',
        success:function(response){
          response.map(el=>{
            console.log(el);
          });
          $("#sqlAssegnazionePunti").css('display','block');
        }
    });
  });
});

That is the cercarisultati.php code:

$sql = $_POST['sql'];

$i = 0;
$arr_match = array();
$result = $conn->query($sql);
    
if ($result->num_rows > 0) {

 while($row = $result->fetch_assoc()) {
  $arr_match[$i]['name'] = $row['name'];
  $arr_match[$i]['bet_home'] = $row['casa'];
  $arr_match[$i]['bet_away'] = $row['trasferta'];
  $i++;
 }
echo json_encode($arr_match);
}
else {  echo "0 results"; }

It seems that $row['name'] is always null, why?

If I remove from the while loop $arr_match[$i]['name'] = $row['name']; it works ok, but, of course, it is not what I want.

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
grandeacci
  • 1
  • 2
    You are blindly executing any SQL that is passed to your PHP script. What if somebody posted to your script passing `DELETE FROM predictiongame_bet`? – Booboo Mar 01 '23 at 15:06
  • I'm the only one user, is a personal project – grandeacci Mar 01 '23 at 15:07
  • Are the `['bet_home']` and `['bet_away']` values populated? – Martin Mar 01 '23 at 15:08
  • yes Martin, they are populated – grandeacci Mar 01 '23 at 15:09
  • Immediately after your `while` code line, what does the line `print_r($row);` give you? – Martin Mar 01 '23 at 15:11
  • Nothing. I click on button but it doesn't do anything. – grandeacci Mar 01 '23 at 15:14
  • Well it must show you something because you say that `['bet_home']` and `['bet_away']` values are populated. To be clear; I mean put the `print_r` on the line underneath the line with `while(...` on it. – Martin Mar 01 '23 at 15:16
  • Ok, I've tried passing the sql query directly on file cerca-risultati.php and when I "print_r($row)" it works correctly. $arr_match[$i]['name'] = $row['name'] still not working. – grandeacci Mar 01 '23 at 15:19
  • my query was to check if the data coming back from the `$result` resource was a numerically indexed array; `$row[0]['name'] .... $row[1]['name']` etc. etc. but it seems like this is not the case. – Martin Mar 01 '23 at 15:23
  • 1
    As an aside: Why don't you just do `sql = "SELECT U.name, PB.bet_home, PB.bet_away FROM etc.`. Then instead of your loop just do `$arr_match = $results->fetch_all(MYSQLI_ASSOC);`? – Booboo Mar 01 '23 at 15:24
  • You can also [check if your `JSON_encod`ing is giving you errors](https://stackoverflow.com/questions/62879227/how-to-find-out-why-json-encode-is-failing-php) – Martin Mar 01 '23 at 15:26
  • @Booboo like this? if ($result->num_rows > 0) { $arr_match = $results->fetch_all(MYSQLI_ASSOC); echo json_encode($arr_match); } – grandeacci Mar 01 '23 at 15:30
  • @grandeacci Right, but also adjust your SELECT statement to remove column aliases since you seemingly do not want to use those names in `$arr_match`. – Booboo Mar 01 '23 at 15:32
  • @Booboo not working, I get HTTP ERROR 500 $sql = "SELECT U.name, PB.bet_home, PB.bet_away FROM predictiongame_bet as PB JOIN users as U ON U.uid = PB.uid WHERE mid=49 ORDER BY U.name ASC"; $arr_match = array(); $result = $conn->query($sql); if ($result->num_rows > 0) { // output data of each row $arr_match = $results->fetch_all(MYSQLI_ASSOC); echo json_encode($arr_match); } – grandeacci Mar 01 '23 at 15:36
  • @grandeacci That should be `$arr_match = $result->fetch_all(MYSQLI_ASSOC);`. My error -- I had `$results` instead of `$result`. – Booboo Mar 01 '23 at 15:44
  • @Booboo print_r($row) works correctly, but echo json_encde($arr_match) still not showing anything. – grandeacci Mar 01 '23 at 15:47
  • @grandeacci I suggested those changes only because it is simpler and thus more efficient than what you had. If each subarray's 'name' field is null, then that is what your data in the table must be. How could it be otherwise? – Booboo Mar 01 '23 at 15:54
  • If you are getting HTTP error 500s you should be checking your [PHP Error Logs](https://stackoverflow.com/questions/5127838/where-does-php-store-the-error-log-php-5-apache-fastcgi-and-cpanel) to see what the problem is – Martin Mar 01 '23 at 17:15
  • 1
    @Martin I found error was caused by a special character "ò" in one of the name, so i removed it, and now json encoding works. But ajax call not trigger the success function.. – grandeacci Mar 02 '23 at 00:13

0 Answers0