Date: 05/06/06     Keywords: php, sql

Ok, I'm a newbie at this so I'm not sure what to do. I've got a MOD for a phpBB forum I run, and it says the following:

#-----[ SQL ]------------------------------------------
#
CREATE TABLE phpbb_invitations (
invitation_id MEDIUMINT( 8 ) NOT NULL AUTO_INCREMENT ,
invitation_code VARCHAR( 8 ) NOT NULL ,
invitation_description TEXT NOT NULL,
invitation_uses MEDIUMINT(8) NOT NULL DEFAULT '1',
invitation_group MEDIUMINT(8) NOT NULL DEFAULT '0',
invitation_email TEXT NOT NULL,
PRIMARY KEY ( invitation_id )
);

CREATE TABLE phpbb_invitation_users (
invitation_id MEDIUMINT( 8 ) NOT NULL,
user_id MEDIUMINT( 8 ) NOT NULL,
PRIMARY KEY ( user_id )
);

INSERT INTO phpbb_config ( config_name , config_value )
VALUES ('invite_only', '1');

I'm not sure how to do this, but I'm thinking that I have to go into the phpMyAdmin on my server and create the "phpbb_invitations" table first, then past in this code.... right? Wrong?

Much thanks in advance for you help.
Kristi

Source: http://community.livejournal.com/mysql/94911.html

Date: 05/06/06     Keywords: php, mysql, database, sql

I'm currently developing a small community site (WOW Guild site), and am working on optimizing the database calls and such.

Currently, when a user loads a page the system checks the user's authentication, and then to see who else is online, checks a field in the db to see how many people have a comparable time...:

1. user loads page > SELECT * FROM `Members` WHERE `name`=$name AND `pass`=$pass




2. update user > UPDATE `Members` SET `lastclick`=$now WHERE `id`=$id




3. find other users online > SELECT `id` FROM `Members` WHERE `lastclick` >= $now

mysql

php_mysql

Source: http://community.livejournal.com/mysql/94558.html

Date: 05/03/06     Keywords: php, mysql, sql

So I have been crafty and created a site that uses only a couple pages(PHP+MySQL)... but now I fear I wont be able to track how my users use my site. The navigation is such that if they pick a category from a list, the query returns limited results (for that category only).

How do I track which category users are selecting? If it helps the categories are listed in their own table.

Source: http://community.livejournal.com/mysql/94367.html

Date: 04/30/06     Keywords: php, sql

I've got a MOD for remembering IP addresses when someone registers on the forum and I can figure everything else out except for how to SQL something. I'm totally lost. Here's the part in the MOD that I don't get:

#-----[ SQL ]-------------------------------------------
#
ALTER TABLE phpbb_users ADD user_regip CHAR(8) NOT NULL DEFAULT '0';
#

That's the very first thing I have to do and I can't figure out where/how to do that.

Anyone? Help? Please?

Kristi

Source: http://community.livejournal.com/mysql/94109.html

Date: 04/26/06     Keywords: no keywords

[UPDATE - no error]

SELECT DATE_FORMAT(invoice_main.invoice_date, "%Y-%m") AS inv_date,
invoice_main.invoice_number,
SUM((invoice_main.mileage*invoice_main.mileage_rate)+ invoice_main.groceries_value - invoice_main.discount_value) AS reimbursements,
invoice_items.service_type,
invoice_items.rate,
invoice_items.start_time,
invoice_items.end_time,
SUM(invoice_payments.pmt_amount) AS payments
FROM invoice_main
LEFT JOIN invoice_items ON invoice_main.invoice_number = invoice_items.invoice_number
LEFT JOIN invoice_payments ON invoice_main.invoice_number = invoice_payments.invoice_number
WHERE DATE_FORMAT(invoice_main.invoice_date, "%Y-%m") = DATE_FORMAT(CURRENT_DATE(), '%Y-%m')
GROUP BY invoice_main.invoice_number



SOLVED "FROM invoice_main" missing...

Just dont know if this is the right logic now :) - I'll let you know

Source: http://community.livejournal.com/mysql/93828.html

Date: 04/21/06     Keywords: php, mysql, database, sql

* EDIT
I figured it out.
The host name was wrong. I was using localhost when it should have been the host name of the server.
thanks for the help.



I just started doing some admin on a MySql database. I was hoping some of you may be able to answer a few questions for me.

1) My user table has the following rows:

mysql> SELECT host, user from user;
+--------------------------+------+
| host                     | user |
+--------------------------+------+
| localhost                |      |
| localhost                | root |
| vipindo1.securesites.net |      |
| vipindo1.securesites.net | root |
+--------------------------+------+
4 rows in set (0.00 sec)

Source: http://community.livejournal.com/mysql/93596.html

Date: 04/21/06     Keywords: web

Is there a good (web) resource that explains the theory behind good DB structure?

I want to know why Tables are layed out in certain ways--and best practices when desinging DB structure.

