-1

I am using smarty (php template engine) in my project.

however, I keep pump into so many problems and smarty's documentation or their support is not nill.

any way, I am trying to use mysqli prepared statement in my php code and it just doesn't return any value but if I use normal mysqli functions it work just fine.

I don't think the issue is from my prepared statement as I am using it in other parts of my project and it works fine. so I can only think that the issue is from smarty.

This works:

$sql = "SELECT DISTINCT category FROM $storeShop";
$query = mysqli_query($db_conx, $sql);
$productCount = mysqli_num_rows($query); // count the output amount
    while($line = mysqli_fetch_array($query, MYSQLI_ASSOC)){
        $cvalue[] = $line;
    }

// Assign this array to smarty...

$smarty->assign('category', $cvalue);



// Assign this array to smarty...
$smarty->assign('$category', $cvalue);

This is my prepared statement which doesn't work:

$query = "SELECT DISTINCT category FROM $storeShop";

if ($stmt = mysqli_prepare($db_conx, $query)) {

    /* execute statement */
    mysqli_stmt_execute($stmt);

    /* bind result variables */
    mysqli_stmt_bind_result($stmt, $category);

    /* fetch values */
    while ($line = (mysqli_stmt_fetch($stmt))) {
        $cvalue[] = $line;
    }

    /* close statement */
    mysqli_stmt_close($stmt);
}

/* close connection */
mysqli_close($db_conx);

// Assign this array to smarty...

$smarty->assign('category', $cvalue);



// Assign this array to smarty...
$smarty->assign('$category', $cvalue);

and this is what i have in my template page:

{section name=category loop=$category}
        <li class="odd"><a href="#">{$category[category].category}</a></li>
        {/section}

is this issue common with smarty and what is the solution to fix this issue?

Thanks

EDIT:

we are getting close now. I use the following code, however, it will return the first letter of each category which is very strange. example: if the categories are book and car, it will return b and c

$query = "SELECT DISTINCT category FROM $storeShop";

if ($stmt = mysqli_prepare($db_conx, $query)) {

    /* bind result variables */
    mysqli_stmt_bind_result($stmt, $category);

        /* execute statement */
    mysqli_stmt_execute($stmt);

    /* fetch values */
    while (mysqli_stmt_fetch($stmt)) {
        $cvalue[] = $category;
    }

    /* close statement */
    mysqli_stmt_close($stmt);
}

/* close connection */
mysqli_close($db_conx);

// Assign this array to smarty...

$smarty->assign('category', $cvalue);



// Assign this array to smarty...
$smarty->assign('$category', $cvalue);

I think $line is a smarty function that will return the whole value of the mysql table. smarty is a whole different language...

SECOND EDIT:

I used the var_dump and it returns the correct categories as it should but in my page, I only get the first letter of the each category.

var_dump($cvalue); will return array(1) { [0]=> string(3) "GFD" } array(2) { [0]=> string(3) "book" [1]=> string(11) "car" } "

var_dump($category); will return string(3) "book" string(11) "car"

user3585872
  • 11
  • 5
  • `EXECUTE` after the binding! – Adrian Preuss Apr 30 '14 at 15:29
  • 1
    @AdrianPreuss, No difference mate. – user3585872 Apr 30 '14 at 15:32
  • What is `$storeShop` in your query`? – Adrian Preuss Apr 30 '14 at 15:34
  • @AdrianPreuss, Its the name of the mysql table. it works fine in my first code. so the issue I don't think is from that. – user3585872 Apr 30 '14 at 15:36
  • Check `$stmt`, it seems that the var `$category` will be not set if `if ($stmt = mysqli_prepare($db_conx, $query)) {` are not passed. I think youve got here an SQL Exception/Error – Adrian Preuss Apr 30 '14 at 15:37
  • Repetition of http://stackoverflow.com/questions/23387692/select-distinct-in-mysqli-prepared-statement – Your Common Sense Apr 30 '14 at 15:37
  • the remedy is the same: **try to copy/paste the code from manual without mistakes**. Believe me, it is not that hard a task. Just try. – Your Common Sense Apr 30 '14 at 15:38
  • who is that follows me just to downvote my questions?! I can't post a question anymore because of him/her/them lol – user3585872 Apr 30 '14 at 15:40
  • @YourCommonSense, your suggestion is not clear mate. so it is a bit hard a task to do as I don't know what you mean :D – user3585872 Apr 30 '14 at 15:41
  • A WHOLE DAY and still I can't make this work! lol this is the exact reason that mysqli prepared statement is hard for anyone to hack into as even the php community doesn't know what they've done. let alone the hackers. :) – user3585872 Apr 30 '14 at 15:54
  • @user3585872 - Have you checked to see which part of the code is failing? You're running `prepare` in an `if` statement - does that check work? There's no `else`, so if prepare fails, you'll also end up with an empty list. If the prepare succeeds, then add checks on the calls to mysqli_stmt_execute and mysqli_stmt_bind_result to see what they're returning - they'll both return false if they fail. – andrewsi Apr 30 '14 at 15:58
  • @andrewsi, I did try the suggestion in the answer bellow and it did return the error/warning that i mentioned bellow. how can I add more checks to each part of the code to pin point the issue? – user3585872 Apr 30 '14 at 16:02
  • @user3585872 - it looks like you're not using `mysqli_stmt_fetch` properly. You're assigning its return value to `$line`, and then doing processing that. Instead, I think you just need to have `while (mysqli_stmt_fetch($stmt))` as the loop, and you can assign `$cvalue[] = $category` inside. – andrewsi Apr 30 '14 at 16:06
  • @andrewsi, also, you are absolutely spot on. I get a `List` but its `empty`. so it seems like it gets the `categories` and it puts them in an `empty list on my page`. what is causing that? – user3585872 Apr 30 '14 at 16:07
  • @andrewsi, i did what you said and it sort of worked but please view my Edit in my question. – user3585872 Apr 30 '14 at 16:15
  • @user3585872 - OK; now, try using `var_dump` - use it inside the loop to see what's being returned from the database into `$category`; and use it after the loop to check the contents of `$cvalue`. – andrewsi Apr 30 '14 at 16:27
  • @andrewsi, please view my second edit. – user3585872 Apr 30 '14 at 16:36
  • @user3585872 - it looks like it's getting the data OK, in that case. I've no experience with Smarty, but I suspect the problem is there. The only thing I can see is that you've got two lines at the end assigning the value in Smarty. Do you need them both? I don't think the second one is right: `$smarty->assign('$category', $cvalue);` – andrewsi Apr 30 '14 at 16:39

1 Answers1

0
/*
    execute & bind will be only work if $stmt is OKAY!
    Here you get an SQL error
*/
if ($stmt = mysqli_prepare($db_conx, $query)) {
    mysqli_stmt_bind_result($stmt, $category);
    /* ... */
} else {
    print "ERROR!!!!";
    var_dump($stmt);
}

I hope it helps. Its only an logical problem. Bind will be not call, but $stmt is not "true" or gives an "false" back.

Adrian Preuss
  • 3,228
  • 1
  • 23
  • 43