FRIHOST • FORUMS • FAQ • TOS • BLOGS • DIRECTORY
You are invited to Log in or Register a Frihost Account!

[MySQL] Complicated query

   Frihost Forum Index -> Scripting -> Php and MySQL
 


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.
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

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