Posts by smantscheff

How a string of images is displayed in the browser depends on their size, the css styles working on them, the width of the surrounding box, (div or table) etc. but has nothing to do with how you retrieve them from the database.
Yes, you are confused. First code a working HTML model for your purpose. Then write the code to generate it from the database.
And better start with valid HTML. Do not omit the image sizes for faster page rendering in the browser, do not omit the ALT tag for code validity.

Another cause might be that - for what reason ever - the session cookies are deleted to early. Try to find the session cookie in your browser after the error has occured - if you can find it and it's not yet expired, my assumption is wrong.

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) . "')";

You have to escape the apostrophe with a backslash.
In PHP there is the function mysql_real_escape_string with adds all the necessary backslashes for feeding text into mysql.
The query must read:

INSERT INTO tbl (Id, courseName) VALUES(123,'Apostroph\'d name');

In PHP you have to make sure that the escape character itself gets escaped:

$query = "INSERT INTO tbl (Id, courseName) VALUES(123,'Apostroph\\'d name')";

But urtrivedi already gave you the solution for that.
What's wrong with

UPDATE listing 
SET email=concat( 
  replace(title,' ', ''),
  substr( email, locate( '@', email ))
);

How do you feed those queries into mysql? The error you've got seems to indicate that you are using the wrong tools.
Also let me remark that the listings table shows a very poor design.

That cannot be that much of a problem. Show some actual testdata.

// Use the mysql week() or weekofyear() functions.

No, you cannot retrieve all week numbers between dates in one query without a stored function or procedure. What you would need is a function which returns a result set of more than one row with two input parameters for the limiting dates. I do not think it is possible.
But you could create a table with 366 values (one for each day) and then link it into a join query which selects all the day numbers between two given day numbers. From this result you might build a query which returns the week numbers for the dates in between.

It looks like the MySQL server and/or the TCP/IP connection on port 3306 do not work properly and have too long reponse times. How high is the average server load (as shown by "top")? If it is above 0.5 most of the time try to add ressources or to move to another machine.

If you do not want all processing in one query you will have to generate the whole bunch of image tags in your query. It might work like that:

SELECT s.Set_Code, GROUP_CONCAT(concat( '<img src="image.php?Product_Code=', t.Product_Code, '"/>&nbsp;' )) AS 'Image'
FROM Thumbnails t
INNER JOIN Sets s 
ON s.Component_Code = t.Product_Code
WHERE s.Set_Code="SBDCR1-WSET"
GROUP BY s.Set_Code

What for do you need the Mimetype in your query?

What you then also will not know is that you can compare multiple columns (= tupels) in a sub-select. The standard comparison is just a 1-tupel. Example:

select * from mytable where (column1, column2) in (select v1, v2 from myothertable);

This looks like rubbish.
How do you join table9 with a link from table6 to table1?
Why don't you post code which you have actually tried?
Submit a test case, complete with table structures, test data and the actual code which you are trying to implement.

Show a sample of how the HTML code should look like.

Yes.

Where you compare a value or a column with the result of a subselect this subselect must have the same number of columns as those which you are comparing - one, as a rule. So you cannot use the * operator in your subselect.
Apart from that your WHERE clause contains an error. Presumably it should read:

SELECT * FROM users AS USR WHERE USR.user_id NOT IN (
   SELECT user_id
   FROM msg_log AS LOG
   WHERE LOG.msg_id = 8

)

If you are not comfortable with sed, try EditPad Pro which has a very comfortable search and replace interface for RegEx's. It's PREG style, so that you can even use the ungreedy modifier, which can make operations quite simple. Just export the database to a textfile, load it in EditPad, do your replacements and re-import it.

For more complicated pattern match and replacement you could also dump the database to a file, run it through sed and import it again.

You could use a flat file database like SQLite. But I doubt that you will be happy with it.

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
)

Use an HTML table instead with one thumbnail image for each table cell.

Hire a programmer. If you need a mysql forum to find an image management software you should definitely not try it yourself, except you got time to burn.

