Posts by smantscheff

http://www.databaseanswers.org/data_models/

To move records from database A to database B just use this:

insert into B (column1, column2) select column_1, column_2 from A

That way you don't have to tackle any special characters.
Since you seem to be using PHP you don't have to write that "little function" yourself but you can use mysql_real_escape_string (as I told you already).

$query = "INSERT INTO tbl (Id, courseName) VALUES('$Id','"  . mysql_real_escape-string($Name) . "')";

This is the model. Adapt to your tables.

delete from grp_performance
where g.id in 
(
SELECT g.id
FROM grp_performance
INNER JOIN category c
ON g.cat_id = c.cat_id
)

Of course: in my 2nd query the group by clause was missing:

select person_id, group_concat(record_id) as r 
from persons_records 
group by person_id
having locate(1,r) and locate(2,r)

2smartscheff -you are right but one more table to keep the record of players in a team is also required.....

@II: MIt all depends on what you want to store - the referee's names, the number of fans, the number of rule violations, the degree of the afterplay rioting and the second spend on the loo during the breaks...a database is a means of content organization. The content which you mention (players) seemed not to be of any concern to the original poster.
By the way, I just realized that this is an old and stale thread. Uggh!

You need
1 table for all the teams (id_team, nationality, id_group)
1 table for the matches between teams (id_team_1, id_team_2, result),
various views for quarter to final matches depending on the match results of the group matches

Host localhost is not allowed something like than

It pays to read error messages a little more closely than "something like that".
What does it say exactly? Probably 'user@localhost is not permitted' or something like that.
Try to connect with the mysql command line program and mysql -uroot In a standard XAMPP installation the root@localhost account does not need a password. Otherwise, if you have set a root password on installing, connect with mysql -uroot -p<password> The you can have a look at the mysql.user and mysql.db tables to see which privileges are set.

Like urtrivedi says, MS Access used to have a decent query composing interface. Navicat has a good query builder, too. But they all are worthless compared to the real stuff: notepad.

Erase all back-ticks.
`OWNER.OWNER_ID` is interpreted as a field name, not as a table- plus field name.
Or code `OWNER`.`OWNER_ID` (urrhg)

These are 4 different wines:

"107";"1 fles kist Blue Hawaii Rielsing"
"108";"1 fles kist Blue Suede Chardonnay"
"109";"1 fles kist Jailhouse Rock Merlot"
"110";"1 fles kist ELV75 cabernet Sauvignon"

Which one do you want to show?

Maybe you are looking for this:

SELECT distinct p.* FROM productlines pl, producten p
	 WHERE pl.prod_id = p.id
    AND (prod_id,bedrag) IN
    (SELECT prod_id, min(bedrag) FROM productlines GROUP BY prod_id)
    ORDER BY ABS(`bedrag`)

Like that?

select * from productlines p
where (prod_id,bedrag) in 
(select prod_id, min(bedrag) from productlines group by prod_id)

Google for "cartesian product".
When you query two tables, the result set is the cartesian product of those two tables, which means, each row of table1 is combined with each row of table2, so you get a result set of n1 * n2 rows.
The query which you propose filters some rows from table1 and some from table2 and builds the cartesian product of those two filtered sets. But it still contains all filtered rows from table1 and all filtered rows from table2 and any combination of them.
To get to a meaningful result you will have to filter the two tables with some common criteria, for example the product model. Now you get a result set which in effect applies all filter criteria to both tables and not only to one.

Show the table definitions (the CREATE TABLE statements). And your design is flawed. Do not add a column for each category but store categories in their own table and refer to that table.

I don't know about C# but I noticed a design flaw in your query. Do not use more than one literal value to link the two tables:

SELECT * FROM `Families`, `Members` WHERE `Families`.`FamilyId`='132' AND `Members`.`FamilyId`=`Families`.`FamilyId`;

In your sample code you use two different FamilyIds which is exactly the kind of error which might occur with your query.

DESC is a reserved keyword in (my)sql used with ORDER BY. Don't name your fields as reserved keywords.

Your problem is bad database design. By which value would you like the aggregate row with idNo 12 to be sorted - 12, 13, or 14/02/2011?
You cannot have distinct rows as a GROUP BY result and at the same time retrieve the non-grouped values separately. The non-grouped values disappear in any query result with aggregate functions. MySQL does a bad job here by displaying randomly just the first non-aggregate field content it encounters.

Make sure that your query is using indexes. What does "EXPLAIN <your-update-query>" tell you?

