Tagged Questions
12
votes
3answers
1k views
Best way to organize SQL queries stored in your code ? (or should you?)
I am sure I am not the only one who gets frustrated when they see a page of code littered with SQL queries. The ActiveRecord and other ORM patterns help mitigate a good amount of SQL used in a ...
0
votes
2answers
263 views
Large MySQL Batch Inserts From PHP: Run insert from script, or save as SQL file?
We have a large dataset that's current residing in many, many spreadsheets.
I've been tasked with getting part of that data into our MySQL DB.
When all is said and done, I will probably be inserting ...
-3
votes
3answers
506 views
How to not let anyone inspect elements of a webpage?
I was thinking to make some jQuery functions that will use ajax to control mysql. The whole idea is to make separate files of PHP (e.g query.php, addRow.php, update.php and delete.php) provide data to ...
0
votes
0answers
34 views
Track time spent on site
We are going to track user engagement (i.e. time spent on site, most viewed part/page of the system, etc).
I don't see Google Analytics / MixPanel being able to do this, since we have to analyze ...
0
votes
2answers
128 views
When do you use subquery in SQL? [closed]
When do you use subquery in SQL, and what type of request is subquery useful for? I am practicing SQL and need to know how to spot a subquery problem immediately
82
votes
10answers
10k views
Why is “Select * from table” considered bad practice
Yesterday I was discussing with a "hobby" programmer (I myself am a professional programmer). We came across some of his work, and he said he always queries all columns in his database (even on/in ...
0
votes
1answer
213 views
Why doesn't MySQL have an explicit “No Limit” option for queries?
I use SQLyog Community edition and like many other SQL applications out there it puts a 1000 result limit on queries that do not have a limit provided.
I am wondering why MySQL doesn't have an ...
1
vote
3answers
104 views
Select custom output formats from database with SQL
I am planning to make a simple rest service application, and I am currently deciding the architecture. I have decided that I want to write the middle layer in multiple languages, so that it is easy to ...
3
votes
1answer
180 views
Best way to rename existing unique field names in database?
I have a database table that contains id, filename, userId
id is unique identifier
filename should also be unique
table may contain >10000 records
When a user uploads a file it should be ...
111
votes
13answers
41k views
Why use a database instead of just saving your data to disk?
Instead of a database I just serialize my data to JSON, saving and loading it to disk when necessary. All the data management is made on the program itself, which is faster AND easier than using SQL ...
3
votes
3answers
394 views
Is it better to use a switch statement or database to look through 5,000 to 10,000 instances?
I have some JSON data in this format (5,000 instances for now):
{"id":"123456","icon":"icon.png","caseName":"name of case"}
I am allowing the user to search for the name of the case and then return ...
1
vote
1answer
143 views
Store data for multiple User Type
I am developing an app where four types of user share same module. So is it a good idea to create:
one table and user_type and user_id (something like that)
table prefixes basically 4 diff tables of ...
0
votes
1answer
343 views
Are there any SQL servers that support compiled queries?
Can SQL queries be compiled into byte code or equivalent to enhance performance?
I know that most database servers support prepared statements, but is that the same thing as compiling? Most ...
1
vote
2answers
2k views
Using Autoincrement Primary keys as Foreign Keys
When creating a table, there is an option for us to set the numeric primary key as autoincrement where it's value increases whenever a new data in inserted.
This primary number can be used for ...
3
votes
6answers
977 views
OOP (php) for beginners - some unclarities
I started reading some tutorials about OOP, because I want to learn the basics.
I have a question about it. I understand how you can have a object "Car" and give it a color like this ...
1
vote
2answers
440 views
Why OTServs have an item cloning problem if the server crashes? [closed]
OTServs are open source MMORPGs with a huge community. Mostly all of them have a serious problem: if the server crashes, people can clone items. This is a dirty trick that can be executed because the ...
1
vote
5answers
1k views
Is it Considered Good SQL practice to use GUID to link multiple tables to same Id field?
I want to link several tables to a many-to-many(m2m) table.
One table would be called location and this table would always be on one side of the m2m table.
But I will have a list of several tables ...
2
votes
2answers
178 views
Better way to search for text in two columns
Here is the scenario. I am making a custom blogging software for my site. I am implementing a search feature. It's not very sophisticated - basically it just takes the search phrase entered and runs ...
4
votes
3answers
333 views
Rule of thumb for field sizes
What VARCHAR sizes should be used for first and last names, phone numbers (preferably international), email adresses, urls, dictionary words and file names?
Edit: Zapped the introductory phrase to ...
3
votes
7answers
4k views
What is the best approach for database design with lots of columns?
I am writing a query based financial application. It lets the user to write complicated equations (much like WHERE part of an SQL query) and find companies matching those criteria.
For the above, I ...
9
votes
9answers
3k views
Foreign key restrictions -> yes or no?
I would like to hear some”real life experience” suggestions if foreign key restrictions are good or bad thing to enforce in DB.
I would kindly ask students/beginners to refrain from jumping and ...
5
votes
2answers
194 views
Storing stop/start points in a database
Let's say I am storing start and stop points per user into a database table.
For example... let's say in a chat system, a user only needs to see lines 24-293, and 500-512. (Let's say he logged off ...
3
votes
3answers
811 views
Why put SQL statement in a variable before using mysql_query()?
I'm been working with PHP on and off for a bit now, and I've seen plenty of code both ways:
$sql = "SELECT …";
mysql_query($sql);
vs
mysql_query("SELECT…");
Is there a reason for separating the ...
0
votes
1answer
123 views
Which EXPLAIN SELECT is better — the one in PostgreSQL or the one in MySQL? [closed]
Or are the two basically the same when it comes to figuring out how to build the right indexes?