Someone please can see why the following prepared statment returns nothing?

Question

$stmt = mysqli_prepare($link,"
    SELECT *
      FROM ads
     INNER JOIN dept ON dept.id_dept = ads.in_dpt
     INNER JOIN members ON members.idMem = ads.from_Mem
     INNER JOIN sub_cat_ad ON id_sub_cat = ads.ads_in_Cat
     INNER JOIN cat_ad ON idCat_ad = sub_cat_ad.from_cat_ad
     WHERE ads_in_Cat = ? ");

if(isset($_GET['fromSCat'])){
    $fromSCat = mysqli_real_escape_string($link,$_GET['fromSCat']);
}

mysqli_stmt_bind_param($stmt,'i',$fromSCat);
mysqli_stmt_execute($stmt);
mysqli_stmt_fetch($stmt);

$tot=mysqli_stmt_num_rows($stmt); //Ouput: 0

Without the prepared statement, it's ok

Answer 1

You've got the mysql_real_escape_string() parameters backwards. it's ($string_to_escape, $optional_database_handle). So you're trying to query against something like WHERE ads_in_Cat='Resource #1' instead of your $_GET parameter.

Answer 2

Probably not the reason it's returning 0, but you're double-escaping $fromSCat - you don't need escape_string in prepared statements, bind_param does that already. Try commenting out the escape_string & see what happens.

Also, you might want to scoot the bind/exec/fetch calls inside your if statement, unless $fromSCat is being defined earlier in the script.

---

Followup re comment: that error means your database is sketchy. Do you have primary keys & indexes on the relevant tables? mysqli is a little odd for throwing an exception over it, it's not any of it's business. If you don't have access to fix up the DB tables, add mysqli_report(MYSQLI_REPORT_OFF); near the top of the script.

(also, if you can: use PDO instead. It's better & cleaner in both syntax and documentation.)