Mysql - String Manipulation

[vendlus]

I'm pulling a list of the columns from a table using 'SHOW COLUMNS FROM tablename'

 

This returns a field called 'Type' which has values such as 'varchar(255)', 'int(10)', 'tinyint(4)', and 'text', etc.

 

I need to pull the numbers out of these values. I was looking at doing something such as

$fieldtype = $field['Type'];

$maxlength = substr("$fieldtype", -3, 2);

but with the differing length of values I can't hardcode those numbers. Does anyone know of a function to pull out the characters between the ( )'s??

 

I can use substr("$fieldtype", 0, -1) to get rid of the rightmost one, but I'm not sure what to do about the left one. I was looking at ltrim, but it seems to be for whitespaces and tabs and such. Any thoughts?? Or did I miss something wtith substr or ltrim??

 

- Vendlus

[vendlus]

Ok, I think I've got it. I pulled this from the comments at the bottom of substr() in the manual.

 

>$fieldtype = $field['Type'];

$start = '(';
$end = ')';

if( $fieldtype != 'text')                        //since there is no number with 'text'
$maxlength = get_string($fieldtype, $start, $end);
else
$maxlength = '100';                          //I'll actually put something useful here later

echo $maxlength;
echo "<br />";

function get_string($h, $s, $e)
{
$sp = strpos($h, $s, 0) + strlen($s);  
$ep = strpos($h, $e, 0); 
return substr($h, $sp, $ep-$sp);
}

Anyone see any problems with this code? It seems to work correctly when I echo the variable.

[surefire]

I'm not exactly sure what you are trying to do. It seems that you have different length fields and you're trying to snip pieces off the ends of the data. If that's the case, you can use strlength() function to get the length of the data string. This piece of information can be used with the other php functions you are using to snip off the size pieces you want.

 

If I misunderstood, please elaborate.

[vendlus]

What I've got above seems to be working correctly.

 

I have a news editor who doesn't know anything about sql. I have a couple of different tables that I want him to be able to insert values into, but I don't want to have to write a new page for each one, or have to change the code if I change the table.

 

So I'm writing a page that takes in the name of the table and creates a simple form based on what the fields in the table are. For that reason, I need the 'Type' field split into type and size.

 

I'm using substr_count to check for the field types (I know, I'm sure there's a better way, but I was already using the function elsewhere, so I just copied it over ), but I needed to pull out the length of the fields.

 

Like I said, the code I put up above seems to work for me so far. Sorry if I've confused you by replying to my own question before anyone else could get to it.