Posts by smantscheff

You need brackets around your OR clause and no apostrophes around column names:

SELECT * FROM stats WHERE 
  (ip LIKE '%192.168.0.1%' OR ip LIKE '%192.168.1.2%')
  AND topic!='' AND cat!=''

Before submitting, I tested my code on this test data:

DROP TABLE IF EXISTS `ref_track`;
CREATE TABLE `ref_track` (
  `mem_id` int(11) DEFAULT NULL,
  `ref_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `ref_track` VALUES (1,1),(1,2),(1,3),(2,1),(2,2);

with this result:

select distinct a.mem_id, 
(select count(b.ref_id) from ref_track b where b.mem_id=a.mem_id) as cnt
from ref_track a
order by mem_id
+--------+------+
| mem_id | cnt  |
+--------+------+
|      1 |    3 |
|      2 |    2 |
+--------+------+

If that's not what you expect, show your test data.

2) is not really a MySQL question. You have to study the login and authorization mechanisms of phpBB and MediaWiki and tweak them so that they point to a common user base. This is exactly what a plugin already does: http://www.mediawiki.org/wiki/Extension:Phpbb_Single_Sign-On

Your form variable is named class_ID, but the mysql column name is class_name. Looks fishy to me.

Yes again.

Yes.

I don't know of any MySQL string function which would cut any strings from the "," up to the next "-". MySQL does not offer regular expression functions which might do the trick. You can write a user defined function, though, which cuts those parts and apply it to the query result, like in

SELECT MyCutFunction(GROUP_CONCAT(location)) from location_list where reservno='00004

Or you can do it like this (adapt the group_concat separator to your needs):

drop table if exists destinations;

CREATE TABLE destinations
    (reservno int, icode int, location varchar(14));

INSERT INTO destinations
    (reservno, icode, location)
VALUES
    (00004, 00021, 'Bohol - Cebu'),
    (00004, 00022, 'Cebu - Manila'),
    (00004, 00014, 'Manila - Bohol');

select 
concat(
(select substr(location,1,locate('-',location) - 1) from destinations limit 1),
group_concat(substr(location,locate('-',location)))
)
 from destinations;

It depends on the exact semantics of your data. If the flow order is also the order of the IDs, you can do it as shown below. Otherwise you need additional criteria do determine the flow order.

select id, lecture, Table1.subject_id, date, c,
(select count(subject_id) + 1 from Table1 as t 
 where t.subject_id = Table1.subject_id
 and (t.date < Table1.date or (t.date = Table1.date and t.id < Table1.id))
 and is_deleted != 1
) as flow_id
from Table1
inner join
(
   select subject_id, count(subject_id) as c
   from Table1
   where is_deleted != 1
   group by subject_id
) as counts
on Table1.subject_id = counts.subject_id
where  is_deleted != 1
order by subject_id, date;

select id, lecture, Table1.subject_id, date, c,
(select count(subject_id) from Table1 as t 
 where t.subject_id = Table1.subject_id
 and t.date <= Table1.date
 and is_deleted != 1
) as flow_id
from Table1
inner join
(
   select subject_id, count(subject_id) as c
   from Table1
   where is_deleted != 1
   group by subject_id
) as counts
on Table1.subject_id = counts.subject_id
where  is_deleted != 1
order by subject_id, date;

See http://sqlfiddle.com/#!3/c689b/7/0

http://lmgtfy.com/?q=mysql+grant
A right to use certain mysql resources.

Your query contains the string "\'". The backslash is not recognized as an escape character by MySQL. Get rid of it. Where does it get into your code?

You cannot INSERT with a WHERE condition. You can UPDATE or DELETE with a WHERE condition.

/*    
drop table if exists customeramount;
create customeramount (custid varchar(20), date date, amount integer);
insert into customeramount values (
('1335279294867','2012-04-28','5'),
('1335518740839','2012-04-28','124'),
('1335357566946','2012-04-28','124'),
('1335279294867','2012-05-17','10');


drop table if exists customerpurchaseamount;
create table customerpurchaseamount (TransactionID integer, CustomerID varchar(20), Amount integer, Date date);
insert into customerpurchaseamount values
('1','1335518740839','5','2012-05-17'),
('2','1335518740839','10','2012-05-31');
*/
select custid, ifnull(r,amount) as result
from
(

select a.custid, 
a.amount, a.amount - (
  select sum(b.Amount) 
  from customerpurchaseamount b 
  where a.custid=b.CustomerID
) as r
from customeramount a
) x

It is not clear what your problem is. Or how you tried to solve it.

You can sort and group your table by race and forecast. If that's what you want, try
select * from races order by race, forecast

If you want an explicit "rank", count the number of horses with higher forecasts in the same race and add 1:
select a.race, a.horse, a.forecast, (select count(*) from races b where a.race=b.race and a.forecast > b.forecast) + 1 as theRank from races a;

This is a network connectivity problem. Might be that the mysql server on stoberwebcom.ipagemysql.com has to high load to response. That can happen on shared servers by other users and other applications than yours.

urtrivedi's solution assumes that the (id,domain) tupels are the same in 1X and 2X, i.e. that the IDs for all domains are the same in both tables. Since I do not believe this to be the case and since ID presumably is an auto_increment field, change the above suggestion to:

insert into 1x (domain) select domain from 2x where domain not in (select domain from 1x);

If there is always a second entry with the same 'ver' value as the latest, you might try:

select * from tblp
where (invoice IS NOT NULL)
order by latest desc, ver desc
limit 1,1

Drop the foreign key relations.
Modify all three tables to the same data type.
Re-create the foreign key relations.

You will definitively have less problems if all your stuff - database, scripts, html templates etc. - are in utf-8. If this is a hassle with phpMyAdmin, use a fine database editor like Navicat or HeidiSQL.
If your site has the proper "encoding" attributes in header and meta taga, then the search engines will correctly interpret the typographical quotes as such and not index them.

Aquilax* solution supposes that the entry date cannot be changed manually after inserting the records. If it can, the query should rather read

SELECT Score FROM scores ORDER BY [B]date[/B] DESC LIMIT 1;

What for would you like to limit the number of rows in the database?
If you really want, you can check the number of rows in PHP with a construct like

$count = mysql_num_rows( mysql( 'select * from mytable', $connection ));

and disable the insertion of new entries if count exceeds your limit.
If you want to have the limit for display purposes, use the LIMIT clause in mysql instead. Example:

select * from mytable LIMIT 10, 10;

Presumably it's

select child.cat_name, child.cat_desc, parent.cat_name
from mytable child, mytable parent
where child.pk_cat_id = parent.fk_cat_id

Maybe this could be an approach:
Make a left join from the_master_table to sentdata.
For any missing rows on the right insert a date way back in the past and sort by this virtual date as well as by actual dates. That way you get all the records which are missing from the right side as well as the oldest ones from the right side.
Like in:

select mt.recid, mt.email, ifnull(sd.datetime,'1970-01-01') as theDate, cusid
from the_main_table mt
left join sentdata sd
on mt.RECID = sd.RECID 
where cusid is null or cusid='BIM24'
order by theDate 
limit 50000;

You can move the WHERE conditions into the left join clause to exclude the undesired rows. Why don't you prepare a nice little test case with complete table structures, test data and your query for us? It might clear things up.

That's why the variable is called i1. With i2 the function reads:

CREATE FUNCTION cutQuote(t text) RETURNS text
    DETERMINISTIC
begin
        declare i1 integer;
	declare i2 integer;
        set i1 = locate( '[quote ', t );
        while (i1 > 0) do
                set i2 = locate( ']', t, i1 );
                set t = concat( substr( t, 1, i1 - 1 ), substr( t, i2 + 1));
                set i1 = locate( '[quote ', t );
        end while;
        return t;
end //

MySQL does not natively support regular expression replacements, therefore there is no built-in functionality for your problem.
You could either compile MySQL with regex support (there is an experimental plug-in somewhere on the net), or (more easily) write a function in MySQL which does the replacement.
The function would look like

CREATE FUNCTION cutQuote(t text) RETURNS text
    DETERMINISTIC
begin
        declare i1 integer;
        set i1 = locate( '[QUOTE ', t );
        while (i1 > 0) do
                set t = concat( substr( t, 1, i1 + 5 ), substr( t, i1 + 17));
                set i1 = locate( '[QUOTE ', t );
        end while;
        return t;
end;

Then use this function like in

update mytable set posts_texts = cutQuote(posts_texts);

Hire a programmer.

job_name in table activity should be job_id.

select * from employee e, job j, activity a 
where e.emp_id = a.emp_id and j.job_id = a.job_id and a.date = <selected_date>
/* optional: */ and e.name = <selected_name>

But you will never learn anything from the homework of others.

Yes.
I wonder, though, why you allow multiple payment_numbers. Make them unique and the primary key. In MySQL:

create table payment(
payment_number integer not null primary key,
payment_date date not null,
payment_amount float not null,
loan_number integer not null,
foreign key(loan_number) references loan(loan_number)
);

Get rid of the quote marks.

select 1 in (concat("'","1","'")); 
-- result: false
select 1 in (concat("'1'")); 
-- result: false
select (1 in ('1'));
-- result: true

And you don't need the group_concat neither:

select * from table_name where RIGHT_CODE IN 
(SELECT RIGHT_CODE 
 FROM table_name 
 GROUP BY `GRP_CODE` , `ROOT_DESC`
 HAVING count( `RIGHT_CODE` ) >=2
);

Look for an mysql primer which explains the uses of the INSERT, UPDATE and DELETE stamements to you. For specific help, post the relevant table structures you're using.

Oh, that looks promising. Thanks for sharing.

Your query tries to compare the equality of a single value with a whole set. Try instead:

SELECT * FROM usergroups u, links l where u.`user_id` = '1' and find_in_set(u.group_id,l.group_id);

The error message is from Apache, it has nothing to do with MySQL. Look in your apache configuration file (httpd.conf) and in the local directory's .htaccess files for access restrictions.

Have a look at the LOCAL option in the upload statement and make sure that upload.txt is readable by the mysql daemon (not by the mysql user).
See http://dev.mysql.com/doc/refman/5.1/en/load-data.html

You have to drop the foreign key relation before you can drop the index. Have a look at the output of

SHOW CREATE TABLE likes

It will show you the internal name of the foreign key constraint which you set up with your first statement. You have to drop this foreign key with

ALTER TABLE likes DROP FOREIGN KEY key_name

before you can drop the index on the foreign key field. InnoDB relies on indexes on all key columns in a foreign key constraint.

Replace

CONCAT('£', act_price) AS Amount,

by

sum(act_price) AS Amount,

You should at least alter the column names. How do you anyone expect to debug this database with names like "s1" or "regno"?

For normalization purposes the subjects should be in their own table and there should be a relation students_and_subjects.

If regno is the student's id and s1 to s4 the grades in up to 4 subjects then you can query the subject count above a certain GRADE with this query:

select regno, if(s1 >= @grade, 1, 0) + if(s2 >= @grade, 1, 0) + if(s3 >= @grade, 1, 0) + if(s4 >= @grade, 1, 0) as number_of_passed_subjects
from sem1
where regno=@student_id

I just installed phpMyAdmin to see what your problem might be, and it turns out that you are misunderstanding the format options.
Format SQL is for sql scripts which can be parsed and executed by the database.
What you want is format CSV which shows also the field and line delimiter options for your CSV format.

Add a column to your table which contains the function value, and create a trigger which computes this function value on any update of the record. Then you can use an index on this column.

You have to bracket the clauses of the OR condition:

SELECT * 
FROM rwb_bk_schedule s 
INNER JOIN rwb_bk_rooms r 
ON s.room_id = r.room_id 
WHERE s.room_status=1 
AND r.room_name=$room
AND (start_date BETWEEN $sdate AND edate OR end_date BETWEEN sdate AND $edate)

Looks more clean to me. That way you avoid doubling of attributes and keep their semantic limitations.

To me it seems that you just need a regular database backup. I would set up a daily cronjob which dumps the whole database into a file with the current date in the filename. Then you can afterwards reconstruct any state of the database for a given date. Another option would be to use binary logging - make a backup of the current database status, and by means of the binary log you can reconstruct any point in the lifetime of your database after the backup.

Your query

SELECT id FROM vwRetrieveCorporateDetails WHERE companyName=companyName AND rocNo=rocNo AND country=country

has the same names on both sides of the equation signs. Which means that all records are retrieved. You have to use variable names which are not the same as the column names.

1) Define order_no as an auto_increment field.
2) Enter ALTER TABLE mytable AUTO_INCREMENT = 70000; at the mysql command prompt.

The following script does not generate any errors on my system.

use test;

drop table if exists test_records;
drop table if exists student_detail;
drop table if exists test_metadata;
drop table if exists test_user;

CREATE TABLE `test_user` (
`u_id` int(11) NOT NULL AUTO_INCREMENT,
`u_name` varchar(45) NOT NULL,
`u_password` varchar(45) NOT NULL,
`u_level` varchar(45) NOT NULL,
PRIMARY KEY (`u_id`),
UNIQUE KEY `u_name_UNIQUE` (`u_name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
CREATE TABLE `test_metadata` (
`test_id` int(11) NOT NULL,
`test_name` varchar(45) NOT NULL,
`test_ques_num` int(11) NOT NULL,
`test_time` int(11) NOT NULL,
`examiner` varchar(45) NOT NULL,
PRIMARY KEY (`test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;
CREATE TABLE `student_detail` (
`Student_id` varchar(45) NOT NULL,
`Student_Name` varchar(45) NOT NULL,
`Class` varchar(45) NOT NULL,
`Semester` varchar(45) NOT NULL,
`u_id` int(11) NOT NULL,
PRIMARY KEY (`Student_id`),
KEY `u_id` (`u_id`),
CONSTRAINT `u_id` FOREIGN KEY (`u_id`) REFERENCES `test_user` (`u_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;
CREATE TABLE `test_records` (
`test_record_id` int(11) NOT NULL AUTO_INCREMENT,
`test_name` varchar(45) NOT NULL,
`result` float DEFAULT NULL,
`status` varchar(45) DEFAULT NULL,
`Student_id` varchar(45) NOT NULL,
`u_id` int(11) NOT NULL,
`test_id` int(11) NOT NULL,
PRIMARY KEY (`test_record_id`),
KEY `Student_id` (`Student_id`),
CONSTRAINT `xyz` FOREIGN KEY (`Student_id`) REFERENCES `student_detail`
(`Student_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;

ALTER TABLE `test_records` 
  ADD CONSTRAINT `Student_id`
  FOREIGN KEY (`Student_id` )
  REFERENCES `student_detail` (`Student_id` )
  ON DELETE CASCADE
  ON UPDATE CASCADE, 
  ADD CONSTRAINT `test_id`
  FOREIGN KEY (`test_id` )
  REFERENCES `test_metadata` (`test_id` )
  ON DELETE CASCADE
  ON UPDATE CASCADE
;

Show the output of
SHOW CREATE TABLE
for all involved tables (preferably as text, not as a screenshot).

Your solution lists only two of the the three possible status values. If you really don't care about the status you can just eliminate it from your query:

SELECT * FROM installs 
WHERE r1 = 'Standard' 
ORDER BY building, appointment_date ASC

select * from test 
where name = 'Meals' 
or parent = (select id from test where name = 'Meals');

You can do it in a query which uses 3 left joins to connect from the highest to the lower levels of the hierarchy. But this is not a general solution as the hierarchy can - in principle - be arbitrarily deeply nested. Therefore you will need a prodedure to build a general tree view model - either in an external procedural language like PHP or, preferably, in MySQL itself.