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)

Christian

Is your schema the same on both systems? What does running this query directly produce? – Cleopatracleopatre

@Cleopatracleopatre 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. – Christian

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. – Stagger

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? – Machellemachete

have you tried to not call unset($stmt); as suggested in your found question comment:#10508348 – Roderica

@Roderica Yes, I tried, but it did not solve my problem. – Christian

@Eray My live database server is the same one as I use locally. It connects to an externally hosted database. – Christian

Answer 1

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

Finable

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. – Christian

Where's the bounty? :) – Finable

I need to wait 13 hours to give it. But I will surely give it to you :) – Christian

Answer 2

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

Longways