SELECT Course.courseID,courseName,courseInstructor,if(103 in (select studentID from studentLink s where studentID=103 and s.courseID=Course.courseID),103,NULL) as ID from Course;

There must be a better solution, but I'm too tired. Have a look at the EXISTS clause in select statements.

The mysql engine treats both queries as identical. Performance differences may be due to some additional parser activity, but these should not cost more than a few CPU cycles.

Use the one which you understand better. From the MySQL point of view it makes no difference.

@ardav: One more reason to use the database instead of the PHP function. The MySQL manual states that substr() is multi-byte safe.

You have to define the referenced (foreign) tables first before you can define a table which contains a foreign key. So create the rental tables as the last one.

Use standard input/output direction from the console window. If your script resides in myscript.sql and your output should go in output.txt, enter mysql -u[I]username[/I] -p[I]password[/I] [I]database[/I] < myscript.sql > output.txt on the command line.

Make sure that you have an index for all search criteria. And combined indexes for combined search criteria. Let MySQL EXPLAIN your query to optimize it. Add more RAM to your server. Increase the key buffer and other cache variables in my.ini.

What is your aim? If you want to convert some old flat file to MySQL without disturbing much of your application, just export it to CSV and import it into the new table.
If you want to have a clean relational setup, go by the book: eliminate functional dependencies in the table setup and store each piece of information only once.
To get more help, show the table setup.

It is a question of code efficiency. You use functions 1) to avoid code repetitions and 2) to make the semantics clearer. If the function is only called one during initialization and you will not use the result at another place, don't store it in a property. If on the other hand the function will be called more than once it is good practice to store the result for re-usage, especially if this is a costly function.

You can check the HTTP_POST_FILES array for the actual file size and gracefully reject larger files. There is also a PHP setting max_upload_size which can limit the upload size with a maybe not so user-friendly error message.
The uploaded file can be converted with the GD image library. Or install ImageMagick and use a system() call for external conversion.

