SQL length question

[alexaugot]

Hi again,

Quick question, but does anyone know how to get MySQL field types out of what php returns? PHP only really returns int, real, string and a few others. It doesn't seem to return TINYINT, MEDIUMINT, DOUBLE, etc.

Anyone know?
-Alex

[[deleted]]

You can't. (and you don't have to)

You can use 'decribe table' to find the types of the columns.

[alexaugot]

Yeah,

I ended up using "SHOW FIELDS FROM table", and walking through it using mysql_fetch_row(); It would be easier though, if PHP reported these attribs natively.

-Alex

[[deleted]]

"It would be easier though, if PHP reported these attribs natively."

Why? What's could possibly be the point of finding out the type of field in a database?

[alexaugot]

Well, reason number one would be error checking SQL statements. If you have a tinyint field and someone trys to put in a gigantic number, you can find that this wont work before you hit the SQL server with your request. It just saves wear and tear on the database.

And if you're using javascript, you can do all the error checking on the client side, saving you even more server bandwidth, et all.

This might not be a big deal if you've got a few tables in a database, but when you're dealing with a bunch of databases, each with a bunch of tables, each with 1000's of records and read/write requests coming in every second, the stress adds up.

-Alex

[[deleted]]

"If you have a tinyint field and someone trys to put in a gigantic number you can find that this wont work before you hit the SQL server with your request."

But why on earth would you need to get this data from the database? Your database was designed to hold the types of data that your application works with, so you already know exactly what the limits for each type of data are.

"And if you're using javascript, you can do all the error checking on the client side, saving you even more server bandwidth, et all."

And then a client disables his javascript and your database explodes. Maybe you should rethink that? 🙂

"This might not be a big deal if you've got a few tables in a database, but when you're dealing with a bunch of databases, each with a bunch of tables, each with 1000's of records and read/write requests coming in every second, the stress adds up."

Let me get this straight... you want to lessen the load on the database by checking the data so you never execute a query that will fail. But before you can check the data you need to have the database format, and you get that by running a query on the database.

Am I missing something here?

[alexaugot]

<i>But why on earth would you need to get this data from the database? Your database was designed to hold the types of data that your application works with, so you already know exactly what the limits for each type of data are.</i>

But what if you DONT know what kind of data is being put into the database? What if group (a) only uses mediumint's and group (b) uses blobs and doubles? Then you have a problem if you typecast what's being sent from the db to your php.

<i>And then a client disables his javascript and your database explodes. Maybe you should rethink that? 🙂 </i>

This is true, in most cases. The script I'm writing is going to be used in a corporate setting, where pretty much every computer is the same (with the execption of some linux boxes), and uses the same browser with JavaScript enabled. Besides, there are plenty of ways to work around java script being disabled.

<i>Let me get this straight... you want to lessen the load on the database by checking the data so you never execute a query that will fail. But before you can check the data you need to have the database format, and you get that by running a query on the database. </i>

There in lies the paradox. But just querying the database is going to be faster than writing to the database. So I could either read, try to write, fail and get an error message. Or I could just read, check in php, and use php to generate the error message. So in terms of an order of operations, you have have a single request, instead of an order of 0² or more...

-Alex

[[deleted]]

"But what if you DONT know what kind of data is being put into the database?"

Then you cannot write an app for it now can you? :-)

"What if group (a) only uses mediumint's and group (b) uses blobs and doubles? Then you have a problem if you typecast what's being sent from the db to your php."

This is getting stranger by the minute.

So now you are writing an app that will store it's data in "whatever is available"?

"There in lies the paradox. But just querying the database is going to be faster than writing to the database."

I doubt that.

"So I could either read, try to write, fail and get an error message. Or I could just read, check in php, and use php to generate the error message. So in terms of an order of operations, you have have a single request, instead of an order of 0² or more..."

That's MySQL thinking again. The database knows what's wrong, and it will tell you. Don't try to outsmart the database.

But the real point is that if your app is anywhere near sane, it will never 'fail' to write, because you app already knows what types of data go in the database and it will give an error without ever touching the database.

With your method you must always do a describe query before you do a write query.
In the real world, 9 out of 10 inserts are successfull, so you'd do 10 describes and 9 inserts.

But if you just write and see if it fails, you do only one operation. that's 10 insert queries, of which one will fail. And if it fails because of 'illegal data' then it will probably come back faster than you can describe the table, parse the output and compare the data with the output.

[alexaugot]

<i>Then you cannot write an app for it now can you? :-)</i>

Of course you can! Just use abstraction. All the script needs to know is that it's going to get some data, it can figure out what kind of data on it's own. If every app you wrote had to know EXACTLY what kind of data it's going to get at all times, well, it wouldn't be very flexable at all.

<i>This is getting stranger by the minute.

So now you are writing an app that will store it's data in "whatever is available"?</i>

No, I'm writing an app not un-like phpMyAdmin, but a little more dumbed down, for managers and other people to use where I work. I need to know the EXACT data type, for a few reasons, but the main reason is that some managers and users don't know SQL. They just want to insert what they want, not have to deal with ints and blobs and whatever. So the database takes care of that for them, by checking the type before it's written to the screen, and comparing the new value of the field to a set of pre-defined values, just incase the user tried to put 23523632623623623 into a TINYINT field.

<i>That's MySQL thinking again. The database knows what's wrong, and it will tell you. Don't try to outsmart the database.

But the real point is that if your app is anywhere near sane, it will never 'fail' to write, because you app already knows what types of data go in the database and it will give an error without ever touching the database.

With your method you must always do a describe query before you do a write query.
In the real world, 9 out of 10 inserts are successfull, so you'd do 10 describes and 9 inserts.

But if you just write and see if it fails, you do only one operation. that's 10 insert queries, of which one will fail. And if it fails because of 'illegal data' then it will probably come back faster than you can describe the table, parse the output and compare the data with the output. </i>

Well, you may be right about this, but I'm not trying to out smart the database. I'm trying to enforce a few rules, and check a few errors that will come up over and over, such as a user accidently changing the name and value of a primary field and thus fucking things up, because they're not suposed to do that.

I've got a few methods, that if something goes wrong with the query, it just spits out the mysql error message. I'm not trying to re-write the error checking code for all of the mysql functions!