Posts by smantscheff

You "cannot access your system" -? What is your system? Your web server? Do you mean a browser connection timeout? Or which kind of error?
Are those manual mysql server restarts in the log? Or are they induced by the safe_mysqld script? If the latter, the question is, why keeps mysqld restarting?
Run "top" or "uptime" on both servers and catch the output. Which server has the really high load - the database or the web server?

How does Drupal allow for "multiple databases"? How does Drupal make use of different databases? Or do you mean that the Drupal database does not have to be on the same server as the PHP scripts? (Which is undoubtely true.)

Since you can code your own plugins/extensions in Drupal, you're completely free to access any accessible MySQL DB using PHP.
Or do you mean: Can Drupal be installed using this database on which also your vb app runs? AFAIK it can. Using its own table prefixes it can install in any database you point at.

Change the line $sql_result = mysql_query($sql,$connection); to $sql_result = mysql_query($sql,$connection) or die(mysql_error()); which will tell you more about the cause.
Since I do not see any syntactical errors I assume that you got either the database name or the table name or some field names wrong. Try select * from directory as your query.
Check your spelling (also upper/lowercase).
And why do you output "&lt;" instead of "<"? It's all ruining your html.

At my system it works:

drop table if exists banks;
create table Banks(Bank_id integer, Bank text);
drop table if exists FX;
create table FX (ID integer, Bk_id integer, currency text, trans enum('Buy','Sell'), value integer);
insert into Banks values (1,'X'),(2,'Y');
insert into FX values 
(1,1,'EUR','Buy',1),
(2,1,'EUR','Sell',2),
(3,1,'USD','Buy',3),
(4,1,'USD','Sell',4),
(5,2,'EUR','Buy',5),
(6,2,'EUR','Sell',6),
(7,2,'USD','Buy',7),
(8,2,'USD','Sell',8);

SELECT Bank_id, 
(select distinct value from fx where currency = 'EUR' and trans = 'buy' and bk_id = Bank_id) as 'EUR - buy',
(select distinct value from fx where currency = 'EUR' and trans = 'sell' and bk_id = Bank_id) as 'EUR - sell',
(select distinct value from fx where currency = 'USD' and trans = 'buy' and bk_id = Bank_id) as 'USD - buy', 
(select distinct value from fx where currency = 'USD' and trans = 'sell' and bk_id = Bank_id) as 'USD - sell'
FROM Banks
group by Bank_id
;

+---------+-----------+------------+-----------+------------+
| Bank_id | EUR - buy | EUR - sell | USD - buy | USD - sell |
+---------+-----------+------------+-----------+------------+
|       1 |         1 |          2 |         3 |          4 |
|       2 |         5 |          6 |         7 |          8 |
+---------+-----------+------------+-----------+------------+

So I assume the problem is in your test data.
Its a problematic query, anyway, because it will give no or false results if there isn't exactly one EUR/USD buy/sell row for each bank.

What's an "item", what is a "course" in your database?

Set up a nice specification and feed it to getafreelancer.com or other outsourcing platforms and let some professional do it for you.
It's not difficult, but if you don't even know where to start then three months could put you in a tight spot. But if you have a clean specification (which is a indismissable part of the work) it can be done in a month's time.
You will need at least the following tables:
- patients
- timetable (references patients),
- invoices (references patients),
- invoice_items (references invoices),
- employees
- suppliers
- supplied_items (references suppliers)
Probably some more.

I assume that the auto_increment field is filled by the system AFTER the insert.
You could instead have an update trigger and bracket the insertion of a new customer with an update statement on the row just inserted into a transaction.
Also I'd like to ask if it really makes sense to duplicate the contents of two fields in a third field?

What is the statement which you enter into the "SQL statement" field of phpMyAdmin? Can you use the mysql command line client instead?

You cannot have a field named 0 in your field list. Are you feeding the database via phpMyAdmin? How do you prepare your insert statement?

LOAD DATA cannot execute any functions on the data.
Create a temporary table with a text field instead of a data field. Import the data into the temporary table. Then add a date field and convert your text field contents into it.
The date_format function does not work on text fields, only on dates. To insert a value into the date field use the format Y-m-d h:m:i.

