gizmola
-
Posts
5,882 -
Joined
-
Last visited
-
Days Won
139
Posts posted by gizmola
-
-
Are you using the same user that phpnuke is configured with?
-
I did understand you, and I believe I addressed your question. There is no magic bullet that I know of with pure mysql SQL for this. If there is a way, it would have to involve some sort of computational column, but I doubt there\'s anything that will get around the group by issue.
So you\'re really down to deciding whether one query with the group by works best, or seperate queries for each group does.
*If* there was some sort of magic way to handle this, it would have to involve a computational trick involving a mysql function, but I couldn\'t thing of any that even warranted an experiment. Typically this would be handled easily (using a cursor) if Mysql offered stored procedures, but it does not.
As to which of your two choices makes sense, as you stated, if you can limit the single result set down to a manageable number of rows my gut instinct is that will be faster than doing a whole series of seperate queries.
If I come up with any brainstorms I\'ll let you know (digs for old copy of SQL for smartys)
-
With mysql updates are limited to a single row, or multiple columns if you specify a WHERE clause that includes a constant. Typically the way to do what you\'re trying to do would be something involving a subquery. I know that mysql is somewhat in the process of adding subqueries but I\'m not 100% current with version 4.x stuff now.
The query would be structured like this (if subqueries were working):
UPDATE staffemails SET is_registered = \'0\' (quotes not needed if is_registered is a numeric column type) WHERE staff_id = (select staff_id from staff where username=\'$user\')
Alas they are not available to the best of my knowledge.
I do question your premise though. You state that this query gives you your \"row\".
SELECT *
FROM staff, staffemails
WHERE staff.users_username=\'$user\'
and staff.staff_id = staffemails.staff_id
It seems that all you are trying to do is shortcut your need to determine the user\'s staff_id. Simply select that first as I did in my subquery and do your simple update with the right staff_id in hand. It does take 2 queries, but they are short and should operate very quickly, especially if you are using the mysql_pconnect.
Even if you aren\'t mysql\'s connections are lightweight and pretty efficient.
-
-
The important question to ask, is what function your \"5 favorite things\" list will have on the site.
1. Is it possible there will be more than 5 favorite things in a list?
2. Will you be searching for all users that share the same favorite thing?
3. Is there a lot of additional information related to a \"favorite thing\" like perhaps a long description of why it\'s their favorite, or a related image or url.
If the answer to question 1, 2 or 3 is yes, then having a 2nd table is a good idea, since it will give you the flexibility and the functionality you need.
One additional question you would need to add is whether or not you need to maintain ordering of the list for a person (#1 Favorite, #2 Favorite etc). I\'m going to assume that this is how your system will operate (although at the cost of added complexity in your code).
Since favorites can\'t exist without the person for whom the favorite was created, the typical way to handle that is with a dependent relationship.
How this is structured, is that you would have a concatenated primary key (that is it will have 2 columns). The first column will be the same name and type as the primary key for your user table. The second column will act both as a way of uniquely identifying a row (needed by the PK) from any other row, and will also maintain the order of the user\'s items.
Here\'s how the tables would look:
I would need to know more about what the are \"favorites\" of. If the favorites list is standardized and people are choosing from a global list of favorites, then the structure could be further broken out to be 3 tables, but that would depend on having answers to my previous questions.
-
your query syntax should be something like this:
SELECT col1, col2 FROM tablename
WHERE col2 LIKE \'%$searchphrase%\'
-
That sounds good. I don\'t think you want one with a NULL username.
-
Ack too much information... to many questions. Try breaking em down one at a time. You will probably have more luck.
-
If you were absolutely in love with the idea of reusing keys you could implement a custom reuse system. What this would involve is that you would need a seperate table in which you stored keys everytime you deleted an item (hence freeing it\'s ID number).
When you needed to insert a new row you would:
LOCK TABLE reuseidtable
select id limit 0,1
If you get a row, you use would delete it from the reuseidtable, and unlock the table.
Then when you went to do the insert into the table, you will specify this as the key value. The interesting thing about AUTO_INCREMENT to realize, is that it only kicks in if you do NOT specify a value for the column. You can override AUTO_INCREMENT by manually specifying a value if you choose.
If you didn\'t get a key then you\'d do your typical insert without specifying a value for the PK column, and auto_increment will work as usual.
This is a lot of complication in order to save what typically is not a particularly precious commodity. Simply specify a large number type for your key and you shouldn\'t have to worry about running out of numbers for the lifetime of your application.
-
Not really. You could use a group by group & item to get the sum(hits). This would give you everything you needed in one query, but you would still have to go through it and discard the items in each group which were not the top 3 in each group. This *might* be more efficient than what you are doing (a seperate query for each group) assuming you have a limited number of total items.
The query would be something like:
select a.group, a.item, sum(hitcount) as hitcount
from item a, hits b
where b.item = a.item
group by a.group, a.item
order by a.group, hitcount
-
All design decisions like this involve tradeoffs. BBS polls are very simple because they can ask only one question, and then have a fixed set of answers that are always an enumerated list. Since they wanted to KISS, they used a compressed non-database centric structure. If your application was focused on polls, then this might be a bad decision, but since polls are essentially fluff in the context of a forum, it really doesn\'t matter. In their case they opted for a structure that is storage friendly but computationally taxing. This however allows them to focus on the forum code and be database agnostic which was probably one of their design goals.
-
Mysql is a table locking database. Selects/inserts and updates all lock the table... so queuing is an intrinsic function of the database.
Mysql uses priorities... the default is that writes are processed in the order received, and have a higher priority than reads. Nevertheless, Reads also lock the database from writes.
By default LOCK TABLES will block/wait until it gets all the table locks you are asking for.
-
Well I\'m gonna eat my words on this one a bit... mysql kinda sucks in numeric presentation. Again if you really want to do this apparently you need to specify a width attribute for the column (in your case 2) with the zerofill attribute. When you do selects, the numbers will come out with leading zeros. Again, none of this necessary if you just use a Date, which gives you the great date formatting.
-
WOW thats chinese! very complicated!
Not really... and it is the best way to handle your problem
My only quibble is that you should use $_POST rather than the $HTTP_POST...Your code btw, is completely incorrect, because all you are checking is whether your query completed or not. It would almost certainly always be true (assuming you didn\'t have a syntax error in your statement).
-
Get into the database and use mysql;
Select * from users;
Look for a * in the user name column and remove that row in the table. After doing so you may have to flush priveleges;
-
The storage and presentation are two different things in sql. You can display a number any way you want using the format() function.
I think that not using a date or datetime column is a very very bad idea... you say you want to, but not the reason why.
Nevertheless if you feel you must fight against the features the database gives you, it sounds as if any mysql integer type will work fine for you. In this case the smallest one will work (two digit day/month/year) so a tinyint sounds right.
-
While Dylan\'s way would work, the better way is to use the min() and max() summary functions.
Although it\'s unclear, you seem to suggest you have a table with at least these columns:
Table TableA
------------------------
id | INT
somedate | DATE
... etc
What\'s confusing is you state you could have multiple rows which have the same id, only the somedate would be different... or in other words, you have \"groups\" of the same id in TableA.
If that is true, then this would get you the earliest date for each id
SELECT MIN(somedate) as mindate, MAX(somedate) as maxdate FROM TableA GROUP BY id
-
drop the index and then do a create index.
-
DylanBlitz had some good thinking but he didn\'t really make clear that either you need a seperate provider table (and this would be where the provider would indicate their location (state/zip etc), or you could use Users for that, and have an attribute of that table that would indicate whether the user was a a user or a provider.
I would recommend that you might want to retain the services of an experienced developer who understands data modelling and can help you make sure you have the right database structure.
-
Try something like:
SELECT *, DATE(ts) as tsdate, TIME(ts) as tstime FROM table
-
I think it matters if someone found your phpmyadmin page, and was able to go into mysql as root because you had no password. From there it\'s quite easy to do a lot of nasty things to not only your database but to the system itself since mysql has the ability to read/write file system files.
-
You managed to pack in quite a few questions here, I\'ll try and answer them:
Hi,
I\'ve have a fairly good idea about database design, normalisation etc..
I have an unusual situation, This is a basic view of what I have
>TABLE Client id PRIMARY name > TABLE Product id PRIMARY client_id name >TABLE Project id PRIMARY product_id name
Now the way it works is, if a person enters in a new project that has no product it creates a default product with its \'name\' the same as the client \'name\'.
My problem is, if a person then edits the client \'name\' i need it to automatically update the the default product \'name\'.
Already you have lost me. The purpose of having a product table is that there should be a row for every product. You indicate that you are creating products that are really \"clients\". This is confusing and my kneejerk reaction is that your design needs work.
Now I can just add this to my php code so it does it, but is it possible to set it up in mysql so that if a certain field must always equal another field in differnt table.
This requirement would indicate there should be some relationship between the two tables, probably where one has a foreign key, but again exactly what is not clear from your description of the requirements of the application.
Also is there any way of recording relationships between tables in mysql. And then a tool to automatically create a dababase design diagram?
In RDBMS parlance, what you\'re referring to would be called declarative referential integrity. Mysql does not support this.
DRI is good for a few types of things: data driven constraints (table A has TableB.id as a foreign key, database will disallow if you attempt to insert a row where the value of TableB.id does not exist in a row in TableB) or cascading deletes (TableB is a child table of TableA, you delete a row in TableA and all the child rows in TableB would automatically be deleted). It\'s also useful to some tools in being able to reverse engineer the design of an existing database into a database design tool. One product that I\'ve recommended in the past (Dezign for databases) has an importer capability that will create a database design from an existing mysql database, however, it may not be able to actually create the relationships that were intended. While I have used Dezign for databases to design databases and generate the DDL sql to create the tables and indexes, I don\'t have the reverse engineering script so I can\'t really vouch for it\'s capabilities.
If your structure is as simple as you describe you might benefit from using somethign like Dezign and simply starting over by creating a design based on what you currently have, and then dropping and recreating it from the Dezign generated scripts. Having designed databases for years, I always use this methodology for my projects, since it guarantees that my database structure is what I think it is, not to mention the benefits of having design diagrams and table and attribute notes to refer to when I am developing.
Does Oracle(I have no experience with it) for example handle relationships better?
Derek
Yes Oracle supports a host of features that you will not find in Mysql, but then again, Oracle costs many thousands of dollars. In my opinion Oracle is the best RDBMS product available, and it\'s list of capabilities is unmatched by any competing product, however lack of DRI is not something that will prevent you from creating a functional reliable application so long as your database design is sound.
My phpfreaks tutorial has several diagrams I created with Dezign for databases that illustrate the type of thinking that should go into figuring out what your database design should be. I can see that you have done some thinking about it, and have a grasp of some of the concepts of normalization, but without knowing more about the purpose of the application, I can\'t really help you further unfortunately.
-
It\'s also a good thing to create one or more users and associate them with individual databases for the purposes of developing scripts or using your own script. This command let\'s you create a new user and give that user permissions to use a database called test_db you created with the command CREATE DATABASE test_db:
GRANT ALL ON test_db.* TO testusr@localhost IDENTIFIED BY \"testusrpw\";
-
Well you either have a mysql password for the root account or you don\'t. If you don\'t you need to set one, which is what the phpmyadmin message is warning you about.
Once you login to mysqladmin you should use the mysql database. Then issue this query to change your root password (in the phpmyadmin sql window):
UPDATE user SET Password=PASSWORD(\'somenewpw\') WHERE user=\'root\'
Once that query completes, issue this command:
FLUSH PRIVILEGES;
htaccess problem
in Apache HTTP Server
Posted
Well the problem as I understand it is that your RewriteCond\'s are Ands. So i
If 1st condition AND 2nd Condition And 3rd Condition. Hence when you have will not work, because while the http://domain.etc does match the first regex, it will not match the 2nd, and so it falls through.
Anything to do with mod_rewrite is tricky, but I\'d question why you are trying to make two different rules when one can satisfy both conditions. On the other hand, simply adding the OR to your Flags might work. If you want to try my swag at having only one rule:
RewriteEngine on RewriteCond %{HTTP_REFERER} !^$ RewriteCond %{HTTP_REFERER} !^http://(.*.|)domain.(.*)/.*$ [NC] RewriteRule (.*.(gif|jpg|avi|mpg|mp3|wmv|wma|rm)$) http://domain.com/hotlinking.php [R,NC]For your existing rule, it\'s certainly worth trying this first however:
RewriteEngine on RewriteCond %{HTTP_REFERER} !^$ RewriteCond %{HTTP_REFERER} !^http://domain.(.*)/.*$ [NC,OR] RewriteCond %{HTTP_REFERER} !^http://(.*).domain.(.*)/.*$ [NC] RewriteRule (.*.(gif|jpg|avi|mpg|mp3|wmv|wma|rm)$) http://domain.com/hotlinking.php [R,NC]