Posts by smantscheff

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.

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.

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

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?

@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

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

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)

Yes it is. Have a close look at my example, especially the group clause.
If it does not work for you, show your table definition and the code.

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

Look up group_concat: select group_concat(id) from <yourtablenamehere> group by ''

If this query is ok, you can link it to the producten table like you did in your original query:

SELECT * 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`)

Why do you order on ABS(bedrag) instead of bedrag? Do you have negative values, too?

Like that?

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

And from those 4 values on the mentioned web page you want to select only the cheapest item? Or what is it what you want to do? In this case this would be two items, though. For that purpose you have to 1) do a query which selects only the cheapest price in any product group (prod_id) and then use it as a subquery when selecting the cheapest item(s) from each prod_id group.

You seem to have several problems here.
You cannot link producten.id to productlines.prod_id. There is, for example, no producten.id=10, but there is a prod_id = 10.
The article number might be a unique key, but there are values missing. And how come that one article number (=12) has two different records?
As you say, it's a mess.
Please post the table data in the mysqldump format (like you did with the structure) so that we can experiment on it.

And if you post table content, do it in the form of INSERT statements so that we can re-construct the test case.

Please post the output of the SHOW CREATE TABLE statements for the tables.
I do not yet understand how product 1 can have two different prices? Where do they come from?

Your query looks fine. What's the problem with it? [If this answer is of any help, please send article 2 to my address.]

Do a UNION query which combines all tables together with a table ID.
Make it a view.
Then query this view for ID, Title, Max(Date), Count(*) GROUP BY tableID.

Yes. PHP has a dir() pseudoclass (and some newer stuff) which allows you to read a directory. Gather all file names in an array, sort them and display them in a directory tree. And if you use any framework, there must be tons of ready-made components for that purpose.

Well, in that case you have to
- upload the image file to the server (probably a temp directory),
- move it to a location where the webserver can serve it,
- store the path of that location in the database,
- include the image file path in an image tag in your PHP generated HTML code.
Which part is it that gives you headaches?
Apart from that I found it always quite handy to store binary image data in the database and serve them directly from there. That way you do not have to worry about file paths and replication etc.

It is not clear from your post if you want to store the binary image data in the database or references to files on the server.

You got your quote marks mixed up.
Try

title='" . $row['username'] . " - " . $row['password']

If you have a well-structured programming style you can put your whole website script into one file.
If your input and your processing code is in the same file chances are higher that you (and your fellow programmers) spend less time searching for it. And re-factoring might be easier, depending on your tools.
Make sure that your code adheres to the MVC model. Do not intermix HTML display and PHP logic. Put your HTML templates for pages and forms in separate files instead of mixing them with PHP code. But don't bother to separate input logic and processing logic on the file level.
P.S.: JS form validation will do you no good if you omit the server-side form validation.

What does "fairly large" mean? 1.000 users, 10.000, a million? If it's below a million you should care primarily for coding and usage efficiency, not for performance. MySQL is quite performant itself. Avoid the wildcard * in your queries, though.
So it boils down to a question of standard table design. If each user may have exactly zero or one movie, book etc. and you have a limited number of categories, those categories belong in the user table. If the category number may be variable you should store the values in a separate table. But then you have to make sure (e.g. with a unique index) that each user has only one entry in each category.
If a user may have more than one value in a category then you definitely need a separate table for the category values. Your example of several values in one field is bad design.

Resolve the functional dependencies to get to 3NF.
Each user has exactly one password, but obviously can have several usernames on various protocols with single passwords.
So the only dependent value which should go in the primary table together with the user name is the password:

Table Users (id_user, username, password)
Table Users_and_Protocols (id_user, protocol, p_username, p_password)

If you want to have better control on the protocol names store them in a reference table:

Table Users (id_user, username, password)
Table Users_and_Protocols (id_user, id_protocol, p_username, p_password)
Table Protocols (id_protocol, protocol)

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.

Aside from utridevis proposal to get at the error itself, your code is pathetically awkward. How can you know how many rows and how many variables you will have? Construct a loop instead which reads from server1 and imports into server2 one row at a time.
Also, if I had to tackle this problem, I would not use PHP but rather the mysql command line interface. Just dump the table from server1, replace the table name (if necessary) and feed them into server2. You can do it all on one line:

mysqldump -h server1 db1 table1 | sed "s/table1/table_new/g" | mysql -h server2 db2

(You have to add your credentials with -u -and -p, of course).

Do not use the apostroph ' to mark column names. Either drop the apostroph (which I always do) or use the backtick ` for column names.

Your assumption is wrong. The value posted from a <select> field is what you code in the value attribute of the selected option. Only if there is no value attribute, the text node value of this option element will be sent as the select value.

Just read the error message carefully.
And then erase one of the double "INTO into" from your query.

You confused formatID and formatType. Instead of

$formatID = mysql_query("select formatID from format where formatType = '$format'") OR die(mysql_error());

code

$formatID = mysql_query("select formatID from format where formatID = '$format'") OR die(mysql_error());

Google for "mysql java alternative". It all depends on your requirements. With mysql you have a full-fledged SQL DB. Which parts do you not need? Maybe you might even be happy with a simple hash table in text format?

Your clients need access to a mysql server, either on their local machines or a server in their own network or a server which is accessible through the internet. So either you setup a mysql server on each client machine, or you set up a server in their network, or you open your developer machine for mysql queries from the outside. for that purpose you can use dyndns.org or selfip.com if you don't have a fixed IP address.

Pritaeas is right about unique fields. You could bracket your select and insert query into a transaction, but that would not exclude the remote possibility that someone with direct access to the database might enter duplicate names and email addresses - for example an admin who is not using your web interface but another database client. Therefore the database design should exclude duplicates where this is needed.

Do not feed bookcatalogue.sql to mysqldump but to mysql:

mysql -uroot -p testCopy < bookcatalogue.sql

Like I said: use mysqldump and the mysql command line. That way you'll get a clean database dump and all error messages.

How exactly did you export and import it and what error message do you get? Use mysqldump for export and the mysql command line interface for import. Make sure that the mysql user account on the import side has the right to create tables.

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

Erase the comma before "DESC"

Just understand this code that how it works and your problem will be solved-

Of course you will have to check first if the table already exists, maybe include a DROP TABLE statement, sanitize the input so that no malicious user can compromise or erase your whole database, and set the character sets and collating sequences according to your needs.