MySQL Forums - MySQL Query Browser

split string into columns (no replies)

sarath chandra — Thu, 16 May 2013 08:12:10 +0000

I have the following data into table "admin_interval"

admin_interval table
--------------------
row1: "00:00:00 - 06:00:00, 22:00:00 - 23:59:59"
row2: "05:00:00 - 06:00:00"

and i want to split string into separate columns like from1, to1 from2, to2.

from1 to1 from2 to2
row1 00:00:00 06:00:00 22:00:00 23:59:59
row2 05:00:00 06:00:00
-----------------------------------------------

Could you please let me know how to do this in best way?

Split a query result (1 reply)

Marc Petr — Fri, 03 May 2013 19:00:18 +0000

Hello,

I'm trying to find a query which allows me to get the data splited.

The problem is that I have about 9 million rows in the table I use, and what I want is to get the average (AVG) of this data grouped into 150 points which I will put im a Graphic Chart (but this is another task).

I looked for a function "split" or somthing like this without result. There is a way to do it?

Thanks in advance

Update two row values in mysql (1 reply)

Jordan Pathsorious — Fri, 03 May 2013 19:02:49 +0000

Hello Everyone,

I have been trying to update value 1 and 0 for two different conditions. I tried to use if.. else but it shows me error. Can i use two different update statement in same procedure? Please help me with it.

Below is my procedure:

DELIMITER $$
CREATE PROCEDURE update11()
BEGIN
Select x From table1 WHERE count1 <= quantity;
IF x= 0 then
UPDATE table1 SET x=1;
ELSE
UPDATE table1 SET x=0;
END IF;
END $$
DELIMITER;

Increase the number of queries in history (no replies)

Erick Machuca — Thu, 04 Apr 2013 22:27:24 +0000

Hello,

Is there a way to increase the number of queries that will be available in the History?

I see that these are kept in file mysqlqb_history.xml. Is there a variable that I can set to increase the max size of this file?

I am using MySql 5.1.11 and MySqlQueryBrowser 1.2.17

Best regards,
EML

Query Update? (3 replies)

Jay Kappy — Tue, 26 Mar 2013 12:40:06 +0000

Need a query to update a field...

xxxx-001.jpg
aaaaaa-001.jpg

I need to remove the -001
Or remove the last 8 characters an replace with .jpg

xxxx.jpg
aaaaaa.jpg

Update Statement? Not sure how the syntax goes...
Thanks

Squirrel and MySQL Browser date interpretation (no replies)

Aurelien PANIZZA — Wed, 06 Mar 2013 17:04:09 +0000

Hi,

I've got a MySQL (5.0) database. There is a table with column DATE. In this field, many data (maybe all) have a wrong date format. When the data's are inserted, the date format provided is 'yyyy' (only the year). So, MySQL convert the data like : '2008-00-00' if the number '2008' was inserted.

When retrieving the date with SQL Browser, it will be display like '2008-00-00'. But when retrieving the date with Squirrel, it's displayed like '2007-11-30'. The result we want is the one displayed by MySQL Browser ('2008-00-00') but our clients use Squirrel.

Is there any option in Squirrel to get the result like the way MySQL Browser does ?

I know the application is quite ugly but, unfortunately change it, is not in the TODO list.

Thank you.

Help : WHERE FIND_IN_SET usage : list is a column (no replies)

Fabrice QUESTEL — Thu, 28 Feb 2013 15:30:41 +0000

Hello all,

I need help resolve my query

My table is 'table1' (id:INT, ref:varchar, ids:text)
---------------------
|id | ref | ids |
---------------------
|1 | a | 2, 3, 4 |
|2 | b | 2, 5, 8 |
---------------------

SELECT id, ref FROM table1 WHERE FIND_IN_SET('5',ids);

I need this result :

-----------
|2 | b |
-----------

But the result of my query is 0 result.

Thanks for your help.

FQ.

Help for a query (no replies)

