Mysqli select statement wrong results

PHP
1

I can’t figure out how to fix the following piece of code. It returns no results while there are a couple. Any help is much appreciated.
I’m migrating an old code into prepared statements to sort out sql injections.


    if($_GET[action]=='view-rss')
    {
        $count =0;
        //$query=mysqli_query($GLOBALS["___mysqli_ston"], "select * from rss where lang='$_SESSION[session_lang]'");
        $stmt = $GLOBALS["___mysqli_ston"]->prepare("select * from rss where lang=(?)");
        $stmt->execute();
        $stmt->bind_param('s', $_SESSION[session_lang]);
        $stmt->bind_result($rss_ids, $rss_titles, $rss_urls, $rss_publishes, $_SESSION[session_lang]);

        while($stmt->fetch())
        {
            $rss_ids[] = $row['id'];
            $rss_urls[] = $row['url'];
            $rss_titles[] = $row['title'];
            $rss_publishes[] = $row['published'];
        }
        $smarty->assign("rss_ids", $rss_ids);
        $smarty->assign("rss_urls", $rss_urls);
        $smarty->assign("rss_titles", $rss_titles);
        $smarty->assign("rss_publishes", $rss_publishes);

        $stmt->close();
    }
2

Hi asmar,

You just need to call $stmt->bind_param before you call $stmt->execute.

3

Thanks Fretburner. I fixed that and works ok apart from the lang session. It just can’t hold the session lang. Any ideas?


    if($_GET[action]=='view-rss')
    {
        $count =0;
        //$query=mysqli_query($GLOBALS["___mysqli_ston"], "select * from rss where lang='$_SESSION[session_lang]'");
        $stmt = $GLOBALS["___mysqli_ston"]->prepare("select id, title, url, published from rss where lang=(?)");
        $stmt->bind_param('s', $_SESSION[session_lang]);
        $stmt->execute();
        $stmt->bind_result($id, $title, $url, $published );

        $result = $stmt->get_result();

        while($row = $result->fetch_array())
        {
            $rss_ids[] = $row['id'];
            $rss_urls[] = $row['url'];
            $rss_titles[] = $row['title'];
            $rss_publishes[] = $row['published'];
            $count= $count +1;
        }
        $smarty->assign("rss_ids", $rss_ids);
        $smarty->assign("rss_urls", $rss_urls);
        $smarty->assign("rss_titles", $rss_titles);
        $smarty->assign("rss_publishes", $rss_publishes);
        $smarty->assign("count", $count);

        $stmt->close();
    }
4

Are you calling session_start() at the top of your script?

5

Not direct on this file but on a different one which is included in top of the page.

Thanks a lot

6

I’ve also just noticed that you’re missing quotes around the $_SESSION array key:

$stmt->bind_param('s', $_SESSION[session_lang]);

You’ve probably got notices turned off in your PHP error handling, as this kind of thing normally displays a ‘Use of undefined constant’ error.

7

Thanks for this, I’ve added the quotes but can’t figure out the issue with the session lang.
Any help is much appreciated.

8

What kind of value is $_SESSION[session_lang? Should it be a string or a number? Also, have you tried echoing the value to the screen to check that it’s actually set?

9

It is a string, en for english de for German etc.

10

I’ve changed a little bit the code and seems to get the session but don’t display the values of the db. Any idea what I’m doing wrong?

   
 if($_GET[action]=='view-rss')
    {
        $count =0;
        $stmt = $GLOBALS["___mysqli_ston"]->prepare("select * from rss where lang=?") or die('Problem preparing query');
        $stmt->bind_param('s', $_SESSION[session_lang]);
        $stmt->store_result();
   		

        while(mysqli_stmt_fetch($stmt))
        
        {
            $rss_ids[] = $row['id'];
            $rss_urls[] = $row['url'];
            $rss_titles[] = $row['title'];
            $rss_publishes[] = $row['published'];
            $count= $count +1;
        }

        $smarty->assign("rss_ids", $rss_ids);
        $smarty->assign("rss_urls", $rss_urls);
        $smarty->assign("rss_titles", $rss_titles);
        $smarty->assign("rss_publishes", $rss_publishes);
        $smarty->assign("count", $count);
        
        $stmt->execute();
		$stmt->close(); 
    }
11

Hi asmar,

You need to call $stmt->execute before you call $stmt->store_result.

12

Hi Fretburner,

Even with your suggestion it still not return any value:


    if($_GET[action]=='view-rss')
    {
        $count =0;
        $stmt = $GLOBALS["___mysqli_ston"]->prepare("select * from rss where lang=?");
        $stmt->bind_param('s', $_SESSION['session_lang']);


        while(mysqli_stmt_fetch($stmt))
        {
            $rss_ids[] = $row['id'];
            $rss_urls[] = $row['url'];
            $rss_titles[] = $row['title'];
            $rss_publishes[] = $row['published'];
            $count= $count +1;
        }
        $smarty->assign("rss_ids", $rss_ids);
        $smarty->assign("rss_urls", $rss_urls);
        $smarty->assign("rss_titles", $rss_titles);
        $smarty->assign("rss_publishes", $rss_publishes);
        $smarty->assign("count", $count);
        $stmt->execute();
        $stmt->store_result();
        $stmt->close();
		
    }
13

I meant you should move execute up, before store_result, not move it down.

Here, try this:


$rss_ids = array();
$rss_urls = array();
$rss_titles = array();
$rss_publishes = array();

$stmt = $GLOBALS["___mysqli_ston"]->prepare("select * from rss where lang=?");
$stmt->bind_param('s', $_SESSION['session_lang']);
$stmt->execute();
$stmt->store_result();

$stmt->bind_result($id, $url, $title, $published);

while($stmt->fetch()))
{
    $rss_ids[] = $id;
    $rss_urls[] = $url;
    $rss_titles[] = $title;
    $rss_publishes[] = $published;
}
$stmt->close();

$smarty->assign("rss_ids", $rss_ids);
$smarty->assign("rss_urls", $rss_urls);
$smarty->assign("rss_titles", $rss_titles);
$smarty->assign("rss_publishes", $rss_publishes);
$smarty->assign("count", count($rss_ids));
14