Warning: Invalid argument supplied for foreach() when retrieving data from database

Question

I am attempting to retrieve data from two tables and echo the results out, the sql appears to be correct but it tells my the argument is invalid. heres my code:

// Retrieve all information related to this post
    function get_post_data($post_id){

        //test the connection
        try{
            //connect to the database
            $dbh = new PDO("mysql:host=localhost;dbname=mjbox","root", "usbw");
        //if there is an error catch it here
        } catch( PDOException $e ) {
            //display the error
            echo $e->getMessage();
        }

        $sql = 'SELECT * FROM mjbox_images JOIN mjbox_posts USING (post_id) WHERE post_id = $post_id';
        $result = $dbh->query( $sql );

        foreach($result as $row):

            echo $row['img_id'];

        endforeach;

    }

Answer 1

The $post_id in your query won't be being expanded because the string is single quoted.

It should work better with:

$sql = "SELECT * FROM mjbox_images JOIN mjbox_posts USING (post_id) WHERE post_id = $post_id";

or:

$sql = 'SELECT * FROM mjbox_images JOIN mjbox_posts USING (post_id) WHERE post_id = '.$post_id;

Answer 2

You need to tell PDO to throw errors:

$dbh = new PDO("mysql:host=localhost;dbname=mjbox","root", "usbw");
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

That will probably tell you what is happening (except for a potential problem when creating the pdo object the line before it...).

I would also recommend switching to prepared statements to avoid potential sql injection or malformed query problems.

And I hope that is not your real password...