Himanshu Nagar — Wed, 27 Feb 2013 05:46:47 +0000

HI Everyone,

I am novice in terms of SQL knowledge , Can some one please explain how is the query executed:

SELECT DISTINCT store_type FROM stores s1
WHERE NOT EXISTS (
SELECT * FROM cities WHERE NOT EXISTS (
SELECT * FROM cities_stores
WHERE cities_stores.city = cities.city
AND cities_stores.store_type = stores.store_type));

I am very confused on this .

Help for a query (no replies)

Luca V — Fri, 08 Feb 2013 10:15:54 +0000

Hi, i need some help for a query.

This query works:

SELECT idcustomer,idpark,iddevice,date(datetime)
FROM `general inputs data` gid
where gid.`LAST UPDATE` >
(SELECT max(`LAST UPDATE`) FROM general_inputs_data_limited )
group by idcustomer,idpark,iddevice,date(datetime)

Takes from "general_inputs_data_limited" the max value of LAST UPDATE
and select all the value of another table "general_inputs_data" that have LAST UPDATE > of that value.

Now, i would delete these selected values from the first table "general_inputs_data_limited "

I tried this but doesn't work...

delete from general_inputs_data_limited
where idcustomer,idpark,iddevice,date(datetime)
IN
(
SELECT idcustomer,idpark,iddevice,date(datetime)
FROM `general inputs data` gid
where gid.`LAST UPDATE` >
(SELECT DATE_ADD(max(`LAST UPDATE`),INTERVAL -30 MINUTE) FROM general_inputs_data_limited)
group by idcustomer,idpark,iddevice,date(datetime)
)

The 2 table have the same field:
IDCUSTOMER, IDPARK, DATETIME -> PRIMARY KEY
IDDEVICE -> PRIMARY KEY but not used
LAST UPDATE -> FIELD

Any advise ?

Query to find missing records (no replies)

Deepak Wahi — Tue, 29 Jan 2013 21:02:00 +0000

Please help. My query creates 2 tables from two different servers. I want to compare the two tables to see what claims are missing between the two table. My query is below but I keep getting an error. What am I doing wrong?

useINSLTDV
Select cr.rsv_typ_cd, c.rpt_num_txt, c.clm_num, c.lif_stts_cd, cr.rins_role_cd, sum(cr.Rsv_amt)as 'LTDV rsv_amt'
into #temp2
from t_clm c inner join t_clm_rsv cr
On (c.clm_num = cr.clm_num and c.xtrct_dt = cr.xtrct_dt)
where c.xtrct_dt ='12/31/2012' and
cr.rsv_typ_cd in ('GAAP')
and c.lif_stts_cd in ('APP', 'SUS')
and cr.feed_cd in ('0001', '0003')
Group by rsv_typ_cd, c.rpt_num_txt, c.clm_num, c.lif_stts_cd, cr.rins_role_cd

useINSGRDB
select rsv_typ_cd as 'grdb rsv_typ_cd' , xprn_num_txt, clm_num as 'grdb clm_num', RINS_ROLE_NM as 'grdb rins_role_nm', sum(rsv_amt)as 'GRDB RSV_AMT'
into #temp3
from dbo.TGRLTDV_RSV
where val_dt = '12/31/2012'
and rsv_typ_cd in ('GAAP', 'STAT')
and feed_num = '1050'
group by rsv_typ_cd, xprn_num_txt, clm_num, RINS_ROLE_NM
order by rsv_typ_cd, clm_num

select *
from #temp2 d inner join #temp3 dr
On (d.clm_num = dr.grdb clm_num)
where d.clm_num <> dr.grdb clm_num)

[SOLVED]... AND ((column1 + column2)>0) bad condition? (no replies)

António Rodrigues — Mon, 28 Jan 2013 17:45:01 +0000

Hi All,

I'm trying this query, i know that result1+result2 is 0 because it doesn't exists in rne.lgm, so the query should return 0 rows. But instead is returning 1 row.

