Hi guys, This is my first post and so glad to start sharing the knowledge. I am using Mysql database with VB.NET and I have this code : Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click Dim str As String = "Server=localhost;Port=3306;Database=testdb;Uid=root;Pwd=password" Using con As New MySqlConnection(str) Dim query As String = "select * from testdata where rfid_tag='" & TextBox3.Text & "' and Date_serve<= '" & Date.Now.ToString("yyyy-MM-dd ") & "' and Start_Time<= '" & Date.Now.ToString("HH:mm:ss ") & "' and End_Time>= '" & Date.Now.ToString("HH:mm:ss ") & "' or amount_serve='' " Dim cm As New MySqlCommand(query, con) con.Open() Dim rd …

Member Avatar
Member Avatar
+1 forum 2

I am trying to select all rows from all tables in the database when a column equal a given name. I have like many tables with same structure and columns. I have written this piece of code but it throws an error. SELECT GROUP_CONCAT(qry SEPARATOR ' UNION ') INTO @sql FROM ( SELECT CONCAT('SELECT * FROM `',table_name,'` where Name like ''','sally%','''') qry FROM ( SELECT distinct table_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = 'db_customers' AND column_name LIKE 'Name' ) A ) B; PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s; This is the error I am getting. I am aware …

Member Avatar
Member Avatar
+0 forum 3

I have been cracking my head on this mater in the past couple of days. I am faced with a situaton where i need to write a MySQL equivalent of PHP code to loop through sql select results. The Php code looks like this $query = "SELECT pubid, author FROM refs"; $sql=$con->prepare($query); $sql->execute(); $sql->setFetchMode(PDO::FETCH_ASSOC); while ($row=$sql->fetch()){ $id= $row['pubid']; $author = $row['author']; $name = explode('., ', $author); } How can one write sql/mysql query that does the same?

Member Avatar
Member Avatar
+0 forum 6

I have two tables like below tb1 Date Cr Dr 2018-04-29 100 50 2018-04-30 0 150 2018-05-01 250 100 2018-05-02 150 100 2018-06-10 300 250 2018-06-11 0 50 tb2 Date Cr 2018-05-01 350 2018-05-02 250 2018-06-10 300 2018-06-11 100 2018-06-15 200 2018-06-18 100 I need the following Result Apr May Jun Cr 100 1000 1000 Dr 200 200 300 Basically I have to add the respective Cr columns from both table and get the Dr from tb1 for the corresponding dates and create a monthly report. I have tried SELECT Date(tb1.Date) as sDate, SUM(tb1.Cr+tb2.Cr) sumcr, SUM(tb1.Dr) sumdr FROM tb1,tb1 WHERE …

Member Avatar
Member Avatar
+0 forum 7

Hi, How can i make query for this: Table1 Column1 Column2 LS11111 0 LS22222 100 LS33333 10 LS44444 0 LS55555 25 Table2 Column1 Column3 LS11111 100 LS22222 80 LS33333 25 Result i need is Column1 Column2 LS11111 100 LS22222 100 LS33333 25 LS44444 0 LS55555 25 Column1 is same on both tables, Column2 is pieces number in table1, and column3 is pieces number in Table2 I need maximum piece numbers. If table2 doesnt exist column1 value then shows table1 column2, and if it exist and is larger than table1 column2 then shows number from table2

Member Avatar
Member Avatar
+0 forum 3

Hello, my code at the minute fetches a record and splits it up into a tuple, this is so each individual element can be displayed in an entry. I am now trying to create a feature which reads what is in the entrybox and updates the record in the database. This is the current code: def Update(self): global Record global Name global TrainerID global Postcode global Age global Gender global Password (Name, TrainerID, Postcode, Age, Gender, Password) = tuple(Record) Name = self.ent_Name.get() TrainerID = self.ent_TrainerID.get() Postcode = self.ent_Postcode.get() Age = self.ent_Age.get() Gender = self.ent_Gender.get() Password = self.ent_Password.get() List = [Name, …

Member Avatar
+0 forum 0

Hi. I'm retrieving a record and populating the data into input fields. there is a column called fld_order_date type timestamp. How do i retrieve the date? i have the usual retrieval: $stmt = $conn->prepare("SELECT * FROM tbl_orders_a154287 WHERE fld_order_id = :oid"); $stmt->bindParam(':oid', $oid, PDO::PARAM_STR); $oid = $_GET['edit']; $stmt->execute(); $editrow = $stmt->fetch(PDO::FETCH_ASSOC); the input field : `<input name="orderdate" type="date" readonly value="<?php if(isset($_GET['edit'])) echo $editrow['fld_order_date']; ?>">` right now what displays in the textbox is : dd/mm/yyyy

Member Avatar
Member Avatar
+0 forum 1

Dear all, I have a scenario, where I have a single table with all my users listed in them. Each user also has a field indicating the user they report to. UserID UserDisplayName UserReportsTo ----------- ------------------------------------------------------------ ------------- 8 Anthony Karimi 3 9 naheed kassam 8 1 Administrator 1 3 Ramakrishna 2 2 SB 2 (5 row(s) affected) In my scenario, i need when the user selected is say userid = 3, then the table displays userid = 3; userid = 8 who reports to userid = 3; and userid = 9 who reports to userid = 8 as shown below …

Member Avatar
Member Avatar
+0 forum 4

I am looking for a mysql query to search and list most common pairs with their IDs and number of occurence. Thanks.

Member Avatar
Member Avatar
+0 forum 11

Hi, i have installed prestashop 1.4.8.2 & i need a report of last order date for each customers, please help me to create mysql query. i need below columns information from database. id_customer, firstname, lastname, email, last order date. or is there any way i can export in active customers list (those customers who didn't placed any order for last 3 months.) thanks, Ritesh

Member Avatar
Member Avatar
+0 forum 2

I have been butting my head against this problem for the past 4 hours, and I have to tap out. I know I can't be too far from correct I am just not sure what the missing ingredients are. Here is the problem question: Student status: List of all students and their enrollment status Fields: student last name and first name (comma separated), years enrolled, academic advisor last name and first name (comma separated) Sort: years enrolled Input: none Filter: only include currently active students and My code: SELECT student_id,std_l_name, std_f_name, faculty_l_name, faculty_f_name,student_id_fk,enroll_date,MONTHS_BETWEEN(CURRENT_DATE ,enroll_date) V1: FROM STUDENT, FACULTY,ENROLLMENT WHERE FACULTY.faculty_id=STUDENT.faculty_id_fk3 …

Member Avatar
Member Avatar
+0 forum 2

SELECT CASE WHEN (Employees.End_Date is null) THEN select EmpId, Emp_Name, Salary , Start_date , End_date ,DATEDIFF(DATE_ADD(Start_Date, INTERVAL 30 DAY), Start_Date) * Salary/30 as 'Total_Salary' from Employees ELSE Select EmpId, Emp_Name, Salary , Start_date , End_date ,DATEDIFF(End_Date, Start_Date) * Salary/30 as 'Total_Salary' from Employees END From Employees; Please help me to find error in this query. I

Member Avatar
Member Avatar
+0 forum 1

I am a beginner practicing how to create tables on SQL Here is what I've done: Create Table PO (Order_id int primary key, order_date date, customer varchar(1000)); Create table PO_Line_Item (Order_id int not null, Line_number int not null, Product varchar (30), Unit_Price decimal (10,2), Qty decimal (10,2), Tax decimal(10,2)); When I press execute it appeared "there is already an object named 'PO' in the database. What have I done wrong?? If any one have any ideas pls let me know

Member Avatar
Member Avatar
+0 forum 1

I want to get sum of estimate quantity from estimates table gropBy material_id. Also want get sum of mprs quantity related to previous query table. My estimates table is as below | id | project_id | material_id | estimatequantity | | 1 | 1 | 1 | 500 | | 2 | 1 | 2 | 899 | | 3 | 1 | 1 | 250 | and my mprs table is as below | id | estimate_id | mprquantity | | --- | --- | --- | | 1 | 1 | 334 | | 2 | 1 | …

Member Avatar
Member Avatar
+0 forum 1

i want to combine multiple rows into a single row using **DB2**. I have found this code, but I do not know how can i edit it>? CREATE FUNCTION MySchema/MyUDF ( PARCol2 CHAR(5) ) RETURNS VARCHAR(1024) LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA CALLED ON NULL INPUT DISALLOW PARALLEL BEGIN DECLARE ReturnVal VARCHAR(1024) NOT NULL DEFAULT ''; FOR CsrC1 AS C1 CURSOR FOR SELECT MyCol1 FROM MyTable WHERE MyCol2 = ParCol2 DO SET ReturnVal = ReturnVal Concat CsrC1.MyCol1; END FOR; RETURN LTRIM(ReturnVal); END ; and call it here Select id, MyUDF(Name) as FruitsAvailable From TableFoo where id = 1 Thanks!

Member Avatar
Member Avatar
+1 forum 1

Hello SQL Gurus, This Query below is doing the job but it is horrible slow. Is there any way to make the query faster ? Perhaps a join .. inner outer, left ? I am looooost. Thanks for any help. SQLQuery1.SQL.Clear; SQLQuery1.SQL.Text:= 'SELECT * FROM orders_batch WHERE invoice_no=:elorder_no'; SQLQuery1.params.parambyname('elorder_no').Asstring := elorder_no; SQLQuery1.open; while not SQLQuery1.EOF do Begin elsku:= SQLQuery1.FieldByName('itemno').asString; SQLQuery2.SQL.Clear; SQLQuery2.SQL.Text:= 'SELECT * FROM stock_qty WHERE sku=:sku'; SQLQuery2.params.parambyname('sku').Asstring :=elsku; SQLQuery2.open; elqty:=SQLQUery2.FieldByName('qty').asInteger; SQLQuery1.Next;

Member Avatar
Member Avatar
+0 forum 4

Hello experts, I have to write a sql joining 6 tables. to retrieve classid, planname, workoutname,date,timeslot,status,staffemail and display them to a gridview. the tables are as below **tblclass** having columns *classid(PK)*, planallocationid*(FK to tblallocation)*, date, timeslotid*(FK to tbltimeslot),*status, staffid*(FK to tblstaff)* **tblallocation** having columns *planallocation(PK)*, planid*(FK to tblplan)*, workoutid*(FK to tblworkout)* **tblplan** having columns *planid(PK)*, planname **tblworkout **having columns* workoutid(PK)*, workoutname **tblstaff **having columns *staffid(PK)*, staffemail **tbltimeslot** having columns *timeslotid(PK)*, timeslot i came up with something like below but am getting lost with all these. Can anyone help! SELECT a.planname, b.workoutname, c.timeslot, e.date, e.status, d.email from tblclass e inner join …

Member Avatar
Member Avatar
+0 forum 3

I am trying to cast/ convert varchar to a numeric value. I have done this succesfuly when I eval the lower range, however I get an error when I do the upper range. this is my evaluation section that works. isnull(cast(measurmentdata.measvalue as numeric),0) < productinfo.lowerrange however when I try to test to see if it is above the upper range it throws an error isnull(cast(measurmentdata.measvalue as numeric),0) > productinfo.upperrange I have tried not between as well. while it does not return any errors it does not correctly test for > or < isnull(cast(measurmentdata.measvalue as numeric),0) not between productinfo.lowerrange and productinfo.upperrange

Member Avatar
Member Avatar
+0 forum 3

Hello Everyone, I am working in MySQL Workbench 6.3 CE and trying to use a trigger inside of a stored procedure to see if it will detect if my email format is not in the right format I specified in the message text error, but I am having trouble because it says a trigger can't be created inside of stored procedure. How else can I accomplish what I need to accomplish. This is a BEFORE TRIGGER since it will check the email validation before any rows are inserted into my Faculty table. What I am I doing wrong? Here is …

Member Avatar
Member Avatar
+0 forum 1

i have this code..,. i dont see what's wrong why its only working on the first record of LOOP of X set serveroutput on size 30000; DECLARE VTEMP NUMBER; VPARENT NUMBER; BEGIN FOR X IN ( SELECT TEMPLATE_ID, TYPE_ID, TYPE_KIND, RECORD_ID FROM RT_RECORDS WHERE TEMPLATE_ID = 106 )LOOP DBMS_OUTPUT.PUT_LINE('TYPE_ID X :' || X.TYPE_ID ); FOR Y IN ( SELECT OBJECT_ID, OBJECT_TYPE CHILD_TYPE, LEVEL, PARENT_ID, RPA_N.GETTYPEID(PARENT_ID)PARENT_TYPE FROM NODES N CONNECT BY PRIOR N.OBJECT_ID = N.PARENT_ID START WITH N.OBJECT_ID = 18464078 ) LOOP DBMS_OUTPUT.PUT_LINE('TYPE_ID :' || X.TYPE_ID || ' PARENT_ID: ' || Y.PARENT_TYPE ); IF X.TYPE_ID = Y.PARENT_TYPE THEN -- CHILD SELECT …

Member Avatar
Member Avatar
+0 forum 1

Hi, i have a db system where i fetch results from two tables. A user and transaction table. In the transaction table i have two columns which are sender and reciver id which refence in the user table. how do i get a sender name and receiver name in a query. Thank you.

Member Avatar
Member Avatar
+0 forum 1

Hello all, I have the following code. Connection connection = getMySQLConnection(); try { Statement st = connection.createStatement(); String SQL = "SELECT ID, NAME FROM STUDENT WHERE ID=5"; ResultSet rs = st.executeQuery(SQL); System.out.println("SQL =" + SQL); while (rs.next()) { // do something... } } catch (SQLException se) { se.pringStackTrace(); } From my J2EE appliation, the query doesn't return anything, and it doesn't enter the while() loop. But, if I run the same query from within my MySQL, I get results alright. I don't know what I am doing wrong? mysql> SELECT ID, NAME FROM MY_TABLE WHERE id=5; +------------+-----------------+ | ID | …

Member Avatar
Member Avatar
+0 forum 3

when i upload image in form using cakephp at that time the image is insert but other data will not inserted...??

Member Avatar
Member Avatar
+0 forum 2

/DROP TABLE STATEMENTS/ DROP TABLE BRANCH CASCADE CONSTRAINT PURGE; DROP TABLE SCREEN CASCADE CONSTRAINT PURGE; DROP TABLE SESSIONS CASCADE CONSTRAINT PURGE; DROP TABLE MOVIE CASCADE CONSTRAINT PURGE; /CREATE TABLE STATEMENT/ CREATE TABLE BRANCH( BRANCHID CHAR(2)NOT NULL, BRANCHNAME VARCHAR2(20), BRANCHSUBURB VARCHAR2(15), BRANCHPOSTCODE CHAR(4), CONSTRAINT BRANCH_PK PRIMARY KEY(BRANCHID)); CREATE TABLE SCREEN( SCREENID CHAR(2) NOT NULL, BRANCHID CHAR(2) NOT NULL, SCREENCAPACITY NUMBER(3), CONSTRAINT SCREEN_PK PRIMARY KEY(BRANCHID,SCREENID), CONSTRAINT SCREEN_FK1 FOREIGN KEY(BRANCHID)REFERENCES BRANCH(BRANCHID)); CREATE TABLE MOVIE( MOVIEID CHAR(2) NOT NULL, MOVIENAME VARCHAR(20), MOVIEGENRE VARCHAR(20), MOVIECLASSIFICATION VARCHAR(5), CONSTRAINT MOVIE_PK PRIMARY KEY(MOVIEID)); CREATE TABLE SESSIONS( SESSIONID CHAR(4), BRANCHID CHAR(2)NOT NULL, SCREENID CHAR(2)NOT NULL, MOVIEID CHAR(2)NOT NULL, SESSIONDATE …

Member Avatar
Member Avatar
+0 forum 2

i try to redirect one controller page to another controller page using cakePHP but it not working so please help me to solve this query....

Member Avatar
Member Avatar
+0 forum 1

Hello all, I have an spring MVC app, that connects to my backend MySQL DB. I have my data source configured properly in my spring bean config XML file, as follow: <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://localhost:3406/mydb" /> <property name="username" value="root" /> <property name="password" value="passwd" /> </bean> <bean id="dbManagerBean" class="com.my.project.MyDbManager"> <constructor-arg ref="dataSource"/> </bean> <bean id="bookBean" class="com.myProject.service.Books"> <constructor-arg ref="dbManagerBean" /> </bean> The ^ above dependency injection is working correctly, as I can see the sysout print after my injection. However, when I attempt to use my JdbcTemplate.query() method, as follow, then I get the below error: @Autowired …

Member Avatar
Member Avatar
+0 forum 5

Hi everyone, I have database in cassandra . I am facing difficulties in cassandra. So I want to migrate cassandra tables in mysql. Is it possible ? How I can do this? Please help me.

Member Avatar
Member Avatar
+0 forum 1

I got two dropdowns in each product in my cart. When i select my both option my class on success is not updating the cost. It only updates when i refresh the page, i dont want to refresh my page to get the cost. this is my script... $(document).ready(function() { $('.fabric, .size').on('change', sendData); function sendData() { var id = $(this).data("id"); var fabricID = $('.fabric[data-id=' + id +']').val(); var sizeID = $('.size[data-id=' + id +']').val(); if ( fabricID !== "" && sizeID !== "") { $.ajax({ type : 'GET', url : 'calculates.php', dataType : 'json', data : { prod_id:id, fabric_id: fabricID, …

Member Avatar
+0 forum 0

From were we can generate backlinks? how its possible to generate a backlink for a day? Many are saying that it is through off page submissions?.. But do anyone know that how much submission should be done for a keyword?.. Please help me out

Member Avatar
Member Avatar
+0 forum 14

Hi, I have a column "statustime" in a table , which has values( like,10:24:10 PM,12:04:00 AM) with mixed of AM and PM . I want to display the other values by "statustime" in ASC . How to do?

Member Avatar
Member Avatar
+0 forum 7

The End.