Search Posts

Category: sql

How to get the value of column if I knew the value of other column in the same row in MySQL?

how to – How to get the value of column if I knew the value of other column in the same row in MySQL? I have to retrieve the column data by using the other column data in the same row which I already knew. Eg. Table table Name: fixtures fix_id | fix_against 1837 | Aus Vs. Eng 2942 | Ind Vs. SA Here I already knew the ‘fix_id’.. So, I need to retrieve the […]

How can I create a mysql table from a youtube id in php?

how to – How can I create a mysql table from a youtube id in php? So basically I want to fetch a youtube video id in PHP from URL and put it into a MySQL table, how can I achieve that? I have this for the moment : (note that the fetched id is define by $vidid) <?php $conn = mysqli_connect(‘localhost’, ‘root’, ”, ‘test’); if (!$conn) { die(“Error connecting to database: ” . mysqli_connect_error($conn)); […]

Round Robin selection in php?

how to – Round Robin selection in php? I have below tables class_student_mapping class_id student_id 1 1 1 2 1 3 1 4 2 1 2 2 student_program_mapping student_id class_id program_id 1 1 1 2 1 1 I need to assign programs to student by class basis i will get class id and program id than i need to check the latest assigned student in student_program_mapping after getting that student i will assign new student […]

Mysql period extraction based on purchase date

how to – Mysql period extraction based on purchase date My table has – join_date ex:(2017-12-16 19:26:45) – expired_date ex:(2018-04-29 17:00:03) – total_charges ex(5) Evey month (30 days) there is a recurring charge, if isn’t cancelled, expired_date gets extended 30 days and total_charges add +1 I need to build a query, able to get recurring charges time from date X to date Y. Seems simple but I am not able to figure it out. So […]

using ifnull with index creation in mysql

how to – using ifnull with index creation in mysql I am trying to create an index in MySQL whereas the query will first check what column is not null. After checking, it will create the index on the column that is not null. However, I am not successful in creating this and it says I have an error, can someone help me? please see my code below create index IDX_KSE_NO_01 on tb_kse(ifnull(ss_no, id_no); Source: […]

MySQL Combine People Names as in Mailing list

how to – MySQL Combine People Names as in Mailing list I have a table Households that is a one to many relationship with a People table. I want to generate an address line for names like: ‘HisFirst & HerFirst TheirLast’, or something like ‘Joe & Jane Doe’. This would serve as the first line of an address label. My query currently returns something like ‘Joe Doe & Jane Doe’. So you see the last […]

Optimize Multiple Joins SQL

how to – Optimize Multiple Joins SQL I need to join all columns from one table (table A) to single columns in two other tables (B & C). My SQL works but is pretty slow. The problem is coming from joining A and C. I’ve looked into optimizing but haven’t found anything to speed it up. Unfortunately, I can’t alter the table design. SELECT tableA.*, tableB.user_email, tableC.transaction_number FROM tableA LEFT OUTER JOIN tableB ON tableA.user_id=tableB.id […]

How to correct a reset of a increasing value in MySql Table

how to – How to correct a reset of a increasing value in MySql Table Im using a MySql table, to save sensor values I collect over RS485. The sensor sums up the charge, that has already gone into the battery. It’s positive when discharged and negative when charged. Sadly I reset the measuring module on accident.. So my charge drops 161 A/h from one row to the other, and new data is related to […]

last record in multiple tables

how to – last record in multiple tables I need to get the last record of a serial taking into account that you have to search in all these tables with the following nomenclature, registers_2017_01, registers_2017_02 etc. I tried as below SELECT c.name,SUBSTRING(c.name_fantasy,1,30) AS name_fantasy,CONCAT(v.title,’ ‘,v.plaque) AS title,v.type_vehicle,r.id,r.sn,r.lat,r.lng, r.direction,r.date_db,r.date_gps,r.volt_main,r.in1,r.in2,r.in3,r.speed,r.ign,r.sat_fix,r.sat,i.name_in1,i.name_in2,i.name_in3,i.name_in4,i.name_in5,i.name_in6,i.name_out1,i.name_out2,i.name_out3,i.name_out4,i.name_out5,i.name_out6 FROM clients c JOIN vehicles v ON c.id = v.clients_id JOIN installations i ON v.id = i.vehicles_id JOIN trackers t ON i.trackers_id = t.id JOIN registers […]

Next Page »