Can anyone help here :(

SELECT registry.id_registry,
registry.registry_num,
registry.name,
registry.sent_date,
registry.nin,
rne.lgm.d_ctrl_fil,
(CASE WHEN (rne.lgm.resultado1+rne.lgm.resultado2 > 0) THEN 1 ELSE 0 END) AS resolved,
registry.ln_number,
registry.la_number,
registry.resolved_abpsl
FROM registry
LEFT JOIN rne.lgm ON rne.lgm.nsc_f = registry.nin
WHERE ((rne.lgm.resultado1 + rne.lgm.resultado2) > 0)
AND (('' = 'VIOLINO HOR') OR (CAST(registry.nin as char) = 'VIOLINO HOR'))
OR (('' = 'VIOLINO HOR') OR (registry.name LIKE '%VIOLINO%'))
OR (('' = 'VIOLINO HOR') OR (registry.registry_num LIKE '%VIOLINO HOR%'))
ORDER BY registry.sent_date DESC

Return Last Row by Each ContactID (no replies)

Paul Henderson — Wed, 16 Jan 2013 09:08:32 +0000

Hi All,

I dont know if this is possible.
I am trying to run a query that will show me the last row in a table for each individual contactid in a given account.

something like:
SELECT contacitd, completeddate FROM activities where accountid = 'AAXA-SZMY8K' ORDER BY contactid DESC LIMIT 10;

The problem is i dont always know how many contactid's will be in a given accountid, so the DESC LIMIT 10 wont work for me.
Also, the above query returns the last 10 rows of a given accountid regardless of contactid, so it usually returns the last 10 rows for a single contactid rather than show the time/date of the last activity for each contactid.

Im running this to export to a csv file, so any suggestions would be welcome.

Thanks,

find and replaced (1 reply)

carlos evora — Sat, 12 Jan 2013 04:31:07 +0000

Hello Im new on mysql

I need to change question marks (?) to a dot (.) on my DB under the fiels products_name and products_description
can some body help me the the script

see here the old server here you dont see question marks (?)
http://74.54.20.226/~medic/shopping/...roducts_id=159

see here the new server here you see question marks (?)
http://www.medicalequipmentfl.com/sh...roducts_id=159

SQL Join (1 reply)

shrawan sahu — Sat, 12 Jan 2013 04:21:22 +0000

Hi,

I need help to to collect data from feedback tables.

I have tables like

1. tbl_parent_child_details

fld_parent_id fld_parent_name fld_child_name fld_class fld_section
1 Tom Ray 8 A
2 Dev angelina 12 B

2. tbl_feedback_children

fld_parent_id fld_quetion_id fld_ans
1 1 1
1 2 3
1 3 2
2 1 4
2 2 5
2 3 2

2. tbl_feedback_admini

fld_parent_id fld_quetion_id fld_ans
1 1 1
1 2 3
1 3 2
2 1 4
2 2 5
2 3 2
__________________________________________________

Now my requirement is I want data of every parent in one row like:

Parent child q1 q2 q3 q4 q5 q6
tom ray 1 3 2 1 3 2
dev angelina 4 5 2 4 5 2

Any help is highly appreciated.

Thanks,
Shrawan

MySQL Error in Firefox (no replies)

Niamh Woodward — Wed, 12 Dec 2012 07:46:56 +0000

Hi, I have this one concern regarding MySQL and I'm hoping that I posted in the right topic. I'm using firefox as my default browser. My concern is that why do I often get Mysql error everytime I'm gonna few sites in Firefox?

Is there any addon in mozilla which help you to manage database editors? (no replies)

Leon Victor — Wed, 05 Dec 2012 12:33:11 +0000

Hello all, I am working on online projects and was wondering for an addon in mozilla through which I can store my database and even access it from everywhere. So please help me out if such kind of addon exist.

The configuration file mysql query browser (no replies)

michal hyzy — Mon, 12 Nov 2012 21:20:03 +0000

Hi,

I need to reinstall the operating system (OS X). I currently have several connections defined in the Mysql Query Browsrer.

Can I transfer all connections defined in the program?
Where is the configuration file of this program?

Thank you for the information.

MySQL Connection with Eclipse (no replies)

Star fish — Tue, 23 Oct 2012 03:46:05 +0000

Hi All,

I want to connect MySQL database with eclipse environment for Struts application, I need code to do so. Can anyone solve this issue if possible?

Appreciate ur replies

Thanks and Regards

How to find Month Difference of two given dates (no replies)

Syam lal — Wed, 10 Oct 2012 20:23:38 +0000

Can anybody tell me how could I get the MONTH Difference of two given dates? In fact, period_diff will not work at all!!!

Ex. date1 = '2009-05-30' & date2 = '2009-06-05'

We all know that the answer is 0, but how to get it using Query in MySQL???

Any help would be appreciated?

Syam

Need Help! MySQL error (no replies)

Russell Hibbing — Sat, 06 Oct 2012 18:53:21 +0000

[SQL] DROP TABLE IF EXISTS BCustOrd;
Affected rows: 0
Time: 0.172ms

[SQL]
CREATE TABLE IF NOT EXISTS BCustOrd LIKE BCustOrderStructure;
Affected rows: 0
Time: 0.187ms

[SQL]
INSERT INTO BCustOrd SELECT DISTINCT
TRUNCATE(mn6l0_redshop_orders.order_id,0) AS order_id, TRIM(mn6l0_redshop_order_item.user_info_id) AS users_info_id,
TRUNCATE(mn6l0_redshop_orders.user_id,0) as user_id, TRUNCATE(order_total,2) AS order_total,
TRUNCATE(coupon_discount,2) AS coupon_discount, TRIM(mn6l0_redshop_orders.order_status) AS order_status,
FROM_UNIXTIME(mn6l0_redshop_orders.cdate,"%m-%d-%Y"), TRIM(mn6l0_redshop_orders.customer_note) as customer_note,
TRUNCATE(product_id,0) AS product_id, TRUNCATE(order_info_id,0) as order_info_id, TRIM(firstname) AS firstname,
TRIM(lastname) AS lastname, TRIM(address_type) AS address_type, TRIM(address) AS address,
TRIM(city) AS city, TRIM(state_code) AS state_code , TRIM(zipcode) AS zipcode, TRIM(phone) AS phone, TRIM(user_email) AS user_email,
SPACE(0) AS Church_Code
FROM mn6l0_redshop_orders LEFT JOIN (mn6l0_redshop_order_item, mn6l0_redshop_order_users_info)
ON (mn6l0_redshop_orders.order_id = mn6l0_redshop_order_item.order_id AND
mn6l0_redshop_orders.order_id = mn6l0_redshop_order_users_info.order_id)
WHERE mn6l0_redshop_orders.order_status = "C" AND mn6l0_redshop_order_item.product_id < 3 AND mn6l0_redshop_orders.order_id > 9
ORDER BY users_info_id, address_type;
Affected rows: 16
Time: 0.188ms

[SQL]

UPDATE BCustOrd
SET Church_Code = UPPER(mn6l0_extendedreg_users.cf_cc_code)
WHERE mn6l0_extendedreg_users.user_id = BCustOrd.user_id;
[Err] 1054 - Unknown column 'mn6l0_extendedreg_users.user_id' in 'where clause'

This is the structure of mn6l0_extendedreg_users:

user_id acceptedterms approve overage ip_addr form_id notes last_activation_request approve_hash terminate_hash block_tstamp cf_cc_code
42 0 1 0 178.199.48.63 0/0/0000 00:00:00 0/0/0000 00:00:00
43 0 1 0 0/0/0000 00:00:00 0/0/0000 00:00:00
47 0 1 0 0/0/0000 00:00:00 0/0/0000 00:00:00
50 0 0 0 0/0/0000 00:00:00 0/0/0000 00:00:00
135 0 1 0 70.196.2.40 2 0/0/0000 00:00:00 0/0/0000 00:00:00 KUPCLA
134 0 1 0 72.146.54.212 2 0/0/0000 00:00:00 0/0/0000 00:00:00 KUPCLA
133 0 1 0 68.18.4.183 2 0/0/0000 00:00:00 0/0/0000 00:00:00 KUPCLA

how to insert Hindi excel data into MySQL database in Hindi Language (1 reply)

chakravarthy l — Fri, 02 Nov 2012 04:04:16 +0000

I have excel with data which is in Hindi language here it is Ex:
FileNumber
----------
13@e 1&100@98 (this is in Hindi format)
Now I want to insert this data into mysql database when I done it, it is loading as looking as it is but i want to insert this with Hindi which I need to print for Client. I have used mysql cmd to load the data.

ft_min_word_length (no replies)

Akshay Vithalkar — Thu, 27 Sep 2012 09:23:49 +0000

Hi All,

I as newbie in MYSql i got the task from my senior ones to change the ft_min_word_length = 1 which is situated in the MY.ini file in windows. i will discuss the wholw scenario,
i have Windows server 2008 R2 installed on my machine with WAMP. i have got an message to set ft_min_word_length to 1.
I have installed MYSql 5.2 and i havnt see my.ini file anywhere.i have seen below files,
My-huge.ini
My-small.ini
My-medium.ini
My-Large.ini
i tried to find ft_min_word_length in all above file but didnt see any like that,
what that means i have add this setting by self?
and if i have then where?
i have also the syntax of that which is like:
#set-variable = ft_min_word_length=3
Please help for this this is high priority issue for me .
Plese help me out..

Thank You,
Akshay V

delete quary (no replies)

amir Bolourchian — Sun, 23 Sep 2012 13:07:43 +0000

Hi
I have a problem
I want to delet the last 3 months of my database tables
can you please give me a query for phpmyadmin that can delete all table contents of last 3 months?

regards

Compare with previous tuple(Row) (no replies)

M Zubair Haroon — Thu, 20 Sep 2012 20:30:06 +0000

Hi! I 'm stuck on a very basic problem, I want to skip the row which has duplicate values over three columns.

Table feeds

id,type,user_id,friend_id,date

1, 'friend', 4 , 5 , 5/5/2010
2, 'friend', 5 , 4 , 5/5/2010

Now since both have same thing, so I want to pick them only as a 1 row not 2.

Now this is how the data is saved (I can't change the saving module)

Need help, thanks

Could not connect to specific instance. MySQL Error Number 2003 (no replies)

Robin Briggs — Mon, 10 Sep 2012 18:41:03 +0000

I have MySQL Installed on a Virtual Machine, and I needed to access the machine from other machines on the network. When I attempted to Open Query Browser or anything else, I would get the MySQL log-in Window.

I entered the:
- hostname (The Machine Name, or if you want to try with an IP you can do this by typing "ipconfig /all" from the command line and check your systems, IP and Hostname),
- the username, in my case I used "root"
- the password
- the default port 3306 is already populated the box, and
- the schema, which is the name of the DB in my case

Every time, The system hangs for about a minute and then throws an error that says:

Could not connect to the specified instance.

MySQL Error Number 2003
Can't connect to MySQL server on 'SGSS-VM1' (10060)

If you want to check the network connection, please click the Ping button.

So I clicked the ping button and the ping worked fine. To correct this problem, I went to the host machine and put in two firewall rules. My windows firewall was "On" and I figured it had this port blocked. Installing MySQL (at least the versuion I had) did not put in firewall rules for this default port "3306". Now I am working in Windows 7 here. Sorry I forgot to mention that.

I went into the Control Panel and navigated to the Firewall Settings page. In Advanced Security,
1. I clicked on "Inbound Rules"
2. and added a "New Rule"
3. the Type was "Port".
4. In the "Specific local ports:" box I put in 3306.
5. On the Next Page, I clicked "Allow the Connection"
6. Then on the next page all three boxes were checked "Domain", "Private", and "Public" then on the next page
7. I put in a Name and a description. This can be whatever you like.
8. I clicked finish.

Repeat this same process for your "Outbound Rules".

This fixed the problem for me. The error you

How do you find out which auto increment was assigned on the last insert? (2 replies)

smith james — Tue, 30 Oct 2012 10:44:06 +0000

plz send me any answer...

Need help building mysql query in phpmyadmin (3 replies)

ben bibik — Sat, 01 Sep 2012 01:48:30 +0000

I need to view some information from a wordpress table but it is way over my head because it is contained in a meta_value

In a wp_usermeta table there are several keys,
umeta_id
user_id
meta_key
meta_value

meta_key has a mgm_member_options value and I need to query all the data stored in meta_value for mgm_member_options.

Any idea how I can build a query in phpmyadmin to view all this data in a table row structure?

Here is the screenshot of what I'm talking about:

Thanks
Ben

how to make short time for select (no replies)

andre gunawan — Mon, 27 Aug 2012 05:01:57 +0000

how to make short time when i'm doing select rows with many tables for selected (there are concate and substring)
in example:

SELECT dpo.tanggal, dpo.bukti, dpo.qtyminta, dpo.qtybeli, dpo.nosppb, nomorsppb, tpo.supplier, tpo.nopo, CASE WHEN (tpo.supplier = 'CASH') THEN sup.supplier ELSE sup.nama END AS namasupp, CONCAT(dpo.bukti,'-', SUBSTRING(dpo.nosppb, 3, 4), '/', LEFT(dpo.nosppb, 2), '/',RIGHT(dpo.periode, 4)) as npo FROM dtlpo dpo, trnpo tpo, supplier sup WHERE tpo.bukti = dpo.bukti AND tpo.periode = dpo.periode and sup.supplier = tpo.supplier

in my case there are 40029 rows selected...

Query too slow (no replies)

Bilel Yahia — Sun, 26 Aug 2012 13:30:35 +0000

Hi,

i want to optimise a query that which take about 2 minutes to display results.
here is the code

Bonjour à tous,

je cherche à optimiser une requête qui prends plus de 87 secondes pour s'exécuter.
merci par avance pour vos idées et suggestion:

SELECT ID, Titre, Prix, Contact, Description,

(SELECT valeur from details where DT.ID = details.ID_Annonce and rubrique='Kilométrage') as Kilometrage,
(SELECT valeur from details where DT.ID = details.ID_Annonce and rubrique='Carburant') as Carburant,
(SELECT valeur from details where DT.ID = details.ID_Annonce and rubrique='Boîte de vitesse') as Boite_Vitesse,
(SELECT valeur from details where DT.ID = details.ID_Annonce and rubrique='Année-modèle') as Annee_modele,
Region, Ville, CodePostal

FROM annonces as DT 

WHERE
DT.Categorie like '%voiture%'
AND (DT.`Export` = '0') AND (DT.`Contact` <> '') AND (char_length(DT.`Contact`) = 10)

SQL QUERY to MYSQL (1 reply)

neha prajapati — Sat, 18 Aug 2012 02:52:21 +0000

Hi

I'm using this query in sqlserver and need same query in mysql .. i'm new in mysql .. please help me out to solve this query..

DECLARE @sql AS nvarchar(max)
set @sql=''
set @sql='Select * From('
SELECT @sql = @sql + N'
SELECT Lead_id,DOB,EP,NMI,POT,ID,IDno,IDexpiry,Pension_Card,Pension_Expiry,Pension_Form,Solar_Form FROM ['+ SCHEMA_NAME(schema_id) +'].[' + name + ']
UNION ALL '
FROM information_schema.`TABLES` T where table_name like 'Custom%'
SELECT @sql = SUBSTRING(@sql, 0, len(@sql)-9)
print @sql