Posts by smantscheff

If you have an account on the server, you can use cronjobs which regularly trigger actions. I recommend that you set up a PHP script which does whatever bookkeeping you need, and that you call it either directly with

php <myscript.php> 

or via the apache server using

wget http://<myserver>/<myscript.php>

If you can do that from the command line, you can put the command in your crontab which you can edit with

crontab -e

from the command line.

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!=''

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.

Learn about aggregate functions like AVG and SUM and about the GROUP BY clause in SELECTs.

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.

And you have to qualify the column id with the table name:

select publication.id ...

1. You don't need a fulltext index for queries with the LIKE operator. To make use of the fulltext index, use the MATCH operator (http://dev.mysql.com/doc/refman/5.5/en//fulltext-search.html).
2. Your code is prone to SQL insertion. Make sure that $_GET[q] contains what you expect.

You have to join the two tables:

select * from publication p, publication_issue i
where i.publication_id = p.id
and p.tags like '%news'
order by something
limit x,y

Show some test data and the code which you have tried.

Start with mediawiki, install the plugin mentioned above. If you need to store more information about your users, add columns to the user table(s) as necessary - they won't interfere with the applications. - A generic user base which fits every purpose is an unlikely beast. If you run an online shoe shop, you have to store the feet sizes of your clients, if your application is for used car dealers you might want to integrate a criminal history of the users into your app. So "generic" is hard to achieve.

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

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

What you can do:
Identify all tables in the database mysql which have a field called User. Export those tables with mysqldump. Feed them into the new database. Make sure that you run mysql with admin privileges (usually as user root). Restart the mysql server with the new database after the import. And hope for the best.

You can export the system database (users, rights etc.) with mysqldump mysql and import it again, but this may lead to all sort of trouble.

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

Probably your forgot to change the delimiter before and after your procedure definition:

DELIMITER //
CREATE PROCEDURE LoginCorrecto ...
  ...
END //
DELIMITER ;

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.

Have a look at mysql events.
http://dev.mysql.com/doc/refman/5.1/en/events.html

Is this the error-producing query? I don't think so. I asked for the content of $result in you 2nd code sample.

Show the content of $result before the error occurs.

Maybe that my query gives sql errors - it's not tested, and some brackets may be unbalanced. But it reflects the logic of your demand most accurately.

Along those lines:

select * from tableA where b = 'y' or
((not exists (select * from tableA where b = 'y')) and a = 'x') or
((not exists (select * from tableA where a = 'x' or b = 'y')))

Show your CREATE TABLE statement.

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.

http://lmgtfy.com?q=mysql+number+of+indexes
http://dev.mysql.com/doc/refman/5.5/en/column-indexes.html

The maximum number of indexes per table and the maximum index length is defined per storage engine. See Chapter 14, Storage Engines. All storage engines support at least 16 indexes per table and a total index length of at least 256 bytes. Most storage engines have higher limits.

If it runs in another interface but not in your code, then your error is in how the query is built in PHP. Debug it. Show us which query actually is posed to the server. What does the string

"SELECT  GROUP_CONCAT(IF((@var_ctr := @var_ctr + 1) = @cnt, 
                            location, 
                            SUBSTRING_INDEX(location,' - ', 1)
                           ) 
                           ORDER BY loc_id ASC
                           SEPARATOR ' - ') AS locations
    FROM location_list, 
         (SELECT @cnt := COUNT(1), @var_ctr := 0 
          FROM location_list 
          WHERE reservno='$cc'
         ) dummy
    WHERE reservno='$cc'"

really resolve to? Which value as $cc? What is the result if you feed the resulting query directly to MySQL?

Use the command line tool mysqldump to dump the whole database to a file which you can then store at a safe place.

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

Have a look at MySQL events which allow you to run code at a certain time.
http://dev.mysql.com/doc/refman/5.1/en/events-overview.html

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)

How do you submit your query to mysql? Show the PHP code? There must be some escaping done somewhere. Could be that you process the $_POST input with magic_quotes=on (always a bad idea) which would explain the escape character.

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?

Obviously you are feeding some text from a variable into a MySQL statement which contains an apostrophe (') which is not escaped.

UPDATE alters all records to which the condition applies. It does not matter if it applies only to one, to several, to none or to all records.
For multiple conditions add them to the WHERE clause with the boolean operators AND, NOT and OR, like in

UPDATE userTable SET status = 'New' WHERE year = 2000 OR year = 2001;

Your second clause is redundant.
In effect your query reads:

select * from customers where id in (...)

This can select at most one customer per id.

Instead use:

SELECT *
FROM customer 
INNER JOIN address ON customer.ID = address.customer_ID
INNER JOIN sites ON address.ID = sites.address_ID 
WHERE upper(customer.$field) LIKE'%$query%'