yeah, so in summary your problem could be solved by not writing shitty code in the first place.

9/21/2007 11:49:28 PM

OH SNAP YOU TOLD ME

9/22/2007 3:05:22 AM

anybody know of or use a good PHP DAL for mysql?

[Edited on October 24, 2007 at 11:43 AM. Reason : comparable to subsonic for .net]

10/24/2007 11:42:42 AM

plz?

10/24/2007 5:15:05 PM

you mean like phpMyAdmin?

http://www.phpmyadmin.net/home_page/index.php

[Edited on October 24, 2007 at 5:27 PM. Reason : i don't know what a DAL is ]

10/24/2007 5:27:20 PM

http://en.wikipedia.org/wiki/Data_access_layer

phpmyadmin should be good...

10/24/2007 9:31:42 PM

not quite. a data-access layer is a toolset that provides object-relational mapping, or a way to turn your tables/rows into objects. an example of what a DAL does is say you have a table called employees with columns id, name, title, payrate - a DAL might generate a class called Employees with public fields Id, Name, Title and Payrate and methods to work with those. PHP has a built-in interface called PDO but it's meh

10/25/2007 11:40:55 AM

Is it possible to have a field with multiple TIMESTAMPS (delimited by something) and then I do a query that returns a row where the current time is greater than any one of those times in that field.

ie

currently it's '2007-10-26 11:43:01'

and the field i want to compare could include (delimited by ||)
2007-10-26 11:40||2007-10-26 11:50||2007-10-26 12:00

I don't have to know which time caused the return to be true, but just that in this above case I would get the row because the current time is past the first time element in the field.

10/26/2007 11:44:48 AM

If you HAD to do this, I think it is possible with a stored procedure (requires MySQL 5).

This works for the first time entry, but obviously that isn't very helpful.

SELECT NOW() > (
	SUBSTR(fieldname, 1, LOCATE('||', fieldname) - 1) FROM tablename,
);

10/26/2007 8:24:10 PM

He is right, that would be a very poor database design. You need a separate table that stores the primary key from your main table and a single TIMESTAMP in each row (if you have 6 delimited TIMESTAMPS now you want 6 rows in the new table). Once you have done that the query will be a snap (not to mention much quicker). String parsing is very slow, you lose the benefit of using a TIMESTAMP in the first place by storing it in a delimited text field.

10/27/2007 12:33:17 AM

11/14/2007 3:11:23 PM

11/14/2007 4:06:10 PM

Is there a single query you can do to get the count like below:

So, table users... with usertype having an integer value.

I want to see the most popular userTypes(integer values) sorted from highest to lowest

So:

244 (55 users)
294 (23 users)
... get the point.

Anyone? THis is more for administrative viewing, and not something that I want to keep track of with increment/decrements

Thanks

[Edited on December 4, 2007 at 12:19 PM. Reason : .]

12/4/2007 12:19:15 PM

SELECT userType, COUNT(userType)
FROM users
GROUP BY userType
ORDER BY COUNT(userType) DESC

12/4/2007 12:26:19 PM

sweet thanks

12/4/2007 12:35:10 PM

this is more of a php question than an sql question but i couldn't find a good php thread

full disclosure: i suck equally with both

i'm pulling data from a table using php/mysql

the table is this:

DAY             COLOR
monday		blue
monday		red
tuesday		orange
wednesday	blue
thursday	orange
thursday	red
friday		blue

$query = SELECT day, color, yes
	 FROM table;

$result = mysql_query($query);

while($row = mysql_fetch_row($result)) {
  $day   = $row[0];
  $color = $row[1];
  $yes   = $row[2];

  echo "<tr><td>day</td>
            <td>color</td></tr>";

}

monday		blue
		red
tuesday		orange
wednesday	blue
thursday	orange
		red
friday		blue

12/12/2007 7:47:33 PM

$previousDay = "";
echo "<table>";
while($row = mysql_fetch_row($result)) {
  $day   = $row[0];
  $color = $row[1];
  $yes   = $row[2];

  echo "<tr>";
  echo "<td>" . (!strcmp($day, $previousDay) ? "" : $day) . "</td>";
  echo "<td>$color</td>";
  echo "</tr>";

  $previousDay = $day;
}
echo "</table>";

12/12/2007 9:44:07 PM

strcmp!

yayayayay

12/12/2007 9:51:21 PM

¡strcmp!

jajajajaja

12/12/2007 10:22:44 PM

Ernie: here is a handy group by function.

SELECT day, GROUP_CONCAT(color SEPARATOR ', ') 
FROM table 
GROUP BY day;

12/13/2007 1:51:52 AM

using ms esql server 2005, is there any way to "simulate" the execution of commands? for example it could be cool to see the results of an update command, to see how many rows it returns before i actually run it.

12/18/2007 11:30:08 AM

here y'all go, help this kid out: message_topic.aspx?topic=506667

12/18/2007 11:38:59 AM

^^ can you not just write a SELECT statement that has the same conditions as your update statement?

12/18/2007 4:27:28 PM

Table 1
---------------------------------------------------
job | computer_name | status | last_report* | weight
---------------------------------------------------
a computer1 running time1 5
b computer2 done time2 2
c computer1 done time3 1
d computer3 done time4 7
e computer1 done time5 10

* assume time1 < time2 < etc.

Table 2
--------------------------------------------
computer_name | computer_status
--------------------------------------------
computer1 online
computer2 online
computer3 offline

Want Results
----------------------------------------------------------------
computer_name | computer_status | SUM(weight)* | MAX(last_report)**
----------------------------------------------------------------
computer1 online 5 time3
computer2 online 0 time2
computer3 offline 0 time4

* only weight of running jobs
** latest time of any job on that computer

How do I get this wanted table?

[Edited on January 15, 2008 at 10:32 AM. Reason : format]

1/15/2008 10:31:20 AM

^ try this, but i haven't tested it out or anything.

SELECT t2.computer_name, t2.computer_status, SUM(t1.weight), MAX(t1.last_report) 
FROM table2 t2 
LEFT JOIN table1 t1 ON t1.computer_name = t2.computer_name 
GROUP BY t1.computer_name 
WHERE t1.status = 'running' 

1/15/2008 12:25:45 PM

^ I think this might cause a problem. If a computer isn't running anything I dont' think it will show up on the results.

Quote :

"WHERE t1.status = 'running' "

Do you need the results to return all computers GetYoNacho?

[Edited on January 15, 2008 at 12:33 PM. Reason : .]

1/15/2008 12:31:47 PM

^ that is the problem, and I do want all the computers in the results

basically, if there are jobs running, i want to sum of the weight for that job. if there are no jobs running, then the weight would be 0

[Edited on January 15, 2008 at 12:35 PM. Reason : .]

1/15/2008 12:32:33 PM

I think you could probably use a subselect to pull it off. I have to run to class now, but if there is still no solution when I get out I'll whip something up.

1/15/2008 12:42:50 PM

sorry, i thought you mean only get computers with jobs. in that case, do this:

SELECT t2.computer_name, t2.status, 
SUM(IF(t1.status = 'running',t1.weight,0)) AS 'weight', 
MAX(t1.last_report) AS 'last_report'
FROM table1 t1 
LEFT JOIN table2 t2 ON t1.computer_name = t2.computer_name 
GROUP BY t1.computer_name 

1/15/2008 1:10:49 PM

^ That works great. Thanks.

I thought I had tried that earlier, but I was doing IF(SUM(...)). DOH

[Edited on January 15, 2008 at 1:20 PM. Reason : .]

1/15/2008 1:18:41 PM

this may be impossible or the wrong way of doing it, but here's what i want to get...

i have a table with two columns: id and price. i want to get a list of the number of items that fall into a price range, say in groups of $100. it won't always be $100 and i wont' know the maximum range.

so if the table looks like this:

id    |   price
---------------
1     |    57 
2     |    34 
3     |    79 
4     |    125
5     |    113
6     |    85

i would want a result of something like:

range       |    number_of_items
------------------------------
0 - 50      |    1
50 - 100   |    3
100 - 150  |   2

4/9/2008 3:18:10 PM

GROUP BY FLOOR(price / 50)

then in your select you just multiply that out for the range -- it'll give you 0-49, 50-99 though

4/9/2008 3:22:46 PM

thanks! that works great and much easier than what i was trying to do.

4/9/2008 3:49:02 PM

^^ hey thats pretty cool. i would have overcomplicated it with case statements

4/9/2008 3:53:01 PM

How do you copy/rename an InnoDB database with relational restraints (with MySQL)? If I use phpmyadmin, it just tries to (in alphabetical order) copy table structure, copy table data, move to next table. Usually this fails very early with a foreign key constraint error. I had to manually copy each table over in order so that the restraints would hold up, but surely there is better way. Does anyone know it?

5/1/2008 12:04:22 PM

^^agreed

5/1/2008 3:13:18 PM

Smacks forehead - easy answer to the question I had - getting old I suppose.

[Edited on May 22, 2008 at 9:11 PM. Reason : -]

5/22/2008 9:04:13 PM

ok, say this is my table structure

fruit            fruit_id            name
                 --------            ----
                 1                   apple
                 2                   orange
                 3                   banana

store            store_id            name
                 --------            ----
                 1                   Food Lion
                 2                   Harris Teeter
                 3                   Lowe's
                 4                   Piggly Wiggly

store_purchase   purchase_id         fruit_id     store_id    timestamp
                 -----------         --------     --------    ---------
                 1                   1            1           1-May
                 2                   1            2           9-May
                 3                   1            3           6-May
                 4                   2            1           4-May
                 5                   2            2           2-May
                 6                   2            4           12-May
                 7                   3            2           3-May
                 8                   3            3           15-May
                 9                   3            1           7-May
                 10                  3            4           5-May

                store name           fruit name   purchase timestamp
                ----                 -----        ---------
                Food Lion            banana       7-May
                Harris Teeter        apple        9-May
                Lowe's               banana       15-May
                Piggle Wiggly        orange       12-May

5/23/2008 9:58:28 AM

Is the timestamp an actual datetime field, or is it text like that?

5/23/2008 10:12:33 AM

datetime

[Edited on May 23, 2008 at 10:15 AM. Reason : just typed it like that for readability]

btw, here's a sql server backup if anybody wants to play with it
http://ken.kaisia.com/tww/stores.bak

[Edited on May 23, 2008 at 10:24 AM. Reason : i didn't bother doing foreign keys or anything since it's a small sample db]

5/23/2008 10:14:30 AM

i can do

select s.name, max(p.timestamp) from fruit f, store s, purchases p where p.fruit_id=f.fruit_id and p.store_id=s.store_id group by s.name order by max(p.timestamp) desc

and it works but i can't get f.name b/c i get "Column 'fruit.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"

i have the feeling i have to do a left join or something but i'm just not seeing it

5/23/2008 10:33:16 AM

^ doesn't work as well as i thought. i need to do group by s.store_id, not s.name since name is not necessarily unique. so then i'll get the group by error on store name and fruit name

5/23/2008 10:52:19 AM

select top (select distinct count(*) from store) s.name, f.name, timestamp from fruit f, store s, purchases p where f.fruit_id = p.fruit_id and s.store_id = p.store_id group by s.name , f.name, timestamp order by timestamp desc

5/23/2008 10:52:42 AM

that works for the example, but the group by should be on s.store_id, f.fruit_id and that breaks it

5/23/2008 10:58:14 AM

SELECT s.name, f.name, p.timestamp
FROM store s
LEFT JOIN store_purchase p ON p.store_id = s.store_id
LEFT JOIN fruit f ON f.fruit_id = p.fruit_id
GROUP BY p.store_id
ORDER BY p.timestamp DESC

oops, i used the wrong table alias on line 4.

[Edited on May 23, 2008 at 11:34 AM. Reason : ALI]

5/23/2008 11:24:37 AM

^

5/23/2008 11:27:13 AM

hmm, since the order by is getting applied after the group, it gives the wrong results. you can do this, but i'm not sure if it's the most efficient query...

SELECT t.store_name, t.fruit_name, t.ts 
FROM (
  SELECT s.name AS 'store_name', s.store_id, f.name AS 'fruit_name', p.ts
  FROM store s
  LEFT JOIN store_purchase p ON p.store_id = s.store_id
  LEFT JOIN fruit f ON f.fruit_id = p.fruit_id
  ORDER BY p.ts DESC
)t
GROUP BY t.store_id

5/23/2008 11:47:31 AM

here's the solution my coworker came up with, it works for the sample db, i'm about to see if it works for the real db

select store.name, fruit.name, table1.date_purchased from
      (select purchase_id, fruit_id, store_id, date_purchased from dbo.store_purchase p
      where purchase_id = (select top(1)purchase_id from dbo.store_purchase where store_id = p.store_id
                                          order by store_purchase.date_purchased desc))
            as table1,
      dbo.fruit,
      dbo.store
where table1.fruit_id = fruit.fruit_id
and table1.store_id = store.store_id
order by table1.date_purchased

5/23/2008 11:54:42 AM

stupid sorted example

5/23/2008 12:00:49 PM

:p

5/23/2008 12:06:09 PM