CODEWITHSUNDEEP
phpmysql
user165242
user165242

Reputation: 1399

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

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?

Upvotes: 0

Views: 1576

Answers (2)

Ervin
Ervin

Reputation: 2452

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.

Upvotes: 1

Adam
Adam

Reputation: 2180

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.

Upvotes: 0

Related Questions