ebmigue

....

Can you provide a script for your sample data and table definitions? Also, what would be the fields in the result? Another, if a company has no entry in tblstocks, would that company be included in the results? If so, what would be the values for the "last_update" and "stockid" columns?

Well, array_unique might do the trick for scalar elements. But as for non-scalar elements, PHP has this disclaimer in the manual (I found out just now): Note: Note that array_unique() is not intended to work on multi dimensional arrays. So, yes, array_unique would suffice for "simple sets" (i.e., sets who do not have nonscalar elements). But as for sets that does have nonscalar elements, it is an entirely different story. I hope PHP in the future will provide built-in set-theoretic operators and constructs.

IMO, array_unique could not do the trick. Consider: $a = array(array(1), array(2)); print_r(array_unique($a)); //outputs array(0=>array(0=>1)) Clearly, $a has two elements which are themselves arrays: array(1) and array(2). IMO, further, array_unique has a bug. It treats two distinct elements (in this case array elements) as equal!

Could you explain this more thoroughly? I can't see why tables/relvars joined with itself is a problem, considering today's SQL standards. To be sure, a table/relvar can be joined with itself without much problems, using today's leading RDBMs. In as much as I would like to offer an SQL code to help, I just can't because the "true problem" in your case is bad design (ambiguous attribute naming). However, to improve your predicament, the most viable solution is using either the "AS" operator, or the SUBQUERY construct. AFAIK, those could only be the solutions. The situation is somewhat hopeless if we bring SPs. Hope it helps.

Your problem is caused by SQL's permission to allow for duplicate attribute names and incomplete support of data types. Had this feature not been allowed in the first place, database designers would have been more cautious in naming their attributes. For instance, giving an attribute a name "ID" in the "invoices" table, where an attribute named "ID" is also found in a "customers" table could have been avoided. How so? If those two tables are JOINed, then the underlying RDBMS ought to have raised an exception (due to the fact that both tables/relvars has the same name but of different purpose/types). Then the designers would obviously avoided such situation by appropriately naming their such attributes to avoid ambiguity. Now for your problem: 1.) If you are only interfacing with/using views and SQL expressions (i.e., SELECT statements), you can always use a SUBQUERY or the simple RENAME operator (a.k.a "AS"). That has already been posted above. 2.) If you are interfacing with stored procedures, the situation is hopeless AFAIK. This has something to do with SQL lack of support for relational closure. In a conventional programming language, a programmer can write a function that, say, takes an integer then returns an integer as a result; which in effect allows you to nest your functions with user-defined or built-in functions. You can't do that in SQL stored procedures. You can't use a stored procedure then use the result as an argument in another relatively complex relational expression (i.e., SELECT statement). Disclaimer: check your version, as this might be supported. At least for MySQL that is the case. For other DBMSs you could simulate that using "table-valued functions," but which I think is not relevant in your case. In any case: Fix your queries (i.e., with the proper non-ambiguous attribute names) then "finalize" them as VIEWS. If you want to have fast and parameterized access to those views, use them with STORED PROCS. The important thing is that VIEWS must be the basis of your fix/improvements. They are more re-usable with other arbitrary SQL expressions (i.e., SELECT statements.) Hope it helps.

I assume you are using MySQL. Create a UNIQUE INDEX on the field that you do not want duplicated. (see MySQL manual). Wrap your code that actually inserts in a TRY{...}CATCH{...} block. If it enters the CATCH block, examine the error message, and perform further processing. Or just notify the user of the error message. Alternatively, before inserting, perform a SELECT * FROM t_clientes where cliente_email= '$cliente_email' It that query returned rows (i.e., row >= 1) throw a "email already used" exception.

....

I could not agree more. IMO, PHP's array is ambivalent of what it really is, i..e., it could be a set (no duplicates), list (no order, has duplicates), or ordered-list (has order, has duplicates). It depends on the context of its use. I've tried to document it here: http://www.reetudes.com/docs/latest/ch04.html#the-model-api.general-constructs.lists As for creating a set in PHP, you can try re. re is an implementation the Relational Model of data. Its basic data type is the relation, which is basically a set. Also, the usual set operators (i.e., UNION, JOIN/INTERSECT, MINUS, TIMES) are made available. Hope it helps.

Try this: SELECT DISTINCT comid, company, phone1, phone2, email, latest FROM tblcompany NATURAL JOIN ( SELECT comid, max(updated) as 'latest' FROM tblstock GROUP BY comid )_tblstock Hope it helps.

