how to extract the field type of a mysql column without using mysql_field_type

Question

I am writing a code where I extract information from tables by entering the query in a php page. So if i write a sql statement in the search box, it would give me the result.

The problem: The mysql statement could be anything that the user enters as explained above. There are a few columns that repeat across tables like that of entered_date and unique_id. I extract the fields using mysql_fetch_assoc. When I do it that way, the duplicate columns get removed. But then I need to check whether there is date field and accordingly change it to a human readable form of "04 January 2010". For this I rely on mysql_field_type. There is the problem.

mysql_field_type it seems takes into account all the fields including the duplicated ones. But my table generated by mysql_fetch_assoc isnt taking the duplicate fields. Therefore as you would understand, from the code below, it is mistakenly thinking that some fields are date fields and I am getting the wrong answer printed.

   while($search_now1=mysql_fetch_assoc($search_now))
            {
        //  echo "in here";
            $checkvalue=0;
            echo "<tr>";
            foreach($search_now1 as $key => $value)
            {
                if($key=='type')
                {
                    switch($value){
                    case 1:$value="Student";break;
                    case 2:$value="Normal";break;
                    case 3:$value="Government";break;
                    case 4:$value="compl";break;
                    case 5:$value="compl-For";break;
                    case 6:$value="Foreign";break;

                    }   

                }
                else
                if($key=='city')
                {
                    $value=city($value);

                }
                else if($key=='state')
                {
                $value=state($value);

                }
                else if($key=='country')
                {
                if($value!='IN')
                        {       
                        $value=state($value);
                        }
                }
                            // this is the error prone area $checkvalue is incremented at the bottom
                else if(mysql_field_type($search_now,$checkvalue)=='date')
                {
                    $value=changedate($value);
                }


                if($value)
                {
                            echo "<td class=\"";
                            if($checkvalue<8)
                            {
                                echo "show";
                            }
                            else
                                echo "none";

                            echo "\"><span class=\"$key\">$value</span></td>";
                }
                else if(!$value)
                            {
                                    echo "<td class=\"";
                            if($checkvalue<8)
                            {
                                echo "show";
                            }
                            else
                                echo "none";

                            echo "\"><span class=\"$key\">-</span></td>";


                }

            $checkvalue++;
            }

How do I correct this?

Answer 1

It's a bit hard to see what you're doing here (without seeing your database tables or SQL query), but I understand you want to have better control of the types of data returned from a mysql_fetch_assoc(); ie. to be able to distinguish between dates, strings, numbers, and even more comlex types like city-names etc.

Perhaps you could cache the columns in the database locally and perform a dictionary (associative array) lookup.

Answer 2

you should use DESCRIBE tablename mysql query. this will list many properties for all the columns of your table. The type column tells you what type is your column.