Posts by smantscheff

If you're using xampp, mysql is also installed on your PC and you might as well use the command line. It is an invaluable tool for learning.
Later on, I'd recommend Navicat or, even better, HeidiSQL. These are fine database interfaces, while phpMyAdmin - due to its http-only limitations - is clumsy and awkward.

Install MySQL on your PC. No need for an external server. And stop nagging me with private messages.

If you want to learn MySQL, forget PHP. Look for a MySQL or plain SQL tutorial which does not use PHP and learn the SQL basics using the command line tool mysql.

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.

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

The downside is that the aggregate function is called for each row of ref_track.

The varchar field should have at least 2.000 characters. URLs can be that long. See http://stackoverflow.com/questions/417142/what-is-the-maximum-length-of-a-url

In a standard installation, the mysql console resides in
C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe

From your mysql console, run

show variables like "%dir%";

The result will contain a variable named datadir which contains the path where you should look for data files.

Yes again.

Yes.

You're trying to get a partial tree as a result. In this setup, you will need a (recursive) (user defined) function which walks all the way up from wtshirt to dress.

So test for an exact match, like in your 2nd query:

SELECT * FROM board_members where FirstName = '$member'"

So where is "Ann" in your table? With those data an exact match query for "Ann" must return an empty set.
"Order by $member" is nonsense. You order by columns, not by fixed values.

Your 2nd query is correct. If it does not return data, then the data are wrong. Show a sample.
And how is "Anna" before "Ann" in your table? You don't have any ORDER BY clause in your query, so the word "before" makes no sense here.

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.

Put them behind a firewall on an encrypted drive. Let no one access the server except your application (using MySQL grants). If you need access from outside, do it over VPN. And there are tons of other security measures which you may find here: http://lmgtfy.com/?q=mysql+security

You should know better than to test code in your database which you do not understand. Suppose I would suggest that you enter the query

DELETE * from table1;

Would you?

Now try:

EXPLAIN
SELECT prices.oen_norm, min(prices.price), a.id, a.quality
FROM table1 as prices, table1 as a
WHERE prices.price = a.price
AND prices.oen_norm = a.oen_norm
AND (a.quality = 1 OR a.quality = 2 or a.quality = 3)
GROUP BY prices.oen_norm

Then add an index on the price field and try again.

No. prices is not unknown because it is defined in the AS clause. The error is that I put the GROUP BY clause before the WHERE clause. So switch them.

Show the table structure. Show the result of EXPLAIN <your query>. Do you have an index on the price and on the oen_norm field?
Effectively you are grouping on oen_norm, take the minimum price of each group and filter all entries with that minimum price and a quality of 1, 2 or 3. So this might be more efficient:

SELECT prices.oen_norm, min(prices.price) as p, a.id, a.quality
FROM table1 as prices, table1 as a
GROUP BY prices.oen_norm
WHERE prices.p = a.price
AND prices.oen_norm = a.oen_norm
AND (a.quality = 1 OR a.quality = 2 or a.quality = 3)

To increase safety, you should also
- restrict user access to the database to the IP address of your webserver, so that no intruders from outside can read the database;
- store the access data (username, database name, password) in a separate file with a name beginning with a dot (like ".access.inc.php"), so that this file won't be served by your webserver even if PHP is deactivated.

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

Your code is flawed. AND has a higher precedence than OR, so you have to bracket the OR clauses for the desired results:

SELECT * FROM dms_docman
WHERE dmname like '%2012%'
AND (catid like "%mysearchterm%" OR catid like "%mysearchterm%")
ORDER BY dmdate_published DESC
limit $offset,$records_per_page`

You have to expand the WHERE clause:

SELECT * FROM dms_docman 
WHERE dmname like '%2012%' 
**AND catid like "%mysearchterm%"**
ORDER BY dmdate_published DESC
limit $offset,$records_per_page`

So what is your problem? Any error messages? Or what?

