CODEWITHSUNDEEP
phpsqlpdo
user3493797
user3493797

Reputation: 63

PHP PDO SQL query

I have two tables, and when I use a SQL Statement like this, it works on phpmyadmin:

SELECT DISTINCT B.ID,
    CASE B.Menu 
        WHEN 'Menu1' THEN S.Menu1 
        WHEN 'Menu2' THEN S.Menu2 
        WHEN 'Menu3' THEN S.Menu3 
        WHEN 'Menu4' THEN S.Menu4 
        WHEN 'Menu5' THEN S.Menu5 
        ELSE 'Unknown' END AS Menu, S.Day 
    FROM order B  JOIN menu_plan S ON B.ID_Date = S.ID
    WHERE B.ID_order = '4000859'

But when I use this Statement in my PHP PDO query, it give nothing back :/

<?php

require_once "config.php";

error_reporting(E_ALL);

try
{
    $con = new PDO("mysql:host=".$db_host.";dbname=".$db_name,$db_user,$db_password);
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (PDOException $e)
{
    echo "Error: ".$e->getMessage();
    exit();
}   

$_sql = sprintf("SELECT DISTINCT B.ID,
    CASE B.Menu 
        WHEN 'Menu1' THEN S.Menu1 
        WHEN 'Menu2' THEN S.Menu2 
        WHEN 'Menu3' THEN S.Menu3 
        WHEN 'Menu4' THEN S.Menu4 
        WHEN 'Menu5' THEN S.Menu5 
        ELSE 'Unknown' END AS Menu, S.Day 
    FROM order B  JOIN menu_plan S ON B.ID_Date = S.ID
    WHERE B.ID_order = '4000859'"); // Here the sql statement
$stmt = $con->query($_sql);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
var_dump($rows);
echo json_encode(array('Order'=>$rows));

?>

EDIT: With this code now it shows two arrays with the variables like in phpmyadmin. But the json_encode gives nothing back. The php file only show this:

array(2) { [0]=> array(3) { ["ID"]=> string(1) "1" ["Menu"]=> string(5) "Meal1" ["Day"]=> string(8) "Thursday" } [1]=> array(3) { ["ID"]=> string(1) "4" ["Menu"]=> string(1) "-" ["Day"]=> string(7) "Sunday" } }

The config.php and the variables for the data base and the table are correct. When I use the PHP file without the case-statement in the sql query then it shows something. In phpmyadmin it shows all with the case-statement.

EDIT ANSWER: The Problem was the utf8-encoding. I have to use this:

$con = new PDO("mysql:host=".$db_host.";dbname=".$db_name,$db_user,$db_password,array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

Upvotes: 1

Views: 251

Answers (2)

meda
meda

Reputation: 45490

Use this code:

$stmt = $con->prepare($_sql);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
var_dump($rows);
echo json_encode(array('Order'=>$rows));

and if the value is hardcoded in the query you don't have to prepare

$stmt = $con->query($_sql);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
var_dump($rows);
echo json_encode(array('Order'=>$rows));

Upvotes: 1

ckpepper02
ckpepper02

Reputation: 3437

Try changing this:

$sql = sprintf("..."); // Here the sql statement
$sql = $con->prepare($_sql);

To this:

$_sql = sprintf("..."); // Here the sql statement
$sql = $con->prepare($_sql);

Looks like you just forgot the underscore in the first sql definition.

Upvotes: 1

Related Questions