Use backticks for column names instead of apostrophes (or omit them if your column names don't contain blanks), but use apostrophes around the field content. Do not use a semicolon inside the statement if you submit it using PHP.

$sql = "INSERT INTO `comments` (user ,pass,comment) VALUES ('$user', '$pass', '$comment')";

If you were my fellow programmer you would use code tags for better readability. And you would boil down your code to the problem without having us to spell our way through lots of irrelevant HTML. Be a good fellow and try again.

It depends on how regular your input is. In your example, domain names consist of lower-char strings with dots in between with an optional protocol name:

$sample = " 1word www.domain.com no period domain.com period. http://domain.com what else? http://www.domain.com ";
if (preg_match_all( '~([a-z]+://)?(([a-z0-9]+\.)+([a-z]{2}[a-z]?[a-z]?))~', $sample, $matches, PREG_SET_ORDER))
  print_r( $matches );

Array
(
    [0] => Array
        (
            [0] => [url]www.domain.com[/url]
            [1] =>
            [2] => [url]www.domain.com[/url]
            [3] => domain.
            [4] => com
        )

    [1] => Array
        (
            [0] => domain.com
            [1] =>
            [2] => domain.com
            [3] => domain.
            [4] => com
        )

    [2] => Array
        (
            [0] => [url]http://domain.com[/url]
            [1] => http://
            [2] => domain.com
            [3] => domain.
            [4] => com
        )

    [3] => Array
        (
            [0] => [url]http://www.domain.com[/url]
            [1] => http://
            [2] => [url]www.domain.com[/url]
            [3] => domain.
            [4] => com
        )

)

// hey ardav, what's a mook?

Use preg_replace() to match and format the values supplied by users.
Strip the phone numbers of any non-numeric characters first. Apply

$number = preg_replace( '/[^0-9]/', '', $number );
$number = preg_replace( '/([0-9]{3})([0-9]{3})([0-9]{4})/', '($1) $2-$3', $number );

Likewise for the domain.

Study regular expressions. You can achieve your goal with the preg_replace() function which can find well-formed links and replace them with the appropriate HTML code.

Without having understood the details of your problem, I see that it cannot work: your LEFT JOIN clause does not contain any comparison:

FROM prod_contacts.cms_contacts
LEFT JOIN prod_contacts.cms_table_packholder ON cms_contacts.packholder

should probably read

FROM prod_contacts
LEFT JOIN cms_contacts ON cms_contacts.packholder = prod_contacts.packholder

array_unique() keeps the indexes intact. If not specified otherwise, these are ascending integers.
Try this instead:

$pid = array_unique($pids);
foreach( $pid as $key => $value )
  echo "$key: $value<br/>";

You do not have to remove the ' but to escape it. Precede it with a backslash.
See http://php.net/manual/de/function.mysql-real-escape-string.php

SELECT a.SO - b.SO as diff
FROM mytable a, mytable b
WHERE a.ticker=b.ticker 
AND a.date = (SELECT min(date) from mytable c where b.ticker=c.ticker and c.date > b.date)

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format

select date_format( dob,'%M');

//if accept is pressed
if ($_POST == 'Accept') {
//do this...
}
//else if reject is pressed
if ($_POST == 'Reject') {
//do this...
}

if($problem == "solved"){
  clickLink("mark_as_solved");
}

If the field may only have four values, use an enum type.

create table publisher(name varchar(20), city enum('mumbai','pune','nasik','panji'))

drop table if exists calculation;
create table calculation (one float, two float, sign enum('+','-','*','/'),answer float);
insert into calculation (one,two,sign) values (1,2,'+'),(1,2,'-'),(1,2,'*');

UPDATE calculation SET answer = 
CASE sign 
WHEN '+' THEN  one+two
WHEN '-' THEN  one-two
WHEN '*' THEN  one*two
ELSE answer=one/two
END
;

+-----+-----+------+--------+
| one | two | sign | answer |
+-----+-----+------+--------+
|   1 |   2 | +    |      3 |
|   1 |   2 | -    |     -1 |
|   1 |   2 | *    |      2 |
+-----+-----+------+--------+

Beware of "0" values in column two.
And what would be the use of calculated values in a table?

Add "FIRST" after the column name.
http://dev.mysql.com/doc/refman/5.1/de/alter-table.html

ALTER TABLE mytable ADD COLUMN xyz(text) FIRST;

You can delete all your mysql data by deleting the "data" directory in \xampp\mysql (at least that's the path in my installation). The access restrictions are stored in a database named mysql, so when you delete and re-install it you should be back on square 1.
This has nothing to do with any windows administrator privileges - mysql maintains its completely independent access restriction system.

Try to state your problem and show the relevant parts of your code instead of posting all your code and leaving us to find out what you are looking for.

1) www.netuse.de. I don't know if they have an english interface, but they have a support which does it's job.
2) How much traffic will the users generate? In general, I'd say: it doesn't matter. The standard shared machine which you can rent for a few bucks will do the job with ease.
Start with a small box. If traffic grows, move database and server to two different small machines. If it still grows, add another web server and a load balancer. If it still grows, move your (mysql) database to a cluster.

%U is a formatting parameter for the date_format function which displays the week of the input date.
To match only the current week use

WHERE WEEK(somedatefield) = week(now())

Sometimes I fear that for all the good reasons to use UTF-8, it has thrown us developers back into the times of ASCII/ANSI incompatibilities.

Make sure that *all* your connections use UTF-8. Use

mysql_set_charset( "utf8", $connection );
mysql_query("SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'", $cconnection);

bytes 0xF8 0x72 0x65 0x20 are not properly encoded UTF-8. Obviously they don't get converted on their way from the database to the XML file. Are you sure that their binary representation in the database is UTF? How did they get in the database?
MySQL's character set property can be misleading. MySQL does not automagically do character set conversions - only if client and server are announcing to use different character sets a conversion takes place. It's quite easy to be fooled to believe that your data are stored in UTF because the table or the field is defined using that character set. But if your store invalid UTF data in such a field, they stay invalid.

You can do that with a union query (I'm ad-libbing table and field names here):

select state as location from states where state like "%AR%"
union
( 
  select concat(city,', ',state) as location 
  from cities join states 
  where cities.state_id=states.state_id and city like"%AR%" or state like "%AR%"
)

In the long run it pays to have your data as structured and un-redundant as possible. So I support pritaeas' advice to use lookup tables for coded values.
Regarding efficiency and performance, with properly set indexes you will not notice any difference if you use text strings or code numbers - at least not if your row count stays below a million or so. Therefore it might be easier to store each string explicitly, but with a foreign key reference pointing to a lookup table so that you cannot store 'uncontrolled' string values.

Change if(preg_match("/^[ a-zA-Z]+/", $_POST['search']) to if(preg_match("/^[ a-zA-Z0-9]+/", $_POST['search'])

These are four different queries. If you use prepared statements, each needs its own statement.