(it's a newbie question--thanks for the patience!)

Source: http://community.livejournal.com/mysql/93089.html

Date: 04/11/06     Keywords: no keywords

hey folks, i have this query

SELECT entries.*, article.*, theme.entrybit
FROM ej_entry entries
LEFT JOIN ej_article article ON (article.articleid = entries.articleid)
LEFT JOIN ej_theme theme ON (theme.themeid = article.themeid)
WHERE article.userid = 10
ORDER BY entries.entry_date DESC

however i want to truncate the data returned, i have a column in the entries table in which i have strings stored such as "test,life,romance,work" as well as keeping it simple and having "test", i'm looking for a function in which i can use to just return entries with 'test' in the string, can anyone tell me what function i'm looking for?

Source: http://community.livejournal.com/mysql/92823.html

Date: 04/07/06     Keywords: mysql, database, sql

Jay Pipes (co-author of Pro MySQL) is in town and will speak about "MySQL Performance Tuning Best Practices". This is the workshop he'll be giving at the MySQL Users Conference, so if you can't go don't miss this meetup! There will be FREE pizza and soda.

We will have giveaways of Pro Mysql and gift certificates for free Apress books, and other swag like T-shirts, buttons, etc. RSVP for a headcount of soda and pizza at http://mysql.meetup.com/137/events/4875276/ (you will have to register; sorry.... :( )

Description of the workshop: Learn where to best focus your attention when tuning the performance of your applications and database servers, and how to effectively find the "low hanging fruit" on the tree of bottlenecks. It's not rocket science, but with a bit of acquired skill and experience, and of course good habits, you too can do this magic! Jay Pipes is MySQL's Community Relations Manager for North America.

We will be meeting on MIT campus, close to the Kendall stop on the Red Line (subway). There is also plenty of free parking -- you can park in ANY MIT lot after 3 pm, even if it says "parking by permit only". We are in building E51, room 372.

If you join the meetup, you'll get these messages automatically and great things like 30% discounts on O'reilly books, discounts to conferences, etc. It's OK to repost/forward this message.

Here is the URL for the MIT Map with the location of this building:
http://whereis.mit.edu/map-jpg ?selection=E51&Buildings=g o

This map shows the MBTA Kendall Stop:
http://whereis.mit.edu/map-jpg ?selection=L5&Landmarks=go
(the stop is in red on that map, and you can see E51 in the bottom right)

Here are the URL's for the parking lots:
http://whereis.mit.edu/map-jpg ?selection=P4&Parking=go
http://whereis.mit.edu/map-jpg ?selection=P5&Parking=go

Pizza and soda will be served, so please RSVP accurately.

When:
Monday, April 10, 2006, 7:00 PM 2006-04-10 07:00:00
Where:
MIT Building E51, Room 372
Wadsworth and Amherst Streets
Cambridge , MA 02117

Source: http://community.livejournal.com/mysql/92621.html

Date: 04/05/06     Keywords: mysql, database, sql, postgresql

Hello all.
Sorry if this question is offtop question.
I have dump of PostgreSQL database and now I want to use it with MySQL.
Can You help me with converting it?
Does anyone now how to do it? Or I should do it by hand?
Thanks.

Source: http://community.livejournal.com/mysql/92257.html

Date: 04/03/06     Keywords: php, mysql, html, sql

One of my projects has recently started listing results multiple times. The persons in charge of the servers say they have not updated the MySQL server or made any changes. In some cases I can accommodate for this by adding DISTINCT to the query. But on other pages, this is not helping.

From an email I just sent to my super':
"There is something up with our SQL server. I just looked at the View Profile page which I know I haven't touched in months and its also showing the profile page twice. In the PHP and HTML, the content is only there once and is called only for the results of the query. For some reason the query is suddenly calling two results of one row."

I am clueless about it. For over a year these things were working fine and only sending one result. Suddenly its sending two or more results for the same row.

If you guys have any idea why the server behavior and query results would change, or an hints at MySQL idiosyncracies, let me know. Thanks.

MySQL 4.1.11-Debian_4sarge2-log. PHP call used in the View Profile: $Result = mysql_db_query($host, $user, $password);

Source: http://community.livejournal.com/mysql/91645.html

Date: 03/31/06     Keywords: no keywords

I have a table that I know has some entries that shouldn't be there. There should only be one entry per story number. I want to run a query to find out which ones are in there twice. The structure is:

TableName: StorySeries
Columns: StoryID (this should be unique), SeriesID, StoryNumber

I do know how to fix it so there won't be future entries with the same StoryID, but I dont know how to find the ones that are currently in there.

Thanks in advance for your help!

Source: http://community.livejournal.com/mysql/91322.html

Date: 03/24/06     Keywords: no keywords

I thought this would be straight forward but I am having no luck with my query

SELECT SUM(Amount) AS total 
FROM office
WHERE datepaid BETWEEN '$range1' AND '$range2'

Source: http://community.livejournal.com/mysql/90996.html

Date: 03/23/06     Keywords: php, database, sql

wondering if anyone could help me, i'm trying to help a friend quickly on a project, and i'm terrible at recursive functions

i have a table of 'genres' for music club 'venues' - each genre can have a 'parentID' if it is a sub-genre of another genre

really roughly he has something like this -

genre
-----
[genreID]
name
parentID

venue
-----
[venueID]
name

venure_genre
------------
[venueID
genreID]

I'm trying to help him build a form to submit venues into a database and assign them 1 or more genres -

i'd like to have the genre selection be a multiple select box, with subgenres spaced over with  's i.e.


Genre
   Sub-Genreption>
      Sub-Sub-Genre
Genre


I don't really need any help with the output as much as the query - I could do this the long way, and query all the top level genres, store them, then query each top level's sub genre's, etc etc, but i was wondering if anyone with experience writing recursive SQL querys/PHP functions could lend a hand

thanks

cross-posted to php

Source: http://community.livejournal.com/mysql/90644.html

Date: 03/21/06     Keywords: no keywords

Hi, can someone point me in the right direction?

If I do an "order by date" or "order by ip", my listings are indeed in an order, however if it is a date they would look like this:

1.23.05
1.24.06
10.23.02
11.24.23
12.33.02
2.23.02.
3......
And so on.

So it is just looking at the first numeral, rather than taking 10,11,&12 to be greater than 2 for example. What is this called? I don't know how to search for this. It also happens with IP addresses, or I guess anything where your numbers have a particular order to them.

Source: http://community.livejournal.com/mysql/90556.html

Date: 03/21/06     Keywords: sql

I have a query which selects a columns from a table however, there are repeats. I understand to get just one request of the value I'd use SELECT distinct.

However the SELECT statement also displays information from a second column and I want only the highest value.

What I want effectively is SELECT DISTINCT columnA and columnB WHERE column B is the highest value in the record for colum A.

Thanks in advance. It's been a while since I've done these regularly and my brain has turned to SQL-mush. It'll come back tho', I promise.

Source: http://community.livejournal.com/mysql/90364.html

Date: 03/20/06     Keywords: software

Source: http://community.livejournal.com/mysql/90045.html

Date: 03/17/06     Keywords: no keywords

Why would this query below bring up more than 150 records?

SELECT Story.*, Author.Author, Author.Email, Rating.Rating FROM Rating, CharacterStory, Story LEFT JOIN AuthorStory ON AuthorStory.StoryID = Story.ID LEFT JOIN Author ON AuthorStory.AuthorID = Author.ID INNER JOIN Characters ON CharacterStory.StoryID = Story.ID WHERE CharacterStory.CharacterID = '1' and Author.Author BETWEEN 'C' and 'D' and Story.RatingID = Rating.ID and Story.Approved = 'Y' GROUP BY Story.ID ORDER BY Author.Author, Story.Title LIMIT 150, 300

Source: http://community.livejournal.com/mysql/89536.html

Date: 03/12/06     Keywords: database

I'm working on a database to organize fan fiction stories. My current problem is figuring out how to generate a list of all the pairings that exist in the database for a given fandom. The relevant tables and columns are these:

table story: storyid
table fandom: storyid | showid
table pairing: storyid | pairid
table pairchar: pairid | charid
table person: charid | charname

Each story has one or more pairings. Each pairing has one or more (1-4 in practice) associated characters. Characters can belong to more than one pairing within a story, and multiple stories may have pairings that involve the same set of characters.

I need results like this:

CharA | CharB | CharC | CharD
Sara | NULL | NULL | NULL
Grissom | Sara | NULL | NULL
Sara | Catherine | NULL | NULL
Grissom | Sara | Catherine | NULL
Grissom | Catherine | Nick | Warrick

I know that getting the names in a consistent order is just a matter of comparing character ids and SELECT DISTINCT will condense the list. What I can't figure out is the correct syntax for what I believe should be a series of left joins to generate the list in the first place. Suggestions?

Source: http://community.livejournal.com/mysql/89003.html

Date: 03/09/06     Keywords: php, mysql, sql

Hi guys,

I'm wondering if it's possible to validate the output of an sql query?

I have the following query/code (using PHP5, and MSSQL):

		$sql = "SELECT TOP 100 PERCENT Mem_Number, Title_1, Name_1, Surname_1, Email, Date_Sent_Renewal, EmailList, Renewal_Date FROM BTCMembers WHERE (DATEDIFF(d, Renewal_Date, GETDATE()) > ".$cutoff.") AND (DATEDIFF(d, Renewal_Date, Date_Sent_Renewal) < 0) AND (Email <> '') AND (Date_Sent_Renewal <> '') AND (EmailList = 1) ORDER BY Mem_Number ASC";



		$result = odbc_exec($contact, $sql); // Run the query

php

sqlserver

mysql

Source: http://community.livejournal.com/mysql/88763.html