Maybe you should post a fresh set of test data.
You're right, there can be no "NULL" strings in Kills/Deaths.
As you see in my previous post, your query does retrieve results. Are they the expected ones?
In your CASE structure

CASE
	WHEN ( bt.Kills IS NULL AND bt.Deaths IS NOT NULL ) THEN bt.Deaths
	WHEN ( bt.Kills IS NOT NULL AND bt.Deaths IS NULL ) THEN bt.Kills
	WHEN ( bt.Kills IS NULL AND bt.Deaths IS NULL ) THEN '0.00'
	WHEN ( bt.Kills IS NOT NULL AND bt.Deaths IS NOT NULL ) THEN bt.Kills/bt.Deaths
	END AS KDR

the last case might lead to a division by zero.

phpMyAdmin shows an italic NULL for null values and a normal NULL for the character string "NULL".
And yes, with your query and my NULL values where you had "NULL" strings the query works. These are the results:

"ID";"Username";"Group";"GUID";"FirstSeenDays";"FirstSeen";"LastSeenDays";"LastSeen";"Server";"IP";"Kills";"Deaths";"KDR"
"347";NULL;"D";"874986";"8";"6/26/2011 10:03:46 PM";"8";"6/26/2011 10:07:36 PM";"TheHonorSquad.comMIX";NULL;"2";"7";"0.2857"
"77";"0x0]Jabbe";"D";"58218627";"29";"6/5/2011 1:47:33 PM";"29";"6/5/2011 1:49:11 PM";"TheHonorSquad.comNUKE";NULL;"7";"4";"1.7500"
"344";"10user10";"D";"76287258";"8";"6/26/2011 10:01:41 PM";"8";"6/26/2011 10:03:02 PM";"TheHonorSquad.comMIX";NULL;"0";"0";NULL
"249";"1jtensai";"D";"83470562";"8";"6/26/2011 7:07:43 PM";"8";"6/26/2011 7:16:30 PM";"TheHonorSquad.comMIX";NULL;"13";"19";"0.6842"
"93";"3-Dime";"D";"74397376";"29";"6/5/2011 2:14:02 PM";"29";"6/5/2011 2:24:08 PM";"TheHonorSquad.comNUKE";NULL;"14";"37";"0.3784"
"317";"3ntry";"D";"31299509";"8";"6/26/2011 9:19:46 PM";"8";"6/26/2011 9:30:55 PM";"TheHonorSquad.comMIX";NULL;"25";"17";"1.4706"
"94";"3| Bewbs!";"D";"33287140";"29";"6/5/2011 2:14:27 PM";"29";"6/5/2011 2:22:23 PM";"TheHonorSquad.comNUKE";NULL;"8";"23";"0.3478"
"402";"666lbBongRip";"D";"7128697";"8";"6/26/2011 11:31:13 PM";"8";"6/26/2011 11:32:34 PM";"TheHonorSquad.comMIX";NULL;"1";"1";"1.0000"
"66";"A PIMP NAMED SLI";"D";"65605163";"29";"6/5/2011 1:34:01 PM";"29";"6/5/2011 2:25:11 PM";"TheHonorSquad.comNUKE";NULL;"142";"224";"0.6339"
"121";"a//Ceejack";"D";"60671101";"22";"6/12/2011 8:16:34 PM";"22";"6/12/2011 8:25:02 PM";"TheHonorSquad.comNUKE";NULL;"16";"6";"2.6667"
"120";"a//kawz";"D";"38605359";"22";"6/12/2011 8:16:01 PM";"22";"6/12/2011 8:25:00 PM";"TheHonorSquad.comNUKE";NULL;"6";"15";"0.4000"
"92";"AAC} Slutbumwal";"D";"53919019";"29";"6/5/2011 2:13:29 PM";"29";"6/5/2011 2:28:51 PM";"TheHonorSquad.comNUKE";NULL;"41";"33";"1.2424"
"139";"AbadSkidMark";"D";"73268088";"22";"6/12/2011 9:33:55 PM";"22";"6/12/2011 9:47:39 PM";"TheHonorSquad.comNUKE";NULL;"30";"18";"1.6667"
"195";"abu3zh2005";"D";"83301710";"8";"6/26/2011 5:29:53 PM";"8";"6/26/2011 5:32:28 PM";"TheHonorSquad.comMIX";NULL;"0";"0";NULL
"3";"aemccolgan";"D";"80330298";"30";"6/4/2011 9:27:54 PM";"30";"6/4/2011 9:32:14 PM";"TheHonorSquad.comNUKE";NULL;"0";"0";NULL
"454";"Akkording";"D";"58249558";"2";"7/2/2011 1:38:06 AM";"2";"7/2/2011 1:45:24 AM";"TheHonorSquad.comMIX";NULL;"8";"6";"1.3333"
"421";"alelallan38";"D";"73721619";"8";"6/26/2011 11:59:29 PM";"7";"6/27/2011 12:06:15 AM";"TheHonorSquad.comMIX";NULL;"4";"10";"0.4000"
"83";"alessandroferruc";"D";"72102181";"29";"6/5/2011 1:52:18 PM";"29";"6/5/2011 1:53:36 PM";"TheHonorSquad.comNUKE";NULL;"0";"5";"0.0000"
"39";"Aluterrian";"D";"73241673";"29";"6/5/2011 6:16:41 AM";"29";"6/5/2011 6:16:41 AM";"TheHonorSquad.comNUKE";NULL;"15";"114";"0.1316"
"257";"Always Sunny ^^";"D";"11321383";"8";"6/26/2011 7:23:55 PM";"2";"7/2/2011 1:33:31 AM";"TheHonorSquad.comMIX";NULL;"138";"65";"2.1231"
"229";"Anonymous Guy";"D";"82973606";"8";"6/26/2011 6:36:32 PM";"8";"6/26/2011 6:46:50 PM";"TheHonorSquad.comMIX";NULL;"36";"16";"2.2500"
"368";"AR52]shannon";"D";"75761405";"8";"6/26/2011 10:35:50 PM";"8";"6/26/2011 10:38:29 PM";"TheHonorSquad.comMIX";NULL;"2";"1";"2.0000"
"34";"arapaleabogdan";"D";"65475647";"29";"6/5/2011 6:02:33 AM";"29";"6/5/2011 1:49:52 PM";"TheHonorSquad.comNUKE";NULL;"62";"94";"0.6596"
"327";"arccos-iZ3dMonit";"D";"32176263";"8";"6/26/2011 9:34:06 PM";"8";"6/26/2011 10:11:26 PM";"TheHonorSquad.comMIX";NULL;"25";"46";"0.5435"
"162";"ARG] Mortadela";"D";"43271396";"11";"6/23/2011 7:55:40 PM";"11";"6/23/2011 8:06:30 PM";"TheHonorSquad.comMIX";NULL;"14";"15";"0.9333"
"243";"ArtIcuLo MorTis";"D";"29252944";"8";"6/26/2011 6:58:40 PM";"8";"6/26/2011 7:00:27 PM";"TheHonorSquad.comMIX";NULL;"3";"3";"1.0000"
"408";"askosta";"D";"72977786";"8";"6/26/2011 11:33:09 PM";"7";"6/27/2011 12:33:16 AM";"TheHonorSquad.comMIX";NULL;"54";"75";"0.7200"
"248";"Aspara GUY";"D";"11029887";"8";"6/26/2011 7:07:43 PM";"8";"6/26/2011 7:16:34 PM";"TheHonorSquad.comMIX";NULL;"6";"15";"0.4000"
"396";"ASS]Aggressor*";"D";"76384112";"8";"6/26/2011 11:19:34 PM";"8";"6/26/2011 11:39:49 PM";"TheHonorSquad.comMIX";NULL;"7";"29";"0.2414"
"312";"AVC022";"D";"80618202";"8";"6/26/2011 9:07:29 PM";"8";"6/26/2011 9:26:49 PM";"TheHonorSquad.comMIX";NULL;"33";"39";"0.8462"
"171";"Awesome Bill fro";"D";"10307757";"11";"6/23/2011 8:08:05 PM";"11";"6/23/2011 8:16:09 PM";"TheHonorSquad.comMIX";NULL;"8";"20";"0.4000"
"419";"awildbergerolmc";"D";"83339178";"8";"6/26/2011 11:56:03 PM";"7";"6/27/2011 12:33:22 AM";"TheHonorSquad.comMIX";NULL;"9";"54";"0.1667"
"205";"Axxtasy";"D";"37991452";"8";"6/26/2011 5:53:15 PM";"8";"6/26/2011 6:56:18 PM";"TheHonorSquad.comMIX";NULL;"209";"122";"1.7131"
"309";"A|X*ALIEN";"D";"63156729";"8";"6/26/2011 9:03:40 PM";"8";"6/26/2011 9:29:49 PM";"TheHonorSquad.comMIX";NULL;"30";"37";"0.8108"
"290";"bach72";"D";"83602277";"8";"6/26/2011 8:34:23 PM";"8";"6/26/2011 8:34:47 PM";"TheHonorSquad.comMIX";NULL;"0";"0";NULL
"332";"baglin3";"D";"74721682";"8";"6/26/2011 9:45:58 PM";"8";"6/26/2011 10:13:17 PM";"TheHonorSquad.comMIX";NULL;"7";"35";"0.2000"
"130";"Baldy McBald";"D";"35765719";"22";"6/12/2011 8:37:21 PM";"22";"6/12/2011 8:49:58 PM";"TheHonorSquad.comNUKE";NULL;"6";"13";"0.4615"
"403";"Barakus";"D";"177833";"8";"6/26/2011 11:31:55 PM";"7";"6/27/2011 12:13:25 AM";"TheHonorSquad.comMIX";NULL;"55";"66";"0.8333"
"49";"Bboy6763";"D";"64673329";"29";"6/5/2011 7:01:56 AM";"29";"6/5/2011 …

