1

I have this code:

## Total number of records without filtering
$query = "SELECT COUNT(*) AS allcount FROM fundme_findmeapi.businesses 
INNER JOIN farmers_findme.farm_animals ON fundme_findmeapi.businesses.business_findme_id = 
farmers_findme.farm_animals.farmer_findme_id 
INNER JOIN farmers_findme.farm_crops ON fundme_findmeapi.businesses.business_findme_id = 
farmers_findme.farm_crops.farmer_findme_id 
WHERE 1";
$stmt = $conn4->prepare($query);
$stmt->execute();
$records = $stmt->fetch();
$totalRecords = $records['allcount'];

## Total number of records with filtering
$query2 = "SELECT COUNT(*) AS allcount FROM fundme_findmeapi.businesses 
INNER JOIN farmers_findme.farm_animals ON fundme_findmeapi.businesses.business_findme_id = 
farmers_findme.farm_animals.farmer_findme_id 
INNER JOIN farmers_findme.farm_crops ON fundme_findmeapi.businesses.business_findme_id = 
farmers_findme.farm_crops.farmer_findme_id 
WHERE 1 ".$searchQuery;
$stmt = $conn4->prepare($query2);
$stmt->execute($searchArray);
$records = $stmt->fetch();
$totalRecordwithFilter = $records['allcount'];

## Fetch records
$query3 = "SELECT * FROM fundme_findmeapi.businesses 
INNER JOIN farmers_findme.farm_animals ON fundme_findmeapi.businesses.business_findme_id = 
farmers_findme.farm_animals.farmer_findme_id 
INNER JOIN farmers_findme.farm_crops ON fundme_findmeapi.businesses.business_findme_id = 
farmers_findme.farm_crops.farmer_findme_id 
WHERE 1 ".$searchQuery;
$stmt = $conn4->prepare("$query3 ORDER BY ".$columnName." ".$columnSortOrder." LIMIT 
:limit,:offset");

// Bind values
foreach($searchArray as $key=>$search){
    $stmt->bindValue(':'.$key, $search,PDO::PARAM_STR);
}

$stmt->bindValue(':limit', (int)$row, PDO::PARAM_INT);
$stmt->bindValue(':offset', (int)$rowperpage, PDO::PARAM_INT);
$stmt->execute();
$records = $stmt->fetchAll();`

Which works fine on wampserver but not onlile. WHen I romove the joins the query works fine. The users connecting to fundme_findmeapi are fundme_farmers2 and fundme_findme. They both have all priviledges enabled so cant figure out why the query is not working.

This is the database connection details

$dsn4 = "mysql:host=localhost; dbname=some_db"; $username = "some_user"; 
$password = "some_pass";

try {
    $conn4 = new PDO($dsn4, "some_user", "some_pass", 
    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET sql_mode=''"));
}
catch(PDOException $e) {
    echo "Main connection 4 failed. ".$e->getMessage();
}

Any help is appreciated. Thanks

Dcoder1
  • 11
  • 2
  • I think you'll want some [error catching in this code](https://stackoverflow.com/questions/2552545/mysqli-prepared-statements-error-reporting) specifically when you run `execute()` Likely mysql is bubbling up some error that is being missed that will clue you in on why it's failing when the joins are present. – JNevill Sep 21 '22 at 21:10
  • @JNevill good point but since this code is using PDO, the error handling guidance needs to be different than the link you provided :-). https://phpdelusions.net/pdo#errors has good guidance. – ADyson Sep 21 '22 at 22:17
  • @Dcoder1 `WHERE 1 ".$searchQuery;` looks worryingly vulnerable to SQL injection attacks. There's not a lot of point using prepared statements if you aren't also going to _parameterise_ your queries. The fact you mention that this is running on a live server online is not good. I think you'd better read https://phpdelusions.net/pdo#prepared as well. You've parameterised the limit and offset, but not the searchQuery bit, so it's only half a job. Also `ORDER BY ".$columnName." ".$columnSortOrder."` can't be parameterised, so I hope you're whitelisting those vars before allowing it in there. – ADyson Sep 21 '22 at 22:19
  • 1
    @JNevill only it shouldn't be called "catching" because the term has a special meaning in programming, which is not applicable here. What is needed here is error must be thrown, not caught. – Your Common Sense Sep 22 '22 at 04:22

0 Answers0