Or do the conversion first on the input data with a sed script (or another text conversion tool) which converts your date field in the Mysql date format.

In my windows system it's in e:\Program Files\MySQL\MySQL Server 5.1\share\mysql_fix_privilege_tables.sql and on my linux system in /usr/bin/mysql_fix_privilege_tables

Also try

show errors;
show warnings;

immediately after the "LOAD DATA". You might see some useful debugging info.

Eliminate the ORDER BY clause from your select statement.
Or add an auto_increment field to your table and ORDER BY this field.

You can import CSV files with the mysql command line client with the LOAD DATA statement (http://dev.mysql.com/doc/refman/5.1/en/load-data.html). Put the CSV file on your server so that you won't have network timeouts.
What does it mean that the entries are not "in sequence"? How do you test the sequence? Please show the CREATE TABLE statment for the table in which you want to import.

General advice: use the mysql command line client for developing and testing your queries. You will be less prone to php and programming logic errors.
You do not need 3 but only 2 primary keys: driverID and vehicleID. Both together must be unique in the bookings table, therefore the unique index.
If you set up the tables as described above (with foreign key checks) the database will take care that you cannot enter invalid values. But if you try it leads to a database error message which you don't want to show to your users.
Therefore you have to check first if the desired values are valid which you can do with the above query.
You cannot have the checking and the insert in one single statement.
If sone time may pass between checking and the insert and invalidate the checking result, you will have to bracket the check and the insert statement into a transaction.

You could also insert data with the INSERT IGNORE statement which does not throw an error on duplicate keys. You can afterwards check the number of "affected rows" by this insert query - if it is 0, no data have been inserted.

What are "3 PK's"?
You can avoid trouble by setting foreign keys in the bookings table. These make sure that you cannot enter a booking with invalid IDs. And set a unique key on the driver/car combination to avoid double bookings. Like that:

create table bookings (driverID integer not null, vehicleID integer not null,
foreign key (driverID) references drivers(driverID),
foreign key (vehicleID) references vehiclles(vehicleID),
unique (driverID,vehicleID)
);

To avoid invalid and double entries in the interface, you have to query if the values you want to enter are already in the table.
You can put it all in one query like this:

select d.driverID, v.vehicleID 
from drivers d, vehicles v
where d.driverID=$newDriverId and v.vehicleID=$newVehicleId
and not (d.driverID,v.vehicleID in (select driverID,vehicleID from bookings));

If this query has a result row, you may use $newDriverId and $newVehicleId for a new booking.

Try a HAVING instead of a WHERE clause:

SELECT a, Count(a) AS CountOfa1  FROM (SELECT Table1.a, Table1.b, Table1.c, Count(Table1.a) AS CountOfa FROM Table1 GROUP BY Table1.a, Table1.b, Table1.c) GROUP BY a
HAVING CountOfa1 > 1;

Also keep in mind that MySQL doesn't have a regex replace function. You might find the pattern, but you cannot replace it. You would have to use a user defined function - which might be the easiest way to tackle it, anyway, if you really have to solve it within MySQL and not, as griswolf suggested, with an external regex program.

I don't understand what you're aiming at. Please provide some sample data and the desired result.

Are you sure this is the right job for you? Try it with debugging and get to the solution:

SELECT approval_date
FROM itemdetail
WHERE CONCAT(substr(approval_date,7,4),'/',substr(approval_date,4,2),'/',substr(approval_date,1,2)) BETWEEN '2011/01/01' AND '2011/01/15';

Last time I tried the workbench it was for database administration (DDL) only, not for data input and editing. Heidi and Navicat are far beyond.
Don't worry about query time in development. MySQL is quite performant.

Use the ifnull function in your update clause:

update mytable set channel=ifnull(identifychannel(centre_code, company_class, stock_type),channel)

You are applying the between operator on a character field. Therefore it does not compare dates, but literal strings.
To compare dates on your data you have either to convert them to date fields or to reverse the sequence of the elements.
Try:

SELECT approval_date
FROM itemdetail
WHERE concat(substr(approval_date,6,4),'/',substr(approval_date),4,2),'/',substr(approval_date,1,2)) BETWEEN '2011/01/01' AND '2011/01/15'