In your table are no null values but strings with the content 'NULL', while your case statements test for NULL.
I changed that in the test data and purged all references to the persons table (which is missing from the test data) and I get a result table with 450 values.

I don't know what you're missing, but I am missing the table structure and some test data.
Are there NULL values in the Kills and Deaths columns?

Which parameter value have you set for season_id ? How and where do you set an initial value for rank (@rownum)?

This query code works for me (i.e. it delivers results which seem to be structurally o.k.):

set @rownum = 0;
SELECT *,
t.team_name as Tm
, @rownum := @rownum+1 AS rank
, ((sum(CASE WHEN (g.home_team = t.team_id AND g.home_goals > g.away_goals)OR(g.away_team = t.team_id AND g.home_goals < g.away_goals) THEN 3 ELSE 0 END)
+ sum(CASE WHEN (g.home_team = t.team_id OR g.away_team = t.team_id) AND g.home_goals = g.away_goals THEN 1 ELSE 0 END)))  AS P
, count(CASE WHEN (g.home_team = t.team_id OR g.away_team = t.team_id) THEN 1 ELSE 0 END) as GP
, sum(CASE WHEN (g.home_team = t.team_id AND g.home_goals > g.away_goals) OR (g.away_team = t.team_id AND g.home_goals < g.away_goals) THEN 1 ELSE 0 END) AS W
, sum(CASE WHEN (g.home_team = t.team_id AND g.home_goals = g.away_goals) OR (g.away_team = t.team_id AND g.home_goals = g.away_goals) THEN 1 ELSE 0 END) AS D
, sum(CASE WHEN (g.home_team = t.team_id AND g.home_goals < g.away_goals) OR (g.away_team = t.team_id AND g.home_goals > g.away_goals) THEN 1 ELSE 0 END) AS L
, SUM(CASE WHEN (g.home_team = t.team_id) THEN g.home_goals WHEN (g.away_team = t.team_id) THEN g.away_goals END) as GF
, SUM(CASE WHEN (g.home_team = t.team_id) THEN g.away_goals WHEN (g.away_team = t.team_id) THEN g.home_goals END) as GA
, (SUM(CASE WHEN (g.home_team = t.team_id) THEN g.home_goals WHEN (g.away_team = t.team_id) THEN g.away_goals END)
- SUM(CASE WHEN (g.home_team = t.team_id) THEN g.away_goals WHEN (g.away_team = t.team_id) THEN …

http://lmgtfy.com/?q=mysql+java+primer

@ardav: Your solution is fine if (PERSON_ID, RECORD_ID) is unique, which we cannot know from the sample data. But I guess your implicit assumption is correct.

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)

Open a command line window and enter %SystemRoot%\system32\eventvwr.msc /s XAMPP/WAMPP is a pre-configured package with Apache, Mysql, PHP and Perl.

select distinct person_id from persons_records
where person_id in (select person_id from persons_records where record_id=1)
and person_id in (select person_id from persons_records where record_id=2);

or, this being mySQL:

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

But your code is fine, though.

Look in the system log for the cause of the error.
In Vista you'll find it at %SystemRoot%\system32\eventvwr.msc /s , in Linux at /var/log/messages

Show the sql statements you are using.

If you really want to achieve this kind of translation you need a higher level representation of the statements to be translated. On which level does the PHP "for" statement match the JAVA "if" statement and vice versa? If it does, it will be on the level of program flow logic. For that you will have to find an abstraction which matches both languages. There is no guarantee that for each language construct element in one language there will be a matching element in the other one. Consider the empty parameter loop "for (;;)" which is perfectly valid in C but not elsewhere.
If there were an equivalent for each language element in the other language, they would only differ in keywords and their order (and things like statement delimiters and brackets).
Beyond "Gödel, Escher, Bach" I recommend reading about Compiler construction which has to tackle this kind of problems.
I pretty much doubt that a relational database will help you much here. A formal representation in XML/XSLT might be easier.

Show some effort. Don't let us do your homework.

I never used this kind of constraint. For this purpose I'd rather use an enum field type for the class column.

Yes, but you don't need to escape the first character of the RegEx (drop the leading backslash).

You need to understand the concept of regular expressions first.
A RegEx is a pattern which matches a string of characters. You cannot convert a string to a regular expression, because there are infinitely many ways to construct a regular expression which matches a given string. For example, showthread.php and forumdisplay.php might be matched by the RegEx's

(showthread\.php|forumdisplay\.php)
(showthread|forumdisplay)\.php
[^\.]+\.php
[a-z]+\.php
.*

Which of those is the RegEx suitable for your purposes is your task to decide.

For your purpose this might do: http://www\.boards\.ie/vbulletin/(showthread|forumdisplay)\.php\?(p|f)=([0-9]+)

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

There is no such thing as a general database template. A database is a means to organize content, and its structure depends on the semantics of the content.

I do not understand your select formula, but it seems to me that you have to introduce a zone function by which you can GROUP users BY zones and then select only the first (or n-th) user from each group. So the question is: What exactly is a zone?

Presumably you will have to reset the root password which you somehow have set.
See: http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

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.

If this is a recurring task, you should use triggers or (with MariaSQL) virtual columns.

So how does your insert statement look like?
And if you have a crawler URL database with a limit of 50 characters per URL you're in trouble, anyway. Why can't you change the field size?

The problem is the column size of both the fields are varchar(50) and thats the way the author worked it out, i mean got the system working from what I have been told

What has field size to do with regular expressions and their validity?

http://www.lmgtfy.com/?q=relational+database+primer

Reqular expressions with variable thread and forum ids are:

http://www\.daniweb\.com/web-development/databases/mysql/threads/[0-9]+
http://www\.daniweb\.com/web-development/databases/mysql/[0-9]+

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.

Find out what a command line program is. Google is your friend.
Press the "windows" button, enter "cmd" into the search field and press enter. You'll get a command line window.
Now enter your mysqldump code. You'll probably have to set the system path, too.