MySQL returns only the first character of the fields, but works fine on local

Question

I don't know what is going on exactly, but all only the first character is returned for all my columns when I uploaded my website. It works perfectly fine on a local machine.

I found a similar question here, but I didn't manage to find the answer:
https://stackoverflow.com/questions/10507848/mysql-query-returns-only-the-first-letter-of-strings-only-when-page-is-viewed-on

    // Log Table Query
    unset($stmt);
    $stmt = $db->stmt_init();
    $stmt = $db->prepare( "SELECT * FROM logs ORDER BY `id` DESC" );

    $stmt->store_result();
    $stmt->bind_result($r_id, $r_time, $r_logger, $r_message, $r_category);
    $stmt->execute();

    while( $stmt->fetch() )
    {
        var_dump($r_message);
        var_dump($r_category);
    }

    $stmt->close();

This outputs on localhost for example:

string(5) "Hello"String(3) "Cow"

But on the live server:

string(1) "H"String(1) "C"

Any ideas?

Edit

I think that this applies only to string types. The integer types return for example:

int(2893)

Is your schema the same on both systems? What does running this query directly produce? — tadman, Mar 08 '16 at 18:39
@tadman Yes, it is. I am actually connecting to the same externally hosted database. When running the query directly in MySQL Workbench, it produces the expected results. — Z0q, Mar 09 '16 at 09:53
I guess you are using `mysqli`, maybe you consider the version of your php from your localhost and to your webserver, and also your datatypes. — Ulysses, Mar 11 '16 at 03:27
I have the similar problem before. Then I found out that in my localhost my attribute in sql was defined as varchar(100), but the same attribute in live server was defined as varchar(1) mistakenly. Can you please check the type of the attribute on the database in live server? — Eray Balkanli, Mar 11 '16 at 03:34
have you tried to not call `unset($stmt);` as suggested in your found question comment:http://stackoverflow.com/questions/10507848/mysql-query-returns-only-the-first-letter-of-strings-only-when-page-is-viewed-on — Armen, Mar 11 '16 at 06:09
@Eray My live database server is the same one as I use locally. It connects to an externally hosted database. — Z0q, Mar 11 '16 at 10:29

Ram · Accepted Answer · 2016-03-11T10:49:27.587

I'm assuming that your database or table config is similar to your localhost (better to double check your table). I noticed one mistake:

1. You called store_result() before calling execute(). As per http://php.net/manual/en/mysqli-stmt.store-result.php execute() should be called first.

See my code this might solve your problem:

    /* unsetting doesn't matter you're
    going to overwrite it anyway */
    unset($stmt);

    /* you dont need to initialize $stmt with $db->stmt_init(),
    $db->prepare() method will create it for you */
    $stmt = $db->stmt_init();
    $stmt = $db->prepare("SELECT * FROM logs ORDER BY `id` DESC");

    /* execute the query first before storing
    the result and binding it your variables */
    if (!$stmt->execute()) {
        echo "query execution error";
        exit();
    }

    /* store the result */
    $stmt->store_result();

    /* then bind your variables */
    $stmt->bind_result($r_id, $r_time, $r_logger, $r_message, $r_category);

    /* fetch data and display */
    while($stmt->fetch()) {
        var_dump($r_message);
        var_dump($r_category);
    }

    $stmt->close();

Let me know if this solved your problem.

Alternatively, you can use the straight forward way since you're not giving any input parameter like WHERE first_name LIKE <input here> to your query:

    $result = $db->query("SELECT * FROM logs ORDER BY `id` DESC");

    if ($result === false) {
        echo "query execution error";
        exit();
    }

    /* You can use either MYSQLI_NUM or MYSQLI_ASSOC os MYSQLI_BOTH
    see php.net for more info */
    echo "<pre>";
    while($line = $result->fetch_array(MYSQLI_NUM)) {
        print_r($line);
        echo "\n";
    }
    echo "</pre>";

Thank you! Problem solved. This was the answer for me: `You called store_result() before calling execute()`. P.S.: I connect to the exact same database on localhost and on my live website. It connects to an externally hosted database. — Z0q, Mar 11 '16 at 10:44
I need to wait 13 hours to give it. But I will surely give it to you :) — Z0q, Mar 11 '16 at 11:06

score 0 · Answer 2 · edited May 23 '17 at 10:28

As suggested in the question you linked, try the code without unset($stmt) and $stmt = db->stmt_init() Instead, you might use free_result()

// Log Table Query
$stmt->free_result(); //Free old results first
$stmt = $db->prepare( "SELECT * FROM logs ORDER BY `id` DESC" );

$stmt->store_result();
$stmt->bind_result($r_id, $r_time, $r_logger, $r_message, $r_category);
$stmt->execute();

while( $stmt->fetch() )
{
    var_dump($r_message);
    var_dump($r_category);
}

$stmt->close();

MySQL returns only the first character of the fields, but works fine on local

2 Answers2