(not tested)

1) Choose the package for your SERVER's operating system.
2) Regarding processing time, there is no difference as far as I know. Use MyISAM tables for optimum speed and InnoDB for relational integrity (foreign keys).
3) The Mysql manual on their site is fairly complete regarding "all the queries." For a start see http://www.webdevelopersnotes.com/tutorials/sql/mysql_primer_creating_a_database.php3 which seems to be a decent primer.
4) Mysql is in my opinion the best for website programming. Very stable and performant. Get HeidiSQL or Navicat as a development interface.

You are mistaking the PHP function parameter for a MySQL input value.
If the PHP value for $email is null, than your query reads: insert into user (username, email) values('somename', ''); because PHP will replace the null value by an empty string.
If you change this query to insert into user (username, email) values('somename', NULL); the database field email will be set to NULL instead of ''.
That's what my str_replace(...) is for.

You can prepare your insert statement in PHP and then replace all empty strings '' by NULL:

$query="insert into user (username, email) values('$username', '$email')";
$query = str_replace( "''", "NULL", $query );

So you have a fixed value you want to compare the per_db.did field to and join the two tables. Like that:

select cname from person, per_db where person.cid=per_db.cid and did=1;
select cname from person, per_db where person.cid=per_db.cid and did=2;
select cname from person, per_db where person.cid=per_db.cid and did=3;

with the last parameter walking throgh your $value array. [By the way, its a silly idea to call an array field "value". Of course it's containng values.]
But with your data, this query does not yield unique results:

select cname from person, per_db where person.cid=per_db.cid and did=1;
+-------+
| cname |
+-------+
| gamal |
| wael  |
+-------+
select cname from person, per_db where person.cid=per_db.cid and did=2;
+-------+
| cname |
+-------+
| wael  |
| samy  |
+-------+

There must be additional requirements to make it unique.

You're right, I overlooked that.
So how about that one:

select a.locationID, 
if(isnull(alternateName),locationName,if(locationName like '$term%',locationName,alternateName)) as theName,  countryCode
from locations l left join alternatenames a
on l.locationID = a.locationID
having  theName like '$term%';

Please mark this thread as solved.

Show the output of your SHOW CREATE TABLE statement and the piece of code where you try to insert something.

Maybe you want to show us your redesign?
The representation of a date field does not affect the relational structure. It's only easier to deal with if you have it in a MySQL DATE format so that you can apply the DBMS's date/time functions to it.

drop table if exists accounts;
create table accounts 
(id integer, account integer, fiscalyear integer, openingbalance float );
insert into accounts values 
('1','1202','2011','1122.00'),
('2','1210','2011','0.0'),
('3','1920','2011','1212.33');

drop table if exists transactions;
create table transactions (id integer, account integer, date date, amount float);
insert into transactions values 
('1','1210','2010-01-01','23.00'),
('2','1210','2010-01-23','566'),
('3','1920','2010-01-21','456'),
('4','1202','2010-01-14','956');

select a.account, fiscalyear, openingbalance, openingbalance + sum(amount) as closingbalance, 
sum(amount) as difference
from accounts a, transactions t
where a.account=t.account and year(date)+1 = fiscalyear
group by a.account

+---------+------------+----------------+------------------+------------+
| account | fiscalyear | openingbalance | closingbalance   | difference |
+---------+------------+----------------+------------------+------------+
|    1202 |       2011 |           1122 |             2078 |        956 |
|    1210 |       2011 |              0 |              589 |        589 |
|    1920 |       2011 |        1212.33 | 1668.32995605469 |        456 |
+---------+------------+----------------+------------------+------------+

I would prefer a solution without the priority field and with only one occurence of the search term:

select a.locationID, ifnull(alternateName,locationName) as theName,  countryCode
from locations l left join alternatenames a
on l.locationID = a.locationID
having theName like '$term%';

Learn about UDFs: http://dev.mysql.com/doc/refman/5.1/en/stored-routines.html
Are we really talking about MySQL? Or are yuo trying to solve an HTML interface problem?

The english spell I with a capital "I". And they use punctuation marks.
It is not quite clear what you want: the person entries which appear more than once in per_db? Or the person entries where the number of occurences in per_db is the same as the value of the did field?
In the first case, this might help:

drop table if exists person;
create table person (cname varchar(255), cid integer);
insert into person values ('gamal',1), ('wael',2), ('samy', 3);
drop table if exists per_db;
create table per_db (cid integer, did integer);
insert into per_db values (1,1),(2,1),(2,2),(3,2);
select cname from person p, per_db pd where p.cid=pd.cid group by cname having count(cname) > 1

If you deal with text strings in the form of day-month-year, month being a text string, you might code a function which replaces the month name by a numerical value, reverts the sequence of elements to year-month-date, and then you could use the mysql between function on the result.
Like in

select mydate(Container_Receving_Date) as crd from mytable having crd between "2010-01-01" and "2010-03-01"

where mydate is a UDF which you would have to create.

"Show the query" means: show the query code which you pass to mysql to get your data.
What field types are your "related with dates" fields? Are this character fields? Or date fields? Or what?

A date field in mysql is in effect a numerical field with some formatting. You cannot apply the regular expression function rlike to it.
For further suggestions, show your query and some input data.

You need the number of rows to check if your query had an emtpy result or not. And you need to check it to give your user an error message if the input value was invalid.

I don't see how your problem refers to mysql.
In mysql you can search for date ranges with a where clause like "mydate between '2011-02-01' and '2011-03-01'.

I don't know the C interface but assume it's the same as PHP's. If so, you could use the mysql_num_rows(res) result, or you could first check the row count by select count(*) from table3 where ID = %d

SELECT CONCAT(ifnull(PropertyRef, ''), ' ', ifnull(SaleType, ''), ' ',ifnull(UnitNo, ''), ' ', ifnull(PropertyType, ''), ' ',ifnull(ProductName, ''), ' ',ifnull(Development, ''), ' ', ifnull(Country, ''), ' ',ifnull(PriceofProperty, '')) AS PropertyRef, PropertyID, ProductTypeID FROM properties

Do you have any reason to mistrust this error message? Delete the last closing bracket where the syntax error occurs.

Yes, you have to apply ifnull to any column which you want to concat() and which might be NULL.
Try this:

SELECT CONCAT(ifnull(PropertyRef, ''), ' ', ifnull(SaleType, ''), ' ',ifnull(UnitNo, ''), ' ', ifnull(PropertyType, ''), ' ',ifnull(ProductName, ''), ' ',ifnull(Development, ''), ' ', ifnull(Country, ''), ' ',ifnull(PriceofProperty, ''))) AS PropertyRef, PropertyID, ProductTypeID FROM properties

