PHP PDO SQL query

Question

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"));

looks like you overwrote the variable $sql with the connection variable. You also have a different variable name in the prepare statment $_sql — ckpepper02
– ckpepper02, Commented Sep 10, 2014 at 18:46

meda · Accepted Answer · 2014-09-10 19:10:17Z

1

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));

answered Sep 10, 2014 at 19:10

meda

45.5k14 gold badges96 silver badges123 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user3493797 Over a year ago

it shows two arrays which are right!! :) But the json_encode give a null back :(

meda Over a year ago

@user3493797 sorry typo check again, use $rows when you encode

user3493797 Over a year ago

Thanks, but there are these 2 arrays from var_dump. But Json_Encode shows nothing.

meda Over a year ago

can you update your question with the dump, I dont see why it fails unless you have bad encoding characters

meda Over a year ago

@user3493797 json_encode will only fail when the strings are not encoded properly

|

ckpepper02 · Accepted Answer · 2014-09-10 18:47:04Z

1

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.

answered Sep 10, 2014 at 18:47

ckpepper02

3,5075 gold badges32 silver badges43 bronze badges

2 Comments

user3493797 Over a year ago

Thanks for answering, but thats not the mistake. I correct my question.

meda Over a year ago

are you passing any parameter to this query

Collectives™ on Stack Overflow

PHP PDO SQL query

2 Answers 2

7 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related