Tag: mysql

Checking if two values in Query are the same, makes Query very slow

My Goal: A Query, which provides all provinces, in which a building is constructing. My Problem: As soon as I make sure that every building is compared to the same building (comparing upgrade_id) the Query takes forever. Without the last part of the where statement it takes 1 sec, which is totally fine. Table Setup Provinces change from time to

What constrain to use when trying to have one type of something (ex.bank account) for each ID?

mysql sql

I currently have a ACCOUNTS table with account_id attribute as primary key, cookie_type_id as foreign key, and client_id as a foreign key. When trying to make sure that each client_id has no overlap in cookie_type_id (one client cannot make 2 chocolate cookies), should I be using UNIQUE contrain on foreign key like: ALTER TABLE ACCOUNTS ADD UNIQUE cookie_type_id ? The

MySQL Select Count of Duplicate Value In Relation Table

join mysql sql

I have 3 tables: foods, order_detail, and orders Here are the records for table foods: Here are the records for table order_detail: And here are the records for orders table: I want to show count order detail grouped by food type. I expected this result: Here is my approach, but it still doesn’t show the expected result. The result is:

MySQL issue on INSERT … SELECT ON DUPLICATE KEY UPDATE and LAST_INSERT_ID()

last-insert-id mysql pdo php sql

In MySQL, I have INSERT … SELECT ON DUPLICATE KEY UPDATE query as below: UPDATE: This is the result from above query. Now I want to create an array of the user_id of either the insert or the update the records. So, my expecting array should be I tried it something like this, but it doesn’t work for me. That

MySql format number with space as thousand delimiter and no decimal zeros

database mysql sql

Having the number: 103,648.340 format it to be as the expected result Expect result: 103 648.34 My progress: TRIM remove decimal zeros, so I’ve 103,648.34 FORMAT add thousand delimited by space, so I’ve 103 648.340 I’ve tried to combine TRIM with FORMAT without success Answer Try this solution: DEMO

I can’t get the correct output when I use / ORDER BY from my SQL example?

mysql phpmyadmin sql

I was playing around SQL sample that I wrote and I’m quite surprised that I couldn’t get the correct output when I use less than, greater than or ORDER BY. for example, select player_name, jersey_number from Players order by ‘jersey_number’ desc; it should give players name and jersey number sort descending order from their jersey number. However, this is what

Cant understand my syntax mistake in MySQL CREATE FUNCTION

function mysql sql

This is my code and I can not find the mistake! I am trying to create a function that multiplies a value to de input parameter and returns that result! It should not be that difficult but I am not finding my sintaxt mistake The mistake says: error code 1064 Answer The DECLARE has to be before the code. I

Need to fix sorting order by name and price

full-text-search mysql php search sql

I am working on school listing project. I have created database name is school and under I add new table name is schoollist Here I have many school. I have added few column on it bellow the list of column. For the above table i want to search Wright Oslo and after the result should be Wright oslo school and

How to show/fetch amount of posts, comments etc a user has made

mysql php sql

I want to show the users the amount of posts, comments etc they have created in their profile section, just like any other forums that exist. I am doing this with php and MYSQLI. Post table: I have tried to add another column to the post table and increment its value by 1 each time the user makes a post