MySQL is an open-source, relational database management system.
7
votes
3answers
144 views
Getting the top 10 stores within a category
I have the following query. I know there's an easier way to do this without the sub queries, or at least optimizing how they're used. I've read about cross joins and using ...
0
votes
0answers
18 views
How to import mysql connector [closed]
As you are aware, I am a complete noob on this.
I am trying to use this reusable class to connect to mysql
Python Connection with MySQL
But whenever i try to run the python I am getting
...
0
votes
0answers
15 views
composite keys in table-creation script [closed]
I have a database which has customers reviewing products. My sql code for the db regarding the tables is below:
...
1
vote
0answers
6 views
PHP Cache Strategy - Genius or Sheer Stupidity? [migrated]
I have a shared hosting with limited capabilities. Memcache and mod_cache are not available to me. I would like to implement my own PHP caching method to cache the results of load-intensive SQL query. ...
7
votes
2answers
455 views
Protect database from SQL Injection and similar attacks - PHP
I am doubtful about the security of my PHP code. I am new to programming, but want to learn how to secure things, protect my databases from SQL injection, and other best practices. I'd like to know if ...
6
votes
3answers
410 views
Check efficiency of PHP code that will query over 8500 orders
I am querying all orders from a WordPress database that uses the WooCommerce Bookings plugin. I want to display upcoming bookings, and there is a potential to have every hour within 2015 to be booked; ...
5
votes
2answers
159 views
DB abstraction, private methods in OOP PHP library
This library registers a new user.
Questions:
Where should the DB class instantiation happen for user class? I tried instantiation in the constructor but that property doesn't seem to be available ...
4
votes
1answer
68 views
Refactoring of this complex PHP method
I have one complex method with 1890 paths. I don't know how can be this refactored. Can somebody add some tips about it? Maybe the MySQL table structure is wrong.
...
10
votes
2answers
61 views
Revision 1 - Step 1: PsychoProductions management tool project
I started a project to build my own invoicing and management system to take the place of prohibitively expensive QuickBooks software. This will be broken down in 5 steps, with the current step in bold ...
5
votes
5answers
422 views
Username, Password and UserType Validation
I have two tables in the database:
Credentials (userid, password, usertype)
Customer ...
4
votes
4answers
487 views
Two while for the same query
If I have to loop results of a query echoing first all fields of a column, then echoing something not to loop, then fields of another column.
...
2
votes
1answer
14 views
Query the Max Nth per group
I'm trying to write the most efficient query I can to pull the relevant data I need for retrieving the latest conversations for a user, ordering groupings of conversations by most recent message in ...
7
votes
1answer
52 views
Step 1: PsychoProductions management tool project
I started a project to build my own invoicing and management system to take the place of prohibitively expensive QuickBooks software. This will be broken down in 5 steps, with the current step in bold ...
2
votes
1answer
63 views
UserDAO with CRUD functionality for my UserRepository
This is my first attempt at creating a DAO.
I would like to get some feedback regarding the following aspects if possible:
Code readability
Efficiency
Usability
I also would appreciate any other ...
1
vote
0answers
50 views
Update database, refresh the page to show changes, show a success message
Follow up question for:
Update database, reload page then show a success message
I have a table of articles, in each row is a button to activate / deactivate the ...
3
votes
2answers
92 views
Dynamically load data into bootstrap accordion navigation bar
I have made a bootstrap accordion navigation bar that loads data dynamically from a database with PHP.
Here are the two tables that I use:
...
8
votes
3answers
100 views
Would this be an efficient way to use MySQL?
I've recently learned how to use MySQL to store/retrieve data. I've created a MySQLManager class for creating connections and executing querys/updates. Would this be an efficient way to do so?
This ...
5
votes
1answer
55 views
Conditional row count across 4 tables
I have the following function that works fine. Is there a better way to do it and will this be efficient if I have 100+ rows returned from each table?
The database
Tables
...
0
votes
1answer
75 views
Did you like my product?
I have the following MySQL query which selects product details and whether the viewing user likes them. I feel like it could be more efficient and that there is a more practical approach than using a ...
0
votes
1answer
56 views
Updating MySQL with shuffled numbers between 1 and 50
I need to assign a number to each userId. I think this is too much of a query for updating. Is it possible to reduce the number of queries, or is it okay?
...
3
votes
1answer
75 views
Update database, reload page then show a success message
I have spent a long time trying to find a way to do the following:
Update a MySQL database with AJAX
Reload the Page to show changes
Show a success message
I think I have finally found a way! ...
5
votes
1answer
208 views
Have I prepared this prepared statement well?
This is a prepared statement (I think). Have I done this well?
...
6
votes
4answers
223 views
Multiple SQL queries, one per ID
ids contains 100 ids that are comma delimited. Is it fine or does it need improvement?
...
0
votes
0answers
8 views
MySQL Structure - Need some feedback [migrated]
I am trying to create a movie database (wiki style) and I am stuck on the database structure. I would like some help and suggestions please. Here is my basic idea:
movie_table
...
4
votes
2answers
65 views
Optimizing cron job for sending reminders
The following script is being run with a cron. It runs once a minute to see if there is a reminder that needs to be sent.
Should I make this run once every 30 minutes? The choices to send a reminder ...
5
votes
2answers
96 views
Revised version of a complex query to count votes with a redistribution system
I've previously posted a (quite popular I must add) question about how to simplify a query to count votes with a distribution system (Complex query to count votes with a redistribution system). One of ...
4
votes
2answers
153 views
Injections and query
I made a class that connects to my DB and inserts some values. Is it secure or how can I protect this further from injections? The object declaration will come from variables with POST from a form, ...
5
votes
1answer
58 views
How can I optimize this SQL ranking generator query?
I have a World Cup fixture app where people can guess the results of the matches. Everything is working great, but each time I update the ranking, it takes 20 minutes for just 3000 users. So there ...
5
votes
2answers
72 views
Benchmarking our LAMP servers with this php script
I've written a small script to benchmark our LAMP hosted servers that assess the performance based on three factors:
Disk I/O
Database I/O (mysql)
Database I/O (sqlite)
The logic is as follows:
...
10
votes
2answers
155 views
Complex query to count votes with a redistribution system
I've spent the last couple of evenings trying to build a system for redistribution of votes in a ranked voting system. I've finally come up with the model for redistributing surpluses from a candidate ...
1
vote
1answer
68 views
A cart that uses SessionID
I wanted to create a cart that I can easily add some item or simply help someone at the other end over the phone. I decided to create a cart that would store everything on MySQL instead of using ...
0
votes
3answers
44 views
3
votes
2answers
85 views
Asking a user to take money from a checking account and move it to a savings account (Revised)
I made a few changes to the source code based on several suggestions I received yesterday. The changes I made were: improved the names of methods and variables to better describe what they do, passed ...
4
votes
3answers
105 views
Search for User by first and/or last name, efficiently
I am refactoring a user search that just feels dirty. (Users#search)
I need to allow a blank param to search on partial or only a first or last, but don't want to return all the records in the ...
11
votes
7answers
1k views
Asking a user to take money from a checking account and move it to a savings account
I'm aiming to get this program right as I might show it at a job interview. The code below runs fine but I would like to know if there's anything that can be improved in terms of readability, good ...
10
votes
3answers
210 views
Why is this geographic search incredibly slow?
I have this table (inradar_ad) with almost 300k entries. I want to know why my query takes 160 secs to run.
I tried limiting with ...
12
votes
3answers
228 views
Search script code efficiency
Is there a more efficient way to write this? This script will be used heavily and I want to make sure I do not have any memory leaks or speed issues.
This script gets an input from a form and ...
1
vote
1answer
36 views
Correct use of Joins & Wheres
I have 3 tables, 1 where voters information is stored, 2 where their address is stored and 3 where voter id's are matched with address id's. I thought this approach was best as several voters can ...
5
votes
2answers
77 views
Modifying `sakila` database
This is not "real life" code. I'm trying to expand upon the well known Sakila sample database for MySQL to make it more complex. Step 7 (or 6) is running ...
2
votes
2answers
72 views
Nested SQL Statement
I haven't done too many nested statements in MySQL and I was hoping to have the below SQL looked at and let me know if there is a better/more efficient way of doing what I am trying to accomplish.
...
0
votes
1answer
27 views
Safely storing and output data
On my application I use this method to store and to output the data. I would like to know if it is safely and correct.
...
0
votes
1answer
33 views
Add user star function triggering PHPmailer email optimisation
The function below simple allows a logged in user to "star" another user for convenience when trying to find the that user another time. At the moment the function works as it should, it inserts the ...
4
votes
2answers
49 views
Long SQL query with much duplication between two halves of a UNION
This works, but it's huge.
We need to repeat each table column on each UNION SELECT, as well as the WHERE clause for ...
1
vote
1answer
102 views
How safe is my MySQL query?
I am using PDO for the first time in my project. In my previous project someone suggested me to use PDO as my queries were wide open to inject. I am pasting a sample code of my project. Can you ...
7
votes
3answers
464 views
Are these Java MySQL connection classes and methods implementation the optimal?
I got three classes for MySQL Database access/manipulation,
First : Conector. It has got methods for connecting, disconnecting, querying and updating db.
Second : ...
1
vote
1answer
39 views
Tag Cloud using 2 queries (one nested), and a foreach loop: Is there a better way?
I have a database that, among other things, stores publications and publication tags. There is a many-to-many relationship between publications and publication tags. Simply put, I query the database ...
1
vote
1answer
51 views
Selecting three tables with relationship
I've been working on improving my website code. I've noticed that querying from a table inside a PHP while function, where ...
3
votes
1answer
60 views
advantage of using __call method
I have wrote simple class that allowing database selection and inserts.Could you please tell me does this a right way to use __call method with any useful advantage of it ?
...
2
votes
1answer
54 views
Delivery geolocations query
I have the below MySQL query that does exactly what I need. But I am sure there are better queries that do the same thing.
If you see something very ugly or bad and have any suggestions, I will be ...
6
votes
1answer
124 views
Is this user login secure?
When it come to security I try to be to better as possible but I don't have the knowledge.
According to what I read on-line my following code should be good but I could use some of your ...