/*    
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

Show some test data and the unexpected as well as the expected results.

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;

The character set is a property of the server, the database, the table and the field - in that order. Each has a default which can be overwritten by the following. You can change it using the alter table syntax.
Then there is the client and the connection character set which define how the character set is translated on its way from the database to the end user. You can alter those by setting defaults in my.ini (or my.cnf) or by starting the mysql server with explicit values for those variables:

character_set_client
character_set_connection
character_set_database
character_set_filesystem
character_set_results
character_set_server
character_set_system
character_sets_dir

Unfiltered use of $_GET variables in SQL statements opens a door for SQL injection. It does not matter if the values are checked against a reference table - when it comes to checking, the harm has already been done.

The + operator does not concatenate strings. Use Concat() instead.
And beware of SQL injection. $_GET[agent_name] is likely to get you into trouble

What does EXPLAIN <query> tell you?
Also have a look at http://code.openark.org/blog/mysql/7-ways-to-convince-mysql-to-use-the-right-index to get some optimization ideas.

So better have a look at the list of built-in functions in MySQL. And you can even write your own...

Use the IF or IFNULL function:

SELECT mem_id, IFNULL(company, CONCAT(fname, ' ', lname)) as theName, addr1, addr2, city, state, zip, country FROM dc_order WHERE STATUS='W'

or

SELECT mem_id, IF(length(company) > 0, company, CONCAT(fname, ' ', lname)) as theName, addr1, addr2, city, state, zip, country FROM dc_order WHERE STATUS='W'

Probably you forgot to change the delimiter before creating the trigger.

DELIMITER //
create trigger ins_notification after insert on transaction_detail
for each row
Begin
  insert into sell_notifications(user_id, product_id, quantity)
  values(new.transaction_id , new.product_id, new.quantity);
End //
DELIMITER ;

Though this would be much easier to verify if you showed us the error message instead of let us grope in the dark.

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);

In the long run you will change the table structure. If you're now starting to get in trouble because of performance issues, chances are that you will find a workaround - which will work until the next magnitude of records has been reached.

As a workaround you might try to split the UNION subquery into single conditions. I'm not sure how the query optimizer will handle it, but it might be worth a try:

SELECT count(mem_id)
FROM members WHERE mem_status = 'F' 
AND (mem_id NOT IN (SELECT mem_id FROM position_1))
AND (mem_id NOT IN (SELECT mem_id FROM position_2))
AND (mem_id NOT IN (SELECT mem_id FROM position_3))
AND (mem_id NOT IN (SELECT mem_id FROM position_4))
AND (mem_id NOT IN (SELECT mem_id FROM position_5))
AND (mem_id NOT IN (SELECT mem_id FROM position_6))
AND (mem_id NOT IN (SELECT mem_id FROM position_7))

or

SELECT count(mem_id)
FROM members WHERE mem_status = 'F' 
AND NOT (
mem_id  IN (SELECT mem_id FROM position_1)
OR  mem_id  IN (SELECT mem_id FROM position_2)
OR  mem_id  IN (SELECT mem_id FROM position_3)
OR  mem_id  IN (SELECT mem_id FROM position_4)
OR  mem_id  IN (SELECT mem_id FROM position_5)
OR  mem_id  IN (SELECT mem_id FROM position_6)
OR  mem_id  IN (SELECT mem_id FROM position_7)
)

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

Maybe you could use an arbitrary sorting using the IDs. If the order of posts with the same date does not matter, increase the limit parameter and keep the date until there are no more posts for this date.

SELECT * FROM $logbook WHERE Date>='$Date' AND ID!='$ID' ORDER BY Date, ID LIMIT $i, 1

Increase $i until there are no more posts.

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

1) You cannot mix aggregate functions with non-aggregate fields with valid results. If you group by p.field1, what would you expect in result for the tupels
(field1, field2) = (1,1), (1,2), (2,1),(2,2) ?
Should the query return 1 or 2 as the value for field2?
The database cannot decide that. MySQL has the bad practice of just returning the first encountered value for the non-aggregate field, which is often more than mildly confusing.
2) Your left joins look like regular (inner) joins to me. Or do you really expect NULL values on the right side?
3) To get a minimum or maximum date from a subquery, use the max() or min() function rather than some order. An order clause in a subquery does not make sense except for implementation quirks like then one mentioned above.

Better show some test data (a complete test case with CREATE TABLE, INSERT and SELECT statements) for further help.

You can use the primary key in one table as a foreign key in as many tables as you like.
You get constraint errors if your data are not consistent - if you insert a foreign key which does not exist as a primary key or if you delete a primary key which is a foreign key in another table.
For further help show your query and the table structure, and the error message, of course.

There is no right way.
A standard method is a mysql script which you feed first into your development database, then, if all is fine, into your production database, and which contains all necessary statements for table creation and the initial population with test data.
You should not do that with a GUI but rather with the MySQL command line interface - that way you will know what you are doing, and you can integrate it into a batch process.

MySQL does not come with regular expression search/replace functions. Therefore you will either have to write a MySQL function which does the following:
- find a <p> in the text;
- find the matching </p> thereafter;
- replace all line breaks in between by spaces.
Or you write a piece of PHP code (or some other script language) which does the same, but with the help of regular expressions.
Or you export the table in SQL format, load it into an editor like EditPad++ and use the RegEx functions of the editor to replace the line breaks. Afterwards you import it into your table again.

You obviously do not understand the code nor what you are doing.
@ suppresses PHP error messages.
The cited line displays MySQL error messages via PHP. Since no PHP error occurs, nothing get suppressed.
If you don't want to see any errors, delete the OR clause:

$result = mysql_query( $sql );

You won't see any SQL errors then, of course. Therefore in my projects I often add a conditional error display, depending on the login status or the client's IP.

5 minutes ago I did not know that celeroo existed. Then I downloaded it and found

$result = mysql_query( $sql ) or die($sql.": ".mysql_error())

in celerooframe\inc\mysql_wrapper.php This is the line you will have to change to suit your needs.

You will have to modify your framework configuration or code. MySQL does not send any error messages to the web server if not explicitly requested. Look in the framework code for the function calls mysql_errno() or mysql_error() which will most probably lead you to the place which you will have to modify.

Having 7 position tables with the same structure is most likely a bad design flaw.
If your query returns a count of 1 with the GROUP BY clause and a higher count without it, that means that all records share the same pos_id and it doesn't make sense to count the different ones.
Better show some test data. Still better, show a complete test case (with CREATE TABLE, INSERT and SELECT statement.