A few small PHP and MySQL questions

Have to define blank varibles? Fastest way to echo? varchar vs. char?

[Soupisgoodfood]

Hi. I have a few questions about PHP and MySQL

1)

Is it nessesary to deffine blank $varibles?

E.G.

 if (blah) {
$var = 'some value';
} else {
$var = '';
}
echo $var;

Is it OK practice to leave out the else? I know it works without it. But not sure if this is good practice.

2)

What's faster?

echo 'stuff'; or
print 'stuff'; or
?> stuff <?

And what if I have alot of text, like an HTML table with 10 or so $varibles that need to be echoed into it?

3)

When should I use varchar coloums in MySQL?
I have a coloum that usaly only has 4 chars, but occationaly needs 120. The data in it is selected more oftern than it is updated. The book I have says there are differences but never gets around to explaining them.

Thanks,
Justin.

[jatar_k]

1 - you don't need to have blank variables, it would depend on what you are doing with it.

if later in the script you need to find out if it is empty and it wasn't initialized then make it blank or test if(!isset($var))

2 - I just use echo "stuff"
I have my html extension set up to parse php so for the most part I just close the ?> and then put the html in as is and then start the php again when I need to.

3 - I would use varchar(120) it adjusts to the size of the data and doesn't waste memory.

[AlbinoRhyno]

how do you create new lines in php echo's? I tried \n, but it just seemed to add a space.

[jatar_k]

if you are echo'ing to html then <br> would do it.

i usually use <br>\n after every line so it reads well in view source.

[AlbinoRhyno]

Doh. Stupid mistake on my part. I was outputting page links, but not putting a br or surrounding it with a p tag.

Sometimes it's the easiest things that cause the most problems!

[Mikael]

You shoul definitely use varchar(120), as char(120) would pad all four letter entries with 116 spaces to make up the space.

If on the other hand, you know all entries are always only going to be 4 characters, then char(4) is better as it would use less space than varchar(4) .

[ergophobe]

>What's faster?

The bigger the HTML chunk, the more likely it is to be faster to escape from PHP. If you have a line of HTML, it's probably faster to just quote it. If it's several lines, it's faster to escape from PHP. Note, however, that you have to do about 1000 iterations to notice the differences using microtime to measure this. Overall, optimizing your SQL queries will give you so much more speed than worrying about this stuff. If you're curious though, according to my tests, running 1000 or 10000 iterations, you find

- "text" is actually marginally faster
than 'text'

- "There's a " . $variable . " here" is marginally faster than "There's a $variable here"

These differences are completely insignificant though and you should go for readability unless you are getting a million hits a day.

>When should I use varchar coloums in MySQL?

One other consideration. If you have big tables, an indexed char column may give you a big performance boost over varchar. You will use a lot of disk space but it may be worth it (though probably not). You can get your data then use trim() to get rid of the trailing spaces.

Tom

[sugarkane]

Welcome to wmw Tom

> using microtime to measure this

Could you elaborate on this? What's your favoured method of performance testing?

[ergophobe]

Instead of elaborating myself, I'll refer you to

[zend.com ]

He gives you two methods
- Apache Bench for an entire script
- microtime() - not as accurate, but lets you isolate parts you want to measure

Cheers,

Tom

[MaliciousDan]

MySQL doesn't allow varchar columns of size 4 or less (it automatically assigns them as char). Having any variable length columns in a table will cause mysql to store the table as dynamic instead of static. Dynamic tables get exponentially slower as they grow in size, if you plan to have much over 1000 rows of data the speed might start to be a problem. These days when hard drive space is fairly cheap it's probably a better idea to keep the speed up and let the disk space run rampant. If you don't plan to have many rows of data in the table it won't make a big speed difference, but it's not going to make much size difference either on a small table.

[scotty]

Here's my two cents.

1). It is *not* necessary to have blank variables, but at the same time, it is a good practise, especially if leave the register_globals "on". For example, people can use some bogus URL to trick you, like

yoursite/yourscript.php?var=<script>document.location=blah;</script>

If you don't explicitly blank variable $var, $var will have the value from GET request, which when you echo it, it sends your visitor to somewhere else. With scripting, people can do much more harm than this...

Except that if you turn register_globals off, you won't have this problem, but you need to change your scripts to us $HTTP_[GET¦POST]_VARS. That means half of the scripts you downloaded and implemented on your site won't work :(

3). Using CHAR instead of VARCHAR helps the table row size to stay static, provided all your other columns have static size. A static row size is more efficient from RDBM's point of view, because it can easily jump to a row just by the index number. However, as someone has pointed previously, a 120-byte row with 116-byte blank is not efficient storage-wise.

I use MySQL, but I don't know much about its internals, however I'll usually suggest using VARCHAR in this case. And if you know that the majority of your records are 4 characters, you can just use the first 4 bytes to create the index to save the space further (and improve efficiency too).