0

I know there are a few bits of infomation about prepared statement, but none specific to my needs. I quite new to PHP and MySQLI so when I started to build my site I was not really aware of the prepared statement. So I'm updating these as I go into each page for updates.

I have the below statement snippet of code originally, and below this is the code I changed to but it's not returning any results.

    $sqlCommand = "SELECT users.id, scout_profile.s_name_first,scout_profile.s_name_mid, scout_profile.s_name_last, scout_profile.nationality, scout_profile.s_country, scout_profile.s_club_main, scout_profile.s_type, scout_profile.status, scout_profile.gender, users.signup, users.avatar, users.username  FROM scout_profile INNER JOIN users ON scout_profile.scout_id=users.id";
include_once("../php_includes/db_conx.php");


   $query = mysqli_query($db_conx,$sqlCommand) or die(mysqli_error($sqlCommand));   
    $count = mysqli_num_rows($query);
if($count >= 1){
    $i = 0; // for ads
    $adCode=""; // for ads
   while($row = mysqli_fetch_array($query,MYSQLI_NUM)){
            // Insert Adds and selected interval - Begin (also look for (for ads))
     $i++;
     if($i==2 || $i==4 ||$i ==6){
         $adCode = '<div class="card-body bg-warning m-2 p-1 add_text" style="height: 100px;">This is Ad Space from Ads server Im on ('.$i.')</div>';
     }else{
         $adCode="";
     }
      $sid = $row[0];
      $s_name_first = $row[1];
      $s_name_mid = $row[2];
      $s_name_last = $row[3];

-- Updated (but not working)

//$fn_searchquery = $_POST['fn_searchquery'] ?? '';
    $sqlCommand = "SELECT users.id, scout_profile.s_name_first,scout_profile.s_name_mid, scout_profile.s_name_last, scout_profile.nationality, scout_profile.s_country, scout_profile.s_club_main, scout_profile.s_type, scout_profile.status, scout_profile.gender, users.signup, users.avatar, users.username  FROM scout_profile INNER JOIN users ON scout_profile.scout_id=users.id";
include_once("../php_includes/db_conx.php");


$statement = $db_conx->prepare($sqlCommand);
$statement->execute();
//$result = $statement->get_result()->fetch_all(MYSQLI_ASSOC);
$result = $statement->get_result();
$statement -> store_result();
$count = $statement->num_rows;    

if($count >= 1){
    $i = 0; // for ads
    $adCode=""; // for ads

   while($row = $result->fetch_row()){
            // Insert Adds and selected interval - Begin (also look for (for ads))
     $i++;
     if($i==2 || $i==4 ||$i ==6){
         $adCode = '<div class="card-body bg-warning m-2 p-1 add_text" style="height: 100px;">This is Ad Space from Ads server Im on ('.$i.')</div>';
     }else{
         $adCode="";
     }
      $sid = $row[0];
      $s_name_first = $row[1];
      $s_name_mid = $row[2];
      $s_name_last = $row[3];

This code is return no data, if I have to I can change to an associated row but would link to keep the way it as it is less code to the change. I think it is something to do with the fetch function but I'm not sure the best way to debug it.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Ashley
  • 83
  • 5
  • For one, your first code with `die(mysqli_error($sqlCommand))` is incorrect. You need to use the db connection's variable and not the query declaration statement. – Funk Forty Niner Oct 03 '19 at 15:26
  • 2
    I don't see where you're echoing anything. Plus, what you have now, doesn't constitute as a "prepared statement". – Funk Forty Niner Oct 03 '19 at 15:27
  • 1
    https://stackoverflow.com/questions/35116807/what-is-the-difference-between-get-result-and-store-result-in-php might be worth a read as you use both. – Nigel Ren Oct 03 '19 at 15:29
  • Remove `store_result()`. You don't need it. – Dharman Oct 03 '19 at 15:30
  • @Dharman If they're wanting to check if a row exists, they would need to use `store_result()`, which would be needed in an actual prepared statement. Per [this Q&A](https://stackoverflow.com/questions/22252904/check-if-row-exists-with-mysql). – Funk Forty Niner Oct 03 '19 at 15:32
  • There is some redundant code in there, and a lack of proper formatting. I recommend you to use a good idea, which will help you format the code. – Dharman Oct 03 '19 at 15:33
  • 1
    You don't need a prepared statement for this query. – Your Common Sense Oct 03 '19 at 15:36
  • @funk I don't follow. Wouldn't both get_result and store_result throw an error? What is so good about store_result, that you would want to use it? – Dharman Oct 03 '19 at 15:36
  • @Dharman Probably. What I mentioned about checking if a row exists, doesn't include `get_result` though. – Funk Forty Niner Oct 03 '19 at 15:38
  • In this case they want to fetch the data, not just check if it exists. For this get_result is better. Even better would be to use PDO instead of MySQLi. – Dharman Oct 03 '19 at 15:39
  • 2
    @Ashley, just follow this tutorial of mine: [How to run a SELECT query with mysqli](https://phpdelusions.net/mysqli_examples/select). It will answer all your questions. – Your Common Sense Oct 03 '19 at 15:42
  • If you don't have errors enabled, see this post. https://stackoverflow.com/a/22662582/1839439 – Dharman Oct 03 '19 at 15:43
  • #Funk Forty Niner - the die code is really irreverent as i going to be removing this code. I'm not showing my echo as there is more to the code but did not feel it was relevant as a snippet of my code issue. I have used get_result and store_result to return num row in other pages and it has worked fine. thanks for you comment i will take them on board. – Ashley Oct 04 '19 at 21:38
  • @Dharman Thanks im going to have a read of your tutorial – Ashley Oct 04 '19 at 21:41

0 Answers0