CODEWITHSUNDEEP
phpmysql
Claire
Claire

Reputation: 53

Field with apostrophe not being returned on MYSQL query

I have a MSQL Table, where I store the results from a form. One of the columns stores the results from a multiple select option, using serialize before entering the data.

When I retrieve the data, in a while loop, it works perfectly except for 2 entries, one uses an apostrophe ' (the entry is Contour d'Or), the other quotes " (the entry is Hair Extensions 18") and I can't seem to get it to recognise these 2 and include them in the query.

I've tried mysqli_real_escape_string, but that doesn't work either. I've just tried something that was suggested on another answer I read which referred to this page: http://ben-major.co.uk/2012/12/mysql-searches-and-apostrophes/

Any help would be gratefully received :)

$q = "SELECT * 
      FROM Departments 
      WHERE `Department` = '$depart' AND `cookie` ='$value'";
$r = mysqli_query($connection, $q) or die("Error description: " . mysqli_error($connection));
$row = mysqli_fetch_assoc($r);

$Treatment = $row['Treatment'];
$Treatments = unserialize($Treatment);

foreach($Treatments as $key => $TreatmentN){
    echo $TreatmentN . " ";


    //INCOME 2014 & 2015
    $q = "SELECT (SUM(TurnOver)- SUM(Discount)) AS total 
          FROM Appointments 
          WHERE `Date` BETWEEN '2014-04-01' AND '2015-03-31' 
            AND `Treatment`LIKE '%$TreatmentN%' 
            OR REPLACE(`Treatment` , '\'', '' ) LIKE '%$TreatmentN%' 
            AND `cookie` ='$value'";
    $sum_query_resa  = mysqli_query($connection, $q);
    $rowa = mysqli_fetch_assoc($sum_query_resa);
    $income += $rowa['total'];
    echo $TreatmentN;
}

echo "<p>Total Income £" .sprintf('%0.2f', $income) ." ";

Upvotes: 2

Views: 158

Answers (1)

Claire
Claire

Reputation: 53

Just in case anyone searching for this answer comes across it, I restructured the database to search on ID's rather than names.

Should have done this right from the start, but I'm learning as I go :)

Upvotes: 1

Related Questions