MySQL Data Retrieval - Printable Version

MySQL Data Retrieval - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: MySQL Data Retrieval (/showthread.php?tid=19742)

MySQL Data Retrieval - El Forum - 06-17-2009

[eluser]bhogg[/eluser]
Hi all,

A quick question. I have the following code to retrieve a post:

Code:
// Connect to the database then get the data

$connection = @mysql_connect($hostname, $username, $password) or $error = TRUE;

@mysql_select_db($database) or $error = TRUE;

$query = mysql_query("select content from posts where id = 3571");

if ($row = mysql_fetch_assoc($query))

    echo($row['content']);

// Use a class to do it

$post = Post::Get(3571);

echo $post->content;

The first bit uses mysql functions directly, the second uses CodeIgniter database class to retrieve the data in a similar way. There's no wrapping in an escape function or anything, and the charset for CI is set to UTF-8.

Problem is the MySQL is retrieving the data nicely (it was inserted using mysql standard code, whereas the CodeIgniter version is retrieving symbols in place of certain characters. The database collation is utf8_generai_ci.

Any ideas why they would ever differ?

Cheers,
Brian

MySQL Data Retrieval - El Forum - 06-17-2009

[eluser]naren_nag[/eluser]
Could you share the code for your model and get method.

cheers.

Naren

MySQL Data Retrieval - El Forum - 06-17-2009

[eluser]bhogg[/eluser]
Sure, here is the part that reloads the data, the rest just checks to make sure it's a valid id. Note that readonly_defn is an empty array.

Code:
$CI =& get_instance();

            $db = $CI->db;

            // Build the select portion of the query, and join readonly values

            $select = self::TABLE.".*";

            $db->from(self::TABLE);

            foreach ($this->_readonly_defn as $field => $arr)

            {

                foreach ($arr as $value => $condition)

                {

                    $select .= ", {$value} as {$field}";

                    $db->join(substr($value, 0, strpos($value, ".")), $condition, 'left');

                }

            }

            $db->select($select);

            // Get only the applicable row

            $db->where(array(self::TABLE.'.id' => $id));

            // Get the results and update our internal data

            $query = $db->get();

            if ($query->num_rows())

            {

                $this->_data = $query->row_array();

                foreach ($this->_readonly_defn as $field => $arr)

                {

                    $this->_readonly[$field] = $this->_data[$field];

                    unset($this->_data[$field]);

                }

            }

MySQL Data Retrieval - El Forum - 06-17-2009

[eluser]naren_nag[/eluser]
Are you building a library/plugin for ORM? Check out Datamapper or Datamapper Overzealous Edition to see how Stensi and Phil have implemented this.

And if you're writing your model like this ... dude, wtf!

Code:
function get($id)

{

 $query = $this->db->where("id",$id)->from("tablename")-get();

 if($query->num_rows() > 0)

   return $query->row()  // or return true and populate the model's public variables

 else

  return false

}

Perhaps if you help me understand WHY you're writing the code you're writing, I'll be able to move away from the WTF zone and answer better Smile

naren

MySQL Data Retrieval - El Forum - 06-18-2009

[eluser]bhogg[/eluser]
The question has nothing to do with it Wink

I'm encapsulating everything within the class instead of using the model CI provides. I can then do more advanced things in the __get / __set methods, handle logic for creating a new instance in __construct, and generate overall cleaner code. ORM solutions likely exist that could do this in a few less lines of code but I don't need to make it that abstract - the chance of the DB backend changing are slim to none.

Regardless, the fact remains that using the DB object as opposed to using the MySQL object directly returns different data, and I'm not sure why? I'm sure there's a configuration parameter that determines how the data is formatted, or else perhaps there's a layer between MySQL that might not need to be there...

MySQL Data Retrieval - El Forum - 07-01-2009

[eluser]bhogg[/eluser]
In case it helps anyone, it was just a matter of manually setting the db params:

$db['default']['char_set'] = "utf8";
$db['default']['dbcollat'] = "utf_general_ci";

Whatever the default is wasn't working correctly with some characters versus whatever the default mysql values are.