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…

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 ke…

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 th…

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 fo…

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 soluti…

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 …

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