You have not yet understood the ifnull function. Look into the manual.
Replace your blanks by empty strings (' ' => '').
Insert a blank (' ') after each ifnull() function call.
Experiment like this:

select ifnull('abc','xyz');
select ifnull('abc',NULL);
select ifnull(NULL,'abc');
select concat(NULL, 'abc' );
select concat(ifnull('abc','xyz'), ' ', 'xyz');
select concat(ifnull(NULL,'xyz'), ' ', 'xyz');

Your last ifnull clause has only one parameter [ifnull(properties.PriceofProperty)].
The ifnull() function returns the first parameter except if it is NULL. In this case the second parameter gets returned.
If the concat() function gets a NULL as one of its parameters, it will return NULL.
Therefore each column has to be guarded against NULL values if you want to use it in a concat function.

Any concatenation with NULL results in NULL.
Therefore you have to use the ifnull function:

SELECT CONCAT(ifnull(properties.PropertyRef,''), ' ', ifnull(properties.SaleType,'') ...

If Mysql isn't the best, as you say, how would you solve this in another DBMS?

Set up a join table:

create table patients_and_items 
(patient_id integer not null
,item_id integer not null
, foreign key (patient_id) references patients (patient_id)
, foreign key (ktem_id) references items (item_id)
)

;

Then query:

select p.*, i.* from patients p, patients_and_items pi, items i
where p.patient_id=pi.patient_id and i.items_id = pi.items_id;

The medication doses and special advice for taking the medicine would also belong in the join table, e.g. in a remarks text field.