Posts by smantscheff

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)

I still wonder ... who would buy a wine with Elvis' name on it? "Blue Hawaii Riesling" - there once was a german wine label "Liebfrauenmilch", a fine product from our local chemical industry. They used to present it to Japanese tourists who never complained about anything.

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.

mysqldump is an OS command, i.e. an executable program, not a mysql statement. You cannot enter it in mysql but only on the command line.

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

Your queries look fine, and I don't know any query generator which would render them better or more compact or readable.
I only wonder why in your last query you use a left join instead of an inner join.

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

What is the exact error message? Also, omit the spaces between parameter flags and the parameters: mysqldump -uroot -ppass**** coke_pos > testbc.sql

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.]

Look at getafreelancer.com and rentacoder.com.

In short: don't. Hire a programmer for the job who will achieve in days what you will likely spend months with.
If you still want to do it yourself, get some working knowledge of Apache/PHP/Mysql.

No. If an employee belongs to exactly one department, there must be no reference to the organization in the employee record but only to the department.

These are not database terms related to MySQL. Ask your teacher to explain what he meant. Maybe a table with a field for headers and a field for sub-headers. Or two tables in a 1:n relation.

MySQL does not have a regex replacement function. Either you code an internal routine, or you use an external tool capable of regex replacements. I would use PHP and the preg_replace function, or export the whole database to a text editor like EditPad and replace it there, or - if you want to automate it - use sed on the export file for string replacements.

If you use foreign code, first try to understand what it is doing.
Define MAXINDEX in both your scripts. Put it in an include file so that all your scripts use the same value.
Create your input fields in a loop, running from 0 to MAXINDEX - 1. This would avoid the error of using keywordlanguage[1] more than once, too.

What if some field is left empty.. any way to integrate into this
script a value check ?

Google for "PHP input validation" as well as for "SQL injection". You will have to check against malicious input. An empty field is just an empty string in the associated $_POST variable.

How do i treat a select feautre in the PHP
meaning how do i use the ID??

The "name" attribute of the select tag is the name of the $_POST variable; the selected option is its value. If the option tag has an explicit "value" attribute, this will be the value of the post variable.

You have to provide indexes to the array variables in your form, and on the receiving side you have to walk through the arrays which those indexes constitute.
For example:

echo "<div class='kywrdformcontnr'><div class='kywrdform'>מילת מפתח:</div> <input type='text' name='keyword[1]' class='kywrd1' /></div>";

...

for ($i = 0; $i < MAXINDEX; $i++) {
$sql=sprintf("INSERT INTO KeywordList (id, keyword, keywordSearch, keywordlanguage) 
VALUES ('%s','%s','%s','%s')", 
  $_POST[id][$i],
  $_POST[keyword][$i],
  $_POST[keywordSeararch][$i],
  $_POST[keywordlanguage][$i]
  )
)";
  mysql($sql);
}

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.

Set up your table, fill it with some test data and let MySQL EXPLAIN your query. Then try an alternative approach using two left joins from the likes table into itself. See which one is more readable and more efficient (in terms of the EXPLAIN results). Try to guess which query version you are likely to understand after a few month not working on the application.

Submit a complete test case with CREATE TABLE statements and INSERT statements and the queries which do not work as expected. I'm sure that you'll find your error when you prepare the test case.

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.

Why don't your store all visits in the visit table? Why do you store the first visit date separate from the others? How do you store jobs? Show the table structure of those tables.

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.

This looks like a somewhat awkward table setup. You'd better have one table for visitdates and one for addresses.

1) Show your login/logout code.
2) Show the exact error message. Does it really say "Unidentified value" -?

In my experience timestamp fields are not really useful because you can access your database not only through your application but also through maintenance tools (like mysql command line, phpMyAdmin etc.). If you alter values with those, also the timestamp will be altered. That may be suitable for your purposes, for mine it has been more often not. I rather have a datetime field which is updated by the application explicitly.

Well, if your tables don't have timestamp fields, looking in the log files is the only general solution I know of.
And just for curiosity's sake: is it especially cool to write as you do? Or are you trying to make it as hard as possible for others to understand your desires?

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.

And obviously you don't know how to spell, too. So look in the manual. If you don't know where that is located, use a search engine or read here: http://dev.mysql.com/doc/refman/5.0/en/log-file-maintenance.html

Look in the log file.

Good design tries to facilitate processes and activities and to allow for as much freedom and variety as possible without compromising ease of use. It does not harm anyone to record day/hour/minute values instead of only days, except maybe the developer who may have to introduce a business rule which says: One book may be only retrieved once per day and user. If such a rule exists it will most probably exists because of "technical" reasons, i.e. bad design.
And well designed systems of course allow for exceptions. The other are the ones which drive us crazy at check-in terminals, ATMs, ticket vending machines and bureaucracy officials telling us of "technical reasons" why something is not possible.

Well, normalization-wise you can't. The transaction ID is an easy way to refer to the checkout table in relations, but it does not solve your original problem. The three column unique index (cardnumber,libraryid,chekcoutdate) which urtrivedi proposed is a step further, but still the user cannot checkout a book on the same day twice, even if he checked it in in between. Therefore you'd better make checkoutdate a time field resolving to minutes or seconds.

Follow the standard procedure for normalization. Make a list of all the fields which you will have to store and resolve the functional dependencies. Do not store anything which can be computed (for example the number of matches). Have a separate lookup table for each list/dropdown value. And do not mix database design with website interface considerations.

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

If you follow in urtridevis direction you will still have to delete the color and image join clauses from your query. Removing the columns from the column list alone does not change the number of result set but only which columns are retrieved from it.

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.

Your query shows the cartesian product of your tables.
Since you have two colors for the product and two images, the result has to have (at least) 4 rows.
Maybe you can use the group_concat function to pack all product and all image values in only one field which you separate later in processing. Or you delete the color and images clause alltogether if you do not need them in further processing.