mySQL with alias field names failing in curl_exec PHP call

Question

I have a simple method that calls a PHP script for data using curl_exec.

function load_entry_list($url) 
{       
    // Initialize an empty array to store entries 
    $entries = array(); 

    // Load the data from the API 
    $ch = curl_init(); 
    curl_setopt($ch, CURLOPT_URL, $url); 
    curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 5); 
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE); 
    $ch_data = curl_exec($ch); 
    curl_close($ch);        

    ***point of failure*** 
    if(!empty($ch_data)) // 
    { 
       // Convert the JSON into an array of entry objects
       $json_data = json_decode($ch_data, TRUE);
       foreach($json_data as $entry)
       {
          $entries[] = (object) $entry;
       }
       return $entries; 
    } 
    else 
    { 
       die('Something went wrong with the API request!'); 
    } 
}

The PHP script looks like this

// Create connection
$conn = new mysqli(DBHOST, DBUSER, DBPASS, DBNAME);

// Check connection
if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT rec.id, rec.name, rec.submitted_by, rec.addDate, rec.hits, rec.metaDesc, rec.metaKeys, 
              IFNULL(pic.picPath,'default.png') as picPath, 
              var.catname as variety, cat.catname as category
        FROM wms_recipes rec LEFT OUTER JOIN 
             wms_categories var on rec.cat = var.id LEFT OUTER JOIN
             wms_categories cat on var.childOf = cat.id LEFT OUTER JOIN
             wms_pictures pic on rec.id = pic.recipe
       WHERE enRecipe  = 'yes'
             AND isApproved  = 'no'
       ORDER BY 1";

$result = $conn->query($sql);   

$rows = array();
if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {              
        $rows[] = $row;
    }
} 
$conn->close();

$articles = array();
foreach($rows as $item) {           
    $myArticle = new article();
    $myArticle->id = $item['id'];       
    $myArticle->name = $item['name'];
    $myArticle->category = $item['category'];  *** it will also work if I comment out this line
    $myArticle->variety = $item['variety'];
    $myArticle->submitted_by = $item['submitted_by'];
    $myArticle->addDate = $item['addDate'];
    $myArticle->hits = $item['hits'];
    $myArticle->description = $item['metaDesc'];
    $myArticle->keys = $item['metaKeys'];
    $myArticle->picPath = IMAGES_URL.'recipe/'.$item['picPath'];
    $articles[] = $myArticle;
}

header('Content-Type: application/json');   
echo json_encode( $articles );
exit;

UPDATE: Here is the issue,

if I keep cat.catname as category in the SQL Query

then in my calling method $ch_data from $ch_data = curl_exec($ch); will be empty

It turns out that one Category is causing the issue. Here is an example of the culprit:

[11] => article Object
    (
        [id] => 12
        [name] => White Zinfandel from Fresh Juice
        [category] => Rosé Wines
        [variety] => Zinfandel
        [submitted_by] => Jim Stevens
        [addDate] => 2016-04-27
        [hits] => 558
        [description] => A good gift wine that all will like
        [metaKeys] => 
        [picPath] => http://localhost/resources/images/recipe/default.png
        [keys] => White Zinfandel
    )

Note the category name Rosé Wines. The acute é is causing the issue. If I spell it rose, the issue goes away. SQL returns it just fine. But curl_exe does not like it. So now my questions are why and how do I fix it.

Sounds like a syntax error when you make the change to the SQL. Show the alternative SQL statement (edit your question, and show it below the existing code). — Sloan Thrasher, Jun 29 '17 at 03:46
It is not a SQL syntax error. Running both SQL in Workbench I get no erros. Alternatively I can remove the assignment $myArticle->category = $item['category']; from the returning array and it is also fine. — JStevens, Jun 29 '17 at 03:52
What if you keep the other one..that is, remove `var.catname as variety` and keep `cat.catname as category`? — Aakash Verma, Jun 29 '17 at 04:04
Try running the php script from a browser with error reporting turned on and using the script that "breaks" the curl call. You might also ```echo print_r($rows,true)``` before the loop and check that the columns exist that you use later in the loop. — Sloan Thrasher, Jun 29 '17 at 04:14
Calling the php script with browser returns exactly as it should no errors. — JStevens, Jun 29 '17 at 04:16
Yes with the bad SQL. Nothing fails in the PHP data script on its own. It only fail when called by curl_exe. — JStevens, Jun 29 '17 at 11:04

score 0 · Accepted Answer · edited Oct 25 '17 at 05:02

0

The easiest way to fix this would be to force the mysql connection to utf-8 like this:

$conn = new mysqli(DBHOST, DBUSER, DBPASS, DBNAME_WMS); $conn->set_charset("utf8");

edited Oct 25 '17 at 05:02

JStevens

2,090
1
22
26

answered Aug 07 '17 at 18:01

user1483770

16

mySQL with alias field names failing in curl_exec PHP call

1 Answers1