SQL Bobbins

[caLLous]

Somebody who needs a slap.

 

[ECA]

What what what? Who the hell designed your database?

He left because he got cancer, probably from having to read all the code he wrote.

 

[caLLous]

Still in line for a slap tbh.

 

[Zarjazz]

I agree with the previous comments. Whom ever designed your database schema is a moron. Storing an integer value in a VARCHAR is beyond stupid. Have they even worked with SQL before??

the code there is:

$orderQuery = ("Select * From tablename Where Number > 0 Order By Number Desc Limit 1");

You can "fix" this query by casting the field to an integer to ensure the order by is correct, so:

$orderQuery = ("SELECT * FROM `tablename` WHERE `Number` > 0 ORDER BY CAST(`Number` AS UNSIGNED) DESC LIMIT 1");

 

[TdC]

My old databases lecturer would have a fit Zarjangle. He'd make me redo the schema so you don't have to do a cast operation every time you run your orderQuery (which I presume you do fairly often).

 

[Zarjazz]

My old databases lecturer would have a fit Zarjangle. He'd make me redo the schema so you don't have to do a cast operation every time you run your orderQuery (which I presume you do fairly often).

I completely agree fixing the schema is the correct option but depending on the situation and the amount of data involved something like an ugly CAST hack may be a very quick fix. I've read all the thread now and it looks like all the duplicate id's need fixing as well. That's a pain but it may be possible to do something, with a script, based on the primary key id to get a valid value.

 

[TdC]

Oh I certainly agree that business needs can make a quick fix more likely to be implemented than the actual repair job. In fact I'd have that discussion with the lecturer fairly often, where once I accused him of living in a fantasy world His reply was that people should just f*****g design their domains properly hahaha

 

[Zarjazz]

His reply was that people should just f*****g design their domains properly hahaha

Hah. Easier said than done! The real world is often very different to some ideal academic world vision

"Oh it's 3 years into a production system. We have a completely new application requesting the data in a unplanned for way and it needs a new INDEX added to the critical table to optimize the data lookup."

"No problem. That table is over 1 TB in size and contains several billion rows. It'll take down our 24x7 core system for 48 hours to add."

"...."

 

[TdC]

he'd prolly have an answer for that too. guy was hella experienced, even though most of the stuff he put to us was not real-world in the slightest. or as he would have put it: most of the people making use of databases are idiots. ah well