Search Posts

Category: sql

Issue Using a .txt File to Populate a MySQL Database

how to – Issue Using a .txt File to Populate a MySQL Database I am learning to use MySQL Workbench and am having trouble importing data. I have created a database, a schema, and a table. The table has three columns. I have been using the following query to try to populate the table using a .txt file. The file uses new lines to separate rows and two spaces to separate columns. LOAD DATA LOCAL […]

Online user matching algorithm

how to – Online user matching algorithm I’m working on a matching algorithm for online users. I would like the solution to be as scalable as possible but for this example lets say we have around 2-3k active users at any given time and a fourth or so of those are constantly searching for a match. Here’s an example of what the available variables would look like for the search: { user_id: 1, user_rating: 3458 […]

Nested query that will fetch latest row in grouped result

how to – Nested query that will fetch latest row in grouped result I have the following query: SELECT * from `provider_info` where provnum not in (select pi_provnum from prov_index where length(pi_provnum)=6) and length(provnum)=6 group by provnum prov_index will only have one row per provnum/pi_provnum. But provider_info can have multiple rows per provnum. I’m basically pulling rows from one table that do not have an entry by provider (provnum/pi_provnum) in the second table. The inner […]

Recursive SQL query on nested-set tree with stop condition

how to – Recursive SQL query on nested-set tree with stop condition I am using a nested-set tree structure in a table. The concept is described here. The example data looks as follow: +—-+———–+——+——-+—————–+ | id | parent_id | left | right | stop_descending | +—-+———–+——+——-+—————–+ | 1 | NULL | 1 | 10 | 0 | | 2 | 1 | 2 | 3 | 0 | | 3 | 1 | 4 | […]

SQL: Join on multiple tables

how to – SQL: Join on multiple tables I am trying to join the following tables: Table 1: users (id, name) Table 2: pivot_table (user_id, user_id2) This table hold the relationship between the users. One user can be related to many other users. Table 3: contacts (id, user_id, name) I am trying to select a user and then find its related users and contacts. I have tried to use the join, i am successfully able […]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near"" [duplicate]

how to – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near"" [duplicate] This question already has an answer here: How can I prevent SQL injection in PHP? 28 answers My php code unable to accept apostrophe (‘) when i enter the word like can’t,don’t …it says that “You have an error in your SQL syntax; check the manual […]

How to optimize my query

how to – How to optimize my query I am working with php mysql.Recently I wrote a query and it worked perfectly.Bu I want to optimize this query better way.I cant understand how? My Query SELECT DISTINCT total,c.id,a.title, a.first_name, a.surname, a.email, p.phone, c.created_at FROM customer c INNER JOIN `application` a ON a.customer_id = c.id INNER JOIN `application_phone` p ON p.application_id = a.id AND p.phone_type = ‘mobile’ INNER JOIN ( select count(a.customer_id) as total,a.customer_id from application […]

Updating column based on relation to another table

how to – Updating column based on relation to another table I have a table_1 that looks something like this NameId SelectedName Null A Null C Null F NameId is a FOREIGN KEY that REFERENCES table_2 (Id) And a table_2 that looks like this Id Name 1 A 2 B 3 C 4 D 5 E 6 F I want to populate NameId with all of the Id numbers associated with the same Name, so […]

Why EXISTS() always returns true?

how to – Why EXISTS() always returns true? Here is my query (used in a TRIGGER): update user_details set views_profile = views_profile + 1 where user_id = new.user_id and not exists ( SELECT 1 FROM views_profile vp WHERE vp.user_id = new.user_id and vp.viewer_id = new.viewer_id ) The TRIGGER: As you can see, my query is an UPDATE statement and the problem is, it never happens. According to some tests, the problem is related to EXISTS. […]

Next Page »