If you could tell us what you are trying to achieve, maybe we could help. It is difficult to guess your intended results by just looking at the posted SQL expression (i.e., query), since it might be incorrect. Also, please provide a sample SQL script with the sample data, for testing purposes (of course we do not need the whole data, as it might be very large; a small sample will do.).

Yep, you could omit the SELECT and $num_rows part. The UPDATE clause suffices: record(s) found according to your predicate (i.e., WHERE condition) will be updated as specified in the SET clause; if no records are found, the table/relvar will be "as is" (i.e., no update will take place.) Hope it helps.

Assuming the two tables have are exactly the same (i.e., has the same attributes), then the classical Relational MINUS ought to do the trick. A MINUS B, would return the records of A that are not found in B. However, since that is not supported in SQL, you can simulate it using WHERE EXISTS, like so: SELECT * FROM nightly_feed WHERE NOT EXISTS( SELECT * FROM apps WHERE apps.field1 = nightly_feed.field1 AND apps.field2 AND nightly_feed.field2 (AND ..etc..) ) In short, use every attribute of the two tables/relvars. Hope it helps.

Inherently there is nothing wrong with your database design. IMO, its unconventional. But it could run into problems, which otherwise will not happen if you stick to classical database design theory. You could use a DECIMAL or FLOAT data types if you insist on your method. Just curious, what will be your query when you retrieve a given person's pet(s)?

I see now. Expressed as a command: Get all teachers who teaches all the subjects selected. Is that it? If so, I think the query ought to be similar to this: $list_of_subjects_selected =implode(",", $_POST['checkbox']); $sql = " SELECT DISTINCT t.TID, t.NAME, s.SUBJECT FROM ( SELECT COUNT(SID) AS 'subj_count' FROM subjects WHERE SID IN($list_of_subjects_selected) )s INNER JOIN ( SELECT DISTINCT t.TID, t.NAME, COUNT(_s.SID) AS 'subj_count' FROM teacher_subjects ts NATURAL JOIN subjects _s NATURAL JOIN teachers t WHERE _s.SID IN ($list_of_subjects_selected) GROUP BY t.TID, t.NAME )t ON s.subj_count = t.subj_count NATURAL JOIN teacher_subjects ts NATURAL JOIN subjects s "; $sql = mysql_query($sql); while($row = mysql_fetch_assoc($sql)){ echo "<p>#{$row['TID']} ... {$row['NAME']}...{$row['SUBJECT']}</p>"; } Most probably there are "variations of the same theme" for this query. Try this first. Hope it helps. P.S. If this still won't work, we'd appreciate if you include an SQL script file in your next post with the table definitions, sample data, and desired results. THis is so we could test it. Thank you.

I would recommend a book by Chris Date, titled An Introduction to Database Systems, 8e. His experience and competence in the field is without a doubt among the top.

I'm sorry that I could not truly help you on this. Your problem concerns database normalization, and is best understood only if studied seriously. Of course, what level of normalization in your case is applicable, you alone could determine based on the specifications of your program. You can google "database normalization." For a start, IMO, "1-to-many" relationship between relvars/tables is applicable in your case. Good luck and hope it helps.

I think I didn't correctly grasped what you wanted. Anyway, is this what you want? List all teachers t that teaches the selected subjects s? If so, the query ought to be like this: $checkbox=implode(",", $_POST['checkbox']); $sql = mysql_query(" SELECT DISTINCT t.TID, t.NAME, s.SUBJECT, FROM teacher_subjects ts NATURAL JOIN teachers t NATURAL JOIN subjects s WHERE s.SID IN ($subjects); "); Hope it helps.

Try this: $sql = mysql_query("select DISTINCT t.TID, t.NAME from SUBJECTS s inner join TEACHERS_SUBJECTS ts using(SID) inner join TEACHERS t using(TID) where s.SID in($checkbox)"); In general, avoid using LEFT JOINS, as they generate the NULL "value." NULL ought not to be supported in a truly Relational System. Hope it helps.

IMO, your table schema ought to be changed to: `Recipe Name` VARCHAR(30) `Ingredient` VARCHAR(30) Then uniquely indexed with the fields Recipe Name and Ingredient (i.e., a composite candidate key.) That way, you can query the recipe that contains a given set of ingredients easily. If am to write a query for what I think is you want as results, it will be very long, and difficult to maintain, especially for recipes that have more than 3 ingredients. Hope it helps.