FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


[MySQL] Complicated query





Stubru Freak
I have a database like this:
Quote:
Code:

+-----------------+------------------+--------------------+------------+
+       URL       +       Date       +       Number       + Other info +
+-----------------+------------------+--------------------+------------+


So basically I want the first three fields (URL, Date and Number) to be unique, so that if URL and Date contain a value they never contained before, Number is 0, but if another row with the same URL and Date is inserted, the Number starts to increment.
This is an example of how the table should look:

Quote:
Code:

+-----------------+------------------+--------------------+
+       URL       +       Date       +       Number       +
+-----------------+------------------+--------------------+
+   google.com    +    2006-04-13    +         0          +
+-----------------+------------------+--------------------+
+   google.com    +    2006-04-14    +         0          +
+-----------------+------------------+--------------------+
+   google.be     +    2006-04-14    +         0          +
+-----------------+------------------+--------------------+
+   google.com    +    2006-04-14    +         1          +
+-----------------+------------------+--------------------+
+   google.com    +    2006-04-14    +         2          +
+-----------------+------------------+--------------------+
+   google.be     +    2006-04-14    +         1          +
+-----------------+------------------+--------------------+
+   google.com    +    2006-04-14    +         3          +
+-----------------+------------------+--------------------+
+   google.com    +    2006-04-15    +         0          +
+-----------------+------------------+--------------------+


I'm not really experienced in SQL, and I have no idea where to start for a query like this.
I would really like to do this in one query.

Could anyone please help me?

Thanks,
Frederik Vanderstraeten
mathiaus
This is rather different. I'll show you how I'd code that but would it not be easier to edit a row and increment the notes rather than add a new row and increment?

http://pastebin.com/658296
Kaneda
mathiaus is right on both counts... You can do it that way, or you could use (by way of normalization) just one row for each URL/Date combination, give it an ID and keep the Number and other info in another table along with that ID.

Anyway, since you said you'd really like to do this in one query... you can modify the query to this (I split it into more lines to make it slightly more readable):

Code:

INSERT INTO $dbtable (URL, Date, Number, Other info)
VALUES (
  '$insert_url',
  '$insert_date',
    (SELECT COUNT(*) FROM $dbtable WHERE URL = '$insert_url' AND Date = '$insert_date'),
  '$insert_other'
)


... and drop the first query (and handling of it) from the script. SELECT COUNT(*) is a good deal faster than SELECT *, simply because the DB engine doesn't need to retrieve any dataset into memory (and send them back to PHP) in order to get the rows counted.
Stubru Freak
Thanks, but the query with subquery doesn't work, the server 2 mysql version doesn't support subqueries.

I guess I'll have to use 2 queries then.

The reason I want this, is because I want the information to be available by URL and Date, but there should be a difference between two rows if they have the same url and date, so that's the reason for the extra field number.

But thanks, I'll use one of these methods.
rohan2kool
If your server doesn't support subqueries, you can do the computing through php or something like that and then construct a query as you need which can then be executed.
Related topics
Chat script
Help with mysql query
An Example to mySQL class
Need Help integrating php variable into mySQL SELECT query
What is wrong with this MySQL query?
Caching Dynamic Images -- no luck
Formatting mySQL date
How to update multiple rows in mysql with one query?
WordPress and special characters
connecting to mysql
javascript not working in IE
Help me writing this query. Urgent
[NOT Solved] Complicated mentions query
Semicolon in SQL
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.